SQL 转 Linq 备忘录

写SQL的分组统计合作等基本没啥问题,随便出手都解决,但最近用MVC5 写了一个项目,其中的查询统计就不这么容易,因为用了EF进行数据连接处理,所有条件都用linq,当然更重要的是懒得加SQL的调用方法.而LING的统计说难也不难,说易也不易.本来写个12个月的累计合加,,简单地用SQL写了一下

"select state,

sum(case month(submdate) when 1 then 1 else 0 end) as "1",

sum(case month(submdate) when 2 then 1 else 0 end) as "2",

sum(case month(submdate) when 3 then 1 else 0 end) as "3",

sum(case month(submdate) when 4 then 1 else 0 end) as "4",

sum(case month(submdate) when 5 then 1 else 0 end) as "5",

sum(case month(submdate) when 6 then 1 else 0 end) as "6",

sum(case month(submdate) when 7 then 1 else 0 end) as "7",

sum(case month(submdate) when 8 then 1 else 0 end) as "8",

sum(case month(submdate) when 9 then 1 else 0 end) as "9",

sum(case month(submdate) when 10 then 1 else 0 end) as "10",

sum(case month(submdate) when 11 then 1 else 0 end) as "11",

sum(case month(submdate) when 12 then 1 else 0 end) as "12"

from K_WebOrderList where year(submdate)=year(getdate()) group by state"

就这句,咋换好呢?想了N多方法,最后是这样解决:

var K_List = K_WebOrderList.GetListBy(a => a.submdate.Value.Year.ToString() == "2019")

.GroupBy(t=>t.state)

.Select(a => new {

state= a.Key,

Jan = a.Sum(item=>item.submdate.Value.Month == 1 ? 1 :0),

Feb = a.Sum(item => item.submdate.Value.Month == 2 ? 1 : 0),

Mar = a.Sum(item => item.submdate.Value.Month == 3 ? 1 : 0),

Apr = a.Sum(item => item.submdate.Value.Month == 4 ? 1 : 0),

May = a.Sum(item => item.submdate.Value.Month == 5 ? 1 : 0),

Jun = a.Sum(item => item.submdate.Value.Month == 6 ? 1 : 0),

Jul = a.Sum(item => item.submdate.Value.Month == 7 ? 1 : 0),

Aug = a.Sum(item => item.submdate.Value.Month == 8 ? 1 : 0),

Sep = a.Sum(item => item.submdate.Value.Month == 9 ? 1 : 0),

Oct = a.Sum(item => item.submdate.Value.Month == 10 ? 1 : 0),

Nov = a.Sum(item => item.submdate.Value.Month == 11 ? 1 : 0),

Dec = a.Sum(item => item.submdate.Value.Month == 12 ? 1 : 0)

}).ToList();

哈哈,感觉头上又有些光环了,当然可以更好,不会现在算抛个砖吧.有更好的可以留言.