CODEWITHSUNDEEP
linqentity-framework
Jonathan Wood
Jonathan Wood

Reputation: 67175

Entity Framework Query to Load Hierarchical Data

If I had a SQL Server table that looks something like this with some authors appearing in multiple rows (meaning they authored multiple books).

BookID INT;
BookTitle NVARCHAR(100);
AuthorID INT;
AuthorName NVARCHAR(100);

Is there any way to write an Entity Framework expression that would populate a memory layout similar to the following?

class AuthorInfo
{
    int ID;
    string Name;
    IEnumerable<int> BookIDs;
}

In other words, I would to have one object for each author, but then have the BooksIDs collection contain all the books for that author.

I know I could first load the unique authors, and then iterate the resulting collection to add all matching books for each author. But is there any way to do something like this with a single linq statement?

Upvotes: 0

Views: 755

Answers (1)

Tim Rogers
Tim Rogers

Reputation: 21713

Does this work? (I haven't tried it.)

var authorInfos = DbContext.BooksAuthors
.GroupBy (ba => new { AuthorId = ba.AuthorId, AuthorName = ba.AuthorName })
.Select (g => new AuthorInfo { 
    ID = g.Key.AuthorId, 
    Name = g.Key.AuthorName,
    BookIDs = g.Select (ba => ba.BookId)
});

Upvotes: 1

Related Questions