c# – 实体框架组通过Sql生成
我坚持使用EF的性能问题.
using (var context = new CustomDbContext()) { var result = context. TransactionLines .Where(x => x.Transaction.TransactionTypeId == 1433 && (x.Transaction.Eob.EobBatchId == null || x.Transaction.Eob.EobBatch.Status == EobBatchStatusEnum.Completed) ) .GroupBy(x => x.VisitLine.ProcedureId) .Select(x => new { Id = x.Key,PaidAmount = x.Sum(t => t.PaidAmount),Code = context.Procedures.Where(h => h.Id == x.Key).Select(h => h.Code).FirstOrDefault() }).ToArray(); } EF生成下一个sql: SELECT 1 AS [C1],[Project6].[ProcedureId] AS [ProcedureId],[Project6].[C2] AS [C2],[Project6].[C1] AS [C3] FROM ( SELECT [Project5].[ProcedureId] AS [ProcedureId],[Project5].[C1] AS [C1],(SELECT SUM([Extent7].[PaidAmount]) AS [A1] FROM [dbo].[TransactionLines] AS [Extent7] INNER JOIN [dbo].[Transactions] AS [Extent8] ON [Extent7].[TransactionId] = [Extent8].[Id] LEFT OUTER JOIN [dbo].[Eobs] AS [Extent9] ON [Extent8].[EobId] = [Extent9].[Id] LEFT OUTER JOIN [dbo].[EobBatches] AS [Extent10] ON [Extent9].[EobBatchId] = [Extent10].[Id] LEFT OUTER JOIN [dbo].[VisitLines] AS [Extent11] ON [Extent7].[VisitLineId] = [Extent11].[Id] WHERE (([Extent9].[EobBatchId] IS NULL) OR (1 = [Extent10].[Status])) AND ([Extent8].[TransactionTypeId] = 1433) AND (([Project5].[ProcedureId] = [Extent11].[ProcedureId]) OR (([Project5].[ProcedureId] IS NULL) AND ([Extent11].[ProcedureId] IS NULL)))) AS [C2] FROM ( SELECT [Project4].[ProcedureId] AS [ProcedureId],[Project4].[C1] AS [C1] FROM ( SELECT [Project2].[ProcedureId] AS [ProcedureId],(SELECT TOP (1) [Extent6].[Code] AS [Code] FROM [dbo].[Procedures] AS [Extent6] WHERE [Extent6].[Id] = [Project2].[ProcedureId]) AS [C1] FROM ( SELECT [Distinct1].[ProcedureId] AS [ProcedureId] FROM ( SELECT DISTINCT [Extent5].[ProcedureId] AS [ProcedureId] FROM [dbo].[TransactionLines] AS [Extent1] INNER JOIN [dbo].[Transactions] AS [Extent2] ON [Extent1].[TransactionId] = [Extent2].[Id] LEFT OUTER JOIN [dbo].[Eobs] AS [Extent3] ON [Extent2].[EobId] = [Extent3].[Id] LEFT OUTER JOIN [dbo].[EobBatches] AS [Extent4] ON [Extent3].[EobBatchId] = [Extent4].[Id] LEFT OUTER JOIN [dbo].[VisitLines] AS [Extent5] ON [Extent1].[VisitLineId] = [Extent5].[Id] WHERE (([Extent3].[EobBatchId] IS NULL) OR (1 = [Extent4].[Status])) AND ([Extent2].[TransactionTypeId] = 1433) ) AS [Distinct1] ) AS [Project2] ) AS [Project4] ) AS [Project5] ) AS [Project6] 查询持续时间约为3秒. SELECT sq.ProcedureId,SUM(sq.PaidAmount),(SELECT TOP(1) Procedures.Code From Procedures Where Procedures.Id = sq.ProcedureId) as Code FROM( SELECT [Extent5].[ProcedureId] AS [ProcedureId],[Extent1].PaidAmount as [PaidAmount] FROM [dbo].[TransactionLines] AS [Extent1] INNER JOIN [dbo].[Transactions] AS [Extent2] ON [Extent1].[TransactionId] = [Extent2].[Id] LEFT OUTER JOIN [dbo].[Eobs] AS [Extent3] ON [Extent2].[EobId] = [Extent3].[Id] LEFT OUTER JOIN [dbo].[EobBatches] AS [Extent4] ON [Extent3].[EobBatchId] = [Extent4].[Id] LEFT OUTER JOIN [dbo].[VisitLines] AS [Extent5] ON [Extent1].[VisitLineId] = [Extent5].[Id] WHERE (([Extent3].[EobBatchId] IS NULL) OR (1 = [Extent4].[Status])) AND ([Extent2].[TransactionTypeId] = 1433) ) sq GROUP BY sq.ProcedureId 我写了不同的linqs但仍然无法强制EF生成GroupBy而不是子查询. 是否有可能强制EF生成与linq中编写的SQL完全相同的SQL? 解决方法
试着避免
context.Procedures.Where(h => h.Id == x.Key).Select(h => h.Code).FirstOrDefault() 通过在GroupBy子句中包含Code – 我知道它似乎是多余的,但是已知EF在转换分组操作时遇到问题,这些操作涉及除了使用密钥访问器和聚合之外的其他内容: //... .GroupBy(x => new { Id = x.VisitLine.ProcedureId,x.VisitLine.Procedure.Code }) .Select(x => new { Id = x.Key.Id,Code = x.Key.Code }).ToArray(); 更新:以上在我的测试环境中生成以下SQL(最新的EF6.1.3): SELECT 1 AS [C1],[GroupBy1].[K1] AS [ProcedureId],[GroupBy1].[A1] AS [C2],[GroupBy1].[K2] AS [Code] FROM ( SELECT [Extent5].[ProcedureId] AS [K1],[Extent6].[Code] AS [K2],SUM([Filter1].[PaidAmount]) AS [A1] FROM (SELECT [Extent1].[VisitLineId] AS [VisitLineId],[Extent1].[PaidAmount] AS [PaidAmount] FROM [dbo].[TransactionLine] AS [Extent1] INNER JOIN [dbo].[Transaction] AS [Extent2] ON [Extent1].[TransactionId] = [Extent2].[Id] LEFT OUTER JOIN [dbo].[Eob] AS [Extent3] ON [Extent2].[EobId] = [Extent3].[Id] LEFT OUTER JOIN [dbo].[EobBatch] AS [Extent4] ON [Extent3].[EobBatchId] = [Extent4].[Id] WHERE (1433 = [Extent2].[TransactionTypeId]) AND ([Extent3].[EobBatchId] IS NULL OR [Extent4].[Status] = 1) ) AS [Filter1] LEFT OUTER JOIN [dbo].[VisitLine] AS [Extent5] ON [Filter1].[VisitLineId] = [Extent5].[Id] LEFT OUTER JOIN [dbo].[Procedure] AS [Extent6] ON [Extent5].[ProcedureId] = [Extent6].[Id] GROUP BY [Extent5].[ProcedureId],[Extent6].[Code] ) AS [GroupBy1] 这比我预想的要好得多. 更新2:EF是一个奇怪的野兽.使用双投影可产生所需的结果: //... .GroupBy(x => x.VisitLine.ProcedureId) .Select(x => new { Id = x.Key,}) .Select(x => new { x.Id,x.PaidAmount,Code = context.Procedures.Where(h => h.Id == x.Id).Select(h => h.Code).FirstOrDefault() }).ToArray(); 产生以下内容: SELECT 1 AS [C1],[Project2].[ProcedureId] AS [ProcedureId],[Project2].[C1] AS [C2],[Project2].[C2] AS [C3] FROM ( SELECT [GroupBy1].[A1] AS [C1],(SELECT TOP (1) [Extent6].[Code] AS [Code] FROM [dbo].[Procedure] AS [Extent6] WHERE [Extent6].[Id] = [GroupBy1].[K1]) AS [C2] FROM ( SELECT [Extent5].[ProcedureId] AS [K1],SUM([Filter1].[PaidAmount]) AS [A1] FROM (SELECT [Extent1].[VisitLineId] AS [VisitLineId],[Extent1].[PaidAmount] AS [PaidAmount] FROM [dbo].[TransactionLine] AS [Extent1] INNER JOIN [dbo].[Transaction] AS [Extent2] ON [Extent1].[TransactionId] = [Extent2].[Id] LEFT OUTER JOIN [dbo].[Eob] AS [Extent3] ON [Extent2].[EobId] = [Extent3].[Id] LEFT OUTER JOIN [dbo].[EobBatch] AS [Extent4] ON [Extent3].[EobBatchId] = [Extent4].[Id] WHERE (1433 = [Extent2].[TransactionTypeId]) AND ([Extent3].[EobBatchId] IS NULL OR [Extent4].[Status] = 1) ) AS [Filter1] LEFT OUTER JOIN [dbo].[VisitLine] AS [Extent5] ON [Filter1].[VisitLineId] = [Extent5].[Id] GROUP BY [Extent5].[ProcedureId] ) AS [GroupBy1] ) AS [Project2] 附:如果不清楚,请回答您的具体问题
没有.相反,您应该以某种方式编写LINQ查询,以获得所需(或更接近)的SQL查询. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |