C#•3mo ago

✅ How to handle complex Include in EF Core

I have the following entity:

public class PlanEntity : ISortable {
    public int Id { get; set; }
    public int SortPriority { get; set; }
    public string Label { get; set; }
    public string PlanId { get; set; }
    public List<TaskSetEntity> TaskSets { get; set; } = [];

    public DateTime? StartDate { get; set; }
    public DateTime? ReadyToTreatDate { get; set; }
    public List<ApplicationUser> Authors { get; set; } = [];
    public SiteEntity TreatmentSite { get; set; }
    public TechniqueEntity Technique { get; set; }
    public RegionEntity TreatedRegion { get; set; }

    public Plan ToPlan() => new Plan {
        Id = Id,
        Label = Label,
        Authors = Authors.Select(x => x.UserName).ToArray(),
        TaskSets = TaskSets.Select(x => new TaskSet {
            Id = x.Id,
            Label = x.Label,
            Colour = x.Color,
            IsHidden = x.IsHidden,
            Checks = x.Checks.ToChecks()}).ToArray(),
        TreatedSite = TreatmentSite is null ? new Site { Name = string.Empty } : TreatmentSite.ToSite(),
        Technique = Technique is null ? new Technique { Name = string.Empty } : Technique.ToTechnique(),
        TreatedRegion = TreatedRegion is null ? new Region { Name = string.Empty } : TreatedRegion.ToRegion(),
    };
}

public static class PlanExtensions {
    public static Plan[] ToPlans(this List<PlanEntity> entities) => entities
        .OrderBy(x => x.SortPriority)
        .Select(x => x.ToPlan())
        .ToArray();
}

public class PlanEntity : ISortable {
    public int Id { get; set; }
    public int SortPriority { get; set; }
    public string Label { get; set; }
    public string PlanId { get; set; }
    public List<TaskSetEntity> TaskSets { get; set; } = [];

    public DateTime? StartDate { get; set; }
    public DateTime? ReadyToTreatDate { get; set; }
    public List<ApplicationUser> Authors { get; set; } = [];
    public SiteEntity TreatmentSite { get; set; }
    public TechniqueEntity Technique { get; set; }
    public RegionEntity TreatedRegion { get; set; }

    public Plan ToPlan() => new Plan {
        Id = Id,
        Label = Label,
        Authors = Authors.Select(x => x.UserName).ToArray(),
        TaskSets = TaskSets.Select(x => new TaskSet {
            Id = x.Id,
            Label = x.Label,
            Colour = x.Color,
            IsHidden = x.IsHidden,
            Checks = x.Checks.ToChecks()}).ToArray(),
        TreatedSite = TreatmentSite is null ? new Site { Name = string.Empty } : TreatmentSite.ToSite(),
        Technique = Technique is null ? new Technique { Name = string.Empty } : Technique.ToTechnique(),
        TreatedRegion = TreatedRegion is null ? new Region { Name = string.Empty } : TreatedRegion.ToRegion(),
    };
}

public static class PlanExtensions {
    public static Plan[] ToPlans(this List<PlanEntity> entities) => entities
        .OrderBy(x => x.SortPriority)
        .Select(x => x.ToPlan())
        .ToArray();
}

how could I arrange this to elegantly grab the info from the dbContext?

48 Replies

SaiyanslayerOP•3mo ago

