This is for Entity Framework for .NET 3.5:
I have the need to query a table and include a collection of the "many" table of a one-to-many relationship. I'm trying to filter that collection as part of the query - I'm pretty new to Entity Framework, and I'm having trouble figuring it out.
Simplified example: Author has Books, and Book has an IsFiction column. I want a filtered list of authors, along with all fiction books.
Without the filter, it's easy:
var q = from a in db.Authors.Include("Books")
where a.BirthYear > 1900
select a;
I can filter after the fact, something like:
var fictionBooks = a.Books.Where(b => b.IsFiction);
But the problem is that the original query already ran, and included those results, which is unnecessary database processing.
I can query separately, like:
var q = from a in db.Authors where a.BirthYear > 1900 select a;
foreach (var a in q)
{
var books = from b in db.Books
where ((b.Author.Id == a.Id) && (b.IsFiction))
select b;
}
But of course that's one call for every author, which I want to avoid as well.
I can go backwards, like:
var allBooks = from b in db.Books.Include("Author")
开发者_StackOverflow中文版 where b.IsFiction
select b;
But then I'm back to the original problem, except now on the author side instead of the book side.
There must be a solution that encompasses everything - I can do it in SQL pretty easily:
select * from author a
left join book b on a.id = b.author_id and b.is_fiction = 1
where a.birth_year > 1900
Any suggestions?
The forward way:
var q = from a in db.Authors.Include("Books")
where a.BirthYear > 1900
select new {
Author = a,
FictionBooks = a.Books.Where(b => b.IsFiction)
};
Another way, derived from the SQL at the bottom of your question:
var q = from a in db.Authors
from b in db.Books.Include("Author")
where a.BirthYear > 1900 && b.IsFiction && a.Id == b.Author.Id
select new { Author = a, Book = b };
The main difference between these two is that the first one will basically give you a collection of authors plus the list of fiction books for each author (which may be empty); while the second will give you a collection of author/books pairs (so it doesn’t return any authors with no fiction books).
精彩评论