SELECT SUM(p.Runs + p.Wickets + p.Catches + p.Runouts) AS points, u.UserId
FROM tblPlayerUser AS pu INNER JOIN
tblUser AS u ON pu.UserId = u.UserId INNER JOIN
tblPlayer AS p ON pu.PlayerId = p.PlayerId
GROUP BY u.UserId
This is my sql query to fetch the records.
I want a code which will perform this query's operation in Linq to Sql(C#)
Can Anyone help me
from pu in db.TblPlayerUser
join u in db.TblUser on pu.UserId equals u.UserId
join p in db.TblPlayer on pu.PlayerId equals p.PlayerId
group new {u, p} by new {
u.UserId
} into g
select new {
points = (System.Int32?)g.Sum(p => p.p.Runs + p.p.Wickets + p.p.Catches + p.p.Runouts),
UserId = (System.Int32?)g.Key.UserId
}
From pu In db.tblPlayerUsers _
Join u In db.tblUsers On New With { pu.userId } Equals New With { u.userId } _
Join p In db.tblPlayers On New With { .playerId = pu.playerId } Equals New With { .playerId = p.PlayerId } _
Group New With {u, p} By u.userId Into g = Group _
Select _
points = CType(g.Sum(Function(p) p.p.Runs + p.p.Wickets + p.p.Catches + p.p.Runouts),Int32?), _
userId = CType(userId,Int32?)
Good Luck
Do FEAR (Face Everything And Rise)
Please mark as Answer if my post helps you..!
markand911
Member
22 Points
55 Posts
Linq code for group by and sum
Dec 21, 2012 09:42 AM|LINK
SELECT SUM(p.Runs + p.Wickets + p.Catches + p.Runouts) AS points, u.UserId FROM tblPlayerUser AS pu INNER JOIN tblUser AS u ON pu.UserId = u.UserId INNER JOIN tblPlayer AS p ON pu.PlayerId = p.PlayerId GROUP BY u.UserIdThis is my sql query to fetch the records.
I want a code which will perform this query's operation in Linq to Sql(C#)
Can Anyone help me
Ramesh T
Contributor
5079 Points
821 Posts
Re: Linq code for group by and sum
Dec 21, 2012 10:24 AM|LINK
Try this
from pu in db.TblPlayerUser join u in db.TblUser on pu.UserId equals u.UserId join p in db.TblPlayer on pu.PlayerId equals p.PlayerId group new {u, p} by new { u.UserId } into g select new { points = (System.Int32?)g.Sum(p => p.p.Runs + p.p.Wickets + p.p.Catches + p.p.Runouts), UserId = (System.Int32?)g.Key.UserId }NadeemZee
Participant
942 Points
178 Posts
Re: Linq code for group by and sum
Dec 21, 2012 11:15 AM|LINK
try this one:
From pu In db.tblPlayerUsers _ Join u In db.tblUsers On New With { pu.userId } Equals New With { u.userId } _ Join p In db.tblPlayers On New With { .playerId = pu.playerId } Equals New With { .playerId = p.PlayerId } _ Group New With {u, p} By u.userId Into g = Group _ Select _ points = CType(g.Sum(Function(p) p.p.Runs + p.p.Wickets + p.p.Catches + p.p.Runouts),Int32?), _ userId = CType(userId,Int32?)Do FEAR (Face Everything And Rise)
Please mark as Answer if my post helps you..!