c# - LINQ:Include 子句导致两个左连接，而本应有一个

Question

我正在使用以下 LINQ 查询:

    CreateObjectSet<ClientCustomFieldValue>()
.Include(scf => scf.ClientCustomField.CustomField)
.Where(str => str.PassengerTripID == passengerTripID).ToList();

这个查询对应的sql是(根据sql profiler)

执行sp_executesql

N'SELECT 
    [Extent1].[ClientCustomFieldValueID] AS [ClientCustomFieldValueID], 
    [Extent1].[ClientCustomFieldID] AS [ClientCustomFieldID], 
    [Extent1].[PassengerTripID] AS [PassengerTripID], 
    [Extent1].[DataValue] AS [DataValue], 
    [Extent1].[RowVersion] AS [RowVersion], 
    [Extent1].[LastChangeSecSessionID] AS [LastChangeSecSessionID], 
    [Extent1].[LastChangeTimeUTC] AS [LastChangeTimeUTC], 
    [Extent2].[ClientCustomFieldID] AS [ClientCustomFieldID1], 
    [Extent2].[ClientID] AS [ClientID], 
    [Extent2].[CustomFieldID] AS [CustomFieldID], 
    [Extent2].[CustomFieldSourceEnumID] AS [CustomFieldSourceEnumID], 
    [Extent2].[RequiredFlag] AS [RequiredFlag], 
    [Extent2].[ValidationRegex] AS [ValidationRegex], 
    [Extent2].[RowVersion] AS [RowVersion1], 
    [Extent2].[PassengerTripStopTypeEnumID] AS [PassengerTripStopTypeEnumID], 
    [Extent2].[LastChangeSecSessionID] AS [LastChangeSecSessionID1], 
    [Extent2].[LastChangeTimeUTC] AS [LastChangeTimeUTC1], 
    [Extent4].[CustomFieldID] AS [CustomFieldID1], 
    [Extent4].[CustomFieldCode] AS [CustomFieldCode], 
    [Extent4].[Description] AS [Description], 
    [Extent4].[RowVersion] AS [RowVersion2], 
    [Extent4].[LastChangeSecSessionID] AS [LastChangeSecSessionID2], 
    [Extent4].[LastChangeTimeUTC] AS [LastChangeTimeUTC2]
FROM    [dbo].[ClientCustomFieldValue] AS [Extent1]
LEFT OUTER JOIN [dbo].[ClientCustomField] AS [Extent2] 
    ON ([Extent2].[DeleteFlag] = 0) 
    AND ([Extent1].[ClientCustomFieldID] = [Extent2].[ClientCustomFieldID])
LEFT OUTER JOIN [dbo].[ClientCustomField] AS [Extent3] 
    ON ([Extent3].[DeleteFlag] = 0) 
    AND ([Extent1].[ClientCustomFieldID] = [Extent3].[ClientCustomFieldID])
LEFT OUTER JOIN [dbo].[CustomField] AS [Extent4] 
    ON ([Extent4].[DeleteFlag] = 0) 
    AND ([Extent3].[CustomFieldID] = [Extent4].[CustomFieldID])
WHERE ([Extent1].[DeleteFlag] = 0) 
    AND ([Extent1].[PassengerTripID] = @p__linq__0)
',N'@p__linq__0 int',@p__linq__0=96

我想知道为什么有两个 left join with 'ClientCustomField' 表。请帮助我理解这一点。

Answer 1

这是一个假设。

第一个左连接，表示为 Extent2，用于 SELECT 子句从 ClientCustomField 表中检索所有必需的字段。不管是否有 Include 方法调用，这无论如何都会出现在查询中。

第二个左连接，表示为Extent3，用于检索CustomField 表字段。如您所见，除了最后一个左连接子句外，它没有在任何地方使用，该子句是专门为此创建的，因为它使用 CustomField 连接所有内容。这是 Include 调用产生的东西。

显然，LINQ 不会检查查询中已经连接了哪些表，而是分别处理查询的每个部分，它会为每个部分生成两个左连接。