CODEWITHSUNDEEP
c#linq
user3040068
user3040068

Reputation: 180

Select second row in a join LINQ statement

I have a 1 to many relationship between Books and Authors dataset. How can I select the second author of the book (or null if there is only one author) using LINQ. I don't want to do a 'left join' because I only want to return books with authors. Some books do not have authors.

var books = from b in db.Books
             join a in db.Authors on b.Id equals a.BookId
            select new {
               Book = b,
               SecondAuthor = a ??? 
            }

Upvotes: 0

Views: 745

Answers (1)

Servy
Servy

Reputation: 203829

So the first thing is that you should be using a group join, rather than a regular join, so that you get all of the authors as a collection, rather than flattening that into separate lines for each matching pair. From that collection you can get the second item:

var books = from b in db.Books
            join a in db.Authors on b.Id equals a.BookId
            into authors
            select new {
               Book = b,
               SecondAuthor = authors.Skip(1).FirstOrDefault(),
            };

Upvotes: 4

Related Questions