在重新映射表时修改实体框架表达式

Question

我现在有一个问题，一个表已经删除了一个 id。

首先，我在下面进行了这个查询，其中实体(表)"RecordsProduct"有一个映射到被告表的"DefendnatId"。没事！

records = records
.Include(r => r.Employer)
.Include(r => r.Contractor)
.Include(r => r.RecordProducts)
.ThenInclude(rp => rp.Defendant)
.Where(r => EF.Functions.Like(r.Employer.DefendantCode, "%" + input.DefendantCode + "%")
|| EF.Functions.Like(r.Contractor.DefendantCode, "%" + input.DefendantCode + "%")
|| r.RecordProducts.Any(rp => EF.Functions.Like(rp.Defendant.DefendantCode, "%" + input.DefendantCode + "%") && rp.IsActive == true));

该DefendantId已从表中删除，并替换为名为ProductDefendant的转换表中的DefendantProductId，如下所示

ProductDefendant表：

• 被告产品编号
• 被告人身份
• 产品编号

所以我不能再这样做了：

rp.Defendant.DefendantCode

现在我必须这样做

rp.ProductDefendant.Defendant.DefendantCode

现在我的查询爆炸了！我能做些什么来修改它以使其更快？还是更改联接的工作方式？

records = records
.Include(r => r.Employer)
.Include(r => r.Contractor)
.Include(r => r.RecordProducts)
.ThenInclude(rp => rp.ProductDefendant.Defendant)
.Where(r => EF.Functions.Like(r.Employer.DefendantCode, "%" + input.DefendantCode + "%")
|| EF.Functions.Like(r.Contractor.DefendantCode, "%" + input.DefendantCode + "%")
|| r.RecordProducts.Any(rp => EF.Functions.Like(rp.ProductDefendant.Defendant.DefendantCode, "%" + input.DefendantCode + "%")
&& rp.IsActive == true));

下面是生成的 SQL。我认为问题出在"哪里"子句中

SELECT [t].[Id], [t].[StartDate], [t].[EndDate], [t].[WitnessName], [t].[SourceCode], [t].[JobsiteName], [t].[ShipName], [t].[EmployerCode]
FROM (
SELECT DISTINCT [r].[RecordID] AS [Id], [r].[StartDate], [r].[EndDate], [r.Witness].[FullName] AS [WitnessName], CASE
WHEN [r].[SourceID] IS NOT NULL
THEN [r.Source].[SourceCode] ELSE N'zzzzz'
END AS [SourceCode], CASE
WHEN [r].[JobsiteID] IS NOT NULL
THEN [r.Jobsite].[JobsiteName] ELSE N'zzzzz'
END AS [JobsiteName], CASE
WHEN [r].[ShipID] IS NOT NULL
THEN [r.Ship].[ShipName] ELSE N'zzzzz'
END AS [ShipName], CASE
WHEN [r].[EmployerID] IS NOT NULL
THEN [r.Employer].[DefendantCode] ELSE N'zzzzz'
END AS [EmployerCode]
FROM [Records] AS [r]
LEFT JOIN [Ships] AS [r.Ship] ON [r].[ShipID] = [r.Ship].[ShipID]
LEFT JOIN [Jobsites] AS [r.Jobsite] ON [r].[JobsiteID] = [r.Jobsite].[JobsiteID]
LEFT JOIN [Sources] AS [r.Source] ON [r].[SourceID] = [r.Source].[SourceID]
LEFT JOIN [Witnesses] AS [r.Witness] ON [r].[WitnessID] = [r.Witness].[WitnessID]
LEFT JOIN [Defendants] AS [r.Contractor] ON [r].[ContractorID] = [r.Contractor].[DefendantID]
LEFT JOIN [Defendants] AS [r.Employer] ON [r].[EmployerID] = [r.Employer].[DefendantID]
WHERE ([r].[IsActive] = 1) AND (([r.Employer].[DefendantCode] LIKE (N'%' + @__input_DefendantCode_1) + N'%' OR [r.Contractor].[DefendantCode] LIKE (N'%' + @__input_DefendantCode_3) + N'%') OR EXISTS (
SELECT 1
FROM [Records_Products] AS [rp]
INNER JOIN [Product_Defendant] AS [rp.ProductDefendant] ON [rp].[DefendantProductID] = [rp.ProductDefendant].[DefendantProductID]
INNER JOIN [Defendants] AS [rp.ProductDefendant.Defendant] ON [rp.ProductDefendant].[DefendantID] = [rp.ProductDefendant.Defendant].[DefendantID]
WHERE ([rp.ProductDefendant.Defendant].[DefendantCode] LIKE (N'%' + @__input_DefendantCode_5) + N'%' AND ([rp].[IsActive] = 1)) AND ([r].[RecordID] = [rp].[RecordID])))
) AS [t]
ORDER BY [t].[SourceCode]
OFFSET @__p_6 ROWS FETCH NEXT @__p_7 ROWS ONLY