This is what I'm currently doing:

    
    public async Task<Result<Patient>> Handle(Request request) {
        using (var context = await _contextFactory.CreateDbContextAsync()) {
            var result = await context
                .PatientsRefactor
                .AsNoTracking()
                .Where(patient => patient.Id == request.id)
                .Include(patient => patient.Plans)
                .ThenInclude(plan => plan.TaskSets)
                .ThenInclude(taskset => taskset.Checks)
                .Include(patient => patient.Plans)
                .ThenInclude(plan => plan.TreatedRegion)
                .Include(patient => patient.Plans)
                .ThenInclude(plan => plan.TreatmentSite)
                .Include(patient => patient.Plans)
                .ThenInclude(plan => plan.Technique)
                .Select(x => new Patient {
                    Id = x.Id,
                    Name = x.Name,
                    IsActive = x.IsActive,
                    Identifier = x.Identifier,
                    Plans = x.Plans.ToPlans()})
                .SingleOrDefaultAsync();

            return result;
        }
    }

    
    public async Task<Result<Patient>> Handle(Request request) {
        using (var context = await _contextFactory.CreateDbContextAsync()) {
            var result = await context
                .PatientsRefactor
                .AsNoTracking()
                .Where(patient => patient.Id == request.id)
                .Include(patient => patient.Plans)
                .ThenInclude(plan => plan.TaskSets)
                .ThenInclude(taskset => taskset.Checks)
                .Include(patient => patient.Plans)
                .ThenInclude(plan => plan.TreatedRegion)
                .Include(patient => patient.Plans)
                .ThenInclude(plan => plan.TreatmentSite)
                .Include(patient => patient.Plans)
                .ThenInclude(plan => plan.Technique)
                .Select(x => new Patient {
                    Id = x.Id,
                    Name = x.Name,
                    IsActive = x.IsActive,
                    Identifier = x.Identifier,
                    Plans = x.Plans.ToPlans()})
                .SingleOrDefaultAsync();

            return result;
        }
    }

Jimmacle•3mo ago

yes, by writing your query so that you project directly to your model instead of loading the whole entity first because you're projecting none of those includes are necesary, EF will load the needed navigations automatically

SaiyanslayerOP•3mo ago

ok, I'm not sure how to project to the model. Is there a link or tutorial on that? or is this something i could do in the model configuration?

Jimmacle•3mo ago

