We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

# Sum and Case to linq RSS

Last post Jun 01, 2021 05:59 AM by yij sun

• Rosethulasi

None

0 Points

1 Post

### Sum and Case to linq

Need help to convert sql to linq in c#

SELECT    SUM(CASE WHEN Evaluation.StatusId = 3 THEN 1 END)AS EvalutionOK,
SUM(CASE WHEN Evaluation.StatusId = 4 THEN 1 END) AS EvalutionTodo,
SUM(CASE WHEN Evaluation.StatusId = 2 THEN 1 END) AS EvalutionNOk
FROM Investor
INNER JOIN Evaluation ON Evaluation.InvestorId = [dbo].[Investor].Id

Sql Result :

EvalutionOK    EvalutionTodo    EvalutionNOk
5                       NULL                  NULL

List<Investor> Investors = ObjectSet.Include(z =>z.Evaluation).ToList();

var test = from i in Investors
select new TestModel
{
EvalutionTodo = i.Evaluation.Sum(e => e.StatusId == 3? 1 : 0),
EvalutionOK = i.Evaluation.Sum(e => e.StatusId == 4? 1 : 0),
EvalutionNOk = i.Evaluation.Sum(e => e.StatusId == 2? 1 : 0),
};

C# result :

Result list  of TestModel

EvalutionOK    EvalutionTodo    EvalutionNOk
1                       0                          0

1                       0                          0

1                       0                          0

1                       0                          0

1                       0                          0

What i am missing? Any help ? How to get sum in sigle liek sql resut ?

Thanks

• yij sun

Contributor

4320 Points

1729 Posts

### Re: Sum and Case to linq

Jun 01, 2021 05:59 AM|yij sun|LINK

Hi Rosethulasi,

You could add group by.Just like this:

```            var test = from i in Idata
join j in Edata on i.Id equals j.InvestorId
group j by 1 into g
select new
{
EvalutionTodo = g.Select(x => x.StatusId == 3 ? 1 : 0).Sum(),
EvalutionOK = g.Select(x => x.StatusId == 4 ? 1 : 0).Sum(),
EvalutionNOk = g.Select(x => x.StatusId == 2 ? 1 : 0).Sum()
};```

Best regards,

Yijing Sun

ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >