Converting SQL Query in to Linq or in lambda

Question

I am trying to convert it in LINQ but I am not getting success , I would appreciate if someone help me out to convert it in linq or write the query using lambda expression

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'

The result of this query is coming in this way

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

I have zero feedbacks right now in my table that is why it is showing null in category and all zero's.

My table looks like this

ID    QualityOfFood   QualityOfServices   CleanlinessOfLounge     FreindlinessOfStaff      OverALLExperience

I asked this type of question but "this type" not the same , so requesting to don't mark it dublicate

Possible duplicate of [Query Related to SQL for count records](https://stackoverflow.com/questions/49579682/query-related-to-sql-for-count-records) — Felix D., Apr 13 '18 at 04:51
You should not repost your question with different accounts ... — Felix D., Apr 13 '18 at 04:51

score 0 · Answer 1 · answered Apr 13 '18 at 06:07

I've Found it by using 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)
        }
    )

Perhaps my [Recipe for SQL Conversion](https://stackoverflow.com/a/49245786/2557128) would help in the future. — NetMage, Apr 13 '18 at 17:34

Converting SQL Query in to Linq or in lambda

1 Answers1