Problem when group by on predictions
Closed this issue · 1 comments
Hello,
consider this query:
db.OrderDetails.Select(i => new { Date = i.ProductID > 10 ? i.ProductID : 0 });
LinqToSql translates it to a subquery with case statement but iqToolkit queries all OrderDetails and executes the projection expression at client.
This behavior has caused problem when using group by:
var q1 = db.OrderDetails.GroupBy(i => i.ProductID > 10 ? i.ProductID : 0).Select(i => new { i.Key, Count = i.Count() });
var q2 = db.OrderDetails.Select(i => new { key = i.ProductID > 10 ? i.ProductID : 0, i }).GroupBy(i => i.key).Select(i => new { i.Key, Count = i.Count() });
both above queries returns invalid results. what is the workaround?
Query text by iqtoolkit:
SELECT t0.[ProductID], COUNT(*) AS [agg1]
FROM [Order Details] AS t0
GROUP BY t0.[ProductID]
Expected sql query (as linqToSql generates):
SELECT COUNT(*) AS [Count], [t1].[value] AS [Date]
FROM (
SELECT
(CASE
WHEN [t0].[ProductID] > @p0 THEN [t0].[ProductID]
ELSE @p1
END) AS [value]
FROM [dbo].[Order Details] AS [t0]
) AS [t1]
GROUP BY [t1].[value]
I found the issue is related to Nominator ProjectionAffinity
. The default ProjectionAffinity is considered ProjectionAffinity.Client
at ColumnProjector
static funstions. Changing it to ProjectionAffinity.Server
solves groupby problem but causes problems in other tests. Specifying ProjectionAffinity.Client
in CrossApplyRewriter
solves join related problem. After all, but two tests fails with this message:
SqlFormatter -> The expression node of type 'OuterJoined' is not supported