Entity Framework Core created a query that does not return
(1) By colonel_giuseppe on 2021-05-11 21:28:12 [link]
After refactoring some Entity Framework Core queries, my application stopped responding. I traced the issue to `sqlite3.dll`, which executes a generates SQL statement that - apparently - does not return. I have copied the query and created a small DB that shows the issue. The database file can be downloaded [from here](https://budgetmagicwand-sto.energycdn.com/dl/UgWid3ofPxwEWk25-ZcpQw/1621372871/846603926/609af5478c6980.74164842/Problem.db). The statement generated by EF Core is rather large, [so I put it in a pastebin](https://pastebin.com/v0hDN3NY). When I try to execute the statement in the sqlite browser, htop states it writes a lot to disk, then runs out of memory after a minute or so. Is this my fault for forcing EF Core to generate a too complex query or is this a real problem?
(2) By Larry Brasfield (larrybr) on 2021-05-12 00:52:07 in reply to 1 [link]
> After refactoring some Entity Framework Core queries, my application stopped responding. That's no surprise considering the number and structure of joins in that query. It is a 13-way join between subqueries, many of which are 3 to 9 way joins. What I would like to know is: What did the query look like before you refactored it? And, did it "return" before your rewrite? > The database file can be downloaded from here. No, it cannot, at least not by those without the right credentials. > The statement generated by EF Core is rather large, so I put it in a pastebin. Is that the original EF-generated query? Or is it your rewrite? > When I try to execute the statement in the sqlite browser, htop states it writes a lot to disk, then runs out of memory after a minute or so. It might do better with some indices to eliminate the need for materialized intermediate results. > Is this my fault for forcing EF Core to generate a too complex query Probably. > or is this a real problem? Yes. It is either your fault for using EF Core way beyond its designed purpose or it is a failure of EF Core to perform in a legitimate use case. It is not a SQLite problem. It would be interesting to see how your "problem" fares with a more heavy-duty DBMS. Have you tried that?
(3) By colonel_giuseppe on 2021-05-12 08:16:39 in reply to 2
Apologies, I thought the download link would be available for anyone, I put the database file on [Google Drive](https://drive.google.com/file/d/1s3XTGVaJJdsPy6Yl9qI_o195LPb7QHku/view?usp=sharing) now. > And, did it "return" before your rewrite? Yes it did! I went back and checked out the generated SQL that worked, and it turns out EF split the query up into multiple separate queries that are much much smaller. I also found a fix for the problem: I explicitly added [`.AsSplitQuery()`](https://docs.microsoft.com/en-us/ef/core/querying/single-split-queries) to the `IQueryable` and now the data are fetched in no time again. > Is that the original EF-generated query? Or is it your rewrite? I only fixed one `Id` on top that was passed in as a parameter and added a default return for a `Name` column I removed. Basically I trimmed the database as much as possible while still allowing the query to work. > It would be interesting to see how your "problem" fares with a more heavy-duty DBMS. Have you tried that? I have the same database running on Sql Server, the generated query however uses `APPLY` and `UNION` operations there so it's not really comparable. > Yes. It is either your fault for using EF Core way beyond its designed purpose After reading through your post I think that I should change my code to use multiple separate calls instead of relying on EF to handle one large query. The `.AsSplitQuery()` works for now, but I will definitely change that. Thank you for taking the time to write this up!