confused on how to use linq to join 3 tables
-
I have an sql db with 3 tables like this Authors ----------- ID AuthorFirst AuthorLast BookAuthor (Serving as a Bridge) --------------------------------- AuthorID BookID Books ----------------------------------- ID Title Keywords Price Retired How would I select All the Authors and all the books associated with them using Linq? I though something like this but I'm very new to sql and Linq (a sight with good tutorials on linq and joins and all that good stuff would be great if you know one ) <pre> var lines = from authors in db.Authors join b in db.Books on authors.ID equals b.ID where authors.AuthorID == AuthorID select authors; </pre> thanks
-
I have an sql db with 3 tables like this Authors ----------- ID AuthorFirst AuthorLast BookAuthor (Serving as a Bridge) --------------------------------- AuthorID BookID Books ----------------------------------- ID Title Keywords Price Retired How would I select All the Authors and all the books associated with them using Linq? I though something like this but I'm very new to sql and Linq (a sight with good tutorials on linq and joins and all that good stuff would be great if you know one ) <pre> var lines = from authors in db.Authors join b in db.Books on authors.ID equals b.ID where authors.AuthorID == AuthorID select authors; </pre> thanks
-
How the hell with this help? If you knew anything about Linq you would understand Linq query syntax IS NOT SQL syntax.
I know the language. I've read a book. - _Madmatt
-
I have an sql db with 3 tables like this Authors ----------- ID AuthorFirst AuthorLast BookAuthor (Serving as a Bridge) --------------------------------- AuthorID BookID Books ----------------------------------- ID Title Keywords Price Retired How would I select All the Authors and all the books associated with them using Linq? I though something like this but I'm very new to sql and Linq (a sight with good tutorials on linq and joins and all that good stuff would be great if you know one ) <pre> var lines = from authors in db.Authors join b in db.Books on authors.ID equals b.ID where authors.AuthorID == AuthorID select authors; </pre> thanks
You're thinking SQL-style. In LINQ, there's no need to use the
joinkeyword to get an author's books. Instead, the generated classes have collection properties to represent relationships between the tables. AFAIK LINQ-to-SQL doesn't support N:M relations directly, but as in the SQL database, you can use N:1 and 1:M relations instead. To get one result row for each author-book combination, you can use multiple from clauses:var query = from a in db.Authors
from b in a.BookAuthors
select new { Author = a, Book = b.Book };Only
a.BookAuthorsis accessed in the nested from, not the whole set of book authors in the database. LINQ-to-SQL will use JOIN to express this query in SQL. These two posts present some nicer ways to handle N:M relations: http://blogs.msdn.com/mitsu/archive/2007/06/21/how-to-implement-a-many-to-many-relationship-using-linq-to-sql.aspx[^] http://blogs.msdn.com/mitsu/archive/2008/03/19/how-to-implement-a-many-to-many-relationship-using-linq-to-sql-part-ii-add-remove-support.aspx[^] -
I have an sql db with 3 tables like this Authors ----------- ID AuthorFirst AuthorLast BookAuthor (Serving as a Bridge) --------------------------------- AuthorID BookID Books ----------------------------------- ID Title Keywords Price Retired How would I select All the Authors and all the books associated with them using Linq? I though something like this but I'm very new to sql and Linq (a sight with good tutorials on linq and joins and all that good stuff would be great if you know one ) <pre> var lines = from authors in db.Authors join b in db.Books on authors.ID equals b.ID where authors.AuthorID == AuthorID select authors; </pre> thanks
Hi dude .. take it easy!! you can accomplish your task with the following code:
Dim db As New DbDataContext()
Dim query=From a in db.Authors _
From b in db.Books _
From ba in db.BookAuthors _
Where a.ID=ba.AuthorID And b.ID=ba.BookID _
Select a,b.:: Wish to become better and better ::.