SQL to Linq - NET CORE 5

Question

我有一个这样的SQL语句:

select      
b.ActivityRecordId, b.ActivityName, b.ActivityDetail, 
b.CustomerId, e.CustomerName, 
b.JobStatusId, c.JobStatusName, b.EstimateStartDate, 
b.EstimateFinishDate, b.StartedDateTime, b.FinishedDateTime,
a.ActivityRecordProgressId, a.CustomerContactId, 
Concat(d.FirstName, ' ', d.MiddleName, ' ', d.LastName) as fullName,
a.Started, a.Finished, a.ActivityDescription, 
g.ActivityTypeId, g.ActivityTypeName, a.ActivitySubTypeId, f.ActivitySubTypeName
from        
ActivityRecordProgress a
right join 
ActivityRecord b on a.ActivityRecordId = b.ActivityRecordId
left join 
ActivitySubType f on a.ActivitySubTypeId = f.ActivitySubTypeId
left join 
ActivityType g on f.ActivityTypeId = g.ActivityTypeId
left join 
CustomerContact d on a.CustomerContactId = d.CustomerContactId
inner join 
JobStatus c on b.JobStatusId = c.JobStatusId
inner join 
Customer e on b.CustomerId = e.CustomerId

然后我尝试将其转换为Linq:

var obj = (from a in _db.ActivityRecords
join b in _db.ActivityRecordProgresses on a.ActivityRecordId equals b.ActivityRecordId into ab
from p in ab.DefaultIfEmpty()
join c in _db.Customers on a.CustomerId equals c.CustomerId
join e in _db.CustomerContacts on c.CustomerId equals e.CustomerId
join d in _db.JobStatuses on a.JobStatusId equals d.JobStatusId
join f in _db.ActivitySubTypes on p.ActivitySubTypeId equals f.ActivitySubTypeId                      
join g in _db.ActivityTypes on f.ActivitySubTypeId equals g.ActivityTypeId
select new OutstandingActivityViewModel { 
ActivityRecordId = p.ActivityRecordId,
ActivityName = a.ActivityName,
ActivityDetail = a.ActivityDetail,
CustomerId = a.CustomerId,
CustomerName = c.CustomerName,
JobStatusId = a.JobStatusId,
JobStatusName = d.JobStatusName,
EstimateStartDate = a.EstimateStartDate,
StartedDateTime = a.StartedDateTime,
EstimateFinishDateTime = a.EstimateFinishDate,
FinishedDateTime = a.FinishedDateTime,
FirstName = e.FirstName, MiddleName = e.MiddleName, LastName = e.LastName,
Started = p.Started, Finished = p.Finished,
ActivityTypeName = g.ActivityTypeName, ActivitySubTypeName = f.ActivitySubTypeName,
ActivityDescription = p.ActivityDescription
}).ToListAsync();

但是结果是不一样的。SQL结果是正确的。它只有4张唱片。但在Linq中，它出现了6条记录。我肯定我在linq语法上做错了什么。

谁能告诉我我的语法错误在哪里?

真的很感激-谢谢你。

Answer 1

感谢您评论我的问题。它非常有用。最后我通过使用SQL生成的反复试验找到了答案。下面是Linq语法的答案:

var obj = (from a in _db.ActivityRecords
join b in _db.ActivityRecordProgresses on a.ActivityRecordId equals b.ActivityRecordId into leftsatu
from leftkesatu in leftsatu.DefaultIfEmpty()
join c in _db.ActivitySubTypes on leftkesatu.ActivitySubTypeId equals c.ActivitySubTypeId into leftdua
from leftkedua in leftdua.DefaultIfEmpty()
join d in _db.ActivityTypes on leftkedua.ActivitySubTypeId equals d.ActivityTypeId into lefttiga
from leftketiga in lefttiga.DefaultIfEmpty()
join e in _db.CustomerContacts on leftkesatu.CustomerContactId equals e.CustomerContactId into leftempat
from leftkeempat in leftempat.DefaultIfEmpty()
join f in _db.JobStatuses on a.JobStatusId equals f.JobStatusId
join g in _db.Customers on a.CustomerId equals g.CustomerId
select new OutstandingActivityViewModel
{
ActivityRecordId = a.ActivityRecordId,
ActivityName = a.ActivityName,
ActivityDetail = a.ActivityDetail,
CustomerId = a.CustomerId,
CustomerName = g.CustomerName,
JobStatusId = a.JobStatusId,
JobStatusName = f.JobStatusName,
EstimateStartDate = a.EstimateStartDate,
StartedDateTime = a.StartedDateTime,
EstimateFinishDateTime = a.EstimateFinishDate,
FinishedDateTime = a.FinishedDateTime,
FirstName = leftkeempat.FirstName,
MiddleName = leftkeempat.MiddleName,
LastName = leftkeempat.LastName,
Started = leftkesatu.Started,
Finished = leftkesatu.Finished,
ActivityTypeName = leftketiga.ActivityTypeName,
ActivitySubTypeName = leftkedua.ActivitySubTypeName,
ActivityDescription = leftkesatu.ActivityDescription
}
);

它将生成如下SQL:

SELECT      [a].[ActivityRecordId], [a].[ActivityName], [a].[ActivityDetail], [a].[CustomerId], [c0].[CustomerName], [a].[JobStatusId], [j].[JobStatusName], 
[a].[EstimateStartDate], [a].[StartedDateTime], [a].[EstimateFinishDate] AS [EstimateFinishDateTime], [a].[FinishedDateTime], 
[c].[FirstName], [c].[MiddleName], [c].[LastName], 
[a0].[Started], [a0].[Finished], [a2].[ActivityTypeName], [a1].[ActivitySubTypeName], [a0].[ActivityDescription]
FROM [ActivityRecord] AS [a]
LEFT JOIN [ActivityRecordProgress] AS [a0] ON [a].[ActivityRecordId] = [a0].[ActivityRecordId]
LEFT JOIN [ActivitySubType] AS [a1] ON [a0].[ActivitySubTypeId] = [a1].[ActivitySubTypeId]
LEFT JOIN [ActivityType] AS [a2] ON [a1].[ActivitySubTypeId] = [a2].[ActivityTypeId]
LEFT JOIN [CustomerContact] AS [c] ON [a0].[CustomerContactId] = [c].[CustomerContactId]
INNER JOIN [JobStatus] AS [j] ON [a].[JobStatusId] = [j].[JobStatusId]
INNER JOIN [Customer] AS [c0] ON [a].[CustomerId] = [c0].[CustomerId]