locked
Sum and Case to linq RRS feed

  • Question

  • User956519895 posted

    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

    Monday, May 31, 2021 2:05 PM

All replies

  • User1535942433 posted

    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

    Tuesday, June 1, 2021 5:59 AM