efcore: 3.0 regression: Produces invalid SQL for complex query
After upgrading to 3.0.0-rc2 daily builds from 2.2.6, a query previously working is now generating invalid SQL. It’s hard for me to make a small reproducible example since it renders into a complex query with lots of columns and joins (292 lines long…), but this is an outline of the resulting query:
SELECT [t14].[lots of columns..], [t21].[lots of columns..], [t28].[lots of columns..]
FROM (
SELECT TOP(1) [lots of columns..]
FROM [a table]
INNER JOIN [other tables..]
LEFT JOIN [**The Missing Table**] AS [t] ON [...]
--- here is the table joined in as [t]
LEFT JOIN [other tables..]
WHERE [several filters]
ORDER BY [a column]
) AS [t14]
OUTER APPLY (
SELECT [t].[a column], [...]
--- this is the error, it tries to reach [t] in this outer apply
) AS [t21]
OUTER APPLY (
[other things]
) AS [t28]
LEFT JOIN [...]
[...]
ORDER BY [t14].[lots of columns..], [t21].[lots of columns..], [t28].[lots of columns..]
As you can see it tries to reach [t] from within outer apply but it’s declared in another scope. How do I proceed to investigate this so that I can give you better details of this problem? I do not even know where to start debugging this to make EF Core produce a valid SQL. Perhaps this is something you know about and are already working on right now?
Further technical details
EF Core version: 3.0.0-rc2 daily builds Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET Core 3.0 Operating system: Win 10 IDE: Visual Studio 2019 preview
About this issue
- Original URL
- State: closed
- Created 5 years ago
- Reactions: 1
- Comments: 31 (30 by maintainers)
@jcemoller - Received. Give me few days to investigate. It is a massive query. 😆
Thanks @smitpatel , I will try this work-around next week. Just wanted to let you know that I have now worked out all (*) the migration issues - and this week we’re finally live with 3.0 code in production! Preliminary results look promising, we’re seeing on average http request times going down from 70 ms to 54 ms with net/ef core 3.0 compared to 2.2.6! Great work!
* I had to skip a single integration test due to some regression in memory provider, will post issue later when I got time to package a reproducible example.
@smitpatel fantastic work!! can confirm that my old quirky queries are working now without any workarounds running 6.0 preview3. Thanks again!
@joakimriedel - I believe with https://github.com/dotnet/efcore/pull/24491 we have most of the fix around this area done. Regression tests cover a good chunk of scenarios with skip/take/collections/first etc. It would be good if you can test out original query once the fix is included in the product.
@smitpatel let’s close this for now!
@smitpatel with the latest RC1 bits I’m able to run this query without any workaround. The issues you found might still apply however, since I did some refactoring on the dto objects earlier this spring and I think they might now be “unnested” one level less deep now than when I originally posted this. But still, it’s great progress and I’m happy not having to load all items to the client any more on this particular query in our application! 🎉
@smitpatel just sent email with a small repro for triggering the invalid SQL code
Thanks @smitpatel, just verified with 3.1.0-preview3.19554.8 but unfortunately the generated SQL is still invalid. I made a diff of the resulting SQL between preview1 and preview3, and the only visible change was related to null checks which seems a bit relaxed in preview3.
Since you had expression tree for query already, can you get the output of
new ExpressionPrinter().Print(query)? ExpressionPrinter is public class available in EF Core 3 to print out expression trees in more readable format.@smitpatel I’d be happy to share the details, is there any way to send them a bit more private?