将 SQL 原始查询转换为 Linq

Question

我有3张表(活动，参与者和注册(，我想知道每个参与者参加的活动数量。我是编写 SQL Linq 查询的新手，请帮助我将以下查询转换为 Linq。

Select count(Activities.ActivityId) AS NO_Activities,Activities.ActivityId,Enrollments.PostTestScore1
from Participants,Activities,Enrollments
where Participants.ParticipantId=Enrollments.ParticipantId
and Activities.ActivityId=Enrollments.ActivityId
and Enrollments.ParticipantId= 2883
Group By Enrollments.PostTestScore1,Activities.ActivityId;

谢谢

Answer 1

我使用 Lambda 查询找到了答案，如下所示。

var no_ctivities = db.Activities
.Join(
db.Enrollments,
ac => ac.ActivityId,
en => en.ActivityId,
(enr, act) => new { en = enr, ac = act })
.Join(
db.Participants.Where(pr => pr.ParticipantId == id),
en => en.ac.ParticipantId,
prt => prt.ParticipantId,
(enr, prt) => new { enr.ac.PostTestScore1, enr.ac.PostTestScore2, enr.ac.ActivityId, enr.ac.AttendFirstday, enr.ac.AttendSecondDay })
.Select(c => new
{
c.ActivityId,
c.PostTestScore1,
c.PostTestScore2,
c.AttendFirstday,
c.AttendSecondDay
}).count();

Answer 2

var query = from a1 in Activities
join e1 in Enrollments on a1.ActivityId equals e1.ActivityId
join p1 in Participants on e1.ParticipantId equals p1.ParticipantId
where e1.ParticipantId= 2883
group new { e1, a1 }
by new { e1.PostTestScore1, a1.ActivityId } into g
select new
{
ActivityId = g.Key.ActivityId,
PostTestScore1 = g.Key.PostTestScore1,
NO_Activities = g.Count()
};