Mehrdad Ghaffari Mehrdad Ghaffari - 3 months ago 10
C# Question

group by in linq doesn't show the expected result

I have these tables :

TestPackagejoint(id,testpackageid,jointid)
Joint(id,endid)


I create a joint between
testpackage
and
joint
and joint based on
jointid,id
.

I want to group the value based on
endid
and
testpackageid
,and count of
endid
or example :

testpackageid endid count
1 2 3
4 2 1
1 3 2


So i write this query

var q = (from i in _ctx.TestPackageJoints
where i.TestPackageId == Id
join joint1 in _ctx.Joints on i.JointId equals joint1.Id
group new {joint1.EndId,i.TestPackageId} by new { i, joint1}
into g1
select new
{
testpackid = g1.Key.i.TestPackageId,
Count = g1.Count(),
endid = g1.Key.joint1.EndId
}).ToList();


But the result :

enter image description here

qxg qxg
Answer

You must understand what want to get. You want to get testpackid and endid, so it's correct to group them. You also want to group by testpackid and endid, why do you group by new {i, ...} here.

Try

group new {joint1.EndId,i.TestPackageId} by new {joint1.EndId,i.TestPackageId}

Or

join ...
let item = new {joint1.EndId,i.TestPackageId}
group item by item