Efcore: Query: Error for queries with enum parameters whose value is of underlying type but the value expected by type mapping is the actual enum type

[aguerrieri82]

Steps to reproduce

I have the following query:

```c#
result.Items = await query.Select(a => new MedicalReportSectionListView()
{
Id = a.Id,
OperatorId = a.OperatorId,
Name = a.Name,
Order = a.Order,
DefaultContent = a.DefaultContent,
Hidden = (a.Flags & MedicalReportSectionFlags.Private) != 0,
Active = (a.Flags & MedicalReportSectionFlags.Active) != 0
}).ToArrayAsync();

the issue is in:

`Hidden = (a.Flags & MedicalReportSectionFlags.Private) != 0,`

Entity:
```c#
[Flags]
public enum MedicalReportSectionFlags
{
    None = 0x0,
    Active = 0x1,
    Private = 0x2,
    Attachment= 0x4
}
public class MedicalReportSection
{
    [Key]
    public int Id { get; set; }
    public Guid OperatorId { get; set; }
    public string Name { get; set; }
    public int Order { get; set; }
    public string DefaultContent { get; set; }
    public virtual Operator Operator { get; set; }
    public MedicalReportSectionFlags Flags { get; set; }
}

Model:
```c#
public class MedicalReportSectionListView
{
public int Id { get; set; }
public Guid OperatorId { get; set; }
public string Name { get; set; }
public int Order { get; set; }
public string DefaultContent { get; set; }
public bool Hidden { get; set; }
public bool Active { get; set; }
}

Stack trace:

System.InvalidCastException
HResult=0x80004002
Message=Invalid cast from 'System.Int32' to 'DocSpecial.Data.Entities.MedicalReportSectionFlags'.
Source=System.Private.CoreLib
StackTrace:
at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
at System.Int32.System.IConvertible.ToType(Type type, IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType)
at Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter2.Sanitize[T](Object value) at Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter2.<>c__DisplayClass3_02.<SanitizeConverter>b__0(Object v) at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.CreateParameter(DbCommand command, String name, Object value, Nullable1 nullable)
at Microsoft.EntityFrameworkCore.Storage.Internal.TypeMappedRelationalParameter.AddDbParameter(DbCommand command, Object value)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalParameterBase.AddDbParameter(DbCommand command, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.CreateCommand(RelationalCommandParameterObject parameterObject, Guid commandId, DbCommandMethod commandMethod) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.<ExecuteReaderAsync>d__17.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.AsyncEnumerator.<InitializeReaderAsync>d__18.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.d__72.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.AsyncEnumerator.d__17.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.d__641.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__641.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToArrayAsync>d__651.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at DocSpecial.Core.MedicalReportSectionListHandler.d__0.MoveNext() in D:\Development\IctMakers\Git\DocSpecial\DocSpecial.Core\Commands\MedicalReportSectionListHandler.cs:line 42

This exception was originally thrown at this call stack:
System.Convert.DefaultToType(System.IConvertible, System.Type, System.IFormatProvider)
int.System.IConvertible.ToType(System.Type, System.IFormatProvider)
System.Convert.ChangeType(object, System.Type, System.IFormatProvider)
System.Convert.ChangeType(object, System.Type)
Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.CreateParameter(System.Data.Common.DbCommand, string, object, bool?)
Microsoft.EntityFrameworkCore.Storage.Internal.TypeMappedRelationalParameter.AddDbParameter(System.Data.Common.DbCommand, object)
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalParameterBase.AddDbParameter(System.Data.Common.DbCommand, System.Collections.Generic.IReadOnlyDictionary)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.CreateCommand(Microsoft.EntityFrameworkCore.Storage.RelationalCommandParameterObject, System.Guid, Microsoft.EntityFrameworkCore.Diagnostics.DbCommandMethod)
...
[Call Stack Truncated]

```
Thanks

Further technical details

EF Core version: 5.0.0-alpha1.19552.2 - 3.1.0-preview3.19554.8
Database provider: SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10 - 1903
IDE: Visual Studio 2019 Preview 16.4.0 Preview 6.0

[blayvant]

Having the same issue using Microsoft.EntityFrameworkCore.Tools & Microsoft.EntityFrameworkCore.SqlServer 3.1.1
Is there a work-around? Without this, we're unable to upgrade our production code.

[vadim82]

This does seem like a pretty serious problem. It prevents us from being able to use Flags in our queries. Has anyone found a workaround for this?

[ajcvickers]

