CODEWITHSUNDEEP
c#sqllinq-to-sqlentity-frameworklinq-to-entities
Vasile Laur
Vasile Laur

Reputation: 695

EF Linq to Entities Query with groupby sum and min

I am trying to make a Linq to Entities Query working but I can't figure it out.

This is my my SQL query which is working fine: 

SELECT SUM(ca.PointValue) as Points, ua.UserFBID, MIN(ua.[Date]) as FirstDate
  FROM [LeaderOfNow].[dbo].[QuestionAnswer] ca
  inner join [LeaderOfNow].[dbo].[LONUserAnswers] ua
  on ca.Id = ua.AnswerId
  group by ua.UserFBID
  order by Points desc, FirstDate asc

An so far my best attempt is: 

            var leaders = db.LONUserAnswers
                .GroupBy(a => a.UserFBID)
                .Select(a =>
                new 
                {
                    FBID = a.Key,
                    CurrentPoints = a.Select(v => v.QuestionAnswer.PointValue).Sum(),
                    FirstAnswered = a.Min(v => v.Date)
                })
.OrderByDescending(a => a.CurrentPoints)
                .OrderBy(a => a.FirstAnswered)
                .Take(10)
                .ToList();

However that renders a mess of sql and only respect the first order by and not the second, which I need to work. Any suggestions on what am I doing wrong? Thank you for the help.

Upvotes: 2

Views: 2926

Answers (1)

Jan
Jan

Reputation: 16032

When you chain OrderBy-Functions you have to use ThenBy() or ThenDescendingBy() for the latter.

Upvotes: 4

Related Questions