.Select(x => new Patient { this is projecting

SaiyanslayerOP•3mo ago

oh Trying it now

SaiyanslayerOP•3mo ago

SaiyanslayerOP•3mo ago

it grabs the patinet info, but not the info deeper within the hierarchy Plan Id, start date, etc actually have values within the DB wait, could i just use Select again within the call for plans? holy, you can. mind blown. Is there a way to organize it to be easier to read or understand? Could I add a Func<PlanEntity, Plan> as a static method to easily call this?

Jimmacle•3mo ago

yes, but it would have to return an Expression<Func<PlanEntity, Plan>>

SaiyanslayerOP•3mo ago

would it be weird if i made it like this:

await context.Patients.SelectPatient().ToArrayAsync()

await context.Patients.SelectPatient().ToArrayAsync()

this is the way it looks now:

    public async Task<Result<Patient>> Handle(Request request) {
        using (var context = await _contextFactory.CreateDbContextAsync()) {
            var result = await context
                .PatientsRefactor
                .AsNoTracking()
                .Where(patient => patient.Id == request.id)
                .Select(x => new Patient {
                    Id = x.Id,
                    Name = x.Name,
                    IsActive = x.IsActive,
                    Identifier = x.Identifier,
                    Plans = x.Plans.Select(plan => new Plan {
                        Id = plan.Id,
                        Label = plan.Label,
                        PlanId = plan.PlanId,
                        StartDate = plan.StartDate,
                        ReadyToTreatDate = plan.ReadyToTreatDate,
                        TreatedRegion = plan.TreatedRegion.ToRegion(),
                        TreatedSite = plan.TreatmentSite.ToSite(),
                        Technique = plan.Technique.ToTechnique(),
                        TaskSets = plan.TaskSets.Select( taskset => new TaskSet {
                            Id = taskset.Id,
                            Label = taskset.Label,
                            Colour = taskset.Color,
                            IsHidden = taskset.IsHidden,
                            Checks = taskset.Checks.ToChecks()
                        }).ToArray(),
                    }).ToArray()})
                .SingleOrDefaultAsync();

            return result;
        }
    }

    public async Task<Result<Patient>> Handle(Request request) {
        using (var context = await _contextFactory.CreateDbContextAsync()) {
            var result = await context
                .PatientsRefactor
                .AsNoTracking()
                .Where(patient => patient.Id == request.id)
                .Select(x => new Patient {
                    Id = x.Id,
                    Name = x.Name,
                    IsActive = x.IsActive,
                    Identifier = x.Identifier,
                    Plans = x.Plans.Select(plan => new Plan {
                        Id = plan.Id,
                        Label = plan.Label,
                        PlanId = plan.PlanId,
                        StartDate = plan.StartDate,
                        ReadyToTreatDate = plan.ReadyToTreatDate,
                        TreatedRegion = plan.TreatedRegion.ToRegion(),
                        TreatedSite = plan.TreatmentSite.ToSite(),
                        Technique = plan.Technique.ToTechnique(),
                        TaskSets = plan.TaskSets.Select( taskset => new TaskSet {
                            Id = taskset.Id,
                            Label = taskset.Label,
                            Colour = taskset.Color,
                            IsHidden = taskset.IsHidden,
                            Checks = taskset.Checks.ToChecks()
                        }).ToArray(),
                    }).ToArray()})
                .SingleOrDefaultAsync();

            return result;
        }
    }

Anton•3mo ago

that's not weird

SaiyanslayerOP•3mo ago

ok, would it be a better approach for someone reading my code? Or would it be easier to have a SelectQuery property in the Entity class?

 public class PlanEntity : ISortable {
    public int Id { get; set; }
    public int SortPriority { get; set; }
    public string Label { get; set; }
    public string PlanId { get; set; }
    public List<TaskSetEntity> TaskSets { get; set; } = [];

    public DateTime? StartDate { get; set; }
    public DateTime? ReadyToTreatDate { get; set; }
    public List<ApplicationUser> Authors { get; set; } = [];
    public SiteEntity TreatmentSite { get; set; }
    public TechniqueEntity Technique { get; set; }
    public RegionEntity TreatedRegion { get; set; }

    public static Expression<Func<PlanEntity, Plan>> SelectQuery =>
        plan => new Plan {
            Id = plan.Id,
            Label = plan.Label,
            PlanId = plan.PlanId,
            StartDate = plan.StartDate,
            ReadyToTreatDate = plan.ReadyToTreatDate,
            TreatedRegion = plan.TreatedRegion.ToRegion(),
            TreatedSite = plan.TreatmentSite.ToSite(),
            Technique = plan.Technique.ToTechnique(),
            TaskSets = plan.TaskSets.Select(taskset => new TaskSet {
                Id = taskset.Id,
                Label = taskset.Label,
                Colour = taskset.Color,
                IsHidden = taskset.IsHidden,
                Checks = taskset.Checks.ToChecks()
            }).ToArray()
        };

}

 public class PlanEntity : ISortable {
    public int Id { get; set; }
    public int SortPriority { get; set; }
    public string Label { get; set; }
    public string PlanId { get; set; }
    public List<TaskSetEntity> TaskSets { get; set; } = [];

    public DateTime? StartDate { get; set; }
    public DateTime? ReadyToTreatDate { get; set; }
    public List<ApplicationUser> Authors { get; set; } = [];
    public SiteEntity TreatmentSite { get; set; }
    public TechniqueEntity Technique { get; set; }
    public RegionEntity TreatedRegion { get; set; }

    public static Expression<Func<PlanEntity, Plan>> SelectQuery =>
        plan => new Plan {
            Id = plan.Id,
            Label = plan.Label,
            PlanId = plan.PlanId,
            StartDate = plan.StartDate,
            ReadyToTreatDate = plan.ReadyToTreatDate,
            TreatedRegion = plan.TreatedRegion.ToRegion(),
            TreatedSite = plan.TreatmentSite.ToSite(),
            Technique = plan.Technique.ToTechnique(),
            TaskSets = plan.TaskSets.Select(taskset => new TaskSet {
                Id = taskset.Id,
                Label = taskset.Label,
                Colour = taskset.Color,
                IsHidden = taskset.IsHidden,
                Checks = taskset.Checks.ToChecks()
            }).ToArray()
        };

}

Okay, this is what I've decided on: I want high cohesion, meaning I want everything related to PlanEntity easily found beside it. So I decided on this:

public class PlanEntity : ISortable {
    public int Id { get; set; }
    public int SortPriority { get; set; }
    public string Label { get; set; }
    public string PlanId { get; set; }
    public List<TaskSetEntity> TaskSets { get; set; } = [];

    public DateTime? StartDate { get; set; }
    public DateTime? ReadyToTreatDate { get; set; }
    public List<ApplicationUser> Authors { get; set; } = [];
    public SiteEntity TreatmentSite { get; set; }
    public TechniqueEntity Technique { get; set; }
    public RegionEntity TreatedRegion { get; set; }

    ...

    public static Func<PlanEntity, Plan> SelectQuery =>
        plan => new Plan {
            Id = plan.Id,
            Label = plan.Label,
            PlanId = plan.PlanId,
            StartDate = plan.StartDate,
            ReadyToTreatDate = plan.ReadyToTreatDate,
            TreatedRegion = plan.TreatedRegion.ToRegion(),
            TreatedSite = plan.TreatmentSite.ToSite(),
            Technique = plan.Technique.ToTechnique(),
            TaskSets = plan.TaskSets.Select(taskset => new TaskSet {
                Id = taskset.Id,
                Label = taskset.Label,
                Colour = taskset.Color,
                IsHidden = taskset.IsHidden,
                Checks = taskset.Checks.ToChecks()
            }).ToArray()
        };

}

public class PlanEntity : ISortable {
    public int Id { get; set; }
    public int SortPriority { get; set; }
    public string Label { get; set; }
    public string PlanId { get; set; }
    public List<TaskSetEntity> TaskSets { get; set; } = [];

    public DateTime? StartDate { get; set; }
    public DateTime? ReadyToTreatDate { get; set; }
    public List<ApplicationUser> Authors { get; set; } = [];
    public SiteEntity TreatmentSite { get; set; }
    public TechniqueEntity Technique { get; set; }
    public RegionEntity TreatedRegion { get; set; }

    ...

    public static Func<PlanEntity, Plan> SelectQuery =>
        plan => new Plan {
            Id = plan.Id,
            Label = plan.Label,
            PlanId = plan.PlanId,
            StartDate = plan.StartDate,
            ReadyToTreatDate = plan.ReadyToTreatDate,
            TreatedRegion = plan.TreatedRegion.ToRegion(),
            TreatedSite = plan.TreatmentSite.ToSite(),
            Technique = plan.Technique.ToTechnique(),
            TaskSets = plan.TaskSets.Select(taskset => new TaskSet {
                Id = taskset.Id,
                Label = taskset.Label,
                Colour = taskset.Color,
                IsHidden = taskset.IsHidden,
                Checks = taskset.Checks.ToChecks()
            }).ToArray()
        };

}

to call it, I do this:

Plans = x.Plans.Select(PlanEntity.SelectQuery).ToArray()

Plans = x.Plans.Select(PlanEntity.SelectQuery).ToArray()

Seems straight forward to me and I feel it sshows where to go if you need details on the query

Anton•3mo ago

that's worse you should have a static helper class that's what I do at least the entity shouldn't have this

SaiyanslayerOP•3mo ago

ah ok is it worse because it's in the entity class, or because it's harder to follow? trying to understand what to look out for to avoid in the future

Anton•3mo ago

This particular projection likely belongs in the request handler that needs that data. The entity class represents just what's in the database

SaiyanslayerOP•3mo ago

ok, it'll be used by a few commands. safe to leave it in a static class like QueryHelper?

SaiyanslayerOP•3mo ago

lastly, I'm struggling with using the expression within the Select() method:

SaiyanslayerOP•3mo ago

this is my first time trying to share a raw Expression and googling it isn't easy

Anton•3mo ago

I make at least one helper class per sort of business idea, which usually correlates to an entity Expression<Func<...>>, not just func it can be static read-only field it's an immutable syntax tree the compiler creates one from your lambda expression see it in sharplab

SaiyanslayerOP•3mo ago

    public static Expression<Func<PlanEntity, Plan>> SelectPlanQuery =>
        plan => new Plan {
            Id = plan.Id,
            Label = plan.Label,
            PlanId = plan.PlanId,
            StartDate = plan.StartDate,
            ReadyToTreatDate = plan.ReadyToTreatDate,
            TreatedRegion = plan.TreatedRegion.ToRegion(),
            TreatedSite = plan.TreatmentSite.ToSite(),
            Technique = plan.Technique.ToTechnique(),
            TaskSets = plan.TaskSets.Select(taskset => new TaskSet {
                Id = taskset.Id,
                Label = taskset.Label,
                Colour = taskset.Color,
                IsHidden = taskset.IsHidden,
                Checks = taskset.Checks.ToChecks()
            }).ToArray()
        };

    public static Expression<Func<PlanEntity, Plan>> SelectPlanQuery =>
        plan => new Plan {
            Id = plan.Id,
            Label = plan.Label,
            PlanId = plan.PlanId,
            StartDate = plan.StartDate,
            ReadyToTreatDate = plan.ReadyToTreatDate,
            TreatedRegion = plan.TreatedRegion.ToRegion(),
            TreatedSite = plan.TreatmentSite.ToSite(),
            Technique = plan.Technique.ToTechnique(),
            TaskSets = plan.TaskSets.Select(taskset => new TaskSet {
                Id = taskset.Id,
                Label = taskset.Label,
                Colour = taskset.Color,
                IsHidden = taskset.IsHidden,
                Checks = taskset.Checks.ToChecks()
            }).ToArray()
        };

I think it is an Expression, unless im confusing something here

Anton•3mo ago

yeah and you don't have to create it each time last time I checked, the compiler doesn't cache these like it does with static functions when they are converted to delegates

SaiyanslayerOP•3mo ago

ok, I can't get it to function within the Select method though. I get this error:

SaiyanslayerOP•3mo ago

I'm using it like this:

 Plans = x.Plans.Select(QueryHelper.SelectPlanQuery).ToArray(),

 Plans = x.Plans.Select(QueryHelper.SelectPlanQuery).ToArray(),

I tried

 Plans = x.Plans.Select(QueryHelper.SelectPlanQuery.Compile()).ToArray(),

 Plans = x.Plans.Select(QueryHelper.SelectPlanQuery.Compile()).ToArray(),

but that failed too

Anton•3mo ago

actually, whatever you're doing won't work as is you can't call other methods in an expression tree TreatmentSite.ToSite() for example you have to copy paste what's in ToSite in to the expression or you have to patch the expression tree yourself It doesn't matter if ToSite is an extension menthod, or an expression tree of its own that's just not going to work now the issue you're getting currently is that Plan (that variable you're assigning) has a wrong type read the error why would you do this it's an IQueryable it expects an expression tree

SaiyanslayerOP•3mo ago

trying to figure out why the expression isn't working

Anton•3mo ago

not a delegate read the error it says some types didn't match actually no either the expression takes a wrong entity or that you're accessing a dbset for a wrong entity wait

SaiyanslayerOP•3mo ago

ok, could it be affected by not being part of the parent call?

Anton•3mo ago

is x the dbcontext

SaiyanslayerOP•3mo ago

no, it's the patiententity

Anton•3mo ago

or are you trying to use that in a projection?

SaiyanslayerOP•3mo ago

yes, I'm trying to use it in a projection

Anton•3mo ago

that won't work you have to copy paste the code from it or patch up the expression manually

SaiyanslayerOP•3mo ago

right, because in the projection I'm workign with lists and array, not querables or contexts?

Anton•3mo ago

well, both yes and no yes in the sense that the types don't match no in the sense that they could support it if they wanted to they just don't

SaiyanslayerOP•3mo ago

ok great, ill need a little time to test it

Anton•3mo ago

https://github.com/scottksmith95/LINQKit

GitHub

GitHub - scottksmith95/LINQKit: LINQKit is a free set of extensions...

LINQKit is a free set of extensions for LINQ to SQL and Entity Framework power users. - scottksmith95/LINQKit

SaiyanslayerOP•3mo ago

lol, I was reading it while you responded above. I'm trying to avoid libraries like that for now: I'm learning and want to do it "the hard way" to know why the libraries are useful then i can use them to make life easier Ok, so it's working. I created a static QueryHelper class I intend to put these reusable queries into:

public static class QueryHelper {

    public static Expression<Func<PatientEntity, Patient>> SelectPatient =>
        patient => new Patient {
            Id = patient.Id,
            Name = patient.Name,
            IsActive = patient.IsActive,
            Identifier = patient.Identifier,
            Plans = patient.Plans.Select(plan => new Plan {
                Id = plan.Id,
                Label = plan.Label,
                PlanId = plan.PlanId,
                StartDate = plan.StartDate,
                ReadyToTreatDate = plan.ReadyToTreatDate,
                TreatedRegion = plan.TreatedRegion.ToRegion(),
                TreatedSite = plan.TreatmentSite.ToSite(),
                Technique = plan.Technique.ToTechnique(),
                Authors = plan.Authors.Select( author => author.Name ).ToArray(),
                TaskSets = plan.TaskSets.Select(taskset => new TaskSet {
                    Id = taskset.Id,
                    Label = taskset.Label,
                    Colour = taskset.Color,
                    IsHidden = taskset.IsHidden,
                    Checks = taskset.Checks.ToChecks()
                }).ToArray(),
            }).ToArray()
    };
}

public static class QueryHelper {

    public static Expression<Func<PatientEntity, Patient>> SelectPatient =>
        patient => new Patient {
            Id = patient.Id,
            Name = patient.Name,
            IsActive = patient.IsActive,
            Identifier = patient.Identifier,
            Plans = patient.Plans.Select(plan => new Plan {
                Id = plan.Id,
                Label = plan.Label,
                PlanId = plan.PlanId,
                StartDate = plan.StartDate,
                ReadyToTreatDate = plan.ReadyToTreatDate,
                TreatedRegion = plan.TreatedRegion.ToRegion(),
                TreatedSite = plan.TreatmentSite.ToSite(),
                Technique = plan.Technique.ToTechnique(),
                Authors = plan.Authors.Select( author => author.Name ).ToArray(),
                TaskSets = plan.TaskSets.Select(taskset => new TaskSet {
                    Id = taskset.Id,
                    Label = taskset.Label,
                    Colour = taskset.Color,
                    IsHidden = taskset.IsHidden,
                    Checks = taskset.Checks.ToChecks()
                }).ToArray(),
            }).ToArray()
    };
}

Anton•3mo ago

look at the generated query you're still using ToSite that's going to work, seemingly but it will actually execute on the client you CANNOT use other methods in these trees

SaiyanslayerOP•3mo ago

ohhh

Anton•3mo ago

it does not affect the query it produces it will make a join and pull everything from the db then call the methods on the client for each entiry

SaiyanslayerOP•3mo ago

    public static SiteEntity FromSite(Site site) 
        => new SiteEntity { Id = site.Id, Name = site.Name };

    public static SiteEntity FromSite(Site site) 
        => new SiteEntity { Id = site.Id, Name = site.Name };

oops, wrong one

    public Site ToSite() 
        => new Site { Id = Id, Name = Name };

    public Site ToSite() 
        => new Site { Id = Id, Name = Name };

Anton•3mo ago

yep that's gonna make it run on the client not in the db

SaiyanslayerOP•3mo ago

even something like that is an issue?

Anton•3mo ago

any method

SaiyanslayerOP•3mo ago

Anton•3mo ago

whatsoever except ones supported by ef core these are most linq methods and EF.x methods

SaiyanslayerOP•3mo ago

kk crap, one of my classes needs to convert from json to an object using a method i think i may have to bite the bullet and make a DTO

Anton•3mo ago

you should always make dtos

SaiyanslayerOP•3mo ago

I was prototyping, so i hoped i could skip it until we finalized the design lesson learned lol anyways, thank you so much for your help. I learned a lot the past hour thanks to you In summation: - using Select() on the parent query allows projection and can entirely avoid Include() and ThenInclude() - avoid using methods within the query, or it'll grab all the entities and run them through the client instead - nested Select() within a Select() are not the same methods and can't nest a static Expression - If you want to reuse the Select query, a static Expression<Func<TSource, TResult>> will work - DTOs should always be used

Gaming

Programming

✅ How to handle complex Include in EF Core