title: EF Core 2.1 Making multiple DB calls
body: <p>Is there a way to prevent EF Core from doing multiple DB round trips on single enumeration function call?</p>
  
  <p>Take into consideration this relatively simple LINQ expression:</p>
  
  <pre><code>var query2 = context.CheckinTablets.Select(ct =&gt; new
              {
                  Id = ct.Id,
                  DeviceName = ct.Name,
                  Status = ct.CheckinTabletStatuses
                      .OrderByDescending(cts =&gt; cts.TimestampUtc).FirstOrDefault()
              }).ToList();
  </code></pre>
  
  <p>In the past expactation was that "One enumeration call translates to one DB call" (if you disable lazy loading). In EF Core this is no longer the case!</p>
  
  <p>In EF 6.2.0 this LINQ is translated to</p>
  
  <pre><code>SELECT [Extent1].[CheckinTabletID] AS [CheckinTabletID],
     [Limit1].[TimestampUtc] AS [TimestampUtc]
    --...
  FROM [dbo].[CheckinTablet] AS [Extent1] OUTER APPLY (
  SELECT TOP (1) [Project1].[CheckinTabletStatusID] AS [CheckinTabletStatusID],
                 [Project1].[CheckinTabletID] AS [CheckinTabletID],
                 [Project1].[TimestampUtc] AS [TimestampUtc]
  FROM (
  SELECT [Extent2].[CheckinTabletStatusID] AS [CheckinTabletStatusID],
         [Extent2].[CheckinTabletID] AS [CheckinTabletID],
         [Extent2].[TimestampUtc] AS [TimestampUtc]
       --...
  FROM [dbo].[CheckinTabletStatus] AS [Extent2]
  WHERE [Extent1].[CheckinTabletID] = [Extent2].[CheckinTabletID]
  ) AS [Project1] ORDER BY [Project1].[TimestampUtc] DESC
  ) AS [Limit1];
  </code></pre>
  
  <p>While quite ugly, it was something that followed <a href="https://en.wikipedia.org/wiki/Principle_of_least_astonishment" rel="nofollow noreferrer" title="POLA">POLA</a> quite nicely. Even more it was something we could work with to optimize DB side (indexes).</p>
  
  <p>With EF Core 2.1.0 we get something like this:</p>
  
  <pre><code>SELECT [ct].[CheckinTabletID] AS [Id], [ct].[strName] AS [DeviceName] FROM [CheckinTablet] AS [ct]
  
  exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=1
  
  exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=2
  
  exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=3
  
  exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=4
  
  exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=5
  </code></pre>
  
  <p>Yes, that is one call to first get all entities (CheckinTablets) and then call per row to get status for each entity... </p>
  
  <p>So in one call <code>ToList()</code> Entity Framework is making <code>n+1</code> calls to database. This is extremely undesirable, is there a way to disable this behaviour or workaround?</p>
  
  <p><strong>Edit 1:</strong></p>
  
  <p>.Include() is not helping the issue... It still makes n+1 DB requests.</p>
  
  <p><strong>Edit 2 (credit <a href="https://stackoverflow.com/a/50743638/2659905">@jmdon</a>):</strong></p>
  
  <p>Not returning object but simple value make only one call! Of course this doesn’t really help if you don't want to flatten your entity, or if you want multiple values from second table. Never the less good to know!</p>
  
  <pre><code>var query2 = _context.CheckinTablets.Select(ct =&gt; new
  {
      Id = ct.Id,
      DeviceName = ct.Name,
      Status = new CheckinTabletStatus
      {
          Id = ct.CheckinTabletStatuses.OrderByDescending(cts =&gt; cts.TimestampUtc).FirstOrDefault().Id,
          CheckinTabletId = ct.CheckinTabletStatuses.OrderByDescending(cts =&gt; cts.TimestampUtc).FirstOrDefault().CheckinTabletId,
      }
  }).ToList();
  </code></pre>
  
  <p>Produces one call to DB:</p>
  
  <pre><code>SELECT [ct].[intCheckinTabletID] AS [Id0],
     [ct].[strName] AS [DeviceName],
  (
      SELECT TOP (1) [cts].[intCheckinTabletStatusID]
      FROM [tCheckinTabletStatus] AS [cts]
      WHERE [ct].[intCheckinTabletID] = [cts].[intCheckinTabletID]
      ORDER BY [cts].[dtmTimestampUtc] DESC
  ) AS [Id],
  (
      SELECT TOP (1) [cts0].[intCheckinTabletID]
      FROM [tCheckinTabletStatus] AS [cts0]
      WHERE [ct].[intCheckinTabletID] = [cts0].[intCheckinTabletID]
      ORDER BY [cts0].[dtmTimestampUtc] DESC
  ) AS [CheckinTabletId]
  FROM [tCheckinTablet] AS [ct];
  </code></pre>