title: Query all records including a count of related records
body: <p>In an ASP.NET Core application with Entity Framework Core, a list of records is displayed. Therefore, all entities of a collection are fetched and some of their properties displayed in a table.</p>
  
  <p>Additionally, there are related entities connected through foreign keys. For some of these, the total count of objects should also be displayed.</p>
  
  <p>For example, there are the following tables (simplified):</p>
  
  <ul>
  <li>Location (Id, Name, Address)</li>
  <li>Device (Id, Name, Comment, LocationId)</li>
  </ul>
  
  <p>Only locations (main entity) and devices (related entity) are shown here, but there are more types besides devices. All devices in the database are assigned to one location. Foreign keys and navigation properties exist in the entity classes, as well as non-mapped count properties (see last example).</p>
  
  <p>The desired result is this:</p>
  
  <ul>
  <li>Location Id 1, Name 1, <strong>15</strong> devices in this location</li>
  <li>Location Id 2, Name 2, <strong>6</strong> devices in this location</li>
  <li>Location Id 3, Name 3, <strong>0</strong> devices in this location</li>
  </ul>
  
  <p>I could just fetch the locations, ordered by name, first, and then fetch the count of devices for each location in separate database queries. That may result in a large number of short queries.</p>
  
  <pre><code>var locations = dbContext.Locations
      .OrderBy(l =&gt; l.Name)
      .ToList();
  foreach (var location in locations)
  {
      int count = dbContext.Devices.Where(d =&gt; d.LocationId == location.Id).Count();
  }
  </code></pre>
  
  <p>I could also fetch all locations with <code>Include(location =&gt; location.Devices)</code> and then just <code>location.Devices.Count</code> to get their count, but that involves fetching all the details of all devices, only to use their total count in the end.</p>
  
  <pre><code>var locations = dbContext.Locations
      .Include(l =&gt; l.Devices)
      .OrderBy(l =&gt; l.Name)
      .ToList();
  foreach (var location in locations)
  {
      int count = location.Devices.Count;
  }
  </code></pre>
  
  <p>I could first fetch all locations, as usual, and then build a single query that collects the device counts of all locations. Then I have to merge these result sets manually by walking through one list and finding the corresponding entry in the other which may lead to a lot of memory searching.</p>
  
  <pre><code>var locations = dbContext.Locations
      .OrderBy(l =&gt; l.Name)
      .ToList();
  var deviceCounts = dbContext.Locations
      .Select(l =&gt; new
      {
          l.Id,
          l.Devices.Count
      });
  foreach (var c in deviceCounts)
  {
      locations.First(l =&gt; l.Id == c.Id).DeviceCount = c.Count;
  }
  </code></pre>
  
  <p>I'm looking for a query to fetch the locations, ordered by name, together with the device count for each location. The result must be a list of my entity types, e.g. <code>List&lt;Location&gt;</code>. Is there a simple or efficient way to achieve this with EF Core?</p>