将 SQL 查询转换为 Linq 或 lambda

Question

我正在尝试在 LINQ 中转换它，但我没有得到成功，如果有人帮助我在 linq 中转换它或使用 lambda 表达式编写查询，我将不胜感激

SELECT MAX('Quality Of Service') AS Category,
       COUNT(CASE WHEN  t.QualityOfService = 'Excellent' THEN 1 END) AS Excellent,
       COUNT(CASE WHEN t.QualityOfService = 'VeryGood' THEN 1 END) AS Very_Good,
       COUNT(CASE WHEN t.QualityOfService = 'Good' THEN 1 END) AS Good,
       COUNT(CASE WHEN t.QualityOfService = 'Bad' THEN 1 END) AS Bad,
       COUNT(CASE WHEN t.QualityOfService = 'Poor' THEN 1 END) AS Poor
  FROM Feedbacks t
 WHERE t.DateOfVisit BETWEEN '2018-03-29' AND '2018-03-29'
UNION
SELECT MAX('Quality Of Food') AS Category,
       COUNT(CASE WHEN t.QualityOfFood = 'Excellent' THEN 1 END) AS Excellent,
       COUNT(CASE WHEN t.QualityOfFood = 'VeryGood' THEN 1 END) AS Very_Good,
       COUNT(CASE WHEN t.QualityOfFood = 'Good' THEN 1 END) AS Good,
       COUNT(CASE WHEN t.QualityOfFood = 'Bad' THEN 1 END) AS Bad,
       COUNT(CASE WHEN t.QualityOfFood = 'Poor' THEN 1 END) AS Poor
  FROM Feedbacks t
 WHERE t.DateOfVisit BETWEEN '2018-03-29' AND '2018-03-29'
UNION
SELECT MAX('Cleanliness Of Lounge') AS Category,
       COUNT(CASE WHEN t.CleanlinessOfLounge = 'Excellent' THEN 1 END) AS Excellent,
       COUNT(CASE WHEN t.CleanlinessOfLounge = 'VeryGood' THEN 1 END) AS Very_Good,
       COUNT(CASE WHEN t.CleanlinessOfLounge = 'Good' THEN 1 END) AS Good,
       COUNT(CASE WHEN t.CleanlinessOfLounge = 'Bad' THEN 1 END) AS Bad,
       COUNT(CASE WHEN t.CleanlinessOfLounge = 'Poor' THEN 1 END) AS Poor
  FROM Feedbacks t
 WHERE t.DateOfVisit BETWEEN '2018-03-29' AND '2018-03-29'
UNION
SELECT MAX('Friendliness Of Staff') AS Category,
       COUNT(CASE WHEN t.FriendlinessOfStaff = 'Excellent' THEN 1 END) AS Excellent,
       COUNT(CASE WHEN t.FriendlinessOfStaff = 'VeryGood' THEN 1 END) AS Very_Good,
       COUNT(CASE WHEN t.FriendlinessOfStaff = 'Good' THEN 1 END) AS Good,
       COUNT(CASE WHEN t.FriendlinessOfStaff = 'Bad' THEN 1 END) AS Bad,
       COUNT(CASE WHEN t.FriendlinessOfStaff = 'Poor' THEN 1 END) AS Poor
  FROM Feedbacks t
 WHERE t.DateOfVisit BETWEEN '2018-03-29' AND '2018-03-29'
 UNION
SELECT MAX('Overall Experience') AS Category,
       COUNT(CASE WHEN t.OverAllExperience = 'Excellent' THEN 1 END) AS Excellent,
       COUNT(CASE WHEN t.OverAllExperience = 'VeryGood' THEN 1 END) AS Very_Good,
       COUNT(CASE WHEN t.OverAllExperience = 'Good' THEN 1 END) AS Good,
       COUNT(CASE WHEN t.OverAllExperience = 'Bad' THEN 1 END) AS Bad,
       COUNT(CASE WHEN t.OverAllExperience = 'Poor' THEN 1 END) AS Poor
  FROM Feedbacks t
 WHERE t.DateOfVisit BETWEEN '2018-03-29' AND '2018-03-29'

此查询的结果以这种方式出现

    Category  Excellent     Very_Good    Good   Bad    Poor
       Null         0             0       0      0       0

我现在的表格中的反馈为零，这就是为什么它在类别中显示空和所有零的原因。

我的表格看起来像这样

ID    QualityOfFood   QualityOfServices   CleanlinessOfLounge     FreindlinessOfStaff      OverALLExperience

我问了这种类型的问题，但"这种类型"不一样，所以要求不要标记它

Answer 1

