TSQL Group By Query in LINQ

Question

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();

You seem to have both r.review and count(r.review) in the select statement. What are you trying to get? — istepaniuk
– istepaniuk, Commented Jan 16, 2013 at 19:21
i try to get reviews written by users. some users write the same review on every object like: "it's good", "it's good", "it's good" on every page. — levi
– levi, Commented Jan 16, 2013 at 19:23
I see you edited your question. So you just wanted to know how many times the same review was repeated? I've edited my answer. — istepaniuk
– istepaniuk, Commented Jan 16, 2013 at 19:33

istepaniuk · Accepted Answer · 2013-01-16 19:36:20Z

2

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();

edited Jan 16, 2013 at 19:36

answered Jan 16, 2013 at 19:28

istepaniuk

4,3042 gold badges38 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

TSQL Group By Query in LINQ

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related