Answer 1

很难给你一个好的建议，因为生成的SQL查询对于该模型来说看起来不错，现在SQL查询优化器(CBO)不应该像旧的RBO那样受到你编写查询的方式的影响(CBO代表基于成本的优化器，RBO - 基于规则的优化器)。他们应该能够将EXISTS或IN转换为JOIN(生成与JOIN相同的执行计划)。当前 SQL 与原始 SQL 之间的唯一区别是增加了一个连接，使用聚集 PK 索引查找时，它应该不会显著影响性能。

但既然你这么说，显然一些未知的东西导致CBO选择一个糟糕的计划。由于计划依赖于我没有的数据，我所能做的就是建议尝试两个功能等效的替代查询。

首先，您当前的(慢速)查询似乎是这样的：

var input = new { DefendantCode = "Abc", Skip = 4, Take = 2 };
var defendantCodePattern = "%" + input.DefendantCode + "%";
var query = db.Set<Record>()
.Where(r => r.IsActive)
.Where(r => EF.Functions.Like(r.Employer.DefendantCode, defendantCodePattern)
|| EF.Functions.Like(r.Contractor.DefendantCode, defendantCodePattern)
|| r.RecordProducts.Any(rp => EF.Functions.Like(rp.ProductDefendant.Defendant.DefendantCode, defendantCodePattern))
)
.Select(r => new
{
ID = r.RecordID,
StartDate = r.StartDate,
EndDate = r.EndDate,
WitnessName = r.Witness.FullName,
SourceCode = r.Source != null ? r.Source.SourceCode : "zzzzz",
JobsiteName = r.Jobsite != null ? r.Jobsite.JobsiteName : "zzzzz",
ShipName = r.Ship != null ? r.Ship.ShipName : "zzzzz",
EmployeeCode = r.Employer != null ? r.Employer.DefendantCode : "zzzzz",
})
//.Distinct()
.OrderBy(t => t.SourceCode)
.Skip(input.Skip).Take(input.Take);

有些事情要提。首先，查询使用的是投影 (Select)，因此不需要Include/ThenInclude(因为它们被忽略了)。其次，在查询外部创建并存储通用搜索模式，因此最终得到 sing 参数而不是 3。第三，此查询不需要Distinct，因此我将其删除。

现在潜在的尝试是提高生成的SQL查询执行速度。

(1)如果Defendant相关表不大，可以预取与搜索过滤器匹配的DefendantID，然后使用Contains(翻译成SQLIN)进行过滤 这将有助于消除一些连接。

例如

var defendantIds = db.Set<Defendant>()
.Where(d => EF.Functions.Like(d.DefendantCode, defendantCodePattern))
.Select(d => d.DefendantID)
.ToList();

然后(第二个Where)：

.Where(r => defendantIds.Contains(r.Employer.DefendantID)
|| defendantIds.Contains(r.Contractor.DefendantID)
|| r.RecordProducts.Any(rp => defendantIds.Contains(rp.ProductDefendant.Defendant.DefendantID))
)

