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

[amyboose]

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

[roji]

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));
}