Linq to SQL - Left Join with > comparison

Question

Hey all, I'm trying to do a left join in linq to sql that has a date1 > date2 comparison in it but can't figure out how. Here's the SQL:

select
 rd.RouteDispatchID,
    r.RouteNumber,
    s.ShortDescription,
    rd.DispatchDate,
    rd.CreationDate,
    e.FirstName,
    e.LastName,
 count(md.MachineDispatchID) NumMachines,
 count(mdp.Pick) TotalPicks,
 sum(mh.BillsToStacker) + sum(mh.BoxCash) TotalCash,
 sum(mh.TubeCash) - sum(mh.CashOut) NetCoinsToTubes
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
 left join dbo.MachineDispatchPick mdp on md.MachineDispatchID = mdp.MachineDispatchID
 **left join dbo.MachineHistory mh on md.MachineID = mh.MachineID and mh.ReadDate > m.LastServiceDate**
group by rd.RouteDispatchID,
    r.RouteNumber,
    s.ShortDescription,
    rd.DispatchDate,
    rd.CreationDate,
    e.FirstName,
    e.LastName

I put the left join that has the issue in bold. Here's what I have for the linq so far but I've left out the mh.ReadDate > m.LastServiceDate since I'm not sure how to do it:

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 { rd.RouteDispatchID, IsSelected = true }
                          equals new { md.RouteDispatchID, IsSelected = md.IsSelected.Value } into md_join
                        from md in md_join.DefaultIfEmpty()
                        join mdp in db.MachineDispatchPicks on md.MachineDispatchID equals mdp.MachineDispatchID into mdp_join
                        from mdp in mdp_join.DefaultIfEmpty()
                        join mh in db.MachineHistories on md.MachineID equals mh.MachineID into mh_join
                        from mh in mh_join.DefaultIfEmpty()
                        group new { rd, r, s, e, md, mdp, mh } by new
                        {
                            rd.RouteDispatchID,
                            r.RouteNumber,
                            s.ShortDescription,
                            rd.DispatchDate,
                            rd.CreationDate,
                            e.FirstName,
                            e.LastName
                        } into g
                        select new RouteView
                        {
                            RouteDispatchID = g.Key.RouteDispatchID,
                            RouteNumber = g.Key.RouteNumber,
                            Status = g.Key.ShortDescription,
                            DispatchDate = g.Key.DispatchDate.Value,
                            CreatedDate = g.Key.CreationDate.Value,
                            FirstName = g.Key.FirstName,
                            LastName = g.Key.LastName,
                            NumMachines = g.Count(),
                            TotalPicks = g.Count(),
                            TotalCash = (g.Sum(p => p.mh.BillsToStacker.Value) + g.Sum(p => p.mh.BoxCash.Value)),
                            NetCoinsToTubes = (g.Sum(p => p.mh.TubeCash.Value) - g.Sum(p => p.mh.CashOut.Value))
                        };

Anyone know how to get this to work?

Answer 1

I loaded your query up in Linqer, and when I tried to convert it, i got this error:

SQL cannot be converted to LINQ: Only "=" operator in JOIN expression can be used. ">" operator cannot be converted.

So I moved the offending criteria to the where clause. Then I was able to convert without issue. Below is the result.

from rd in db.RouteDispatch
join r in db.Route on rd.RouteID equals r.RouteID
join s in db.Reference on new { StatusCodeReferenceID = rd.StatusCodeReferenceID } equals new { StatusCodeReferenceID = s.ReferenceID }
join e in db.Employee on new { CreatedByEmployeeID = rd.CreatedByEmployeeID } equals new { CreatedByEmployeeID = e.EmployeeID }
join md in db.MachineDispatch
      on new { rd.RouteDispatchID, IsSelected = true }
  equals new { md.RouteDispatchID, md.IsSelected } into md_join
from md in md_join.DefaultIfEmpty()
join mdp in db.MachineDispatchPick on new { MachineDispatchID = md.MachineDispatchID } equals new { MachineDispatchID = Convert.ToString(mdp.MachineDispatchID) } into mdp_join
from mdp in mdp_join.DefaultIfEmpty()
join mh in db.MachineHistory on md.MachineID equals mh.MachineID into mh_join
from mh in mh_join.DefaultIfEmpty()
group new {rd, r, s, e, mh} by new {
  rd.RouteDispatchID,
  r.RouteNumber,
  s.ShortDescription,
  rd.DispatchDate,
  rd.CreationDate,
  e.FirstName,
  e.LastName
} into g
select new {
  RouteDispatchID = (System.Int32?)g.Key.RouteDispatchID,
  RouteNumber = (System.Int32?)g.Key.RouteNumber,
  g.Key.ShortDescription,
  DispatchDate = (System.DateTime?)g.Key.DispatchDate,
  CreationDate = (System.DateTime?)g.Key.CreationDate,
  g.Key.FirstName,
  g.Key.LastName,
  NumMachines = (Int64?)g.Count(),
  TotalPicks = (Int64?)g.Count(),
  TotalCash = (System.Int32?)(g.Sum(p => p.mh.BillsToStacker) + g.Sum(p => p.mh.BoxCash)),
  NetCoinsToTubes = (System.Int32?)(g.Sum(p => p.mh.TubeCash) - g.Sum(p => p.mh.CashOut))
}

Answer 2

from rd in dc.RouteDispatches
join r in dc.Routes on rd.RouteID equals r.RouteID
join s in dc.References on rd.StatusCodeReferenceID equals s.ReferenceID
join e in dc.Employees on rd.CreatedByEmployeeID equals e.EmployeeID
from md in (
  from md in dc.MachineDispatches
  where md.RouteDispatchID == rd.RouteDispatchID
     && md.IsSelected == 1
  select md
  ).DefaultIfEmpty()
from mdp in (
  from mdp in dc.MachineDispatchPicks 
  where mdp.MachineDispatchID == md.MachineDispatchID
  select mdp
  ).DefaultIfEmpty()
from mh in (
  from mh in dc.MachineHistories
  where mh.MachineID == md.MachineID
     && mh.ReadDate > m.LastServiceDate
  select mh
  ).DefaultIfEmpty()
group .... etc...