过滤"包括"表上的实体框架查询 [英] Filter the "Includes" table on Entity Framework query

问题描述

这是实体框架.NET 3.5：

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.

简单的例子：作者有书籍，并书有一个IsFiction列。我想作者的过滤列表，所有小说类书籍以及

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.

我可以单独查询，如：

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.

我可以倒着走，如：

var allBooks = from b in db.Books.Include("Author")
               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.

有必须是涵盖一切的解决方案 - 我能做到这一点的SQL很轻松：

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

有什么建议？

推荐答案

前进的道路：

var q = from a in db.Authors.Include("Books")
        where a.BirthYear > 1900
        select new {
            Author = a,
            FictionBooks = a.Books.Where(b => b.IsFiction)
        };

另一种方式，从SQL你的问题的底部来源：

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).

这篇关于过滤"包括"表上的实体框架查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！