c# - LINQ to SQL : Group, 计数、求和。我很困惑

Question

大家早上好 我整个上午都被困在这上面，感觉就像碰壁了。我很乐意在这一点上提供任何建议。 我的表格基本如下:

PatientName|LivingSpace
-----------|-----------
Patient 1  | Unit 1
Patient 2  | Unit 1
Patient 3  | Unit 2
Patient 4  | Unit 2
Patient 5  | Unit 3
Patient 6  | Unit 3
Patient 7  | Unit 3
Patient 8  | Unit 3

我需要一个 LINQ to SQL 查询来说明这一点:

   Unit|Count
   ----|-----
Unit 1 |  2
Unit 2 |  2
Unit 3 |  4
TOTAL  |  8

我的 SQL 查询工作正常，我只是在将其转换为 LINQ 时遇到问题:

SELECT LivingSpace, COUNT(LivingSpace) AS LivingSpace
FROM PatientTable
WHERE Status = 'Active'
GROUP BY LivingSpace
    UNION ALL
    SELECT 'SUM' LivingSpace, COUNT(LivingSpace)
    FROM PatientTable

Answer 1

var counts = from x in ctx.PatientTable
      group x by x.LivingSpace into y
      select new { Key = y.Key Count = y.Count() };

var total  = new { Key = "Total" , Count = ctx.PatientTable.Count() };

var full = counts.ToList();

full.Add(total);