✅ How to handle complex Include in EF Core
I have the following entity:
how could I arrange this to elegantly grab the info from the dbContext?
48 Replies
This is what I'm currently doing:
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
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?
.Select(x => new Patient {
this is projectingoh
Trying it now
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?
yes, but it would have to return an Expression<Func<PlanEntity, Plan>>
would it be weird if i made it like this:
this is the way it looks now:
that's not weird
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?
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:
to call it, I do this:
Seems straight forward to me and I feel it sshows where to go if you need details on the query
that's worse
you should have a static helper class
that's what I do at least
the entity shouldn't have this
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
This particular projection likely belongs in the request handler that needs that data. The entity class represents just what's in the database
ok, it'll be used by a few commands. safe to leave it in a static class like QueryHelper?
lastly, I'm struggling with using the expression within the Select() method:
this is my first time trying to share a raw Expression and googling it isn't easy
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
I think it is an Expression, unless im confusing something here
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
ok, I can't get it to function within the Select method though. I get this error:
I'm using it like this:
I tried but that failed too
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
trying to figure out why the expression isn't working
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
ok, could it be affected by not being part of the parent call?
is x the dbcontext
no, it's the patiententity
or are you trying to use that in a projection?
yes, I'm trying to use it in a projection
that won't work
you have to copy paste the code from it
or patch up the expression manually
right, because in the projection I'm workign with lists and array, not querables or contexts?
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
ok great, ill need a little time to test it
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
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:
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
ohhh
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
oops, wrong one
yep that's gonna make it run on the client
not in the db
even something like that is an issue?
any method
oh
whatsoever
except ones supported by ef core
these are most linq methods
and EF.x methods
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
you should always make dtos
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