CODEWITHSUNDEEP
c#sqllinqsql-server-2008t-sql
levi
levi

Reputation: 3521

TSQL Group By Query in LINQ

Review table has userid.

select u.userid,review,COUNT(review) as coun,u.firstName,u.lastname,u.avatar,u.userid from reviews r
inner join users u on u.userid=r.userid
group by u.userid,review,u.firstName,u.lastname,u.avatar,u.userid
order by coun desc

how is this script translated into linq? I have the following not working, incomplete code. CODE

var reviews = from r in context.Reviews.Where(r => r.CreateDate > dateFrom && r.CreateDate < dateTo)
                              group r by new { r.Review1, r.UserID } into g
                              join u in context.Users on g.Key.UserID equals u.UserID
                              select new UserMan
                              {
                                  ID = u.UserID,
                                  Avatar = u.Avatar,
                                  LastName = u.LastName,
                                  FirstName = u.FirstName,
                                  Review = g.Key.Review1,
                                  Reviews = g.Count()
                              };
                return reviews.ToList();

Upvotes: 0

Views: 165

Answers (1)

istepaniuk
istepaniuk

Reputation: 4269

Just get rid of that join.

var reviews = from r in context.Reviews.Where(r => r.CreateDate > dateFrom && r.CreateDate < dateTo)
    group r by new { r.Review1, r.User } into g
    select new UserMan
         {
           ID = g.Key.User.UserID,
           Avatar = g.Key.User.Avatar,
           LastName = g.Key.User.LastName,
           FirstName = g.Key.User.FirstName,
           Review = g.Key.Review1,
           Reviews = g.Count()
         };

return reviews.ToList();

Upvotes: 2

Related Questions