Query with case statement from SQL Server to Linq query c#

Question

I have the following query in sql server

    select COUNT(mc_owner) as nbr ,
    case mc_owner when 'Element1' then 'Element1' else 'others' end Owner 
    from [dbo].[full] 
    where (date_reception > '01-01-2015')
    group by (CASE mc_owner WHEN 'Element1' THEN 'Element1' ELSE 'others' END)
    order by nbr desc

I need to convert it to entityframework linq query

Possible duplicate: http://stackoverflow.com/questions/936028/linq-case-statement — Evaldas Buinauskas, Mar 05 '15 at 13:22
here is what i'm trying to show http://stackoverflow.com/questions/28876742/sql-server-group-by-count-query/28876816#28876816 — , Mar 05 '15 at 13:37

Mahesh · Accepted Answer · 2015-03-05T14:39:31.593

0

Try this,

var result = fulltableData.Where(x=>x.date_reception > '01-01-2015')
            .GroupBy(x=> x.mc_owner.Equals("Element1")?"Element1":"others")
            .Select(x=> new{ nbr = x.Count(), Owner = x.Key})
            .OrderByDescending(x=>x.nbr);

edited Mar 05 '15 at 14:39

answered Mar 05 '15 at 14:22

Mahesh

8,694
2
32
53

The part Owner = x.Key.Equals("Element1")?"Element1":"others"} is unnecessary as the Key already either has the value "Element1" or "others". Should be Owner = x.Key. – Thomas F. Mar 05 '15 at 14:37

score 0 · Answer 2 · answered Mar 05 '15 at 14:25

I understand you want to select the number of occurrences of a certain mc_owner compared to all others. The following LINQ query should produce the same result:

full.Where(f => f.date_reception > new DateTime(2015, 1, 1))
  .GroupBy(f => f.mc_owner == "Element1" ? f.mc_owner : "others")
  .OrderByDescending(group => group.Count())
  .Select(group => new { Owner = group.key, nbr = group.Count());

Query with case statement from SQL Server to Linq query c#

2 Answers2

Linked