Multiple include in Entity Framework queries

Question

Our project is is already in production and working on enhancements. Since we are seeing multiple performance issues. We identified this is because if having multiple include statements. We have understand that we need to break into multiple queries. Since application is vast. It is time consuming.

We can't use Entity Framework Plus which has include optimized.

Is there any custom extension method which breaks the query into multiple queries?

Answer 1

You can transform your query into a database stored procedure and have Entity Framework call the stored procedure. Most of the time (especially with multiple includes), the generated SQL is not efficient.

You can start by capturing the generated SQL and tuning it into an efficient stored procedure. If you are using EF Core, you can use the FromSqlRaw() function to call the stored procedure. You can use the UI for older Entity Framework versions.

Good luck!

Answer 2

We had the exact same problem with our project while upgrading from Entity Framework Core 2.2 to 3.1. The problem is that as of Entity Framework Core 3.1, Include statements no longer split into separate queries.

UPDATE: As per comments, Entity Framework Core 5 has split queries. This is the better answer.

Original Answer

Other than custom stored procedures (as per the other answer) and Entity Framework Core Plus (Include Optimised documented here, with an example here), the only other thing I countered was to split the query into individual load statements. I did not yet try this myself, because we decided to go the Entity Framework Core Plus route (because we did not want to completely rewrite our queries), but you can use this example as a demonstration.

I found this link while working through the Entity Framework Core 3 breaking changes related to this issue where they linked to this comment as part of a larger discussion on this issue.