not able to select in Group by Entity Framework

Question

I have this SQL query which I am trying to translate into Linq

SELECT 
    DATEPART(yyyy, ce.DueDate) AS year,
    DATEPART(mm, ce.DueDate) AS Month,
    COUNT(CASE WHEN rt.Code = 'Pass' THEN 1 ELSE NULL END) AS NumPass,
    COUNT(CASE WHEN rt.Code = 'Fail' THEN 1 ELSE NULL END) AS NumFail
FROM
    ControlEvent ce
INNER JOIN 
    ProcessEvent pe ON pe.ControlEventId = ce.Id
INNER JOIN 
    ResultType rt ON pe.ResultTypeId = rt.Id
WHERE 
    DATEDIFF(dd,ce.DueDate,GETDATE()) <= 0
    AND DATEDIFF(dd,ce.DueDate,DATEADD(mm, 3, GETDATE())) >= 0
    AND pe.ProcessId = 1040
GROUP BY 
    DATEPART(yyyy, ce.DueDate), DATEPART(mm, ce.DueDate)
ORDER BY 
    DATEPART(yyyy, ce.DueDate), DATEPART(mm, ce.DueDate)

I have done this till now

var result =
   (from ce in ControlEvents

    join pe in ProcessEvents on ce.Id equals pe.ControlEventId
    join rt in ResultTypes on pe.ResultTypeId equals rt.Id into resultType

    where ce.DueDate >= startDate &&
    ce.DueDate <= endDate &&
    pe.ProcessId == 1048

    orderby ce.DueDate.Value.Year, ce.DueDate.Value.Month

    group ce by new {
       ce.DueDate.Value.Year,
       ce.DueDate.Value.Month,
    } into g

    select new {
       g.Key.Year,
       g.Key.Month,
    }

    ).ToList();

My question is how can I bring the case statements from my SQL query to linq Select. Thanks.

Answer 1

First, remove into resultType because it creates a group join, and your SQL query does not use such construct.

Second, move the orderby clause after groupby.

Finally, use the fact that SQL Count(CASE WHEN condition THEN 1 ELSE NULL END) is equivalent to SUM(condition, 1, 0) which is supported by LINQ.

So the equivalent LINQ query could be something like this:

var result =
   (from ce in ControlEvents
    join pe in ProcessEvents on ce.Id equals pe.ControlEventId
    join rt in ResultTypes on pe.ResultTypeId equals rt.Id
    where ce.DueDate >= startDate &&
        ce.DueDate <= endDate &&
        pe.ProcessId == 1048
    group rt by new {
       ce.DueDate.Value.Year,
       ce.DueDate.Value.Month,
    } into g
    orderby g.Key.Year, g.Key.Month
    select new {
       g.Key.Year,
       g.Key.Month,
       NumPass = g.Sum(e => e.Code == "Pass" ? 1 : 0),
       NumFail = g.Sum(e => e.Code == "Fail" ? 1 : 0)
    }
   ).ToList();

And the resulting EF6.1.3 generated SQL query looks like this:

SELECT 
    [Project1].[C5] AS [C1], 
    [Project1].[C3] AS [C2], 
    [Project1].[C4] AS [C3], 
    [Project1].[C1] AS [C4], 
    [Project1].[C2] AS [C5]
    FROM ( SELECT 
        [GroupBy1].[A1] AS [C1], 
        [GroupBy1].[A2] AS [C2], 
        [GroupBy1].[K1] AS [C3], 
        [GroupBy1].[K2] AS [C4], 
        1 AS [C5]
        FROM ( SELECT 
            [Filter1].[K1] AS [K1], 
            [Filter1].[K2] AS [K2], 
            SUM([Filter1].[A1]) AS [A1], 
            SUM([Filter1].[A2]) AS [A2]
            FROM ( SELECT 
                DATEPART (year, [Extent1].[DueDate]) AS [K1], 
                DATEPART (month, [Extent1].[DueDate]) AS [K2], 
                CASE WHEN (N'Pass' = [Extent3].[Code]) THEN 1 ELSE 0 END AS [A1], 
                CASE WHEN (N'Fail' = [Extent3].[Code]) THEN 1 ELSE 0 END AS [A2]
                FROM   [dbo].[ControlEvents] AS [Extent1]
                INNER JOIN [dbo].[ProcessEvents] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ControlEventId]
                INNER JOIN [dbo].[ResultTypes] AS [Extent3] ON [Extent2].[ResultTypeId] = [Extent3].[Id]
                WHERE ([Extent1].[DueDate] >= @p__linq__0) AND ([Extent1].[DueDate] <= @p__linq__1) AND ([Extent2].[ProcessId] = @p__linq__2)
            )  AS [Filter1]
            GROUP BY [K1], [K2]
        )  AS [GroupBy1]
    )  AS [Project1]
    ORDER BY [Project1].[C3] ASC, [Project1].[C4] ASC

