在LINQ创建的语句中，JOIN顺序没有被考虑

本文关键字：顺序 JOIN 创建 LINQ 语句 | 更新日期: 2023-09-27 18:10:44

在LINQ创建的语句中，JOIN顺序没有被考虑。

我写了一个像这样的LINQ查询

        public IQueryable<SubscribeTotalHistory> Search(int subscribeId, string     fromDate, string toDate)
        {
        var result =
            FindAll().Include(i => i.SubscribeIdentityHistory)
                .Include(i => i.SubscribeMeterHistory)
                .Include(i => i.SubscribeMeterHistory.MeterDefinition)
                .Include(i => i.SubscribeMeterHistory.MeterDefinition1)
                .Include(i => i.SubscribePowerHistory.StandardVoltage)
                .Include(i => i.SubscribeStateHistory)
                .Include(i => i.Subscribe)
                .Include(i => i.SubscribeTariffHistory)
                .Include(i => i.SubscribeTariffHistory.BillingCalculateType)
                .Include(i => i.SubscribeTariffHistory.WorkType)
                .Where(
                    i =>
                    i.SubscribeId == subscribeId && i.Status == AnnounceState.Valid &&
                    ((i.FromDateTime.CompareTo(toDate) <= 0 && i.ToDateTime.CompareTo(fromDate) >= 0) ||
                     (i.FromDateTime.CompareTo(fromDate) <= 0 && i.ToDateTime.CompareTo(toDate) >= 0)));
        return result;
    }

，我想得到一个像这样的T/SQL 

        Select *
    FROM     [billing].[SubscribeTotalHistory] AS [Extent1]
INNER JOIN [billing].[SubscribeIdentityHistory] AS [Extent2] ON [Extent1].[SubscribeIdentityHstryId] = [Extent2].[Id]
LEFT OUTER JOIN [billing].[SubscribeMeterHistory] AS [Extent3] ON [Extent1].[SubscribeMeterHistoryId] = [Extent3].[Id]
LEFT OUTER JOIN [billing].[MeterDefinition] AS [Extent4] ON [Extent3].[MeterDefinitionActiveCode] = [Extent4].[Id]
LEFT OUTER JOIN [billing].[MeterDefinition] AS [Extent5] ON [Extent3].[MeterDefinitionReactiveCode] = [Extent5].[Id]
INNER JOIN [billing].[SubscribePowerHistory] AS [Extent6] ON [Extent1].[SubscribePowerHistoryId] = [Extent6].[Id]
INNER JOIN [global].[StandardVoltage] AS [Extent7] ON [Extent6].[StandardVoltageId] = [Extent7].[Id]
INNER JOIN [billing].[Subscribe] AS [Extent8] ON [Extent1].[SubscribeId] = [Extent8].[Id]
INNER JOIN [billing].[SubscribeStateHistory] AS [Extent9] ON [Extent1].[SubscribeStateHistoryId] = [Extent9].[Id]
INNER JOIN [billing].[SubscribeTariffHistory] AS [Extent10] ON [Extent1].[SubscribeTariefHistoryId] = [Extent10].[Id]
LEFT OUTER JOIN [billing].[WorkType] AS [Extent11] ON [Filter1].[WorkTypeId] = [Extent11].[Id]
.....

但我有一个问题像这样

