Efcore: Enum property stored as string, filter queries do not work

[fulviocanducci]

Entity Creditincluded an enum property Status, stored as a string, at the time the filter returns an "exception" as follows:

System.Data.SqlClient.SqlException: 'Conversion failed 
when converting the nvarchar value 'Active' to data type int.'

Steps to reproduce

Code:

Enum

```c#
public enum State
{
Active,
Desactive
}

### Class
```c#
public partial class Credit
{
    public Credit()
    {
        Notice = new HashSet<Notice>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public State Status { get; set; }

    public virtual ICollection<Notice> Notice { get; set; }
}

Mapping Class

```C#
modelBuilder.Entity(entity =>
{
entity.Property(e => e.Name).HasMaxLength(50);
entity.Property(e => e.Status)
.HasConversion();
});

### Filter

At the time of saving and changing everything OK, but in a filter returns the exception, example:

```C#
Credit
    .Where(w => w.Status == State.Active) // Problem
   .ToList()

Problem

.Where(w => w.Status == State.Active)

System.Data.SqlClient.SqlException: 'Conversion failed 
when converting the nvarchar value 'Active' to data type int.'

---

Further technical details

EF Core version: 2.1 Preview 1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win 10
IDE: Visual Studio 2017 15.5.6

[smitpatel]

We discovered this issue recently. See https://github.com/aspnet/EntityFrameworkCore/issues/3620#issuecomment-366469273
It is fixed in current dev/nightly builds.

[ajcvickers]

@fulviocanducci Could you test with a nightly build? See https://github.com/aspnet/EntityFrameworkCore for the feed.

[fulviocanducci]

@ajcvickers Hi,

In version 2.1.0-preview2-30220 there was the translation in SQL correctly, example of generated SQL:

Credit.Where(w => w.Status == State.Active)

---

SELECT [w].[Id], [w].[Name] AS [Text]
FROM [Credit] AS [w]
WHERE [w].[Status] = N'Active'

Now, Very Nice

[alborozd]

It doesn't work for nested properties.
For example:

C# _context.MedicationStatement .Include(t => t.MedicationStatementUpdate) .Where(t => t.MedicationStatementUpdate.FirstOrDefault().Status == MedicationStatementUpdateStatus.Added)

And I got error:
Conversion failed when converting the nvarchar value 'added' to data type int.'

[ajcvickers]

@alborozd Please file a new issue including a runnable project/solution or complete code listing that demonstrates the behavior and a full exception message and stack trace.

[m-demydiuk]

Hello. Today I got the exact same error as @alborozd.

This works as expected

var childs = _dbContext.Childs
    .AsNoTracking()
    .Where(ch=> ch.Status == ChildStatus.New).ToList();

and is translated to

SELECT [ch].[Status]
FROM [Child] AS [ch]
WHERE [ch].[Status] = 'NEW'

But the code below

var parents= _dbContext.Parents
    .AsNoTracking()
    .Include(pnt => pnt.Childs)
    .Where(pnt => pnt.Childs.FirstOrDefault().Status == ChildStatus.New).ToList();

throws exception
Conversion failed when converting the varchar value 'NEW' to data type int.
and is translated to

SELECT [pnt].*
FROM [Parent] AS [pnt]
WHERE (
    SELECT TOP(1) [ch].[Status]
    FROM [Child] AS [ch]
    WHERE [pnt].[Id] = [ch].[ParentId]
) = 1

The above c# and sql code is simplified and names of models are changed to give the main idea of the bug - enum in nested navigation property is not translated to string as expected. But is translated to the int. I use value convertion to store string value of enum in the db.

[smitpatel]

@m-demydiuk Duplicate of #13192

[m-demydiuk]

@smitpatel how is this issue references to the #13192 ?
And do you have any suggestions?

[smitpatel]

Where query contains something like entity.FirstOrDefault().Property == constant, we need to determine the type mapping from property and use it to generate the constant literal on server side. Since this type mapping contains value converter, it would convert value appropriately too. Currently this work for normal scenario like e.Property == constant. #13192 is to infer correct type mapping for complex scenarios.