(2)以下技巧将EXISTS替换为LEFT JOIN。将第二个Where替换为：

.SelectMany(r => r.RecordProducts.DefaultIfEmpty(), (r, rp) => new { r, rp })
.Where(x => EF.Functions.Like(x.r.Employer.DefendantCode, defendantCodePattern)
|| EF.Functions.Like(x.r.Contractor.DefendantCode, defendantCodePattern)
|| EF.Functions.Like(x.rp.ProductDefendant.Defendant.DefendantCode, defendantCodePattern)
)
.Select(x => x.r)

并取消注释.Distinct()(此处需要它，因为LEFT JOIN(从SelectMany)乘以源记录)。在这种情况下，生成的 SQL 如下所示：

SELECT [t].[ID], [t].[StartDate], [t].[EndDate], [t].[WitnessName], [t].[SourceCode], [t].[JobsiteName], [t].[ShipName], [t].[EmployeeCode]
FROM (
SELECT DISTINCT [r].[RecordID] AS [ID], [r].[StartDate], [r].[EndDate], [r.Witness].[FullName] AS [WitnessName], CASE
WHEN [r].[SourceID] IS NOT NULL
THEN [r.Source].[SourceCode] ELSE N'zzzzz'
END AS [SourceCode], CASE
WHEN [r].[JobsiteID] IS NOT NULL
THEN [r.Jobsite].[JobsiteName] ELSE N'zzzzz'
END AS [JobsiteName], CASE
WHEN [r].[ShipID] IS NOT NULL
THEN [r.Ship].[ShipName] ELSE N'zzzzz'
END AS [ShipName], CASE
WHEN [r].[EmployerID] IS NOT NULL
THEN [r.Employer].[DefendantCode] ELSE N'zzzzz'
END AS [EmployeeCode]
FROM [Records] AS [r]
LEFT JOIN [Ships] AS [r.Ship] ON [r].[ShipID] = [r.Ship].[ShipID]
LEFT JOIN [Jobsites] AS [r.Jobsite] ON [r].[JobsiteID] = [r.Jobsite].[JobsiteID]
LEFT JOIN [Sources] AS [r.Source] ON [r].[SourceID] = [r.Source].[SourceID]
LEFT JOIN [Witnesses] AS [r.Witness] ON [r].[WitnessID] = [r.Witness].[WitnessID]
LEFT JOIN [Defendants] AS [r.Contractor] ON [r].[ContractorID] = [r.Contractor].[DefendantID]
LEFT JOIN [Defendants] AS [r.Employer] ON [r].[EmployerID] = [r.Employer].[DefendantID]
LEFT JOIN [Records_Products] AS [r.RecordProducts] ON [r].[RecordID] = [r.RecordProducts].[RecordID]
LEFT JOIN [Product_Defendant] AS [r.RecordProducts.ProductDefendant] ON [r.RecordProducts].[DefendantProductID] = [r.RecordProducts.ProductDefendant].[DefendantProductID]
LEFT JOIN [Defendants] AS [r.RecordProducts.ProductDefendant.Defendant] ON [r.RecordProducts.ProductDefendant].[DefendantID] = [r.RecordProducts.ProductDefendant.Defendant].[DefendantID]
WHERE ([r].[IsActive] = 1) AND (([r.Employer].[DefendantCode] LIKE @__defendantCodePattern_1 OR [r.Contractor].[DefendantCode] LIKE @__defendantCodePattern_1) OR [r.RecordProducts.ProductDefendant.Defendant].[DefendantCode] LIKE @__defendantCodePattern_1)
) AS [t]
ORDER BY [t].[SourceCode]
OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY

正如我在一开始所说，通常这不会影响CBO计划。但我肯定看到与原始执行计划不同的估计执行计划，因此值得尝试(尽管 LINQ 查询看起来很丑陋)。