Linq to SQL using Group by

Question

我不确定如何使用linq to SQL来表达以下SQL

SELECT p.*, T1.RecordCount
FROM Person p
INNER JOIN 
(
    SELECT PersonId, count(1) as RecordCount FROM [PersonView]
    WHERE LastName like 'LIS%'
    GROUP BY PersonId 
) AS T1 ON T1.PersonId = p.PersonId

Answer 1

你可以试试:

var innerQuery=from pv in context.PersonView
               where pv.LasName.StartWith("LIS")
               group pv by pv.PersonId into g
               select new{ PersonId=g.Key,RecordCount = g.Count()};
var query= from p in context.Person
           join t1 in innerQuery on p.PersonId equals t1.PersonId
           select new{p, t1.RecordCount};