convert sql query included case when then to entity frame work query

I need to do the below query in my project with entity framework. This the query from SQL server

select sum(case when beef_dairy_stat = 1 and param_id = 1 then 1 else 0 end) as ctn
from user_behave_fact 
group by (year(fact_date))
go

And this is what I tried :

fee_ctn = db.user_behave_fact.Sum(x=> x.beef_dairy_stat == true && x.param_id == 16 ? 1 : 0).GroupBy(y => y.fact_date.Value.Year).Select(y => y.Count()).ToList();

I am not familiar with linq querying …. the above SQL server query gives me

eg: 0    //count for year 2017
    15   //count for year 2018

And why I needed that case when in my SQL query => query to select count of records for each year

Answer

You need to reverse GroupBy and Sum in EF. You can also use a condition inside Count directly:

fee_ctn = db.user_behave_fact
    .GroupBy(y => y.fact_date.Value.Year)
    .Select(y => y.Count(x=> x.beef_dairy_stat == true && x.param_id == 16))
    .ToList();

Advertisement

Answer