如何在VB.NET中编写此SQL查询LINQ

Question

如何在VB.NET LINQ中编写此SQL查询？

SELECT COUNT(*) AS COUNT_NO 
FROM A INNER JOIN B ON A.ID = B.ID 
WHERE A.SERIAL_NO IN (SELECT MAX (A.SERIAL_NO) 
         FROM A INNER JOIN B ON A.ID = B.ID 
         GROUP BY A.SUBJECT_ID) 
AND B.STATUS = 'N'; 

感谢

Answer 1

好了，直接合理的C＃转换会是这样的：

var validSerialNumbers = from a in dbContext.A 
         join b in dbContext.B on a.ID equals b.ID 
         group a.SerialNo by a.SubjectId into g 
         select g.Max(); 

var query = from a in dbContext.A 
      join b in dbContext.B on a.ID equals b.ID 
      where validSerialNumbers.Contains(a.SerialNo) 
      where b.Status == "N" 
      select new { a, b }; 

var count = query.Count(); 

我会想象你可以进行转换的VB非常相似。不幸的是，Reflector在这个方面做得不好，但是就我所知，这些条款应该换算为1。