Linq to SQL使用Group by(Linq to SQL using Group by)

Linq to SQL使用Group by(Linq to SQL using Group by)

我不知道如何使用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

I am not sure how I can express the following SQL using linq to 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

最满意答案

你可以试试这个：

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};

You can try this:

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};