Query: Contains method on client list of nullable guid/int fails to translate to server

[winterlimelight]

When a nullable Guid and a non-nullable Guid are compared using the Contains() method I get a log message: "could not be translated and will be evaluated locally". Unfortunately this is then downloading a big table to perform the comparison causing a performance problem. This occurs whether the nullable is an argument to or the caller of .Contains() as will be shown in the four cases below.

This may be related to #7616

Steps to reproduce

Here is a cut-down version of the entity:

public class Skill
{
	public Guid SkillId { get; set; }
	public Guid? PrimarySkillId { get; set; }

	// Navigation (self-join)
	public Skill PrimarySkill {get;set;}
}

Here are the four cases with resulting log messages and MySQL

//nullable Guid list, nullable field - works
List<Guid?> primarySkills = ...;
var outerQuery = dc.Skills.Where(s => primarySkills.Contains(s.PrimarySkillId));
var result = await outerQuery.ToListAsync();

SELECT s.SkillId, s.Name, s.PrimarySkillId, s.Source, s.SourceId
FROM Skills AS s
WHERE s.PrimarySkillId IN (...)

//nullable Guid list, non-nullable field - fails
List<Guid?> primarySkills = ...;
var outerQuery = dc.Skills.Where(s => primarySkills.Contains(s.SkillId) );
var result = await outerQuery.ToListAsync();

The LINQ expression '{__primarySkills_0 => Contains(Convert([s].SkillId))}' could not be translated and will be evaluated locally.
The LINQ expression 'Contains(Convert([s].SkillId))' could not be translated and will be evaluated locally.
SELECT s.SkillId, s.Name, s.PrimarySkillId, s.Source, s.SourceId
FROM Skills AS s

and the inverse is true:

//non-nullable Guid list, non-nullable field - works
List<Guid> primarySkills = ...; 
var outerQuery = dc.Skills.Where(s => primarySkills.Contains(s.SkillId));
var result = await outerQuery.ToListAsync();

SELECT s.SkillId, s.Name, s.PrimarySkillId, s.Source, s.SourceId
FROM Skills AS s
WHERE s.SkillId IN (...)

//non-nullable Guid, nullable field - fails
List<Guid> primarySkills = ...;
var outerQuery = dc.Skills.Where(s => /*primarySkills.Contains(s.SkillId) ||*/ s.PrimarySkillId != null && primarySkills.Contains(s.PrimarySkillId.Value));
var result = await outerQuery.ToListAsync();

The LINQ expression '{__primarySkills_0 => Contains(Convert([s].PrimarySkillId))}' could not be translated and will be evaluated locally.
The LINQ expression 'Contains(Convert([s].PrimarySkillId))' could not be translated and will be evaluated locally.
SELECT s.SkillId, s.Name, s.PrimarySkillId, s.Source, s.SourceId
FROM Skills AS s
WHERE s.PrimarySkillId IS NOT NULL

Unfortunately what I need to do is compare to both fields at once - so I can't get away from the nullable problem so easily. Ideally what I want is:

.Where(s => primarySkills.Contains(s.PrimarySkillId ?? s.SkillId));

Further technical details

These are my relevant project references:
PackageReference Include="Microsoft.EntityFrameworkCore" Version="1.1.1"
PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="1.1.1"
PackageReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.1.0-preview4-final"
PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="1.1.0-"
PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Design" Version="1.1.0-"

Workaround

Having written all this up I realised a stupidly simple workaround, and this works fine, but I'm thinking this is still something that'd be useful for you to know about.

List<Guid> primarySkills = ...;
List<Guid?> primarySkillsNullable = primarySkills.Cast<Guid?>().ToList();
var outerQuery = dc.Skills.Where(s => 
	primarySkills.Contains(s.SkillId) || primarySkillsNullable.Contains(s.PrimarySkillId));

SELECT s.SkillId, s.Name, s.PrimarySkillId, s.Source, s.SourceId
FROM Skills AS s
WHERE s.SkillId IN (...) OR s.PrimarySkillId IN (...)

[AnilCAE]

This seems this also same when we use contains with List<int?>?

[TPJ11]

For EF team to see the same issue with INT - http://stackoverflow.com/questions/42856379/c-sharp-linq-nullable-int-field-contain-within-listint-sql-translation/42856449

[pmiddleton]

I just took a look into this issue, and it can be closed.

I can confirm the issue is happening on 1.1.1, but it has already been resolved in the dev branch.

@ajcvickers @smitpatel - Let me know if you want me to add a unit test for this issue to test for regressions.

[ajcvickers]

@pmiddleton PRs with additional tests are always welcome. 😸