c# - INNER JOINS 和 LEFT JOINS 的 EF Core 难度
问题描述
我正在尝试从一个非常大的数据库中检索一些数据。为了检索它,我需要从 6 个内部连接中获取一些数据。以下选项是我已经尝试过的:
选项 1 使用 EF Core en ThenInclude
var enquiry = await _context.Enquiries
.Where(e => e.Id == id)
.Include(e => e.EnqPartJobs.Where(ej => ej.RecordStateId == 0))
.ThenInclude(ej => ej.AnswerGrpBatch)
.ThenInclude(agb => agb.AnswerGrps)
.ThenInclude(ag => ag.EnqPropResults)
.ThenInclude(epr => epr.EnqPropResultDevs)
.ThenInclude(epr => epr.Deviation)
.ToListAsync();
这种方法的优点是它可以很好地映射到带有 AnswerGrpBatches 列表的 Inquiry 类中。顺便说一句,我没有命名这些类,这就是数据库的外观。
查看查询时,如下所示:
SELECT [e].[SID], [e].[FK_CRMPartner_SID], [e].[FK_Certificate_SID], [e].[createDate], [e].[displayDate], [e].[extID], [e].[finishDate], [e].[fixedGenId], [e].[aName], [t2].[SID], [t2].[FK_Enquiry_SID], [t2].[aName], [t2].[FK_RecordState_SID], [t2].[remark], [t2].[validFrom], [t2].[validTo], [t2].[SID0], [t2].[FK_EnqPartJob_SID], [t2].[FK_RecordState_SID0], [t2].[SID1], [t2].[FK_AnswerGrpBatch_SID], [t2].[FK_RecordState_SID1], [t2].[SID00], [t2].[FK_AnswerGrp_SID], [t2].[FK_EnqPropertyForm_SID], [t2].[FK_PossResult_SID], [t2].[FK_RecordState_SID00], [t2].[value], [t2].[SID000], [t2].[FK_Deviation_SID], [t2].[FK_EnqPropResult_SID], [t2].[SID0000], [t2].[aDescription], [t2].[aName0]
FROM [Enquiry] AS [e]
LEFT JOIN (
SELECT [e0].[SID], [e0].[FK_Enquiry_SID], [e0].[aName], [e0].[FK_RecordState_SID], [e0].[remark], [e0].[validFrom], [e0].[validTo], [a].[SID] AS [SID0], [a].[FK_EnqPartJob_SID], [a].[FK_RecordState_SID] AS [FK_RecordState_SID0], [t1].[SID] AS [SID1], [t1].[FK_AnswerGrpBatch_SID], [t1].[FK_RecordState_SID] AS [FK_RecordState_SID1], [t1].[SID0] AS [SID00], [t1].[FK_AnswerGrp_SID], [t1].[FK_EnqPropertyForm_SID], [t1].[FK_PossResult_SID], [t1].[FK_RecordState_SID0] AS [FK_RecordState_SID00], [t1].[value], [t1].[SID00] AS [SID000], [t1].[FK_Deviation_SID], [t1].[FK_EnqPropResult_SID], [t1].[SID000] AS [SID0000], [t1].[aDescription], [t1].[aName] AS [aName0]
FROM [EnqPartJob] AS [e0]
LEFT JOIN [AnswerGrpBatch] AS [a] ON [e0].[SID] = [a].[FK_EnqPartJob_SID]
LEFT JOIN (
SELECT [a0].[SID], [a0].[FK_AnswerGrpBatch_SID], [a0].[FK_RecordState_SID], [t0].[SID] AS [SID0], [t0].[FK_AnswerGrp_SID], [t0].[FK_EnqPropertyForm_SID], [t0].[FK_PossResult_SID], [t0].[FK_RecordState_SID] AS [FK_RecordState_SID0], [t0].[value], [t0].[SID0] AS [SID00], [t0].[FK_Deviation_SID], [t0].[FK_EnqPropResult_SID], [t0].[SID00] AS [SID000], [t0].[aDescription], [t0].[aName]
FROM [AnswerGrp] AS [a0]
LEFT JOIN (
SELECT [e1].[SID], [e1].[FK_AnswerGrp_SID], [e1].[FK_EnqPropertyForm_SID], [e1].[FK_PossResult_SID], [e1].[FK_RecordState_SID], [e1].[value], [t].[SID] AS [SID0], [t].[FK_Deviation_SID], [t].[FK_EnqPropResult_SID], [t].[SID0] AS [SID00], [t].[aDescription], [t].[aName]
FROM [EnqPropResult] AS [e1]
LEFT JOIN (
SELECT [e2].[SID], [e2].[FK_Deviation_SID], [e2].[FK_EnqPropResult_SID], [d].[SID] AS [SID0], [d].[aDescription], [d].[aName]
FROM [EnqPropResultDev] AS [e2]
INNER JOIN [Deviation] AS [d] ON [e2].[FK_Deviation_SID] = [d].[SID]
) AS [t] ON [e1].[SID] = [t].[FK_EnqPropResult_SID]
) AS [t0] ON [a0].[SID] = [t0].[FK_AnswerGrp_SID]
) AS [t1] ON [a].[SID] = [t1].[FK_AnswerGrpBatch_SID]
WHERE [e0].[FK_RecordState_SID] = 0
) AS [t2] ON [e].[SID] = [t2].[FK_Enquiry_SID]
WHERE [e].[SID] = 1790797
但它必须是以下两个选项之一:
使用 IS NOT NULL 语句,但我无法弄清楚如何制作这个语句,因为它在不同的类中很深。
SELECT [e].[SID], [e].[FK_CRMPartner_SID], [e].[FK_Certificate_SID], [e].[createDate], [e].[displayDate], [e].[extID], [e].[finishDate], [e].[fixedGenId], [e].[aName], [t2].[SID], [t2].[FK_Enquiry_SID], [t2].[aName], [t2].[FK_RecordState_SID], [t2].[remark], [t2].[validFrom], [t2].[validTo], [t2].[SID0], [t2].[FK_EnqPartJob_SID], [t2].[FK_RecordState_SID0], [t2].[SID1], [t2].[FK_AnswerGrpBatch_SID], [t2].[FK_RecordState_SID1], [t2].[SID00], [t2].[FK_AnswerGrp_SID], [t2].[FK_EnqPropertyForm_SID], [t2].[FK_PossResult_SID], [t2].[FK_RecordState_SID00], [t2].[value], [t2].[SID000], [t2].[FK_Deviation_SID], [t2].[FK_EnqPropResult_SID], [t2].[SID0000], [t2].[aDescription], [t2].[aName0]
FROM [Enquiry] AS [e]
LEFT JOIN (
SELECT [e0].[SID], [e0].[FK_Enquiry_SID], [e0].[aName], [e0].[FK_RecordState_SID], [e0].[remark], [e0].[validFrom], [e0].[validTo], [a].[SID] AS [SID0], [a].[FK_EnqPartJob_SID], [a].[FK_RecordState_SID] AS [FK_RecordState_SID0], [t1].[SID] AS [SID1], [t1].[FK_AnswerGrpBatch_SID], [t1].[FK_RecordState_SID] AS [FK_RecordState_SID1], [t1].[SID0] AS [SID00], [t1].[FK_AnswerGrp_SID], [t1].[FK_EnqPropertyForm_SID], [t1].[FK_PossResult_SID], [t1].[FK_RecordState_SID0] AS [FK_RecordState_SID00], [t1].[value], [t1].[SID00] AS [SID000], [t1].[FK_Deviation_SID], [t1].[FK_EnqPropResult_SID], [t1].[SID000] AS [SID0000], [t1].[aDescription], [t1].[aName] AS [aName0]
FROM [EnqPartJob] AS [e0]
LEFT JOIN [AnswerGrpBatch] AS [a] ON [e0].[SID] = [a].[FK_EnqPartJob_SID]
LEFT JOIN (
SELECT [a0].[SID], [a0].[FK_AnswerGrpBatch_SID], [a0].[FK_RecordState_SID], [t0].[SID] AS [SID0], [t0].[FK_AnswerGrp_SID], [t0].[FK_EnqPropertyForm_SID], [t0].[FK_PossResult_SID], [t0].[FK_RecordState_SID] AS [FK_RecordState_SID0], [t0].[value], [t0].[SID0] AS [SID00], [t0].[FK_Deviation_SID], [t0].[FK_EnqPropResult_SID], [t0].[SID00] AS [SID000], [t0].[aDescription], [t0].[aName]
FROM [AnswerGrp] AS [a0]
LEFT JOIN (
SELECT [e1].[SID], [e1].[FK_AnswerGrp_SID], [e1].[FK_EnqPropertyForm_SID], [e1].[FK_PossResult_SID], [e1].[FK_RecordState_SID], [e1].[value], [t].[SID] AS [SID0], [t].[FK_Deviation_SID], [t].[FK_EnqPropResult_SID], [t].[SID0] AS [SID00], [t].[aDescription], [t].[aName]
FROM [EnqPropResult] AS [e1]
LEFT JOIN (
SELECT [e2].[SID], [e2].[FK_Deviation_SID], [e2].[FK_EnqPropResult_SID], [d].[SID] AS [SID0], [d].[aDescription], [d].[aName]
FROM [EnqPropResultDev] AS [e2]
INNER JOIN [Deviation] AS [d] ON [e2].[FK_Deviation_SID] = [d].[SID]
) AS [t] ON [e1].[SID] = [t].[FK_EnqPropResult_SID]
) AS [t0] ON [a0].[SID] = [t0].[FK_AnswerGrp_SID]
) AS [t1] ON [a].[SID] = [t1].[FK_AnswerGrpBatch_SID]
WHERE [e0].[FK_RecordState_SID] = 0
) AS [t2] ON [e].[SID] = [t2].[FK_Enquiry_SID]
WHERE [e].[SID] = 1790797 AND [t2].[SID000] IS NOT NULL
强制 INNER JOINS:
SELECT [e].[SID], [e].[FK_CRMPartner_SID], [e].[FK_Certificate_SID], [e].[createDate], [e].[displayDate], [e].[extID], [e].[finishDate], [e].[fixedGenId], [e].[aName], [t2].[SID], [t2].[FK_Enquiry_SID], [t2].[aName], [t2].[FK_RecordState_SID], [t2].[remark], [t2].[validFrom], [t2].[validTo], [t2].[SID0], [t2].[FK_EnqPartJob_SID], [t2].[FK_RecordState_SID0], [t2].[SID1], [t2].[FK_AnswerGrpBatch_SID], [t2].[FK_RecordState_SID1], [t2].[SID00], [t2].[FK_AnswerGrp_SID], [t2].[FK_EnqPropertyForm_SID], [t2].[FK_PossResult_SID], [t2].[FK_RecordState_SID00], [t2].[value], [t2].[SID000], [t2].[FK_Deviation_SID], [t2].[FK_EnqPropResult_SID], [t2].[SID0000], [t2].[aDescription], [t2].[aName0]
FROM [Enquiry] AS [e]
LEFT JOIN (
SELECT [e0].[SID], [e0].[FK_Enquiry_SID], [e0].[aName], [e0].[FK_RecordState_SID], [e0].[remark], [e0].[validFrom], [e0].[validTo], [a].[SID] AS [SID0], [a].[FK_EnqPartJob_SID], [a].[FK_RecordState_SID] AS [FK_RecordState_SID0], [t1].[SID] AS [SID1], [t1].[FK_AnswerGrpBatch_SID], [t1].[FK_RecordState_SID] AS [FK_RecordState_SID1], [t1].[SID0] AS [SID00], [t1].[FK_AnswerGrp_SID], [t1].[FK_EnqPropertyForm_SID], [t1].[FK_PossResult_SID], [t1].[FK_RecordState_SID0] AS [FK_RecordState_SID00], [t1].[value], [t1].[SID00] AS [SID000], [t1].[FK_Deviation_SID], [t1].[FK_EnqPropResult_SID], [t1].[SID000] AS [SID0000], [t1].[aDescription], [t1].[aName] AS [aName0]
FROM [EnqPartJob] AS [e0]
LEFT JOIN [AnswerGrpBatch] AS [a] ON [e0].[SID] = [a].[FK_EnqPartJob_SID]
LEFT JOIN (
SELECT [a0].[SID], [a0].[FK_AnswerGrpBatch_SID], [a0].[FK_RecordState_SID], [t0].[SID] AS [SID0], [t0].[FK_AnswerGrp_SID], [t0].[FK_EnqPropertyForm_SID], [t0].[FK_PossResult_SID], [t0].[FK_RecordState_SID] AS [FK_RecordState_SID0], [t0].[value], [t0].[SID0] AS [SID00], [t0].[FK_Deviation_SID], [t0].[FK_EnqPropResult_SID], [t0].[SID00] AS [SID000], [t0].[aDescription], [t0].[aName]
FROM [AnswerGrp] AS [a0]
INNER JOIN (
SELECT [e1].[SID], [e1].[FK_AnswerGrp_SID], [e1].[FK_EnqPropertyForm_SID], [e1].[FK_PossResult_SID], [e1].[FK_RecordState_SID], [e1].[value], [t].[SID] AS [SID0], [t].[FK_Deviation_SID], [t].[FK_EnqPropResult_SID], [t].[SID0] AS [SID00], [t].[aDescription], [t].[aName]
FROM [EnqPropResult] AS [e1]
INNER JOIN (
SELECT [e2].[SID], [e2].[FK_Deviation_SID], [e2].[FK_EnqPropResult_SID], [d].[SID] AS [SID0], [d].[aDescription], [d].[aName]
FROM [EnqPropResultDev] AS [e2]
INNER JOIN [Deviation] AS [d] ON [e2].[FK_Deviation_SID] = [d].[SID]
) AS [t] ON [e1].[SID] = [t].[FK_EnqPropResult_SID]
) AS [t0] ON [a0].[SID] = [t0].[FK_AnswerGrp_SID]
) AS [t1] ON [a].[SID] = [t1].[FK_AnswerGrpBatch_SID]
WHERE [e0].[FK_RecordState_SID] = 0
) AS [t2] ON [e].[SID] = [t2].[FK_Enquiry_SID]
WHERE [e].[SID] = 1790797
问题是它将对除最后一个之外的每个 Include 和 ThenInclude 使用左连接。但这也会检索我不需要的信息。所以我尝试进行 LINQ 查询。
选项 2 LINQ
LINQ 查询总是使用 INNER JOINS,所以我完全可以从数据库中得到我想要的。唯一的问题是我不知道如何将其映射到 Inquiry 类,其中 INNER JOINS 是 Inquiry 类中的一个列表。就像选项 1 一样。
var query = from enquiry in _context.Set<Enquiry>()
join enqPartJob in _context.Set<EnqPartJob>()
on enquiry.Id equals enqPartJob.EnquiryId
join answerGrpBatch in _context.Set<AnswerGrpBatch>()
on enqPartJob.Id equals answerGrpBatch.EnqPartJobId
join answerGrp in _context.Set<AnswerGrp>()
on answerGrpBatch.Id equals answerGrp.AnswerGrpBatchId
join enqPropResult in _context.Set<EnqPropResult>()
on answerGrp.Id equals enqPropResult.AnswerGrpId
join enqPropResultDev in _context.Set<EnqPropResultDev>()
on enqPropResult.Id equals enqPropResultDev.EnqPropResultId
join deviation in _context.Set<Deviation>()
on enqPropResultDev.DeviationId equals deviation.Id
where enquiry.Id == id
select new { enquiry, enqPropResultDev };
是的,我知道 EFCore 使用 LEFT JOINS 是有原因的,但这不是我的数据库,所以我必须适应它。
解决方案
感谢 Ivan Stoev,他评论了这个建议。
从 Deviation 开始,一直到 Inquiry 为我解决了问题。查询现在看起来像这样(您可以看到它包含 INNER JOINS)。
SELECT [t].[SID], [t].[aDescription], [t].[aName], [t0].[SID], [t0].[FK_Deviation_SID], [t0].[FK_EnqPropResult_SID], [t0].[SID0], [t0].[FK_AnswerGrp_SID], [t0].[FK_EnqPropertyForm_SID], [t0].[FK_PossResult_SID], [t0].[FK_RecordState_SID], [t0].[value], [t0].[SID1], [t0].[FK_AnswerGrpBatch_SID], [t0].[FK_RecordState_SID0], [t0].[SID2], [t0].[FK_EnqPartJob_SID], [t0].[FK_RecordState_SID1], [t0].[SID3], [t0].[FK_Enquiry_SID], [t0].[aName], [t0].[FK_RecordState_SID2], [t0].[remark], [t0].[validFrom], [t0].[validTo], [t0].[SID4], [t0].[FK_CRMPartner_SID], [t0].[FK_Certificate_SID], [t0].[createDate], [t0].[displayDate], [t0].[extID], [t0].[finishDate], [t0].[fixedGenId], [t0].[aName0]
FROM (
SELECT TOP(1) [d].[SID], [d].[aDescription], [d].[aName]
FROM [Deviation] AS [d]
WHERE [d].[SID] = 5038
) AS [t]
LEFT JOIN (
SELECT [e].[SID], [e].[FK_Deviation_SID], [e].[FK_EnqPropResult_SID], [e0].[SID] AS [SID0], [e0].[FK_AnswerGrp_SID], [e0].[FK_EnqPropertyForm_SID], [e0].[FK_PossResult_SID], [e0].[FK_RecordState_SID], [e0].[value], [a].[SID] AS [SID1], [a].[FK_AnswerGrpBatch_SID], [a].[FK_RecordState_SID] AS [FK_RecordState_SID0], [a0].[SID] AS [SID2], [a0].[FK_EnqPartJob_SID], [a0].[FK_RecordState_SID] AS [FK_RecordState_SID1], [e1].[SID] AS [SID3], [e1].[FK_Enquiry_SID], [e1].[aName], [e1].[FK_RecordState_SID] AS [FK_RecordState_SID2], [e1].[remark], [e1].[validFrom], [e1].[validTo], [e2].[SID] AS [SID4], [e2].[FK_CRMPartner_SID], [e2].[FK_Certificate_SID], [e2].[createDate], [e2].[displayDate], [e2].[extID], [e2].[finishDate], [e2].[fixedGenId], [e2].[aName] AS [aName0]
FROM [EnqPropResultDev] AS [e]
INNER JOIN [EnqPropResult] AS [e0] ON [e].[FK_EnqPropResult_SID] = [e0].[SID]
INNER JOIN [AnswerGrp] AS [a] ON [e0].[FK_AnswerGrp_SID] = [a].[SID]
INNER JOIN [AnswerGrpBatch] AS [a0] ON [a].[FK_AnswerGrpBatch_SID] = [a0].[SID]
INNER JOIN [EnqPartJob] AS [e1] ON [a0].[FK_EnqPartJob_SID] = [e1].[SID]
INNER JOIN [Enquiry] AS [e2] ON [e1].[FK_Enquiry_SID] = [e2].[SID]
WHERE [e0].[FK_RecordState_SID] = 0
) AS [t0] ON [t].[SID] = [t0].[FK_Deviation_SID]
ORDER BY [t].[SID], [t0].[SID], [t0].[SID0], [t0].[SID1], [t0].[SID2], [t0].[SID3], [t0].[SID4]
推荐阅读
- python - 带有 pyqt 信号的 Cython
- authentication - 如何弄清楚如何使用 http 请求对自己进行身份验证
- moodle - 如何直接从服务器配置 Moodle O365 SSO?
- javascript - 如何用javascript切换不同的div
- linux - 如何添加几个平方的总和(bourne 脚本)
- c# - C# LINQ - 道具字符串的值为 System.Collections.Generic.List`1[System.String]
- python - 从python中的元组内部删除重叠的数字,这样没有2个元组具有相同的开始或结束数字
- android - 使用带有 TextInputLayout 的线性布局但遇到问题
- sql-server - SQL中多条记录重叠期间如何新建记录
- javascript - 使用 Javascript 更改外部 Web 应用程序中的值