我如何用LINQ编写这个查询?

Question

如何用LINQ编写这个查询?

 SELECT  OrderID, [1] T1, [2] T2, [3] T3, [4] T4, [5] T5
    FROM    (SELECT OrderId, ReportTypeId FROM OrderReport) p PIVOT
    ( COUNT(ReportTypeId) FOR ReportTypeId IN ([1], [2], [3], [4], [5]) ) pvt

Answer 1

据我所知，您的查询可以重写为常规COUNT/CASE;

SELECT OrderID, 
  COUNT(CASE WHEN ReportTypeId = 1 THEN 1 ELSE NULL END) T1,
  COUNT(CASE WHEN ReportTypeId = 2 THEN 1 ELSE NULL END) T2,
  COUNT(CASE WHEN ReportTypeId = 3 THEN 1 ELSE NULL END) T3,
  COUNT(CASE WHEN ReportTypeId = 4 THEN 1 ELSE NULL END) T4,
  COUNT(CASE WHEN ReportTypeId = 5 THEN 1 ELSE NULL END) T5
FROM OrderReport
GROUP BY OrderId;

…在Linq;

中看起来像这样(未经测试)

var query = db.myTable
    .GroupBy(t => t.OrderID)
    .Select(t => new {
        OrderID = t.Key,
        T1 = t.Where(c => c.ReportTypeId == 1).Count(),
        T2 = t.Where(c => c.ReportTypeId == 2).Count(),
        T3 = t.Where(c => c.ReportTypeId == 3).Count(),
        T4 = t.Where(c => c.ReportTypeId == 4).Count(),
        T5 = t.Where(c => c.ReportTypeId == 5).Count()
    });