Vikrant Vikrant - 16 days ago 4
C# Question

convert Conditional Join in SQL to Linq

I have a normally simple looking query in SQL as:

SELECT table1.Id, count(table2.col) AS OrderCol
FROM table1
LEFT JOIN table2 ON table1.Id = table2.Id
LEFT JOIN table3 ON table2.Id = table3.Id AND table2.condition = 3 //some integer value
GROUP BY table1.Id
ORDER BY count(table2.col) DESC


When
AND
clause appears inside join, I am not sure about how to convert this to LINQ...

How to achieve it?

Answer

Try this:

var answer = (from t1 in table1
             join t2 in table2 on t1.Id equals t2.Id into subData1
             from t2sub in subData1.DefaultIfEmpty()
             join t3 in table3 on new { Id = t2sub == null ? 0 : t2sub.Id, condition = t2sub == null ? 0 : t2sub.condition } equals new { t3.Id, condition = 3 } into subData
             from t3sub in subData.DefaultIfEmpty()
             group new { t1, t2sub } by t1.Id into subGroup
             orderby subGroup.Count(x => x.t2sub != null) descending 
             select new {
                 Id = subGroup.Key,
                 OrderCol = subGroup.Count(x => x.t2sub != null)
             }).ToList();