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!! :)

You should change your title from "to linq to sql" to "from SQL to LINQ". — Chris Thompson, Jun 20 '09 at 08:24
Hey Chris, I'm actually new to stackoverflow so don't know my way around yet, how do I edit the title of my post? — Justin, Jun 21 '09 at 21:44

score 3 · Accepted Answer · answered Jun 22 '09 at 22:21

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
                        };

In order for this post to be useful for others, you should mention the changes you've made to the original code and how they solved the problems. — Hosam Aly, Jul 29 '09 at 12:13
Hey, Justin! I am trying to convert SQL to LINQ on 2 tables but with 5 equijoins and group by everything in the SELECT to get the Count. My question is here: http://stackoverflow.com/q/43766663/1735836 Any help would be greatly appreciated. — Patricia, May 03 '17 at 19:07
So, basically, did you join first then group and finally select into RouteView? I'll give it a try. — Patricia, May 03 '17 at 19:10

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

1 Answers1