dotnet
AsSplitQuery generates incorrect sql if Convert(date, ...) is used
Hello,
after migration from Ef6 to EfCore we found one issue. Unfortunately I can't provide the source code, but can explain the issue and show examples in the generated sql.
The issue appear if we use the .Date property from the DateTime object to truncate the time from the database column. We also have some dynamically build groupings and many includes. Therefore wanted to use AsSplitQuery() to reduce count of returned columns. But the generated sql is invalid:
the first query:
SELECT [t].[P0], COUNT(*)
FROM (
SELECT CONVERT(date, [l].[ValueDate]) AS [P0]
FROM [<table>] AS [l]
WHERE EXISTS (
SELECT 1
FROM [<another table>] AS [a]
WHERE (CASE
WHEN [a].[X] = 25 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END & CASE
WHEN [a].[ClientID] = [l].[ClientId] THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END) = CAST(1 AS bit))
) AS [t]
GROUP BY [t].[P0]
ORDER BY [t].[P0]
OFFSET 0 ROWS FETCH NEXT 300 ROWS ONLY
notice the CONVERT(date, [l].[ValueDate]) AS [P0] ?
then the second query:
SELECT [t1].[Currency], [t1].[c], [t0].[P0]
FROM (
SELECT [t].[P0], date AS [c] <--- Here date again
FROM (
SELECT CONVERT(date, [l].[ValueDate]) AS [P0]
FROM [<Table>] AS [l]
WHERE EXISTS (
SELECT 1
FROM [<Another table>] AS [a]
WHERE (CASE
WHEN [a].[CGID] = 25 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END & CASE
WHEN [a].[ClientID] = [l].[ClientId] THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END) = CAST(1 AS bit))
) AS [t]
GROUP BY [t].[P0]
ORDER BY [t].[P0]
OFFSET 0 ROWS FETCH NEXT 300 ROWS ONLY
) AS [t0]
CROSS APPLY (
...
)
And we get an sql server error, that column date doesn't exists. Which is true.
The query without splitting looks like:
SELECT [t0].[P0], [t1].[Currency], [t1].[c], [t0].[c]
FROM (
SELECT [t].[P0], COUNT(*) AS [c]
FROM (
SELECT CONVERT(date, [l].[ValueDate]) AS [P0]
FROM [Tabke] AS [l]
WHERE EXISTS (
SELECT 1
FROM [Another Table] AS [a]
WHERE (CASE
WHEN [a].[CGID] = 25 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END & CASE
WHEN [a].[ClientID] = [l].[ClientId] THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END) = CAST(1 AS bit))
) AS [t]
GROUP BY [t].[P0]
ORDER BY [t].[P0]
OFFSET 0 ROWS FETCH NEXT 300 ROWS ONLY
) AS [t0]
OUTER APPLY (
...
)
it looks like the [P0] column additionally gets date in the name between queries.
Include provider and version information
EF Core version: 7.0.15 Database provider:Microsoft.EntityFrameworkCore.SqlServer Target framework: NET 6.0 Operating system: Windows 10 IDE: rider 2023
@PavelFischerCoupa any chance you can put together a minimal console program that shows this happening? We don't need your actual project, but we need to see actual code that triggers this problem (as always) in order to properly investigate it.
EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.
BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.
