Using Case When within Entity Framework LINQ query

Question

I've been trying to convert the following T-SQL query to Entity Framework for C# LINQ, but I can't seem to find a way to incorporate the CASE WHEN THEN part of it.

Does anyone have a method for accomplishing this?

SELECT 
    a.Number, 
    CASE 
        WHEN COUNT(DISTINCT b.Id) > 1 
            THEN 'MULTIPLE' 
            ELSE MAX(b.Test) 
    END AS 'Test',
    CASE 
        WHEN COUNT(DISTINCT c.Id) > 1 
            THEN 'MULTIPLE' 
            ELSE MAX(c.FirstName + ' ' + c.LastName) 
    END AS 'Name',
FROM 
    ZZ.WORK a
INNER JOIN 
    ZZ.PACKAGE b ON a.Id = b.Id
INNER JOIN 
    ZZ.FLOW c ON b.Id = c.Id
WHERE 
    a.Number = 150
GROUP BY 
    a.Number

I'm selecting the data into an object like this

var results = await (from a in dbContext.table1
                     join b in dbContext.table2 on a.Id equals b.Id
                     join c in dbContext.table3 on b.Id equals c.Id                   
                     where a.Number == 150
select new GetDataclass
{
  Value1 = a.Number,
  Value2 = b.Test.Count() > 1 ? "MULTIPLE" : b.Test,
  Value3 = (c.FirstName + ' ' + c.LastName).Count() > 1 ? "MULTIPLE" : (c.FirstName + ' ' + c.LastName)
 }).ToListAsync()

If I run this in SQL Server, I get one row back with the values for Value 2/3. When I run this in EF, it also returns one row, but I get the "MULTIPLE" value every time, even though I shouldn't in this scenario.

Answer 1

As mentioned in the comment:

1. Your LINQ query misses the GROUP BY expression.
2. The second and third properties are not matched with the expression in the SQL query. You are counting the length of string values instead of counting the (distinct) id from the tables.

Your query should be as below:

var results = await (from a in dbContext.table1
    join b in dbContext.table2 on a.Id equals b.Id
    join c in dbContext.table3 on b.Id equals c.Id                   
    where a.Number == 150
    group new { b, c } by a.Number into grp
    select new
    {
        Value1 = grp.Key,
        Value2 = grp.Select(x => x.b.Id).Distinct().Count() > 1 
            ? "MULTIPLE" 
            : grp.Max(x => x.b.Test),
        Value3 = grp.Select(x => x.c.Id).Distinct().Count() > 1 
            ? "MULTIPLE" 
            : grp.Max(x => x.c.FirstName + ' ' + x.c.LastName)
    }).ToListAsync();