@maumar @smitpatel @roji Workarounds here?

[aguerrieri82]

for me worked change:
(a.Flags & MedicalReportSectionFlags.Private) != 0
to
(a.Flags & MedicalReportSectionFlags.Private) == MedicalReportSectionFlags.Private

[maumar]

@aguerrieri82 what is the contents of "query"

I can't reproduce this issue on current bits, as well as 3.1.1 and 3.1.0.

Here is the code that I'm using. Please let me know what are the differences between your sample and this, or ideally provide full runnable repro.

    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new Context19128())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();

                var e1 = new MedicalReportSection
                {
                    Name = "name1",
                    DefaultContent = "content1",
                    Order = 1,
                    OperatorId = Guid.NewGuid(),
                    Flags = MedicalReportSectionFlags.Active
                };

                var e2 = new MedicalReportSection
                {
                    Name = "name2",
                    DefaultContent = "content2",
                    Order = 2,
                    OperatorId = Guid.NewGuid(),
                    Flags = MedicalReportSectionFlags.Attachment
                };

                var e3 = new MedicalReportSection
                {
                    Name = "name3",
                    DefaultContent = "content3",
                    Order = 3,
                    OperatorId = Guid.NewGuid(),
                    Flags = MedicalReportSectionFlags.None
                };

                var e4 = new MedicalReportSection
                {
                    Name = "name4",
                    DefaultContent = "content4",
                    Order = 4,
                    OperatorId = Guid.NewGuid(),
                    Flags = MedicalReportSectionFlags.Private
                };

                var e5 = new MedicalReportSection
                {
                    Name = "name4",
                    DefaultContent = "content4",
                    Order = 4,
                    OperatorId = Guid.NewGuid(),
                    Flags = MedicalReportSectionFlags.Private | MedicalReportSectionFlags.Active
                };

                var e6 = new MedicalReportSection
                {
                    Name = "name4",
                    DefaultContent = "content4",
                    Order = 4,
                    OperatorId = Guid.NewGuid(),
                    Flags = MedicalReportSectionFlags.Private | MedicalReportSectionFlags.Active | MedicalReportSectionFlags.Attachment
                };

                ctx.MedicalReportSections.AddRange(e1, e2, e3, e4, e5, e6);
                ctx.SaveChanges();
            }

            using (var ctx = new Context19128())
            {
                var result = ctx.MedicalReportSections.Select(a => new MedicalReportSectionListView()
                {
                    Id = a.Id,
                    OperatorId = a.OperatorId,
                    Name = a.Name,
                    Order = a.Order,
                    DefaultContent = a.DefaultContent,
                    Hidden = (a.Flags & MedicalReportSectionFlags.Private) != 0,
                    Active = (a.Flags & MedicalReportSectionFlags.Active) != 0
                }).ToArray();
            }
        }
    }

    public class Context19128 : DbContext
    {
        public DbSet<MedicalReportSection> MedicalReportSections { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=.;Database=Repro19128_take2;Trusted_Connection=True;MultipleActiveResultSets=True");
        }
    }




    [Flags]
    public enum MedicalReportSectionFlags
    {
        None = 0x0,
        Active = 0x1,
        Private = 0x2,
        Attachment = 0x4
    }
    public class MedicalReportSection
    {
        [Key]
        public int Id { get; set; }
        public Guid OperatorId { get; set; }
        public string Name { get; set; }
        public int Order { get; set; }
        public string DefaultContent { get; set; }
        //public virtual Operator Operator { get; set; }
        public MedicalReportSectionFlags Flags { get; set; }
    }


    public class MedicalReportSectionListView
    {
        public int Id { get; set; }
        public Guid OperatorId { get; set; }
        public string Name { get; set; }
        public int Order { get; set; }
        public string DefaultContent { get; set; }
        public bool Hidden { get; set; }
        public bool Active { get; set; }
    }

[aguerrieri82]

I'm providing the full code, i tried again, and I confirm the issue:

var dataContext = Context.Require<DsDbContext>();

var query = (IQueryable<MedicalReportSection>)dataContext.MedicalReportSections;

if (command.Filter.OperatorId != null)
{
    Context.Require<ISecurityContext>().EnsureOperatorVisible(command.Filter.OperatorId.Value);

    query = query.Where(a => a.OperatorId == command.Filter.OperatorId);
}

