Problem when group by on predictions

[afruzan]

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]

[afruzan]

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