ICollection.Contains isn't working properly with Enums in the DbSet<>.Where

[RebelionTheGrey]

DbContext.DbSet.Where isn't work properly with ICollection<T>, where T is an Enum.

So, for example, the code like this always return emply ICollection<>:
var result = await context.Users.Where(e => e.WorkingRoles.Contains(sameRole)).ToListAsync();

but sameCollection in the code below isn't empty.

            var rolesCollection= await context.Users.Select(e => e.WorkingRoles).ToListAsync();
            var sameCollection = tst.Where(e => e.Contains(request.Role));

Part of code to reproduce

pubilc class User
{
    public Guid Id { get;set;}
    public ICollection<Roles> WorkingRoles { get; set; }
    public string Name { get; set;}
    public string Password { get; set; }
}
public enum Roles
{
    Customer = 1,
    Seller = 2
}

public class UserStoreDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public UserStoreDbContext(DbContextOptions<UserStoreDbContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder); 
        
        modelBuilder.Entity<User>().Property(f => f.WorkingRoles).HasConversion(new RolesToStringConveter());
    }
}

public class RolesToStringConveter : ValueConverter<ICollection<Roles>, string>
{
    public RolesToStringConveter() : base(v => string.Join(";", v.Select(f => f.ToString())),
                                 v => v.Length > 0 ? v.Split(new[] { ';' }).Select(f => (Roles)Enum.Parse(typeof(Roles), f)).ToList() : new List<Roles>())
        { }
}

Let's context be the scoped instance of UserStoreDbContext resolved by standard MS DI.

class TmpStorage
{
     public Guid Id { get; set; }
     public ICollection<Roles> Roles { get; set; }
 }

public ICollection<User> GetUsersByWorkingRole(Roles role)
{
     var resultUserCollection = await context.Users.Where(e => e.WorkingRoles.Contains(role)).ToListAsync();

     var tmpRolesCollectionsList = await context.Users.Select(e => new TmpStorage() { Roles = e.WorkingRoles, Id = e.Id }).ToListAsync();
     var usersId = tmpRolesCollectionsList .Where(e => e.Roles.Contains(role)).Select(f => f.Id).ToList();
     var anotherResultUserCollection = await context.Users.Where(e => usersId.Contains(e.Id)).ToListAsync();

     return resultUserCollection;
}

So, in this method resultUserCollection always empty, but anotherResultUserCollection not, it contains correct users which have asked role in their WorkingRoles collection.

Further technical details

EF Core version: 3.1.0-preview3.19554.8
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: 3.1.100-preview3-014645
Operating system: Windows Server 2016.
IDE: Microsoft Visual Studio Community 2019 Preview Version 16.4.0 Preview 5.0

[ajcvickers]

Note for triage: repros on 3.1. SQL generated is:

      SELECT [u].[Id], [u].[Name], [u].[Password], [u].[WorkingRoles]
      FROM [Users] AS [u]
      WHERE CAST(1 AS bit) = CAST(0 AS bit)

/cc @maumar @smitpatel

[smitpatel]

We cannot translate Contains over ICollection<Roles> which is mapped as string on database side using value converter.

[ajcvickers]

@smitpatel So we should throw then, right?

[smitpatel]

Yup. Somewhere I guess we saw different types and converted it to false.