I have a SQL Statement like this:

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

Then I tried to convert it to 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();

But the result is not the same. The SQL result is the right one. It only 4 records. But in Linq, it appears 6 records. I'm sure I did something wrong with the linq syntax.

Can anyone show me where the mistake in my syntax is?

Really appreciated - thank you.

CodePudding user response：

Thanks for commenting my question. It was very useful. Finally I found the answer after trial and error using SQL generated. Below is the answer for the Linq syntax:

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

It will generate SQL like:

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]