CODEWITHSUNDEEP
c#.netentity-framework-core
ygoe
ygoe

Reputation: 20414

Query all records including a count of related records

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.

Additionally, there are related entities connected through foreign keys. For some of these, the total count of objects should also be displayed.

For example, there are the following tables (simplified):

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).

The desired result is this:

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.

var locations = dbContext.Locations
    .OrderBy(l => l.Name)
    .ToList();
foreach (var location in locations)
{
    int count = dbContext.Devices.Where(d => d.LocationId == location.Id).Count();
}

I could also fetch all locations with Include(location => location.Devices) and then just location.Devices.Count to get their count, but that involves fetching all the details of all devices, only to use their total count in the end.

var locations = dbContext.Locations
    .Include(l => l.Devices)
    .OrderBy(l => l.Name)
    .ToList();
foreach (var location in locations)
{
    int count = location.Devices.Count;
}

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.

var locations = dbContext.Locations
    .OrderBy(l => l.Name)
    .ToList();
var deviceCounts = dbContext.Locations
    .Select(l => new
    {
        l.Id,
        l.Devices.Count
    });
foreach (var c in deviceCounts)
{
    locations.First(l => l.Id == c.Id).DeviceCount = c.Count;
}

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. List<Location>. Is there a simple or efficient way to achieve this with EF Core?

Upvotes: 1

Views: 3348

Answers (2)

Felipe Oriani
Felipe Oriani

Reputation: 38638

First, create a ViewModel class and define the properties you want to show on the list. The ViewModel will provide just the data you need, not the entire entity. For sample:

public class DeviceViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int DeviceCount { get; set; }
}

And then, perform the query you want returning a list of ViewModel hydrated with data from the entity defining the order you want before the Select method.

var devices = dbContext.Locations
                       .Include(l => l.Devices)                           
                       .OrderBy(x => x.Name)          
                       .Select(l => new DeviceViewModel()
                       {
                          Id = l.Id,
                          Name = l.Name,
                          DevicesCount = l.Devices.Count()
                       })
                       .ToList();

Remember to call ToList() to force the ORM to execute a query on the database.

Upvotes: 1

Nagashree Hs
Nagashree Hs

Reputation: 853

You need to select values needed.

var locations = dbContext.Locations
.Include(l => l.Devices)
.OrderBy(l => l.Name)
.Select( l => new {
             LocationId = l.Id,
             LocationName = l.Name,
             DeviceCount = l.Devices.Count()
        })
.ToList();

Upvotes: 1

Related Questions