if (!string.IsNullOrWhiteSpace(command.Filter.Text))
{
    query = query.Where(a => (a.Name).Contains(command.Filter.Text))
            .OrderBy(a => a.Order).ThenBy(a => a.Name);
}
else
    query = query.OrderBy(a => a.Order).ThenBy(a => a.Name);

var result = new CoPaginationResult<MedicalReportSectionListView>();
result.Count = query.Count();

query = query.Skip(command.Filter.Start).TakeAllWithZero(command.Filter.Count);

//query = query.Where(a => ((a.Flags & MedicalReportSectionFlags.Active) != 0) || ((a.Flags & MedicalReportSectionFlags.Private) != 0));

result.Items = await query.Select(a => new MedicalReportSectionListView()
{
    Id = a.Id,
    OperatorId = a.OperatorId,
    Name = a.Name,
    Order = a.Order,
    DefaultContent = a.DefaultContent,
    Flags = a.Flags,
    Hidden = (a.Flags & MedicalReportSectionFlags.Private) != 0,
    Active = (a.Flags & MedicalReportSectionFlags.Active) != 0

}).ToArrayAsync();

TakeAllWithZero is an extension method that simply skip the take if you pass 0 as parameter

[maumar]

@aguerrieri82 thanks for the update, I'm able to reproduce it now.

[maumar]

This one is quite interesting.

Simplified repro:

        [ConditionalFact]
        public virtual void Repro19128()
        {
            using (var ctx = new Context19128())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();

                var query = ctx.MedicalReportSections
                    .OrderBy(x => x.Id)
                    .Skip(1)
                    .Select(a => a.Flags & MedicalReportSectionFlags.Active).ToList();
            }
        }

        public class Context19128 : DbContext
        {
            public DbSet<MedicalReportSection> MedicalReportSections { get; set; }


            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer(@"Server=.;Database=Repro19128;Trusted_Connection=True;MultipleActiveResultSets=True");
            }
        }

        [Flags]
        public enum MedicalReportSectionFlags
        {
            None = 0,
            Active = 1,
        }
        public class MedicalReportSection
        {
            public int Id { get; set; }
            public MedicalReportSectionFlags Flags { get; set; }
        }

What's happening here is that during parameter extraction, we parameterize argument to the skip method (int = 1), which we always do. However, because the value of MedicalReportSectionFlags.Active is also int = 1, it also gets parameterized, and the same parameter is assigned. So after parameter extraction we have the following:

DbSet<MedicalReportSection>
    .OrderBy(x => x.Id)
    .Skip(__p_0)
    .Select(a => (MedicalReportSectionFlags)((int)a.Flags & __p_0))

When this gets translated to sql we get:

SELECT [m].[Flags] & @__p_0
FROM [MedicalReportSections] AS [m]
ORDER BY [m].[Id]
OFFSET @__p_0 ROWS

However, the type mapping of parameter in the projection is sql int, backed by clr MedicalReportSectionFlags (inferred from the column). When we visit the projection, this is the parameter that gets added to Parameters collection on RelationalCommand.
However the parameterValues contains a value typed as clr int, so when we try to add that value to the parameter collection, exception is thrown

[maumar]

@aguerrieri82 as a workaround you can manually parameterize enum constants you are using, like so:

var _medicalReportSectionFlagsPrivate = MedicalReportSectionFlags.Private;
var _medicalReportSectionFlagsActive = MedicalReportSectionFlags.Active;

result.Items = await query.Select(a => new MedicalReportSectionListView()
{
    Id = a.Id,
    OperatorId = a.OperatorId,
    Name = a.Name,
    Order = a.Order,
    DefaultContent = a.DefaultContent,
    Flags = a.Flags,
    Hidden = (a.Flags & _medicalReportSectionFlagsPrivate) != 0,
    Active = (a.Flags & _medicalReportSectionFlagsActive) != 0

}).ToArrayAsync();

This way we create separate parameters for all enum values and the clash doesn't happen.

[maumar]

@blayvant @vadim82 - decent workaround in my previous comment

[blayvant]

@maumar Thank you for the explanation and the suggested workaround; however, in my case, enum constants are not being used. Instead the bitwise operation is performed on object properties.
Ex:

 var query = _db.LeadActivities.Where(l =>
  l.CompanyId == request.CompanyId &&
  l.ApprovedDate > lastSent &&
  l.Status == LeadStatus.Approved &&
  l.ConsentToPolicies &&
  l.LeadType != LeadType.Shared
);

if (request.LeadType > 0)
      query = query.Where(l => (request.LeadType & (int)l.LeadType) == (int)l.LeadType);

[maumar]

