ThenInclude not working in join since 2.1.1

[ManuelHaas]

The following statement was added about a year ago and worked till update 2.1.1:

var data = await (from c in db.Checks
   join ct in db.CheckTemplates
		.Include(a => a.Implementers).ThenInclude(u => u.User)
		.Include(o => o.ObservationLine) 
   on c.CheckTemplateId equals ct.CheckTemplateId
   where c.CheckId == checkId
   select new
   {
	   CheckId = c.CheckId,
	   Abbreviation = ct.Abbreviation,
	   Label = ct.Label,
	   Deadline = c.Deadline,
	   Started = c.Start,
	   Description = ct.Description,
	   Implementers = ct.Implementers,
	   NumExaminers = ct.Examiners.Count
   }).SingleAsync();

Now (EF Core 2.1.1) the ThenInclude(u => u.User) does not include the User class anymore! So for example data.Implementers.First().User.LastName leads to a null reference exception now.

The above statement leads to the following sql-code:

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (1ms) [Parameters=[@__checkId_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [c].[CheckId], [ct].[Abbreviation], [ct].[Label], [c].[Deadline], [c].[Start] AS [Started], [ct].[Description], (
SELECT COUNT(*)
FROM [CheckTemplateExaminers] AS [c0]
WHERE [ct].[CheckTemplateId] = [c0].[CheckTemplateId]
) AS [NumExaminers], [ct.ObservationLine].[EmailFromAdress], [ct.ObservationLine].[EmailFromDisplayName], [ct].[CheckTemplateId]
FROM [Checks] AS [c]
INNER JOIN [CheckTemplates] AS [ct] ON [c].[CheckTemplateId] = [ct].[CheckTemplateId]
LEFT JOIN [ObservationLines] AS [ct.ObservationLine] ON [ct].[ObservationLineId] = [ct.ObservationLine].[ObservationLineId]
WHERE [c].[CheckId] = @__checkId_0
ORDER BY [ct].[CheckTemplateId]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (11ms) [Parameters=[@__checkId_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [ct.Implementers].[CheckTemplateId], [ct.Implementers].[UserId], [t].[CheckTemplateId]
FROM [CheckTemplateImplementers] AS [ct.Implementers]
INNER JOIN (
SELECT TOP(1) [ct0].[CheckTemplateId]
FROM [Checks] AS [c1]
INNER JOIN [CheckTemplates] AS [ct0] ON [c1].[CheckTemplateId] = [ct0].[CheckTemplateId]
LEFT JOIN [ObservationLines] AS [ct.ObservationLine0] ON [ct0].[ObservationLineId] = [ct.ObservationLine0].[ObservationLineId]
WHERE [c1].[CheckId] = @__checkId_0
ORDER BY [ct0].[CheckTemplateId]
) AS [t] ON [ct.Implementers].[CheckTemplateId] = [t].[CheckTemplateId]
ORDER BY [t].[CheckTemplateId]

This is a severe issue I think (for me anyway) because this has already worked and is used widely in my app.

Further technical details

EF Core version: 2.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win10 x64
IDE: Visual Studio 2017 15.7.4

[divega]

@ManuelHaas thanks for reporting this. Could you provide a more complete repro?

@maumar could you please investigate? This is a possible regression in 2.1.1.

[smitpatel]

Duplicate of #12181

[Eilon]

That dup bug was closed by-design. So we don't need to patch anything?

[ManuelHaas]

@smitpatel thanks for directing me to #12181!

I am sorry to say but it is really annoying that with every new version some of the statements that already worked for some time are not working after the update.

Changing a behaviour like this that is already used in production scenarios should be done much more wisely! Like with some kind of deprecated message in the log or something that way.

After searching the solution for that kind of statements I have to investigate more than 160 statements if they are affected by the change!

[divega]

@maumar will be working on providing an easier workaround for this issue.

[maumar]

@ManuelHaas you can try the following workaround:

var checkId = 1;

var data = (from c in db.Checks
    		 join ct in db.CheckTemplates
				  .Include(a => a.Implementers).ThenInclude(u => u.User)
			 on c.CheckTemplateId equals ct.CheckTemplateId
			 where c.CheckId == checkId
			 select new
			 {
				 CheckId = c.CheckId,
				 Description = ct.Description,
				 Implementers = ProjectImplementers(ct),
				 NumExaminers = ct.Examiners.Count
			 }).FirstOrDefault();

        public static List<Implementer> ProjectImplementers(CheckTemplate checkTemplate) => checkTemplate.Implementers;

This mimics what EFCore was doing under the covers before 2.1.