Return parent entities based on (and including) child entities

I am trying to retrieve a list of entities based on properties of their child entities - and include those same child entities. I am using EntityFramework Core 3.1, although I am happy to upgrade to 5.x if there've been any changes that would solve this for me. I have also not explored EntityFramework much beyond some very basic CRUD boilerplate until now, so I am not sure if this is something more LINQ-oriented or specific to EF (Core). The below is a heavily simplified example of a method in a project that I will be using to ultimately return data to a consumer of my API.

A point of interest (POI) has a number of historical records (History). POI has a List<History> and a History has a PointID which is used by EF Core to populate the POI's List<History>.

Here is how I would get all the POIs and their histories, where a point was first registered since a certain date (using a nullable date parameter for this method)

var result = _context.POIs
            .Where(point => (registeredSince == null || point.RegisteredAt >= registeredSince))
            .Include(point => point.Histories)
            .ToList();

However, my question is.. how would I then get only POIs based on an attribute within the History of that POI (and include those same History records?) Or, to use an example; I want to return only POIs that have History records with an areaId == 5 (and include those in the results)

One way, without hugely in-depth EF knowledge, would be:

  • First run a query to return History entities where history.areaId == 5 and only select history.PointId
  • Second query would be to get all POIs where id is in the returned PointId list above
  • ..including History where history.areaId == 5 (a duplication)

However, I would be running part of this twice, which seems inefficient. Basically, could I efficiently use LINQ/EF to get all POIs where history.areaId == 5 (and then only include those History records with an areaId of 5)? Would I have to write something that unavoidably loads all POI and their History records, before I am able to narrow the results down, or is that something EF can happily do?

2 answers

  • answered 2021-03-08 17:13 Neil W

    You should be able to use the following:

    var result = _context.POIs
        .Include(poi => poi.Histories)
        // Enumerate linked Histories & get the areaId from each into a list)
        // ... then see if that list contains the areaID we're looking for.
        .Where(poi => poi.Histories.Select(h => h.areaId).Contains(areaIdParam))
        .ToList();
    

  • answered 2021-03-08 19:33 atiyar

    You can use Filtered include introduced in EF Core 5.x, to query like -

    var result = _context.POIs
                .Include(p => p.Histories.Where(h => h.areaId == 5))            
                .ToList();
    

    This will return a list of POI where each will contain only histories for which areaId == 5.

    If you want to filter POI too, then use like -

    var result = _context.POIs
                .Include(p => p.Histories.Where(h => h.areaId == 5))
                .Where(p => (registeredSince == null || p.RegisteredAt >= registeredSince))
                .ToList();