转换条件加盟SQL到LINQ
问题描述:
我在SQL中是正常看似简单的查询:转换条件加盟SQL到LINQ
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
当AND
子句中加入出现,我不知道如何将其转换为LINQ ..
如何实现它?
答
试试这个:
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();
答
它去是这样的:
from t1 in db.Table1
join t2 in db.Table2 on t1.field equals t2.field
select new { t1.field2, t2.field3}
需要在第纠正'{t2.Id,t2.condition}'...应该是'{t2sub .Id,t2sub.condition}' – Vikrant
@Vikrant,修正了 –
仍然'对象引用未设置为对象的实例'错误来了 – Vikrant