bjjrolls bjjrolls - 1 year ago 116
SQL Question

Translating my SQL Query to c# linq/lambda. Multiple parameter GroupBy

I've been puzzling over this problem all morning and can't figure out how to do it in C#.

My SQL query as follows:

select a.CourseID,
from audit a
inner join results r on a.UserID = r.UserID
inner join Course c on a.CourseID = c.CourseID
where c.CourseType = 9 and a.Guid = 'A123F123D123AS123123'
and a.Result = 'Passed' and r.Class = 'Maths'
group by a.CourseID, a.UserID
order by a.UserID

returns exactly what I want, but I can't seem to translate it into linq format. (the format being used here is what is required in my job at the moment so please advise on this format)

So far I have the following:

var audits = auditRepository.Get(a => a.Course.CourseType == 9 && a.GUID == this.Company.GUID && a.Result == "Passed", null, null,
a => a.Course, a => a.User)
.Join(resultsRepository.Get(r => r.GUID == this.Company.GUID && r.Class == class),
a => a.UserID,
r => r.UserID,
(a, r) => new Audit
User = a.User,
Course = a.Course,
Result = a.Result,
Timestamp = a.Timestamp,
AuditID = a.AuditID,
UserID = a.UserID
.OrderByDescending(o => o.Timestamp)
.GroupBy(u => new { u.User, u.Course })

.Select(grp => grp.ToList())

However this returns duplicates.

Any advice is appreciated, thanks


Answer Source

Instead of

.Select(grp => grp.ToList())

Select only the first element from each group to exclude duplicates:

.Select(grp => grp.First())

If you need a count also:

.Select(t => new{grp = t.First(),cnt = t.Count()} )


.Select(t => new { grp = t.First(), cnt = t.Select(s => s.AuditID).Distinct().Count() })
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download