在EF包含中过滤 [英] Filter in EF Include

问题描述

我有这个LINQ查询，该查询在Include中的过滤器上给出了错误.在搜索我的朋友Google时，我发现无法在包含"中进行过滤.我已经找到了另一种方法来执行此操作，但是我无法使其针对我的特定情况起作用.

I have this LINQ query that gives an error on the filter in the Include. When searching my friend Google I've found that it's not possible to filter in an Include. I've found some ways to do this on an onther way but I can't get it to work for my specific case.

return context.Timesheets.Where(t => t.UserId == userId && t.SubjectDate == date && t.WorkingDaySchedules.Count() > 0)
    .Include(t => t.Project)
    .Select(t => t.Project)
        .Include(p => p.Account)
        .Include(pc => pc.ProjectConsultants.Where(c => c.UserId == userId));

这是最后一个让我头疼的Include :)有人知道怎么做吗?

It's the last Include that gives me a headache :) Anyone an idea how to do it?

推荐答案

我认为这里需要改进一些地方.

There are a few things I think need improving here.

这根本不是正确的选择.

It's simply not the correct place to do so.

包含旨在自动检索所有链接的实体.由于您不想要实体的全部(只想要一个子集)，因此不应使用 Include .
另外，只要您愿意删除内存中的不需要的条目(即在加载完毕后)，就仍然可以使用 Include .但是我想你不想要那个.

Include is meant to automatically retrieve all linked entities. Since you don't want all of the entities (you only want a subset), you shouldn't be using an Include.
Alternatively, you could still use an Include, so long as you're happy to remove the unwanted entries in memory (i.e. after they've been loaded). But I assume you don't want that.

相反，您可以使用显式的 Select 语句.举一个简单的例子:

Instead, you could use an explicit Select statement. As a simple example:

context.Projects
             .Where(p => p.Id == projectId)
             .Select(p => new ConsultantSearchResult() {
                   Project = p,
                   ConsultantsNamedBob = p.Consultants.Where(c => c.FirstName == "Bob")
              }).ToList();

请注意，缺少 Include .如前所述， Include 用于自动(隐式)加载相关数据.但是因为您明确地在 Select 中声明了所需的数据，所以不再需要隐式包含.EF会给您您所要的.

Notice the absence of the Include. As I said before, Include is used for automatically (and implicitly) loading the related data. But because you explicitly stated the data you want in the Select, there's no need for an implicit inclusion anymore. EF will give you exactly what you ask for.

我认为您期望得到的与您得到的有所不同.查看代码:

I think you're expecting something different than what you're getting. Looking at the code:

return context.Timesheets    //1
    .Where(...)              //2
    .Select(t => t.Project)  //3

看看会发生什么:

1. 您选择所有时间表.
2. 您过滤了时间表，只剩下了时间表的一部分
3. 您会获得每个时间表的项目列表 .

如果您的过滤(第2步)为您提供了来自同一项目的多个时间表，则 .Select(t => t.Project)将为您提供<同一项目的strong>多个实例.那就不好了.

If your filtering (step 2) leaves you with multiple timesheets from the same project, then .Select(t => t.Project) will give you multiple instances of the same project. And that's not good.

这里有两个例外:

