How to query the actual value in Entity Framework core when using a Value Converter?

[robbaman]

Ask a question

We have a fairly simple situation where an EF Core entity contains a value object that we map to a string:

builder.Property(_ => _.Nummer)
    .HasConversion(i => i.ToString(), i => ZijdeNummer.FromString(i))
    .IsRequired().HasMaxLength(9);

This works fine for retrieval and storage, but for filtering we're looking for a way to filter rows by substring. If it'd be a normal string column you can simply do something like this:

dbSet.TheTable.Where(t => t.Nummer.Contains("some text")).ToList()

Obviously the custom ZijdeNummer doesn't contain a recognized .Contains() method. I've tried using .ToString().Contains() but alas, this also doesn't work.

Finally, I've also tried accessing the column as if it were a shadow-property:

dbSet.TheTable.Where(t => EF.Property<string>(t, "Nummer").Contains("some text"))

But it won't be fooled as it still knows that EF.Property<T>(t, "Nummer") is not actually a string :(

Lastly I've dabbled in using a combination of HasDbFunction() with HasTranslation() but I couldn't get this to work, and also seems like a very difficult way to perform something that in its core seems so simple.

Is there a way to make EF-Core just query the raw column type? Any help would be greatly appreciated.

[ajcvickers]

@robbaman Try:

dbSet.TheTable.Where(t => ((string)(object)t.Nummer).Contains("some text")).ToList()

Not sure it will work, but it might.

[robbaman]

@ajcvickers that's crazy talk, there's no way a solution that simple could possibly .... AARGH, that's hours of my life I will never get back!?! 🤣🤣

Maybe it's a good idea to have this solution added to the documentation. It seems to me that it's a reasonably common use case that the actual string a HasConversion leads to is wanted in a query. I'm not quite sure how I'd go about suggesting that to the docs team though.

Anyway, I'll close this issue for now. Thanks a lot for helping out!

[ajcvickers]

Yeah, we should doc this. Re-opening to track. (I don't know if this was ever an intention part of the design. @smitpatel?)