SELECT 
[Filter1].[Id1] AS [Id], 
[Filter1].[AnnounceId1] AS [AnnounceId], 
[Filter1].[AnnounceType] AS [AnnounceType], 
[Filter1].[ExecutiveUnitId] AS [ExecutiveUnitId]
FROM   (SELECT
             [Extent1].[Id] AS [Id1],
          [Extent1].[AnnounceId] AS [AnnounceId1],
         [Extent1].[AnnounceType] AS [AnnounceType],
          [Extent1].[ExecutiveUnitId] AS [ExecutiveUnitId]
FROM     [billing].[SubscribeTotalHistory] AS [Extent1]
INNER JOIN [billing].[SubscribeIdentityHistory] AS [Extent2] ON [Extent1].    [SubscribeIdentityHstryId] = [Extent2].[Id]
    LEFT OUTER JOIN [billing].[SubscribeMeterHistory] AS [Extent3] ON [Extent1].[SubscribeMeterHistoryId] = [Extent3].[Id]
LEFT OUTER JOIN [billing].[MeterDefinition] AS [Extent4] ON [Extent3].[MeterDefinitionActiveCode] = [Extent4].[Id]
LEFT OUTER JOIN [billing].[MeterDefinition] AS [Extent5] ON [Extent3].[MeterDefinitionReactiveCode] = [Extent5].[Id]
INNER JOIN [billing].[SubscribePowerHistory] AS [Extent6] ON [Extent1].[SubscribePowerHistoryId] = [Extent6].[Id]
INNER JOIN [global].[StandardVoltage] AS [Extent7] ON [Extent6].[StandardVoltageId] = [Extent7].[Id]
INNER JOIN [billing].[Subscribe] AS [Extent8] ON [Extent1].[SubscribeId] = [Extent8].[Id]
INNER JOIN [billing].[SubscribeStateHistory] AS [Extent9] ON [Extent1].[SubscribeStateHistoryId] = [Extent9].[Id]
INNER JOIN [billing].[SubscribeTariffHistory] AS [Extent10] ON [Extent1].[SubscribeTariefHistoryId] = [Extent10].[Id]
WHERE 1 =  CAST( [Extent1].[Status] AS int) ) AS [Filter1]
LEFT OUTER JOIN [billing].[WorkType] AS [Extent11] ON [Filter1].[WorkTypeId] = [Extent11].[Id]

为什么其中一个JOIN位于主Select之外?

在LINQ创建的语句中，JOIN顺序没有被考虑

between()的所有内容都被认为是派生表，最后一次连接是在该表的结果上。这是完全有效的，但是使用普通的表表达式可读性更好。我猜linq不关心可读性:)

使用CTE的相同查询看起来像这样，现在最后一个连接看起来更"正常"。

WITH [Filter1] AS (
    SELECT
        [Extent1].[Id] AS [Id1],
        [Extent1].[AnnounceId] AS [AnnounceId1],
        [Extent1].[AnnounceType] AS [AnnounceType],
        [Extent1].[ExecutiveUnitId] AS [ExecutiveUnitId]
    FROM
        [billing].[SubscribeTotalHistory] AS [Extent1]
        INNER JOIN [billing].[SubscribeIdentityHistory] AS [Extent2] ON [Extent1].    [SubscribeIdentityHstryId] = [Extent2].[Id]
        LEFT OUTER JOIN [billing].[SubscribeMeterHistory] AS [Extent3] ON [Extent1].[SubscribeMeterHistoryId] = [Extent3].[Id]
        LEFT OUTER JOIN [billing].[MeterDefinition] AS [Extent4] ON [Extent3].[MeterDefinitionActiveCode] = [Extent4].[Id]
        LEFT OUTER JOIN [billing].[MeterDefinition] AS [Extent5] ON [Extent3].[MeterDefinitionReactiveCode] = [Extent5].[Id]
        INNER JOIN [billing].[SubscribePowerHistory] AS [Extent6] ON [Extent1].[SubscribePowerHistoryId] = [Extent6].[Id]
        INNER JOIN [global].[StandardVoltage] AS [Extent7] ON [Extent6].[StandardVoltageId] = [Extent7].[Id]
        INNER JOIN [billing].[Subscribe] AS [Extent8] ON [Extent1].[SubscribeId] = [Extent8].[Id]
        INNER JOIN [billing].[SubscribeStateHistory] AS [Extent9] ON [Extent1].[SubscribeStateHistoryId] = [Extent9].[Id]
        INNER JOIN [billing].[SubscribeTariffHistory] AS [Extent10] ON [Extent1].[SubscribeTariefHistoryId] = [Extent10].[Id]
    WHERE 
        1 = CAST( [Extent1].[Status] AS int)
)
SELECT 
    [Filter1].[Id1] AS [Id], 
    [Filter1].[AnnounceId1] AS [AnnounceId], 
    [Filter1].[AnnounceType] AS [AnnounceType], 
    [Filter1].[ExecutiveUnitId] AS [ExecutiveUnitId]
FROM
    [Filter1]
    LEFT OUTER JOIN [billing].[WorkType] AS [Extent11] ON [Filter1].[WorkTypeId] = [Extent11].[Id]
相关文章：