Not sure if this should be in Linq or Database so forgive me. How would I do a left join in linq to sql in c#
-
Here's My issue: I have 4 Tables
Author BookAuthor
ID BookID
AuthorFirst AuthorID
AuthorLastBook BookNumber
ID ID
Title BookID
Price SurrogateNumber
Retired
Keywordsauthor.id linked to bookauthor.authorid
book.id linked to bookauthor.bookid
book.id linked to booknumber.bookidI don't understand how to get all my records that have instances in BookNumber. Basically what is happening is I have 94 rows so far in booknumber (which is what i want), but since booknumber contains duplicate bookid's i only get a totaly of 86 rows back when i do the following:
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; books = db.books.tolist(); bookbindingsource.datasource = books;and whenever I look in the database 86 books are in books (so that is right) because i have duplicates that were added to booknumber to make it the total of 94. So my question is how do i get all 94 books instead of just the 86? I tried this but its obviously wrong because I got like 116 results :(
var RightJoin = 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 into bookandBook
from bn in bookandBook.DefaultIfEmpty()
select b;
bookBindingSource.DataSource = RightJoin.ToList();I just want to be able to show every single book I have entered in the database where it has a unique surrogate number. In the current case only 94 books... thanks for your help. :confused:
-
Here's My issue: I have 4 Tables
Author BookAuthor
ID BookID
AuthorFirst AuthorID
AuthorLastBook BookNumber
ID ID
Title BookID
Price SurrogateNumber
Retired
Keywordsauthor.id linked to bookauthor.authorid
book.id linked to bookauthor.bookid
book.id linked to booknumber.bookidI don't understand how to get all my records that have instances in BookNumber. Basically what is happening is I have 94 rows so far in booknumber (which is what i want), but since booknumber contains duplicate bookid's i only get a totaly of 86 rows back when i do the following:
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; books = db.books.tolist(); bookbindingsource.datasource = books;and whenever I look in the database 86 books are in books (so that is right) because i have duplicates that were added to booknumber to make it the total of 94. So my question is how do i get all 94 books instead of just the 86? I tried this but its obviously wrong because I got like 116 results :(
var RightJoin = 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 into bookandBook
from bn in bookandBook.DefaultIfEmpty()
select b;
bookBindingSource.DataSource = RightJoin.ToList();I just want to be able to show every single book I have entered in the database where it has a unique surrogate number. In the current case only 94 books... thanks for your help. :confused:
I think this issue of left outer join so go through below link once may be this will help you http://www.devsource.com/c/a/Languages/Defining-a-Left-Join-with-LINQ-Queries/1/[^]