Answer 2

You were almost there. Just before your select statement you had a sequence of groups, where each group was a sequence of join results, where each join result had the same year / month.

So for example you had the following groups

• group1 (January 2015) = sequence of join results with duedate jan 2015
• group2 (February 2015) = sequence of join results with duedate feb 2015
• group3 (February 2015) = sequence of join results with duedate mar 2015

You already found out that the key contained the year and the month you wanted.

For NumPass of group jan 2015, you want all elements of the sequence of jan 2015 that match joinResult.resultType.code == "Pass",

As an object oriented programmer I always have a bit of difficulty writing my Linq statements in this half SQL like syntax, so if it does not bother you too much, I rewrote it using lambda expressions:

ControlEvents.Join(ProcessEvents,
    key1 => key1.Id,              // from ControlEvents take Id
    key2 => key2.ControlEventId   // from processEventt take ControlEventId
    (x, y) => new                 // where they match,
    {
        DueDate = x.DueDate,           // take ControlEvent.Duedate
        ProcessId = y.ProcessId,       // take ProcessId.Id
        ResultTypeId = y.ResultTypeId, // take Process.ResultTypeId
    })

.Where (joinResult =>                  // limit the join result before the 2nd join
    joinResult.DueDate >= startDate &&
    joinResult.DueDate <= endDate &&
    joinResult.ProcessId == 1048)

.Join(ResultTypes,             // join the previous result with ResultTypes
    key1 => key1.ResultTypeId  // from previous join take ResultTypeId
    key2 => key2.Id            // from ResultTypes takd Id
    (x, y) => new              // where they match, take:
    {
        Year = x.DueDate.year,
        Month = x.DueDate.Month,
        // ProcessId = x.ProcessId, not needed anymore
        // unless you want the where statement after the 2nd join 
        ResultCode = y.Code,
    })
 .Orderby(joinResult => joinResult.Year)
 .ThenBy(joinResult => joinResult.Month)
 .GroupBy(sortResult => new {Year = sortResult.Year, Month = sortResult.Month}

• by now you have groups with key {Year, Month}.
• Each group has a sequence of objects with properties { Year, Month, ResultCode}
• Within one group the Year / Month are all the same

Now all you have to do is count all elements within one group that match "Pass" and those that match "Fail":

Continuing the LINQ statement:

.Select(group => new
{
    Year = group.key.Year,
    Month = group.key.Month,
    NumPass = group
        .Where(groupElement => groupElement.ResultCode.Equals("Pass"))
        .Count(),
    NumFail = group
        .Where(groupElement => groupElement.ResultCode.Equals("Fail"))
        .Count(),
 }
 .ToList();

This should do the trick.

Note that I put your Where statement for ProcessId == 1048 before the 2nd join, because I guess that limits the amount of items to join. Maybe the following would even be smarter:

ControlEvents
    .Where(controlEvent => controlEvent.DueDate >= startDate
           && controlEvent.DueDate <= endDate)
    .Join (ProcessEvents.Where(processEvent => processEvent.Id == 1048),
           key1 => etc,

I guess this would really limit the number of elements to join.

Also, consider ordering by Year / Month after the final select, because in that case you also have to order over a much smaller collection