Convert SQL Server query to Entity Framework query

Question

I have a SQL Server query like this:

select 
    month(fact_date) as month,
    sum(case when beef_dairy_stat = 1 and param_id = 1 then 1 else 0 end) as cnt
from 
    user_behave_fact
where 
    YEAR(fact_date) = 2018
group by 
    month(fact_date)
order by 
    month

with a result of

month   cnt
------------
  1     10
  2     20

Now I need to convert this query to its corresponding Entity Framework query.

This is my current attempt:

var sql_rez_ICC = new List<Tuple<int, int>>();

sql_rez_ICC = db.user_behave_fact
                    .Where(x => x.fact_date.Value.Year == selected_year)
                    .GroupBy(y => y.fact_date.Value.Month)
                    .Select(y =>new { month = y.Select(x=>x.fact_date.Value.Month), icc_count = y.Count(x => x.beef_dairy_stat == true && x.param_id == 1) })
                    .AsEnumerable()
                    .Select(y => new Tuple<int, int>(y.month, y.icc_count))
                    .ToList();

However on second .Select, I get an error on month which is

Cannot convert from System.Collection.Generic.IEnumrable to int

Gert Arnold · Accepted Answer · 2018-02-11 15:29:58Z

1

y.Select(x=>x.fact_date.Value.Month) returns an IEnumerable<int>. Use y.Key instead.

answered Feb 11, 2018 at 15:29

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert SQL Server query to Entity Framework query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related