SQLite Forum

Entity Framework Core created a query that does not return
Login

Entity Framework Core created a query that does not return

(1) By colonel_giuseppe on 2021-05-11 21:28:12 [link] [source]

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.

The statement generated by EF Core is rather large, so I put it in a pastebin.

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] [source]

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 [source]

Apologies, I thought the download link would be available for anyone, I put the database file on Google Drive 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() 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!