当与联接和where子句一起使用时，Linq Include是否已断开

好吧，在又一个晚上的挠头之后，我破解了它。

在LinqToSQL:中

        using (var context = new TestSQLModelDataContext())
        {
            context.DeferredLoadingEnabled = false;
            DataLoadOptions ds = new DataLoadOptions();                
            ds.LoadWith<ProductionVersion>(prod => prod.Release);
            context.LoadOptions = ds;
            var combined = from release in context.Releases
                             where release.Name == "Hello World"
                             select from prodVer in release.ProductionVersions
                                    where prodVer.Status == 1
                                    select prodVer;
            var allProductionsForChosenRelease = combined.ToList();
        }

这产生了更合理的SQL:

SELECT [t2].[Id], [t2].[Status], [t2].[ReleaseID], [t0].[Id] AS [Id2], [t0].[Name], (
    SELECT COUNT(*)
    FROM [dbo].[ProductionVersions] AS [t3]
    WHERE ([t3].[Status] = 1) AND ([t3].[ReleaseID] = [t0].[Id])
    ) AS [value]
FROM [dbo].[Releases] AS [t0]
OUTER APPLY (
    SELECT [t1].[Id], [t1].[Status], [t1].[ReleaseID]
    FROM [dbo].[ProductionVersions] AS [t1]
    WHERE ([t1].[Status] =1) AND ([t1].[ReleaseID] = [t0].[Id])
    ) AS [t2]
WHERE [t0].[Name] = 'Hello World'
ORDER BY [t0].[Id], [t2].[Id]

哪个产生正确的结果：

Id  Status  ReleaseID   Id2 Name        value
2   1       1           1   Hello World 1

在LinqToEntities中（我无法使用Include语法，所以我使用了一个怪癖，在结果中包含所需的表可以正确地将其链接起来）：

        using (var context = new TestEntities1())
        {
            var combined = (from release in context.Releases
                            where release.Name == "Hello World"
                            select from prodVer in release.ProductionVersions
                                   where prodVer.Status == 1
                                   select new { prodVer, Release =prodVer.Release });
            var allProductionsForChosenRelease = combined.ToList();
        }

这就产生了SQL:

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[C1] AS [C1], 
    [Project1].[Id1] AS [Id1], 
    [Project1].[Status] AS [Status], 
    [Project1].[ReleaseID] AS [ReleaseID], 
    [Project1].[Id2] AS [Id2], 
    [Project1].[Name] AS [Name]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Join1].[Id1] AS [Id1], 
        [Join1].[Status] AS [Status], 
        [Join1].[ReleaseID] AS [ReleaseID], 
        [Join1].[Id2] AS [Id2], 
        [Join1].[Name] AS [Name], 
        CASE WHEN ([Join1].[Id1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Releases] AS [Extent1]
        LEFT OUTER JOIN  (SELECT [Extent2].[Id] AS [Id1], [Extent2].[Status] AS [Status], [Extent2].[ReleaseID] AS [ReleaseID], [Extent3].[Id] AS [Id2], [Extent3].[Name] AS [Name]
            FROM  [dbo].[ProductionVersions] AS [Extent2]
            INNER JOIN [dbo].[Releases] AS [Extent3] ON [Extent2].[ReleaseID] = [Extent3].[Id] ) AS [Join1] ON ([Extent1].[Id] = [Join1].[ReleaseID]) AND (1 = [Join1].[Status])
        WHERE 'Hello World' = [Extent1].[Name]
    )  AS [Project1]
    ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC

这是相当理智的，但确实有效。

Id  C1  Id1 Status  ReleaseID   Id2 Name
1   1   2   1       1           1   Hello World

所有这些让我得出结论，林克还远远没有结束。它可以使用，但要格外小心。使用它作为一种强类型和编译时检查，但费力/容易出错的编写糟糕SQL的方法。这是一种权衡。您可以在C#端获得更多的安全性，但伙计，这比编写SQL要困难得多！

再看一眼，我现在明白了Include难以捉摸的效果。

就像在普通SQL中一样，当连接的右侧是1-n关联的"n"端时，LINQ中的连接将重复结果。

假设您有一个Release和两个ProductionVersion s。如果没有Include，联接将给您两个相同的Release s，因为在所有语句select s发布之后。现在，当您添加Include时，EF不仅会返回两个版本，还会完全填充它们的ProductionVersions集合。

再深入一点看，在上下文的缓存中，EF似乎只实现了1个Release和2个ProductionVersion s。只是在最终结果集中返回了两次释放。

在某种程度上，你得到了你想要的：给我版本，乘以它们的版本数量。但这不是你想要问的。

你（可能）的意图揭示了EF工具箱中的一个弱点：我们不能Include部分集合。我认为您试图让版本只填充Status=1的ProductionVersions。如果可能的话，你宁愿这样做：

context.Releases.Include(r => r.ProductionVersions.Where(v => v.Status == 1))
       .Where(r => r.Name == "Hello World")

但这引发了一个例外：

Include路径表达式必须引用在类型上定义的导航属性。对引用导航属性使用虚线路径，对集合导航属性使用Select操作符。参数名称：路径

这个"过滤包含"问题在EF团队（或贡献者）决定抓住这个问题之前就已经被注意到了，我们必须进行详细的解决方案。我在这里描述了一个常见的。