Unnecessary SQL CAST when using EF.Functions.Like with strongly-typed value-converted types

Description

When using EF.Functions.Like on a property mapped via a ValueConverter from any strongly-typed type to string (both struct and class), Entity Framework Core emits a redundant CAST(... AS string) (or ::text in PostgreSQL) in the generated SQL.

Your code:

using System;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using Microsoft.Extensions.Logging;

namespace EfInvalidSql;
public class Program
{
    public static async Task Main(params string[] args)
    {
        MyContext context = new();
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        var users = await context.Set<User>()
            .Where(x => EF.Functions.Like(x.Name, "Name%"))
            .ToListAsync();
    }
}

public class User
{
    public required int Id { get; set; }
    public required FullName Name { get; set; }
}

public readonly record struct FullName
{
    public FullName(string value)
    {
        Value = value;
    }

    public string Value { get; }

    public static implicit operator string(FullName fullName)
    {
        return fullName.Value;
    }
}

public class FullNameConverter : ValueConverter<FullName, string>
{
    public FullNameConverter() : base(
        v => v.Value,
        v => new FullName(v))
    {
    }
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer("Server=localhost,7438;Database=TestDb;User Id=sa;Password=RMfL3Tx%bZ5b;TrustServerCertificate=True;")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
    }

    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        configurationBuilder.Properties<FullName>()
            .HaveConversion<FullNameConverter>()
            .HaveColumnType("text");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(builder =>
        {
            builder.ToTable("Users");
        });

        modelBuilder.Entity<User>()
            .HasData(
                new User { Id = 1, Name = new FullName("Name1") },
                new User { Id = 2, Name = new FullName("Name2") }
                );
    }
}

Generated SQL by MSSQL provider:

SELECT [u].[Id], [u].[Name]
FROM [Users] AS [u]
WHERE CAST([u].[Name] AS nvarchar(max)) LIKE N'Name%'

Generated SQL by Npgsql provider:

SELECT u."Id", u."Name"
FROM "Users" AS u
WHERE u."Name"::text LIKE 'Name%'

EF Core version

9.0.6

Database provider

MSSQL provider and Npgsql Entity Framework Core provider for PostgreSQL

Target framework

.NET 9.0

Operating system

Windows 11

IDE

Visual Studio 17.4.5

Confirmed. The extra useless cast in the SQL is the result of the implicit cast on the .NET side, which is required since EF.Functions.Like accepts a string. Placing this in the backlog since the additional SQL CAST is very unlikely to actually impact anything, and the usage seems somewhat niche. If any actual performance degradation (or other issue) is observed because of the CAST, please post the relevant information and we'll reconsider prioritizing this.

Cleaned up minimal repro

using var context = new MyContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

var users = await context.Set<User>()
    .Where(x => EF.Functions.Like(x.Name, "Name%"))
    .ToListAsync();

public class User
{
    public required int Id { get; set; }
    public required FullName Name { get; set; }
}

public class FullName(string value)
{
    public string Value { get; set; } = value;

    public static implicit operator string(FullName fullName) => fullName.Value;
}

public class MyContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>().Property(x => x.Name)
            .HasConversion<FullNameConverter>();
    }

    public class FullNameConverter() : ValueConverter<FullName, string>(v => v.Value, v => new FullName(v));
}

The extra CAST messes up the query plan and forces an index scan.

On second look it indeed makes sense that the extra cast interferes with index usage, as foo LIKE 'a%' can use an index, whereas CAST(foo AS nvarchar(max)) LIKE 'a%' likely does not.

Any news? I just wasted a full day on this before I found this issue. It is 10 month ago that it was reported.

Why is this niche? Anyone using ValueObjects fails with EF Core using LIKE. We use many filter operations where we need LIKE. The only workaround is to use rawSQL.

