dotnet
An issue with query filtering and aggregation, after upgrading from 6.0.0 to 6.0.16
Hello,
We recently upgraded Microsoft.EntitiyFrameworkCore.SqlServer from 6.0.0 to 6.0.2, and it seems like this caused a regression n an unchanged part of our code.
// The EventTable decleration
public DbSet<EventTableEntity> EventTable { get; set; }
// ---------------------------------------------------------------------
// When the table context (`ctx` below) is created, it is done with:
modelBuilder.Entity<EventTableEntity>()
.HasQueryFilter(ConvertFilterExpression(this.IsEventReadableExpression<EventScope>(), typeof(EventTableEntity)));
// IsEventReadableExpression is intended to filter EventTable entities based on user permissions and roles.
// ---------------------------------------------------------------------
// The failing code
await someFilterFunction(ctx.EventTable
.AsNoTracking()
.Where(event => event.FirstSeen >= date.AddHours(-1 * lookbackInHours) && event.FirstSeen < date))
.GroupBy(event => (EF.Functions.DateDiffDay(lookbackTimestamp, event.FirstSeen) * MinutesInDay)
+ (event.FirstSeen.Value.Hour * MinutesInHour)
+ ((event.FirstSeen.Value.Minute / 10) * 10))
.Select(g => new { key = g.Key, count = g.Count() })
.ToListAsync();
I want to emphasize that we didn’t change the code at all, only upgraded nugets. From some reason, it seems EF doesn’t succeed to deal with the count aggregation as it did before.
Below is the succeeding SQL query that was the result of the code running with version 6.0.0:
DECLARE @__lookbackTimestamp_3 datetime2 = '2023-07-10T07:40:00.000';
DECLARE @__ef_filter__p_0 smallint = 32;
DECLARE @__ef_filter__p_1 bit = 0;
DECLARE @__ef_filter__ProductsUserIsScopedIn_3 smallint = 32;
DECLARE @__AddHours_0 datetime2 = '2023-07-10T07:43:17.318';
DECLARE @__date_1 datetime2 = '2023-07-11T07:43:17.318';
exec sys.sp_set_session_context @key = N'IsExposedToAllProducts', @value = 1;
exec sys.sp_set_session_context @key = N'ProductContext', @value = 'Mtp';
SELECT ((DATEDIFF(day, @__lookbackTimestamp_3, [r].[FirstSeen]) * 1440) + (DATEPART(hour, [r].[FirstSeen]) * 60)) + ((DATEPART(minute, [r].[FirstSeen]) / 10) * 10) AS [key], COUNT(*) AS [count]
FROM [Tenant1].[EventTable] AS [r]
WHERE (((@__ef_filter__p_0 & [r].[BitwiseProducts]) = [r].[BitwiseProducts]) AND ((((@__ef_filter__p_1 = CAST(1 AS bit)) OR [r].[ServiceSource] NOT IN (128, 16)) OR (([r].[BitwiseProducts] & @__ef_filter__ProductsUserIsScopedIn_3) = CAST(0 AS smallint))) OR (
SELECT TOP(1) [a].[EventId]
FROM [Tenant1].[EventScopes] AS [a]
WHERE ([r].[EventId] = [a].[EventId]) AND [a].[EvidenceId] IS NULL
GROUP BY [a].[EventId]
HAVING COUNT(DISTINCT (CASE
WHEN CASE
WHEN ([a].[ScopeType] = CAST(1 AS tinyint)) AND ([a].[ScopeValue] <> 'Dlp_AdminUnits_1') THEN CAST(0 AS tinyint)
ELSE [a].[ScopeType]
END <> CAST(0 AS tinyint) THEN CASE
WHEN ([a].[ScopeType] = CAST(1 AS tinyint)) AND ([a].[ScopeValue] <> 'Dlp_AdminUnits_1') THEN CAST(0 AS tinyint)
ELSE [a].[ScopeType]
END
END)) = COUNT(DISTINCT ([a].[ScopeType]))) IS NOT NULL)) AND (([r].[FirstSeen] >= @__AddHours_0) AND ([r].[FirstSeen] < @__date_1))
GROUP BY ((DATEDIFF(day, @__lookbackTimestamp_3, [r].[FirstSeen]) * 1440) + (DATEPART(hour, [r].[FirstSeen]) * 60)) + ((DATEPART(minute, [r].[FirstSeen]) / 10) * 10)
And there's the failing SQL query that was the result of the code running with version 6.0.2:
DECLARE @__lookbackTimestamp_3 datetime2 = '2023-07-10T10:20:00.000';
DECLARE @__ef_filter__p_0 smallint = 32;
DECLARE @__ef_filter__p_1 bit = 0;
DECLARE @__ef_filter__ProductsUserIsScopedIn_3 smallint = 32;
DECLARE @__AddHours_0 datetime2 = '2023-07-10T10:25:23.694';
DECLARE @__date_1 datetime2 = '2023-07-11T10:25:23.694';
exec sys.sp_set_session_context @key = N'IsExposedToAllProducts', @value = 1;
exec sys.sp_set_session_context @key = N'ProductContext', @value = 'Mtp';
SELECT ((DATEDIFF(day, @__lookbackTimestamp_3, [r].[FirstSeen]) * 1440) + (DATEPART(hour, [r].[FirstSeen]) * 60)) + ((DATEPART(minute, [r].[FirstSeen]) / 10) * 10) AS [key], (
SELECT COUNT(*)
FROM [Tenant1].[EventTable] AS [r0]
WHERE ((((@__ef_filter__p_0 & [r0].[BitwiseProducts]) = [r0].[BitwiseProducts]) AND ((((@__ef_filter__p_1 = CAST(1 AS bit)) OR [r0].[ServiceSource] NOT IN (128, 16)) OR (([r0].[BitwiseProducts] & @__ef_filter__ProductsUserIsScopedIn_3) = CAST(0 AS smallint))) OR ((
SELECT TOP(1) [a0].[EventId]
FROM [Tenant1].[EventScopes] AS [a0]
WHERE ([r0].[EventId] = [a0].[EventId]) AND ([a0].[EvidenceId] IS NULL)
GROUP BY [a0].[EventId]
HAVING COUNT(DISTINCT (CASE
WHEN CASE
WHEN ([a0].[ScopeType] = CAST(1 AS tinyint)) AND ([a0].[ScopeValue] <> 'Dlp_AdminUnits_1') THEN CAST(0 AS tinyint)
ELSE [a0].[ScopeType]
END <> CAST(0 AS tinyint) THEN CASE
WHEN ([a0].[ScopeType] = CAST(1 AS tinyint)) AND ([a0].[ScopeValue] <> 'Dlp_AdminUnits_1') THEN CAST(0 AS tinyint)
ELSE [a0].[ScopeType]
END
END)) = COUNT(DISTINCT ([a0].[ScopeType]))) IS NOT NULL))) AND (([r0].[FirstSeen] >= @__AddHours_0) AND ([r0].[FirstSeen] < @__date_1))) AND (((((DATEDIFF(day, @__lookbackTimestamp_3, [r].[FirstSeen]) * 1440) + (DATEPART(hour, [r].[FirstSeen]) * 60)) + ((DATEPART(minute, [r].[FirstSeen]) / 10) * 10)) = (((DATEDIFF(day, @__lookbackTimestamp_3, [r0].[FirstSeen]) * 1440) + (DATEPART(hour, [r0].[FirstSeen]) * 60)) + ((DATEPART(minute, [r0].[FirstSeen]) / 10) * 10))) OR (([r].[FirstSeen] IS NULL) AND ([r0].[FirstSeen] IS NULL)))) AS [count]
FROM [Tenant1].[EventTable] AS [r]
WHERE (((@__ef_filter__p_0 & [r].[BitwiseProducts]) = [r].[BitwiseProducts]) AND ((((@__ef_filter__p_1 = CAST(1 AS bit)) OR [r].[ServiceSource] NOT IN (128, 16)) OR (([r].[BitwiseProducts] & @__ef_filter__ProductsUserIsScopedIn_3) = CAST(0 AS smallint))) OR ((
SELECT TOP(1) [a].[EventId]
FROM [Tenant1].[EventScopes] AS [a]
WHERE ([r].[EventId] = [a].[EventId]) AND ([a].[EvidenceId] IS NULL)
GROUP BY [a].[EventId]
HAVING COUNT(DISTINCT (CASE
WHEN CASE
WHEN ([a].[ScopeType] = CAST(1 AS tinyint)) AND ([a].[ScopeValue] <> 'Dlp_AdminUnits_1') THEN CAST(0 AS tinyint)
ELSE [a].[ScopeType]
END <> CAST(0 AS tinyint) THEN CASE
WHEN ([a].[ScopeType] = CAST(1 AS tinyint)) AND ([a].[ScopeValue] <> 'Dlp_AdminUnits_1') THEN CAST(0 AS tinyint)
ELSE [a].[ScopeType]
END
END)) = COUNT(DISTINCT ([a].[ScopeType]))) IS NOT NULL))) AND (([r].[FirstSeen] >= @__AddHours_0) AND ([r].[FirstSeen] < @__date_1))
GROUP BY ((DATEDIFF(day, @__lookbackTimestamp_3, [r].[FirstSeen]) * 1440) + (DATEPART(hour, [r].[FirstSeen]) * 60)) + ((DATEPART(minute, [r].[FirstSeen]) / 10) * 10)
As you'll be able to find out from the examples above, the part that differs between the queries is count = g.Count() in the EFC code that's mapped to COUNT(*) AS [count] in the resulting working SQL query, but on the failed one, it's mapped to an entire subquery that is failing (and looks somewhat like a duplicate of other parts of the query).
Thanks in advance.
P.S. We actually updated to version 6.0.16 in the beginning, which is also the version that resulted in the failed query above. However, our testing shows that the issue actually emerged in version 6.0.2 and not 6.0.16, which is why I chose to mention 6.0.2 throughout the issue.
based on the info provided, this is the most likely culprit: https://github.com/dotnet/efcore/issues/27102. @NivZo you can disable this particular change by using AppContext switch with the value "Microsoft.EntityFrameworkCore.Issue27102"
Hey @maumar!
Thanks for the reply - enabling this switch indeed solves the issue. However, this is somewhat hacky, and we'd like to keep our production code clean of such behaviors as much as possible.
Is there a known (or estimated) time for an official fix for this issue in a new version release?
@NivZo thanks for the confirmation.
Your repro above doesn't provide the full details on what's going on - we don't have the full query (the query filter gets built dynamically), nor the model. Can you please try to put together a minimal repro in a console program? At that point we can investigate exactly what's going on and whether a fix is needed etc.
In the meantime, you can use the AppContext switch as a workaround.
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.
Hey,
We create smaller reproduce for the issue. In the readme file you can see the instruction that need to do in-order to reproduce the issue. EntityFrameWorkProjectDemo.zip
