Efcore: Treat ToString as casting to string in the database

Created on 11 Apr 2020 · 3Comments · Source: dotnet/efcore

I'm trying to do a contains search on enum property in my DbSet and EF Core 3.1 throws the below error

The LINQ expression 'DbSet .Where(d =>
d.Position.ToString().Contains("acc"))' could not be translated.
Either rewrite the query in a form that can be translated, or switch
to client evaluation explicitly by inserting a call to either
AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()

Entity:

```c#
public class DemoEntity
{
[Key]
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public Position Position { get; set; }
}

Enum - Position:

```c#
public enum Position
{
    [Display(Name = "Accountant")]
    Accountant,
    [Display(Name = "Chief Executive Officer (CEO)")]
    ChiefExecutiveOfficer,
    [Display(Name = "Integration Specialist")]
    IntegrationSpecialist,
    [Display(Name = "Junior Technical Author")]
    JuniorTechnicalAuthor,
    [Display(Name = "Pre Sales Support")]
    PreSalesSupport,
    [Display(Name = "Sales Assistant")]
    SalesAssistant,
    [Display(Name = "Senior Javascript Developer")]
    SeniorJavascriptDeveloper,
    [Display(Name = "Software Engineer")]
    SoftwareEngineer
}

DbContext:

```c#
public class DemoDbContext : DbContext
{
public DemoDbContext(DbContextOptions options)
: base(options) { }

public DbSet<DemoEntity> Demos { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<DemoEntity>()
        .Property(e => e.Position)
        .HasConversion<string>();
}

}

When I query the table as follows I'm getting the error

```c#
try
{
    var test = await _context.Demos.Where(x => x.Position.ToString().Contains("acc")).ToListAsync();
}
catch (System.Exception e)
{
    //throw;
}

The Position is of type NVARCHAR(MAX) in my database.

This is not possible? If so please can you help me with explanation?

area-query customer-reported type-enhancement

Source

fingers10

Most helpful comment

@fingers10 This should work:

```C#
.Where(x => ((string)(object)x.Position).Contains("acc"))

EF Core then interprets this as a cast to string and generates:

```sql
SELECT [d].[Id], [d].[FirstName], [d].[LastName], [d].[Position]
FROM [DemoEntity] AS [d]
WHERE CHARINDEX(N'acc', CAST([d].[Position] AS nvarchar(max))) > 0

However, we discussed that it would be useful to allow ToString to do the same thing--it would avoid the non-obvious trick of casting to object. There is some concern that the output of ToString on on the client could be very different to what is generated on the database, but given the weak semantics of ToString anyway, that's probably okay.

ajcvickers on 14 Apr 2020

👍3

All 3 comments

@fingers10 This should work:

```C#
.Where(x => ((string)(object)x.Position).Contains("acc"))

EF Core then interprets this as a cast to string and generates:

```sql
SELECT [d].[Id], [d].[FirstName], [d].[LastName], [d].[Position]
FROM [DemoEntity] AS [d]
WHERE CHARINDEX(N'acc', CAST([d].[Position] AS nvarchar(max))) > 0

ajcvickers on 14 Apr 2020

👍3

Also see https://github.com/dotnet/efcore/issues/14205
Convert a different ToString represent can give incorrect results in query.