将SQL查询转换为LINQ/dot表示法

Question

我一直在将下面的sql转换为LINQ查询。基本上，我只想把我的考试分组在我的表中，然后计算通过、不通过、参加的次数和通过率

SELECT testID, 
(SELECT COUNT(testID) FROM tblGrade AS b 
    WHERE b.testID= a.testID AND b.Grade < 80) AS 'Failed',
(SELECT COUNT(testID) FROM tblGrade AS b 
    WHERE b.testID= a.testIDAND b.Grade >= 80) AS 'Passed',
--taken = failed + passed, 
--passingrate = (passed / taken) * 100
FROM dbo.tblGrade AS a
GROUP BY testID
ORDER BY testID

编辑：我的解决方案如下：它有效，但我认为这不是最好的方法，尤其是失败和通过的属性。

var xx1 = _unitOfWork.tblGrade.GetAll().GroupBy(a => new { a.testID});
var xx2 = xx1.Select(b => new
           {
             testID= b.Key.testID,
             failed = _unitOfWork.tblGrade.Query(filter: a => a.testID == b.Key.testID).Where(c => c.Grade < 80).Count(),
             passed = _unitOfWork.tblGrade.Query(filter: a => a.testID == b.Key.testID).Where(c => c.Grade >= 80).Count(),
             //taken = failed + passed, 
             //passingrate = (passed / taken) * 100
            }).ToList();

Answer 1

Count()可以将谓词（Expression<Func<T, bool>>）作为参数，所以我认为您可以只执行

var xx2 = xx1.Select(b => new
           {
             testID= b.Key.testID,
             failed = b.Count(x => x.Grade < 80),
             passed = b.Count(x => x.Grade >=80),
             taken = b.Count()
            })
            .Select(b => new {
               testID = b.TestID,
               failed = b.failed,
               passed = b.passed,
               taken = b.taken,
               passingrate = b.taken == 0 ? 0.0 : ((float)b.passed / b.taken) * 100
            }).ToList();

查询语法具有（在本例中为usefull）let关键字，因此读取可能更容易

var xx2 = from b in xx1
          let failed = b.Count(x => x.Grade < 80)
          let passed = b.Count(x => x.Grade >= 80)
          let taken = failed + passed
          select new {
              testID = b.Key.TextID,
              failed = failed,
              passed = passed,
              taken = taken,
              passingrate = taken == 0 ? 0.0 : ((float)passed / taken) * 100
          }

Answer 2

我会这样做：

  var h = list.GroupBy(a => a.testID)
            .Select(a => {
               int _failed = a.Count(g => g.Grade < 80);
               int _passed = a.Count(g => g.Grade >= 80);
               int _rate = (int)(_passed / (double)a.Count() * 100.0);
               return new {
                 testID = a.Key,
                 failed = _failed,
                 passed = _passed,
                 taken = a.Count(),
                 rate = _rate,
              };     
            }); 

完整测试代码：

void Main()
{
  List<aGrade> list = new List<aGrade>() {
    new aGrade() { Grade = 40, testID = 1 },
    new aGrade() { Grade = 50, testID = 1 },
    new aGrade() { Grade = 45, testID = 1 },
    new aGrade() { Grade = 70, testID = 1 },
    new aGrade() { Grade = 80, testID = 1 },
    new aGrade() { Grade = 90, testID = 1 },
    new aGrade() { Grade = 40, testID = 2 },
    new aGrade() { Grade = 50, testID = 2 },
    new aGrade() { Grade = 45, testID = 2 },
    new aGrade() { Grade = 70, testID = 2 },
    new aGrade() { Grade = 80, testID = 2 },
    new aGrade() { Grade = 90, testID = 2 },
    new aGrade() { Grade = 40, testID = 3 },
    new aGrade() { Grade = 50, testID = 3 },
    new aGrade() { Grade = 45, testID = 3 },
    new aGrade() { Grade = 70, testID = 3 },
    new aGrade() { Grade = 80, testID = 3 },
    new aGrade() { Grade = 90, testID = 3 },
  };    
  var h = list.GroupBy(a => a.testID)
            .Select(a => {
               int _failed = a.Count(g => g.Grade < 80);
               int _passed = a.Count(g => g.Grade >= 80);
               int _rate = (int)(_passed / (double)a.Count() * 100.0);
               return new {
                 testID = a.Key,
                 failed = _failed,
                 passed = _passed,
                 taken = a.Count(),
                 rate = _rate,
              };     
            }); 
  h.Dump(); 
}
// Define other methods and classes here
public class aGrade
{
    public int Grade {  get; set; }
    public int testID { get; set; }
}   

注意-此代码将在LinqPad中按原样运行。（linqpad.com）我推荐linqpad来测试这种类型的代码。。。让你的工作变得简单。试试看。