需要帮助将带有联接、分组/计数的查询转换为LINQ to SQL。

Question

我需要帮助将这个查询转换为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

以下是我目前完成的内容，但我无法弄清如何使分组计数(group by/count)工作，我的Linqer工具也无法解决这个问题...

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

感谢能够解决这个问题的人，我还是linq to sql的新手，每天都会有2-3个新问题！！:)

- Justin

---

你应该将标题从“to linq to sql”改为“from SQL to LINQ”。 - Chris Thompson

嘿，Chris，我其实是个新手，对stackoverflow还不太熟悉，我该如何编辑我的帖子标题？ - Justin

Answer 1

我搞定了...

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

- Justin

---

为了让这篇文章对其他人有用，您应该提及您对原始代码所做的更改以及它们如何解决问题。 - Hosam Aly

嘿，Justin！我正在尝试将SQL转换为LINQ，涉及2个表格，5个等值连接和在SELECT中分组所有内容以获取计数。我的问题在这里：https://dev59.com/naHia4cB1Zd3GeqPTW4-。任何帮助都将不胜感激。 - Patricia

所以，基本上，你是先加入组，然后最终选择进入RouteView的吗？我会尝试一下。 - Patricia