Help needed with LINQ to SQL Syntax

Question

Hi there i'm getting mixed up trying to build a group by result set in LINQ

The following TSQL gets what i want in LINQ, the expression is:

select s.SiteDescription,count(*) as TotalIncidents
from Site as s,Incident as i
where s.SiteId = i.SiteId
group by s.SiteDescription

But I want to create the same thing in LINQ in my Controller code, here is what i have but its no good and i'm all over the place today :

var qry = from s in _db.Sites
            join i in _db.Incidents on s.SiteId equals i.SiteId
            group s.SiteDescription
                into grp
                select new
                {
                    Site = grp.Key,
                    Count = grp.Select(x => x.Incidents).Distinct().Count()
                };

My errors are :

Error 27
Cannot convert lambda expression to type
'System.Collections.Generic.IEqualityComparer' because it is not a delegate type C:\Documents and Settings\Administrator\Desktop\IRenewables_EMAS\IRenewables_EMAS\Controllers\IncidentController.cs 40 23 Emas.Web
Error 28
The type arguments for method 'System.Linq.Enumerable.OrderBy(System.Collections.Generic.IEnumerable, System.Func)' cannot be inferred from the usage. Try specifying the type arguments explicitly. C:\Documents and Settings\Administrator\Desktop\IRenewables_EMAS\IRenewables_EMAS\Controllers\IncidentController.cs 51 33 Emas.Web
Error 29
Expected contextual keyword 'by' C:\Documents and Settings\Administrator\Desktop\IRenewables_EMAS\IRenewables_EMAS\Controllers\IncidentController.cs 41 27 Emas.Web

Thanks in Advance,

Answer 1

Try this:

var qry =   from s in _db.Sites
            join i in _db.Incidents on s.SiteId equals i.SiteId
            group s by s.SiteDescription into grp
            select new
            {
                Site = grp.Key,
                Count =  grp.Count()
            };