Configuring value objects as complex types is a work around, but it fails with OwnedTypes because they don't support complex properties. Hitting a wall again! This is another major issue that is open for more than 2 years (#33170). Serious question: Does someone in the team hates DDD?

@KillerBoogie

@KillerBoogie nobody here hates DDD - there's no need for that kind of communication here. But the fact is that this issue has received zero votes since it was initially opened, and since we have a great deal of issues which have been voted on, this hasn't been prioritized.

But I agree that since this is a subtle and hard-to-detect issue with potentially serious performance consequences, we should take a look.

@roji

@roji please accept my apology, I didn’t mean to sound rude. It was honestly just frustration speaking.

Over the years I’ve really struggled using value objects with EF Core and keep running into the same walls. It often feels like support for these scenarios gets pushed from one version to the next, and when something finally lands it still doesn’t cover some very common and important cases. That’s hard to understand.

Right now the issue with DbFunctions.Like or Contains not being translated for value objects is a big pain. The workaround with implicit or explicit string conversion means adding extra code to value objects just for EF Core. And even if you accept that, you run into performance issues.

The other option—configuring value objects as complex types—would help, but that still doesn’t work with OwnedTypes, and that’s been open for more than two years.

So the only real option left is raw SQL. At that point it’s hard to see the benefit of supporting value objects if you can’t express something basic like LIKE in a query.

I further found out the hard way that indexes can't be set on complex type properties (#31246).

I get that prioritization isn’t easy, but this makes it really hard to use value objects with EF Core in practice. The benefits of value objects—especially always-valid objects—are huge, which makes these gaps even more frustrating.

I would like to add a thought that maybe votes should not be the only guideline that a shortcomming or a bug gets fixed. A lot of people probably have never realized that the Cast exists.

To understand the full picture I tried to sketch the global issue:

With EF Core 10 ComplexTypes would be the best solution, but (a) they are not supported inside OwnsMany and (b) they don't support indices. One needs to add indicies manually to migration, which is error prone on changes and doesn't work for development using EnsureCreate. There is a private nuget package that adds indices on ComplexTypes if one wants to depend on it.

OwnsOne supports indices, but for DbFunctions.Like with wildcard at end / or .StartsWith an implicit or explicit conversion is needed. This conversion currently adds a CAST() that can't use the index. Only in-text search with Like / Contains is not affected by the CAST().

So you can choose how to get screwed by EF Core or choose the overhead with DBO using primitive types and mappings between domain model and DBOs.

@roji

@roji thank you for considering the fix. Honestly, I believe that it needs to be pushed. Many are waiting for years to get proper value object support in EF Core. Much has been done, but the issues or half-implementations with each option still hinder usage in production scenarios.

Strip no-op SQL CASTs in SqlExpressionSimplifyingExpressionVisitor

Strip no-op SQL CASTs (dotnet#38156)

Document stripping of no-op SQL CASTs

Document stripping of no-op SQL CASTs (#5348)

Document provider-facing breaking change: no-op SQL CAST stripping

Document provider-facing breaking change: no-op SQL CAST stripping (#…

Navigation Menu

Repository navigation

Unnecessary SQL CAST when using EF.Functions.Like with strongly-typed value-converted types #36247

Description

Your code:

EF Core version

Database provider

Target framework

Operating system

IDE

1 remaining item

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Unnecessary SQL CAST when using EF.Functions.Like with strongly-typed value-converted types #36247

Description

Description

Your code:

EF Core version

Database provider

Target framework

Operating system

IDE

Activity

roji commented on Jun 14, 2025

arnileibovits commented on Nov 25, 2025

roji commented on Nov 25, 2025

dejonghee commented on Jan 26, 2026

roji commented on Jan 27, 2026

KillerBoogie commented on Apr 15, 2026

roji commented on Apr 15, 2026

KillerBoogie commented on Apr 15, 2026

KillerBoogie commented on Apr 21, 2026

1 remaining item

KillerBoogie commented on Apr 22, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions