EF Core Catesian Explosion with Include() - is there any easy work around for database first approach?

Question

Hi All,

I'm working on a database first approach with EF Core 3.1 and 5 pre-release using EF Power Tools to build my entities. 

I've hit a 'cartesian explosion' issue when I query the parent data with the .Include(x => x.ChildRecords) code and I wondered if there is a clean way to avoid this while still retaining the parent child relationships as per the generated database first code.  Currently the only way I know how to get around this is to manually delete the parent object from the child entity class.  I'm struggling to find good documentation on this as the database first approach appears to be an unpopular choice (I'm coming from a database background so I'm pro designing a database within SSMS itself and building the app on top of that).

Thanks in advance,

Sandy

Answer 1

Hi OZIT,
EFCore 3.0 changed the query generated by using .Include() which caused Cartesian explosion problem.
Since version 3.0.0, each Include will cause an additional JOIN to be added to SQL queries produced by relational providers, whereas previous versions generated additional SQL queries. 
This can significantly change the performance of your queries. So in order to avoid the Cartesian explosion problem, LINQ queries with an exceedingly high number of Include operators may need to be broken down into multiple separate LINQ queries.
More discusses in these links you can refer to.
20x slowdown in gigantic query after updating to EF Core 3 compared to 2.2
Significant Query Slowdown When Using Multiple Joins Due To Changes In 3.0
Hope these are helpful to you.
Best Regards,
Daniel Zhang

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

 I'm struggling to find good documentation on this as the database first approach appears to be an unpopular choice (I'm coming from a database background so I'm pro designing a database within SSMS itself and building the app on top of that).

Developers go with the code first becuase all the tutorials teach it. With code first, the developer has to bang the code out manually, and he or she had better know what he or she is doing but most don't.

With DB first/reverse engineering,  a program whether it is the Power Tool or scaffolding the code in generated and accurate. 

Answer 3

Thanks for the replies so far.  I'm a fan of database first because the database also gets used for reporting etc. and I like being fully in charge of how it's created.  Maybe I'm missing something here with code first though .... possibly not the real issue of this problem.

I seem to be experiencing the Cartesian explosion with only one database join between the parent and the child - i.e. one Include only in the linq query.  The parent and child have a one to many relationship and so the parent entity gets created with an array of children object link and the child gets created with one parent object link.  The json query results have the parent and children, then the parent linked to the children, then the children linked to the parent and so on down the tree repeating the data.  

Has anyone else experienced this and is there a way in linq to stop it going further than the first child level in the tree?  

Thanks again.

Answer 4

This Cartesian is in effect when you do a Linq Join as opposed to an Include function?

Answer 5

Yes it's included in a Linq Join as well as the Include.  Ideally the relationship between parent and child would be just one way or EF Core would allow a way to just produce one level in the tree of results - i.e. just the parent and immediate children.  

Answer 6

Hi OZIT,
In order to avoid the Cartesian explosion problem, the general solution is to split a LINQ query into multiple queries.
So you can try to  query the parent data and child data separately.
And you can also try to Timovzl's suggestion without spliting a LINQ query.
Best Regards,
Daniel Zhang

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 7

One way to get around it would be to read parent and child tables individually.

Answer 8

Thanks for the responses.

I think I'll have to split the Linq query up - getting parent and child data separately.  It's a shame EF Core is behaving this way - I feel that's an area for possible improvement.... maybe in EF Core 6?

I appreciate everyone's time in responding.

Answer 9

I'm just testing this and it's getting ridiculous - I have to choose to either write separate line queries for all entities that have parents or basically recreate the entities with view models without the reverse parent-child relationships.

E.g. currently I'm querying a person and getting their addresses.  This works ok by querying the separate parent person record and then the children addresses.  BUT the issue then occurs again because the addresses have an address type classification child and so I need to query out the address type separately in order for it not to also pull back the address which pulls back the address type which pulls back the address etc...........

Surely there has to be a better way.  Will I need to hand write my SQL queries in order for it to be clean and efficient?!  Will I need to manually update my children entities generated automatically with EF Core Power Tools to remove the return reference to the parent?  Or will I need to hand-write my entities instead of auto-generating them?  

I'll have to put my thinking cap on some more.

Answer 10

For anyone else facing the same issue I thought I'd write the result of my journey here.  

I've created view models with relationships from the parent to the child and not the other way around.  I use Automapper to map between the entities and the view models.  This removes the complexity of the reversible parent-child relationships on the auto-generated entities while still allowing me to utilise awesome tools like EF Core Power Tools and save a lot of time.

The view models are used in the web app and so it's a win win workaround.

Answer 11

I've created view models with relationships from the parent to the child and not the other way around.  

View models do not have relationships. A view model is strong typed to a view and allows data to be displayed on a view. And maybe, you are being lead to the classic mistake of viewing the EF persistence model as a view model that is causing all this problem for you.

https://www.dotnettricks.com/learn/mvc/understanding-viewmodel-in-aspnet-mvc

https://deviq.com/kinds-of-models/

I view the view model like I view the domain model and neither one of them are the persistence model.

https://blog.sapiensworks.com/post/2012/04/07/Just-Stop-It!-The-Domain-Model-Is-Not-The-Persistence-Model.aspx