🚀 EF Core 8: How to optimize a query?

[@johnnyvlee]

using Select to query a subset of columns is what I'd love to do, but my query is quite complex with a number of layers of Include/ThenInclude. When I try to use this technique just on the top level table I get runtime errors with the data in joined tables. For example, the model generated database-first has both a Foreignkeyid property and Childentity property. If I select the Foreignkeyid field, it does not know how to bring in the Childentity model.
In your example you have a Dto record which I presume is a more flattened representation of the Employee model. You reference a child entity property x.User.UserName. In my case I want the x.User. And probably some child entity of x.User as well.
I haven't seen great examples of optimizing columns selected by EF queries (or in my case EF Core). There are only really simple cases being demoed.

[@sa-es-ir]

You got the main idea about having required columns only, if you have time to create a simple repo about what you need then we can fix it together, I'd would say it is easy to tell EF core to generate what we want.

[@johnnyvlee]

@@sa-es-ir Thanks for the reply. I was able to work out the syntax to get what I want in the long run. However, I am now realizing that if you try to include more than one child MANY relationship, performance tanks noticeably. I am aware of the Split() function, but I've come to the conclusion it's just better for me to run multiple queries rather than one or two big complex queries.

[@samedsakman9955]

How did you get the releated entities without using .include()?

[@sa-es-ir]

Well EF will analyse your Linq before translating to SQL query and since we have a Select method that getting property from navigation props like 'x.Company.Name', EF know we have relation between Employee and Company and will join them for us.

[@ala-eddinebejaoui2493]

Pretty obvious optimisations but like the video !
It doesn't hurt to review basics Time to Time !!
Thanks

[@sa-es-ir]

Thanks, believe me these simple steps are the main ideas of optimization, the rest like compiled queries or raw sql or others are just very specific options and no needed most of the times. Any specific trick you can suggest?

[@ala-eddinebejaoui2493]

@@sa-es-ir I believe you ahaha ! And that's why when i see some people advocating for the use of compiled queries and for ultra specific optimisations like removing thé query tracking and all this kind of staff when in the first time thé query they wrote has no optimizations and 500k round trips for a get , i tend to vidéos like yours today to remember people that it's absurd to micro-optimize a query that is not correctly formed in the first place.
That the same debate for people arguing about LINQ microseconds perfs loss ...
Like your content whatsoever !

[@sa-es-ir]

@@ala-eddinebejaoui2493 oh yeah those are just micro-optimization and isn't worth to add extra complexity to the code, just to point here AsNoTracking doesn't work for my case as I'm using a DTO to select and AsNoTracking is only an options that you're using Entities so that EF know what not to track;)
Appreciate the support🙏

[@ala-eddinebejaoui2493]

@@sa-es-ir yeah i know, since you are projecting to a DTO there's no tracking involved of course :)
I was talking about LinkedIn ppl in general ;)
I'll keep an eye on your content :)

[@Add0w]

I think it will be better if we put .take(2) before .select(...)

[@sa-es-ir]

I think there is no difference and EF will do the same but not sure. Need to check the generated query