c# - Entity Framework : What would cause slow queries after adding a table?

Question

我有一个连接到 SQLite 数据库的 Entity Framework 5 项目。我采用模型优先方法，我能够在大约 3 秒内从 Table_A 查询 30,000 条记录。

现在我所做的只是另一个 Table_B，它对 Table_A 的父记录有 0 到 1 个引用。对 Table_A 运行 SAME 查询需要 3 分钟多的时间。 Table_B 中有零条记录。

还值得注意的是，EDMX 向 Table_A 和 Table_B 添加了导航属性。但是它只将外键列添加到 Table_B。什么会导致 Entity Framework 变慢那么多？当我将更改恢复到旧模型时，它运行得很快。

更新

作为引用，查询是一个标准的 linq to sql 查询。

var matches = Table_A.Where(it => it.UserName == "Waldo" || it.TimeStamp < oneMonthAgo);

Answer 1

我刚刚运行了 ToTraceString() 来查找这个人在他的回答 here 中建议的生成的 SQL 查询:

事实证明， Entity Framework 试图变得“聪明”，预计我会使用子记录中的数据。这实际上很酷!只是稍微减慢了我的查询速度，所以我可能会找到更快的解决方法。

请注意，此查询在 LINQ 语法中是相同的。这只是我在 EDMX 图中添加另一个表后立即生成的底层 SQL。

这是 FAST 查询:(为清楚起见缩写)

SELECT *

FROM   [Table_A] AS [Extent1]
INNER JOIN  (SELECT 
[Extent2].[OID] AS [K1], 
[Extent2].[C_Column1] AS [K2], 
Max([Extent2].[Id]) AS [A1]
FROM [Table_A] AS [Extent2]
GROUP BY [Extent2].[OID], [Extent2].[C_Column1] ) AS [GroupBy1] ON [Extent1].[Id] = 
[GroupBy1].[A1]
INNER JOIN [OtherExistingTable] AS [Extent3] ON [Extent1].[C_Column1] = [Extent3].[Id]

在添加 Table_B 之后，这是生成的新查询，它使事情变得更慢。

SELECT *
FROM    [Table_A] AS [Extent1]
LEFT OUTER JOIN [Table_B] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Table_B_ForeignKey_To_Table_A]
INNER JOIN  (SELECT 
    [Join2].[K1] AS [K1], 
    [Join2].[K2] AS [K2], 
    Max([Join2].[A1]) AS [A1]
    FROM ( SELECT 
        [Extent3].[OID] AS [K1], 
        [Extent3].[C_Column1] AS [K2], 
        [Extent3].[Id] AS [A1]          
        FROM  [Table_A] AS [Extent3]            
        LEFT OUTER JOIN [Table_B] AS [Extent4] ON [Extent3].[Id] = [Extent4].[Table_B_ForeignKey_To_Table_A]
    )  AS [Join2]
    GROUP BY [K1], [K2] ) AS [GroupBy1] ON [Extent1].[Id] = [GroupBy1].[A1]
INNER JOIN [FeatureServices] AS [Extent5] ON [Extent1].[C_Column1] = [Extent5].[Id]