title: Entity Framework GroupBy to Sql Generation
body: <p>I stuck with performance problem with EF.</p>
  
  <pre><code>        using (var context = new CustomDbContext())
          {
              var result = context.
                  TransactionLines
                  .Where(x =&gt; 
                      x.Transaction.TransactionTypeId == 1433 &amp;&amp;
                      (x.Transaction.Eob.EobBatchId == null || x.Transaction.Eob.EobBatch.Status == EobBatchStatusEnum.Completed)
                  )
                  .GroupBy(x =&gt; x.VisitLine.ProcedureId)
                  .Select(x =&gt; new
                  {
                      Id = x.Key,
                      PaidAmount = x.Sum(t =&gt; t.PaidAmount),
                      Code = context.Procedures.Where(h =&gt; h.Id == x.Key).Select(h =&gt; h.Code).FirstOrDefault()
                  }).ToArray();
          }
  </code></pre>
  
  <p>EF generate the next sql:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>The query duration is ~3 seconds.
  If write sql query directly using Group By then a query duration is 1.5 seconds and it use half less CPU resources.</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>I wrote different linqs but still can't force EF generate GroupBy instead of subqueries.
  Ideally, i wouldn't want to use functions or manually written sql, because I have a lot of conditions in building linq logic.</p>
  
  <p>Is it possible to force EF to generate SQL exactly as it was written within linq ?</p>