我通过使用linqer找到了它

  ( from t in
    (   from t in db.Feedbacks
        where
          t.DateOfVisit >= "2018-03-29" && t.DateOfVisit <= "2018-03-29"
        select new {
          Column1 = "Quality Of Service",
          Column2 = 
          t.QualityOfService == "Excellent" ? (System.Int64?)1 : null,
          Column3 = 
          t.QualityOfService == "VeryGood" ? (System.Int64?)1 : null,
          Column4 = 
          t.QualityOfService == "Good" ? (System.Int64?)1 : null,
          Column5 = 
          t.QualityOfService == "Bad" ? (System.Int64?)1 : null,
          Column6 = 
          t.QualityOfService == "Poor" ? (System.Int64?)1 : null,
          Dummy = "x"
        })
        group t by new { t.Dummy } into g
            select new {
          Category = g.Max(p => "Quality Of Service"),
          Excellent = g.Count(p => p.Column2 != null),
          Very_Good = g.Count(p => p.Column3 != null),
          Good = g.Count(p => p.Column4 != null),
          Bad = g.Count(p => p.Column5 != null),
          Poor = g.Count(p => p.Column6 != null)
        }
    ).Union
    (   from t in
    (   from t in db.Feedbacks
        where
          t.DateOfVisit >= "2018-03-29" && t.DateOfVisit <= "2018-03-29"
        select new {
          Column1 = "Quality Of Food",
          Column2 = 
          t.QualityOfFood == "Excellent" ? (System.Int64?)1 : null,
          Column3 = 
          t.QualityOfFood == "VeryGood" ? (System.Int64?)1 : null,
          Column4 = 
          t.QualityOfFood == "Good" ? (System.Int64?)1 : null,
          Column5 = 
          t.QualityOfFood == "Bad" ? (System.Int64?)1 : null,
          Column6 = 
          t.QualityOfFood == "Poor" ? (System.Int64?)1 : null,
          Dummy = "x"
        })
        group t by new { t.Dummy } into g
            select new {
          Category = g.Max(p => "Quality Of Food"),
          Excellent = g.Count(p => p.Column2 != null),
          Very_Good = g.Count(p => p.Column3 != null),
          Good = g.Count(p => p.Column4 != null),
          Bad = g.Count(p => p.Column5 != null),
          Poor = g.Count(p => p.Column6 != null)
        }
    ).Union
    (   from t in
    (   from t in db.Feedbacks
        where
          t.DateOfVisit >= "2018-03-29" && t.DateOfVisit <= "2018-03-29"
        select new {
          Column1 = "Cleanliness Of Lounge",
          Column2 = 
          t.CleanlinessOfLounge == "Excellent" ? (System.Int64?)1 : null,
          Column3 = 
          t.CleanlinessOfLounge == "VeryGood" ? (System.Int64?)1 : null,
          Column4 = 
          t.CleanlinessOfLounge == "Good" ? (System.Int64?)1 : null,
          Column5 = 
          t.CleanlinessOfLounge == "Bad" ? (System.Int64?)1 : null,
          Column6 = 
          t.CleanlinessOfLounge == "Poor" ? (System.Int64?)1 : null,
          Dummy = "x"
        })
        group t by new { t.Dummy } into g
            select new {
          Category = g.Max(p => "Cleanliness Of Lounge"),
          Excellent = g.Count(p => p.Column2 != null),
          Very_Good = g.Count(p => p.Column3 != null),
          Good = g.Count(p => p.Column4 != null),
          Bad = g.Count(p => p.Column5 != null),
          Poor = g.Count(p => p.Column6 != null)
        }
    ).Union
    (   from t in
    (   from t in db.Feedbacks
        where
          t.DateOfVisit >= "2018-03-29" && t.DateOfVisit <= "2018-03-29"
        select new {
          Column1 = "Friendliness Of Staff",
          Column2 = 
          t.FriendlinessOfStaff == "Excellent" ? (System.Int64?)1 : null,
          Column3 = 
          t.FriendlinessOfStaff == "VeryGood" ? (System.Int64?)1 : null,
          Column4 = 
          t.FriendlinessOfStaff == "Good" ? (System.Int64?)1 : null,
          Column5 = 
          t.FriendlinessOfStaff == "Bad" ? (System.Int64?)1 : null,
          Column6 = 
          t.FriendlinessOfStaff == "Poor" ? (System.Int64?)1 : null,
          Dummy = "x"
        })
        group t by new { t.Dummy } into g
            select new {
          Category = g.Max(p => "Friendliness Of Staff"),
          Excellent = g.Count(p => p.Column2 != null),
          Very_Good = g.Count(p => p.Column3 != null),
          Good = g.Count(p => p.Column4 != null),
          Bad = g.Count(p => p.Column5 != null),
          Poor = g.Count(p => p.Column6 != null)
        }
    ).Union
    (   from t in
    (   from t in db.Feedbacks
        where
          t.DateOfVisit >= "2018-03-29" && t.DateOfVisit <= "2018-03-29"
        select new {
          Column1 = "Overall Experience",
          Column2 = 
          t.OverAllExperience == "Excellent" ? (System.Int64?)1 : null,
          Column3 = 
          t.OverAllExperience == "VeryGood" ? (System.Int64?)1 : null,
          Column4 = 
          t.OverAllExperience == "Good" ? (System.Int64?)1 : null,
          Column5 = 
          t.OverAllExperience == "Bad" ? (System.Int64?)1 : null,
          Column6 = 
          t.OverAllExperience == "Poor" ? (System.Int64?)1 : null,
          Dummy = "x"
        })
        group t by new { t.Dummy } into g
            select new {
          Category = g.Max(p => "Overall Experience"),
          Excellent = g.Count(p => p.Column2 != null),
          Very_Good = g.Count(p => p.Column3 != null),
          Good = g.Count(p => p.Column4 != null),
          Bad = g.Count(p => p.Column5 != null),
          Poor = g.Count(p => p.Column6 != null)
        }
    )