Expression with value converter could not be translated

[Schaemelhout]

I have created a value converter to store a list of strings as a semicolon-separated string and it looks like EF Core can't translate a LINQ expression to filter these and evaluates it locally.

Is this an example of what's stated in the limitations section of the docs? Or is there another way for me to have EF translate this WHERE statement correctly (apart from writing raw SQL)?

Use of value conversions may impact the ability of EF Core to translate expressions to SQL. A warning will be logged for such cases. Removal of these limitations is being considered for a future release.

Steps to reproduce

public class Post
{
  public int Id { get; set; }
  public string Title { get; set; }
  public List<string> Labels { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder
    .Entity<Post>()
    .Property(x => x.Labels)
    .HasConversion(
      x => string.Join(';', x),
      x => x.Split(new[] { ';' }).ToList()
    );
}

When executing this query:
_dbContext.Set<Post>().Where(x => x.Labels.Contains(".NET CORE")).ToList()
I get the following warning:

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where {[x].Labels => Contains(".NET CORE")}' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Contains(".NET CORE")' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (41ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [x].[Id], [x].[Title], [x].[Labels]
FROM [Post] AS [x]

Further technical details

EF Core version: 2.2.6
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 2.2
Operating system: Windows 10
IDE: Visual Studio 2019 16.0

[smitpatel]

This wouldn't be supported due to existence of value converter.

[ajcvickers]

@Schaemelhout Issue #10434 is tracking support for allowing value converters to influence generation of SQL--I will add a note to consider this case. However, this particular case could end up being too complicated.

This is a case where JSON mapping (#4021) would probably a good fit, once implemented.

[Schaemelhout]

Thanks for the clarification.

[MoazAlkharfan]

@Schaemelhout

There is a possible workaround to be able to write your query.

First you make your labels into a first class object, and stick an explicit string conversion operator to allow the query code to compile.

public class PostLabels : List<string>
{
    // Add your validation, initialization, overrides, etc..

    // Only to support ef core query.
    public static explicit operator string(UserExternalIds v)
    {
        throw new NotImplementedException();
    }
}

public class Post
{
  public int Id { get; set; }
  public string Title { get; set; }
  public PostLabels Labels { get; set; }
}

Then you would write your query as such.

_dbContext.Set<Post>().Where(x => ((string)x.Labels).Contains(".NET CORE")).ToList()

1. It compiles because we have the conversion operator above.
2. Because this is an expression used to translate linq into SQL, the exception won't be thrown, unless it had to do client side evaluation.
3. You can only do queries on strings.

[caztial]

@Schaemelhout

There is a possible workaround to be able to write your query.

First you make your labels into a first class object, and stick an explicit string conversion operator to allow the query code to compile.

public class PostLabels : List<string>
{
    // Add your validation, initialization, overrides, etc..

    // Only to support ef core query.
    public static explicit operator string(UserExternalIds v)
    {
        throw new NotImplementedException();
    }
}

public class Post
{
  public int Id { get; set; }
  public string Title { get; set; }
  public PostLabels Labels { get; set; }
}

Then you would write your query as such.

_dbContext.Set<Post>().Where(x => ((string)x.Labels).Contains(".NET CORE")).ToList()

1. It compiles because we have the conversion operator above.
2. Because this is an expression used to translate linq into SQL, the exception won't be thrown, unless it had to do client side evaluation.
3. You can only do queries on strings.

Improvement

public class PostLabels : List<string>
{
        public PostLabels() : base()
        {
        }

        public PostLabels(IEnumerable<string> collection) : base(collection)
        {
        }
    
    // Only to support ef core query.
    public static explicit operator string(PostLabels v)
    {
        throw new NotImplementedException();
    }
}

public class Post
{
  public int Id { get; set; }
  public string Title { get; set; }
  public PostLabels Labels { get; set; }
}

and your value Conversation should look like this

 builder.Property(a => a.Tags).HasConversion(
                    i => string.Join(";", i),
                    o => new PostLabels(o.Split(new char[] { ';' }).ToList()));