@blayvant the query you posted contains constant enums in the comparison - try parameterize them. Although I don’t see where the initial parameter that would trigger all of this comes from. If you still see the problem after manually parameterizing all enum constants in the query, please post full runnable repro and I will investigate. Perhaps there are more problems in the scenario.

[blayvant]

@maumar For testing purposes, I replaced l.LeadType in query.Where(l => (request.LeadType & (int)l.LeadType) == (int)l.LeadType) with a variable:
c# var testLeadType = LeadType.Download; if (request.LeadType > 0) query = query.Where(l => (request.LeadType & (int)testLeadType) == (int)testLeadType);
This eliminated the error, but it is not a realistic approach since I don't know the value of l.LeadType ahead of time. The only way I can think of getting this to work would involve building some kind of complex expression to pass to Where.

[maumar]

@blayvant l.LeadType is fine to leave as is, you only need to worry about constant enums:

l.Status == LeadStatus.Approved

l.LeadType != LeadType.Shared

etc.

Those you should be able to parameterize relatively easily.

[blayvant]

@maumar I already tried replacing the constant enums with variables and it made no difference. Conversely, when I left the constant enums in place and replaced l.LeadType in the lambda with a variable, the error was resolved.

[maumar]

@blayvant interesting. Could you provide a full repro of your case? It's possible that there is more to this issue than we understand.

[blayvant]

@maumar Here's a simple app which re-creates the issue:
https://github.com/blayvant/efcore-bitflag-bug

[maumar]

@blayvant thanks! I'm able to reproduce the issue.

You can workaround the problem by removing explicit converts to int and keeping everything typed as LeadType:

            var sumLeadTypes = LeadType.Download | LeadType.Product;
            var matchingLeads = db.Leads
                .Where(l => (sumLeadTypes & l.LeadType) == l.LeadType)
                .ToList();

Alternatively (oddly enough) you can use a workaround that is is the opposite to the one I suggested to the original problem, i.e. inlining enum constants directly into the query:

            var matchingLeads = db.Leads
                .Where(l => (((int)LeadType.Download + (int)LeadType.Product) & (int)l.LeadType) == (int)l.LeadType)
                .ToList();

Basic issue is that when we extract the parameter values, the value is created as int, however the actual sql parameter's type is Enum (we are removing convert from the l.LeadType and the parameter type is inherited from the l.LeadType column type. And the issue is because we don't handle the type discrepancy correctly.

[maumar]

Repro code:

        [ConditionalFact]

        public void Test19128()
        {
            var db = new LeadsDbContext();
            db.Database.EnsureDeleted();
            db.Database.EnsureCreated();
            var downloadLead = new Lead { LeadType = LeadType.Download };
            var productLead = new Lead { LeadType = LeadType.Product };
            var leads = new List<Lead> { downloadLead, productLead };
            db.AddRange(leads);
            db.SaveChanges();

            //fails
            var sumLeadTypes = (int)LeadType.Download + (int)LeadType.Product;
            var matchingLeads = db.Leads
                .Where(l => (sumLeadTypes & (int)l.LeadType) == (int)l.LeadType)
                .ToList();

            // works
            var sumLeadTypes2 = LeadType.Download | LeadType.Product;
            var matchingLeads2 = db.Leads
                .Where(l => (sumLeadTypes2 & l.LeadType) == l.LeadType)
                .ToList();
        }

        public class Lead
        {
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int Id { get; set; }
            public LeadType LeadType { get; set; }
        }

        [Flags]
        public enum LeadType
        {
            PremiumContent = 1,
            ActiveProjectLeads = 2,
            Product = 4,
            ContactRequest = 8,
            Shared = 16,
            Download = 32,
            InternalHelpRequest = 64,
            Article = 128
        }

        public class LeadsDbContext : DbContext
        {
            public DbSet<Lead> Leads { get; set; }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer(@"Server=.;Database=Repro19128;Trusted_Connection=True;MultipleActiveResultSets=True");
            }
        }

[blayvant]

@maumar Thank you for the explanation and the suggested workarounds. I assume this is still considered a bug though.

[maumar]

yes, it's actually a couple different bugs

[suadev]

@smitpatel It looks like @maumar fixed this issue, so do we need to wait until release of 5.0 ?

Your workaround is not working for me, and i also use enum in the query. It tries to convert it to Int32 insistently.

[maumar]

bringing back to triage so we can discuss if it meets the bar for patch

[smitpatel]

This has not been merged to release/3.1