• 您知道，您将总共找到一个时间表.但是然后您应该使用 First ， Single ， FirstOrDefault 或 SingleOrDefault .如果可能会得到多个结果，则仅应使用 Where .
• 您期望有多个时间表，但是您知道，您将永远不会从同一项目中找到两个时间表(因此，当您调用 Select 时，永远不会创建重复的时间表).我会假设(通过阅读实体名称)特定顾问有可能为同一项目设置多个时间表，但事实并非如此.
  • 如果我的推论是正确的，那么在执行 Select (选择)后，您将遇到重复项目的问题.
  • 如果我的推论是不正确的，那么我希望时间表和顾问之间有更牢固的关系，因为每个项目顾问都将拥有准确的1个(或没有)时间表，而永远不会超过1个.但是您当前的数据结构缺少任何真实的时间表和顾问之间的关系.
  • You know that you are going to find one timesheet in total. But then you should be using First, Single, FirstOrDefault or SingleOrDefault. You should only use Where if it's possible that you get more than one result.
  • You expect more than one timesheet, but you know that you'll never find two timesheets from the same project (thus never creating duplicates when you call the Select). I would assume (by reading the entity names) that it's possible for a specific consultant to have multiple timesheets for the same project, but maybe that's not true.
    • If my inference is correct, then you will experience the problem with duplicate projects after you do the Select.
    • If my inference is not correct, then I would expect a much stronger relationship between timesheets and consultant, as every project consultant would have exactly 1 (or no) timesheet, never more than 1. But your current data structure lacks any real relationship between timesheets and consultants.

    一种快速的解决方案是使用 Distinct :

    A quick solution would be to use Distinct:

    return context.Timesheets    
        .Where(...)              
        .Select(t => t.Project)  
        .Distinct()
    

    但是我个人认为更好的解决方案是反转查找:从项目开始，在时间表上过滤项目(而不是过滤时间表):

    But I personally think that a better solution would be to invert the lookup: start with projects, filter the projects on their timesheets (rather than filtering the timesheets):

    return context.Projects    
        .Include(p => p.Timesheets)  
        .Where(p => p.Timesheets.Any(t => t.UserId == userId && ...))              
        .ToList();  
    

    这避免了重复项目的问题.请注意，这还不能解决您的过滤包含"问题.

    This precludes the issue with the duplicate projects. Note that this doesn't yet solve your "filtered Include" problem.

    评论中也提到了这一点.这是一个可行的选择，但我发现这是一种肮脏的方法，会在行下创建不直观的代码.

    This was also mentioned in the comments. This is a viable option, but I find it a dirty approach that is going to create unintuitive code down the line.

    一个简单的示例:

    context.Configuration.LazyLoadingEnabled = false;
    var parent = context.Set<Entity>().First(e => e.Name = "ABC");
    // Load relations in separate query
    context.Set<Child>()
           .Where(c => c.Parent.Name == "ABC")
           .OrderBy(c => c.Name) // You can at least try it but as mentioned above it may not work in all scenarios
           .Load();
    // Now parent.Children collection should be filled 
    

    该示例使用 OrderBy 而不是 Where ，但是两者的工作方式相同.

    The example uses OrderBy instead of Where, but both will work the same way.

    即使您分别查询了子级和父级，它们的导航属性也会不断更新，因为您是在相同的上下文中运行查询的.

    Even though you queried the children and the parent separately, their navigational properties will continuously be updated because you're running your queries in the same context.

    这对您来说是一个可行的选择，但是我对此代码有点担心，因为可读绝对不会使第二个查询更改第一个查询的结果.
    对我来说，例如在属性的 get 或 set 中具有业务逻辑.它可以工作，但是会导致意外的行为，并且将使调试变得非常困难.

    It's a viable option for you, but I'm a bit apprehensive of this code, as it is in no way readable that the second query alters the result of the first query.
    To me, this feels equally dirty to e.g. having business logic in the get or set of a property. It works, but it leads to unexpected behavior and will make debugging really difficult.

    请注意，您可能很清楚幕后发生的事情，但是其他开发人员在查看代码时很容易掩盖它.

    Note that it may be clear to you what is happening behind the scenes, but it's easy for a different developer to gloss over it when looking at the code.

    我个人不喜欢这样，但是您的看法可能有所不同.

    I personally don't like this, but your opinion may differ.

    看看您的代码示例，我认为您的数据一致性有点问题.您在两个地方使用 userId 过滤器:

    Looking at your code sample, I think there's a bit of an issue with your data consistency. You are using the userId filter in two places:

    • 时间表: t =>t.UserId == userId
    • 顾问: c =>c.UserId == userId

    如果时间表与顾问联系在一起，则这两个实体之间应该存在关系.按照目前的状态，您的项目有一个时间表列表和一个顾问列表，时间表和顾问之间没有可辨别的关系.
    这就是为什么您的查询会变得很复杂的原因.您正在尝试嘲笑不存在的关系.

    If timesheets are connected to a consultant, then there should be a relationship between these two entities. As it currently stands, your project has a list of timesheets and a list of consultants, with no discernible relationship between timesheets and consultant.
    This is why your lookup is co complicated. You're trying to mock a relationship that isn't there.

    如果这种关系确实存在，那么查找所有内容会容易得多:

    If the relationship did exist, it would be much easier to look everything up:

    return context.Timesheets
                       .Include(t => t.Project)
                       .Include(t => t.Project.Account)
                       .Include(t => t.Consultant)
                       .Where(t => t.Consultant.UserId == userId && t.SubjectDate == date && t.WorkingDaySchedules.Count() > 0)
                       .ToList()
    

    然后您得到想要的东西.您不再需要执行两个单独的 userId 检查，您不再需要手动同步"伪造关系，查找过程更加简化和可读.

    And then you get what you're looking for. You no longer have to do two separate userId checks, you're no longer required to "manually synchronize" the fake relationship, the lookup process is much more streamlined and readable.

    也许你还不知道的东西.您可以重写

    Maybe something you didn't know yet. You can rewrite

    t.WorkingDaySchedules.Count() > 0
    

    为

    t.WorkingDaySchedules.Any() //is there at least one item in the collection?
    

    具有以下附加优点:您可以在需要时添加过滤器

    With the added benefit that you can add filters if you need to:

    t.WorkingDaySchedules.Any(wds => wds.IsActive)  //is there at least one item in the collection that meets the condition?
    

    这篇关于在EF包含中过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！