Is it possible to rewrite complex EF Core ".Include" calls to "Join" calls?

user_6877104 0
Viewed 160

I have two EF Core entity models named Service and ServiceBranch:

public class Service
{
    public Guid ID { get; set; }
    public Guid? ParentServiceID { get; set; } // foreign key to the same table
    public string Name { get; set; }
    public DateTime? DateDeleted { get; set; }

    public virtual ICollection<Service> InverseParent { get; set; } // navigation property
    public virtual ICollection<ServiceBranch> ServiceBranches { get; set; } // navigation property
}

public class ServiceBranch
{
    public Guid ID { get; set; }
    public Guid ServiceID { get; set; } // foreign key
    public string Name { get; set; }
    public DateTime? DateDeleted { get; set; }

    public virtual Service Service { get; set; } // navigation property
}

For simplicity, let's say parent Service can have only one level of children Services. both parent and children services are being referenced by a lot of service branches.

I want to update DateDeleted field for a specific Service ID and update DateDeleted in all other rows that are referencing it (if that field doesn't already have a value).

Currently my call to bring all the required entities looks like this:

var serviceEntity = _context.Set<Service>().Where(x => x.ID == neededID && x.DateDeleted == null)
                .Include(x => x.ServiceBranches)
                .Include(x => x.InverseParent)
                    .ThenInclude(InverseParent => InverseParent.ServiceBranches)
                .FirstOrDefault();

I use Where clause for the main entity but unfortunately I cannot use Where inside .Includes since we haven't upgraded to EF Core 5.0 yet (which supports filtering inside .Includes).

Thus I bring too many unneeded rows (where DateDeleted already has a value) from the database and then filter them within a foreach loop by comparing that field to null:

if (myentity.DateDeleted == null) myentity.DateDeleted = DateTime.Now;

I want to rewrite my query so that I use "join" instead of ".Include", something like this:

var serviceEntity = from service in _context.Set<Service>()
                where service.ID == neededID && service.DateDeleted == null
                join branches in _context.Set<ServiceBranch>() on service.ID equals branches.ServiceID
                where branches.DateDeleted == null // filter which I cannot use with .Include-s
                let // whatever, can't make it work
                select // etc

I can't get my head around how to rewrite it. Or if it is even possible to do it with joins.

entity-framework-core asp.net-core linq c#
2 Answers

In this particular case you don't need joins, but flat list containing the desired Service entity plus all its children. In general this requires recursive query (not supported by LINQ / EF Core), but for single level it is a matter of simple filter like

service.ID == neededID || service.ParentServiceID == neededID

Then you could apply the additional DateDeleted criteria. Finally, to get the related ServiceBranch entities with DateDeleted filter, instead of Include just use projection (Select).

And when processing the returned data, use the projected entities and ignore their navigation properties. e.g.

var itemsToUpdate = _context.Set<Service>()
    .Where(service => (service.ID == neededID || service.ParentServiceID == neededID)
        && service.DateDeleted == null)
    .Select(service => new
    {
        Service = service,
        ServiceBrances = service.ServiceBranches
            .Where(branch => branch.DateDeleted == null),
    });

foreach (var item in itemsToUpdate)
{
    item.Service.DateDeleted = DateTime.Now;
    foreach (var branch in item.ServiceBrances)
        branch.DateDeleted =  DateTime.Now;
}
user_52025630

Include puts the loaded objects directly on your Entity Framework model, and that model is really supposed to directly represent what's in the database. If you're wanting a filtered collection, I'd recommend coming up with a different model that represents what you're trying to represent, and then using Select to project to that model. Something like this:

var serviceEntity = _context.Set<Service>()
    .Where(x => x.ID == neededID && x.DateDeleted == null)
    .Select(x => new // you can make this a named type if necessary
        {
           Service = x,
           ActiveBranches = x.ServiceBranches.Where(b => b.DateDeleted == null),
           InverseParent,
           InverseParentServiceBranches = x.InverseParent.ServiceBranches
        })
    .FirstOrDefault();
user_1209550
Related