c# - Entity Framework : best practice to filter data inside multiple "one-to-many" relationships

Question

在这种情况下:

public class Software
{
    [Key]
    public int Id { get; set; }

    // Some properties

    public virtual ICollection<Release> Releases { get; set; }
}

public class Release
{
    [Key]
    public int Id {get; set;}

    public int Version {get; set;}

    public virtual Software Software {get; set;}
    public virtual ICollection<Report> Reports{get; set;}
}

public class Report
{ 
    [Key]
    public int Id {get; set;}

    // Some properties

    public virtual Release Release {get; set;}
}

如果我必须按 Software.Id 和 Release.version 过滤报告，最好使用此查询:

from report in context.Reports
where report.Release.Version == filterVersion && 
      report.Release.Software.Id == filterId
select report

或者改用这个查询？

from software in context.Softwares
where software.Id == filterId
     from release in software.Releases
     where release.Version == filterVersion
          from report in release.Reports
          select report

“先过滤”数据还是使用 Entity Framework 导航属性更好？

Answer 1

当然，对于我们来说，不可能判断哪个查询执行得最好。这主要取决于每个表中的索引和记录数，这两者对执行计划都有很大的影响。

但我认为在您的情况下，两种备选方案的查询形状都是相同的。例如，report.Release.Version 和 release.Reports 都将生成内部联接。并且谓词将以相同的方式生成。

所以这归结为个人喜好。

我总是喜欢将我的查询粗略地构造为...

• 来自结果中我想要的实体
• where 过滤器
• 选择

...因为以后很容易添加/删除谓词。

那就是:

from report in context.Reports
where report.Release.Version == filterVersion && 
      report.Release.Software.Id == filterId
select report

在备选...

from software in context.Softwares
where software.Id == filterId
     from release in software.Releases
     where release.Version == filterVersion
          from report in release.Reports
          select report

...谓词“无处不在”(好吧，没那么复杂，但你明白我的意思)。

但是如果你想要一个包含来自所有实体的数据的投影，情况就不同了:

from report in context.Reports
where report.Release.Version == filterVersion && 
      report.Release.Software.Id == filterId
select new
{
    report.Name,
    report.Release.Version,
    report.Release.Software.Id,
    report.Release. ...
}

... 由于所有点引用，它的可读性略低于 ...

from software in context.Softwares
where software.Id == filterId
     from release in software.Releases
     where release.Version == filterVersion
          from report in release.Reports
select new
{
    report.Name,
    release.Version,
    software.Id,
    release. ...
}

但同样，SQL 查询可能仍然相同。