Need help converting query with joins and group by/count to LINQ to SQL

Question

I need help converting this query to linq to sql:

select
    rd.RouteDispatchID,
    r.RouteNumber,
    s.ShortDescription Status,
    rd.DispatchDate,
    rd.CreationDate CreatedDate,
    e.FirstName,
    e.LastName,
    count(md.MachineDispatchID) NumMachines
from
dbo.RouteDispatch rd
    inner join dbo.Route r on rd.RouteID = r.RouteID
    inner join dbo.Reference s on rd.StatusCodeReferenceID = s.ReferenceID
    inner join dbo.Employee e on rd.CreatedByEmployeeID = e.EmployeeID
    left join dbo.MachineDispatch md on rd.RouteDispatchID = md.RouteDispatchID and md.IsSelected = 1

Here's what I have so far, but I can't figure out how to get the group by/count to work and neither can my Linqer tool...

var query = from rd in db.RouteDispatches
                join r in db.Routes on rd.RouteID equals r.RouteID
                join s in db.References on new { StatusCodeReferenceID = rd.StatusCodeReferenceID } equals new { StatusCodeReferenceID = s.ReferenceID }
                join e in db.Employees on new { CreatedByEmployeeID = rd.CreatedByEmployeeID } equals new { CreatedByEmployeeID = e.EmployeeID }
                join md in db.MachineDispatches
                      on new { RouteDispatchID = rd.RouteDispatchID, IsSelected = true }
                  equals new { RouteDispatchID = md.RouteDispatchID, IsSelected = md.IsSelected.Value } into md_join
                from md in md_join.DefaultIfEmpty()
                select new RouteView {
                  RouteDispatchID = rd.RouteDispatchID,
                  RouteNumber = r.RouteNumber,
                  Status = s.ShortDescription,
                  DispatchDate = rd.DispatchDate.Value,
                  CreatedDate = rd.CreationDate.Value,
                  FirstName = e.FirstName,
                  LastName= e.LastName,
                  NumMachines = 0//.Count()
                };

Thanks to whoever can figure this out, I'm still a newbie with linq to sql so am coming up with like 2-3 new questions per day!! :)

Answer 1

Got it working...

var query = from rd in db.RouteDispatches
                        join r in db.Routes on rd.RouteID equals r.RouteID
                        join s in db.References on new { StatusCodeReferenceID = rd.StatusCodeReferenceID } equals new { StatusCodeReferenceID = s.ReferenceID }
                        join e in db.Employees on new { CreatedByEmployeeID = rd.CreatedByEmployeeID } equals new { CreatedByEmployeeID = e.EmployeeID }
                        join md in db.MachineDispatches
                              on new { RouteDispatchID = rd.RouteDispatchID, IsSelected = true }
                          equals new { RouteDispatchID = md.RouteDispatchID, IsSelected = md.IsSelected.Value } into md_join
                        from md in md_join.DefaultIfEmpty()
                        group new { rd, r, s, e, md } by new
                        {
                            rd.RouteDispatchID,
                            r.RouteNumber,
                            s.ShortDescription,
                            rd.DispatchDate,
                            rd.CreationDate,
                            e.FirstName,
                            e.LastName
                        } into g
                        select new RouteView
                        {
                            RouteDispatchID = (System.Int32)g.Key.RouteDispatchID,
                            RouteNumber = g.Key.RouteNumber,
                            Status = g.Key.ShortDescription,
                            DispatchDate = (System.DateTime)g.Key.DispatchDate,
                            CreatedDate = (System.DateTime)g.Key.CreationDate,
                            FirstName = g.Key.FirstName,
                            LastName = g.Key.LastName,
                            NumMachines = g.Count(),
                            TotalRecordCount = 0
                        };