EFCore with User Type and HasConversion

[pvidal-hdr]

Ask a question

How can I use my custom type in my entity models and still query on that property? If this can't be done, did I miss it in the documentation? Also, is there a plan to support this eventually?

I have a model (Inspection) with a property that's a custom type (InspectionWorkflowState). I want the workflow state so that I can, based on the string value from InspectionWorkflowState.Name, determine other information (e.g., is it in QA? QC? can it be reviewed? etc.). I can't create a new table with this data, so Owned Entities aren't an option. It seemed like ValueConversion would be the right option and it worked well - until I tried to query on the property. I've tried everything, but I keep getting the error shown below. I've reviewed the documentation, but haven't seen where it says properties with a conversion can't be used in queries (though the limitations section does mention they can't be used in raw queries).

InvalidOperationException: The LINQ expression 'DbSet<Inspection>().Where(i => i.Status.Name == "Open")' could not be translated.

I've tried:

• Using InspectionWorkflowState as a readonly struct (current iteration)
• Using InspectionWorkflowState as a record and a class (including a sealed class)
• Using only HasConversion
• Using HasConversion with SetValueComparer
• Including implicit and explict operators (Expression with value converter could not be translated #17879)
• Using the (string)(object) trick (#30197)

Also I found it interesting to note that the migrations built successfully.. so EF seems to understand what I'm trying to do.

Example code

Can provide the full example project if that'd help.

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Inspection>()
            .ToTable(nameof(Inspection));

        modelBuilder.Entity<Inspection>()
            .HasKey(p => p.Id);

        modelBuilder.Entity<Inspection>()
            .Property(inspection => inspection.Status)
            .HasConversion(value => value.Name, value => new InspectionWorkflowState(value));

        modelBuilder.Entity<Inspection>()
            .Property(inspection => inspection.Status)
            .Metadata
            .SetValueComparer(new ValueComparer<InspectionWorkflowState>(
                    (left, right) => string.Equals(left.Name, right.Name, StringComparison.OrdinalIgnoreCase),
                    value => value.GetHashCode(),
                    value => value)
                );
    }

    public DbSet<Inspection> Inspections { get; set; }
}

public readonly struct InspectionWorkflowState
{
    public string Name { get; }
    public string TestedValueName { get; } = "";

    public InspectionWorkflowState(string stateName)
    {
        Name = stateName;

        if (stateName == "Open")
        {
            TestedValueName = "Success!";
        }
    }

    public override string ToString() => Name;

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

public static class InspectionStates
{
    public const string OPEN = "Open";
    public const string IN_REVIEW = "In Review";
    public const string FINAL = "Final";
    public const string CLOSED = "Closed";
}
public class Inspection
{
    public Guid Id { get; set; }
    public required InspectionWorkflowState Status { get; set; }
}

Include provider and version information

EF Core version: 7.0.8
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows
IDE: Visual Studio 2022 17.6.1

[roji]

@pvidal-hdr querying properties on value-converted user types indeed isn't supported, since EF has no way of knowing about the database representation of your user type (necessary in order to query into the value). That representation is created in your C# code in the value converter, which is opaque to EF (adding a conversion operator doesn't make it any less opaque). I'm adding a note to make that clearer in dotnet/EntityFramework.Docs#4406.

It's theoretically possible to use user-defined function mapping to enable translating method calls on your user type, but for member accesses you'd need to go deeper into EF internals, which isn't recommended. Doing something better here is tracked by #10434.

One option is to use JSON columns, where EF takes care of serializing your user type to a single JSON column; simple forms of querying into such a column are supported (and many more are coming in EF Core 8.0), and databases also generally support indexing into such columns.