how would one convert this sql to linq to sql ? [Solved]

[tonyonlinux]

is there a free converter anywhere? How would I convert this to linq to sql query?

SELECT Book.*, Authors.*,BookNumber.*
FROM Book INNER JOIN
(Authors INNER JOIN BookAuthors ON Authors.ID = BookAuthors.AuthorID)
ON Book.ID = BookAuthors.BookID INNER JOIN
BookNumber ON Book.ID = BookNumber.BookID

WHERE AuthorFirst = @AuthorFirst AND AuthorLast = @AuthorLast;

var query = from b in db.Books
join ba in db.BookAuthors on b.ID equals ba.BookID
join a in db.Authors on ba.AuthorID equals a.ID
join bn in db.BookNumbers on ba.BookID equals bn.BookID
where a.AuthorFirst == splitstring[1].ToString() && a.AuthorLast == splitstring[0].ToString()
select b;

in the linq i posted everything seems to join except the booknumber table any help would be great. thanks.

modified on Thursday, February 11, 2010 12:13 AM

[N a v a n e e t h]

I'd suggest you to take a look at SQL tracer in SQL server and see the SQL statement executed. This will give an idea about how joins are formed.

Best wishes, Navaneeth

[PunkIsNotDead]

Hi tonionlinux! Shuldn't you use in 5th line

join bn in db.BookNumbers on b.ID equals bn.BookID

:confused: good luck ;)

[tonyonlinux]

Thanks I changed that but for some reason i only get the first surrogate number. even though when i look in the debugger everything is there. Hmmm.

    private void treeView1\_AfterSelect(object sender, TreeViewEventArgs e)
    {
        try
        {
            toolStripStatusLabel1.Text = "DATABASE IS CURRENTLY FILTERED PLEASE HIT REFRESH BUTTON IN TOP RIGHT HAND CORNER TO UNFILTER";
            
            using (var db = new mombooksDataContext(Properties.Settings.Default.BooksConnectionString2))
            {
                
                if (treeView1.SelectedNode.Text == "Authors")
                { return; }
                else
                {


                    String\[\] splitstring = null;
                    string treeviewtext = treeView1.SelectedNode.Text.Trim();
                    splitstring = treeviewtext.Split(',');

                    var query = from b in db.Books
                                join ba in db.BookAuthors on b.ID equals ba.BookID
                                join a in db.Authors on ba.AuthorID equals a.ID
                                join bn in db.BookNumbers on b.ID equals bn.BookID
                                where a.AuthorFirst == splitstring\[1\].ToString() && a.AuthorLast == splitstring\[0\].ToString()
                                select b;

                    //var query =
                    //             from a in db.Authors
                    //             join ba in db.BookAuthors on a.ID equals ba.AuthorID
                    //             join b in db.Books on ba.BookID equals b.ID
                    //             join bn in db.BookNumbers on b.ID equals bn.BookID
                    //             where a.AuthorFirst == splitstring\[1\].ToString() && a.AuthorLast == splitstring\[0\].ToString()
                    //             select a;


                    bookBindingSource.DataSource = query.ToList();
                    bindingNavigator1.BindingSource.DataSource = bookBindingSource.DataSource;

                }
              
                
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

        
    }

Here is some info on my database. I have 4 Tables Authors, Books, BookAuthor, Booknumbers Author --------

[tonyonlinux]

I figured it out I had to add

var options = new DataLoadOptions();
options.LoadWith<Book>(c => c.BookAuthors);
options.LoadWith<BookAuthor>(c => c.Author);
options.LoadWith<Book>(c => c.BookNumbers);

                db.LoadOptions = options;

forgot about that but it works now :)