How to convert a method of a ValueObject to SQL with Entity Framework Core 3.1

Question

I have this value object

public class ProductReference : ValueObject
{
   protected ProductReference(){}
   public ProductReference(string value){}
   public string Value{get; protected set;}
}

I use it in my entity as :

public class Product : Entity<long> 
{
   protected Product(){} 
   public ProductReference Reference{get; protected set;}   
}

In the OnModelCreating of my DbContext I defined :

modelBuilder.Entity<Product>(entity => {
                     entity.Property(a => a.Reference)
                    .HasColumnName("Reference")
                    .HasConversion(
                        a => a.Value,
                        s => new ProductReference (s);
 });

When I do :

await dbcontext.Products.Where(p=>p.Reference.Value.Contains("some text")).toArrayAsync();

I get an exception

Expression cannot be converted to a valid SQL statement

I know for sure there is a way to create a custom expression converter, but I cannot find a good, simple and EF Core 3.1 compatible example to deal with my issue and that explain me clearly the concepts I miss.

I found this very interesting project https://github.com/StevenRasmussen/EFCore.SqlServer.NodaTime but it is too advanced for me to reproduce it for only my use case.

[EDIT] the ValueObject ans Entity are from CSharpFunctionalExtensions nuget package, I dont think they are really relevant in my question.

You should also show the code of the base objects for people to reproduce your issue properly. — Gert Arnold, Jan 08 '21 at 08:35

Tobias · Answer 1 · 2021-01-14T10:03:51.890

I am not completely sure if i understand correctly what you want to accomplish, but you could try to configure your ProductReference as an Owned Entity Type.

Here you would transform the following code from:

modelBuilder.Entity<Product>(entity => {
                 entity.Property(a => a.Reference)
                .HasColumnName("Reference")
                .HasConversion(
                    a => a.Value,
                    s => new ProductReference (s);
});

to

modelBuilder.Entity<Product>(entity => {
                 entity.OwnsOne(a => a.Reference, referenceBuilder => {
                           referenceBuilder.Property(p => p.Value).HasColumnName("Reference");
                 });
});

With that your select statement should work. It could be that you have to play around with the properties of your class ProductReference, or use some modifiers of the fluent API.

The only thing that can make trouble is using the Property name as a string. Just change the `.HasColumnName("Reference")` to `.HasColumnName(nameof(Product.Reference))`; — Saeid Amini, Feb 04 '23 at 15:55

score 0 · Answer 2 · answered Jan 13 '21 at 18:00

So first for some context on what is happening here behind the scenes and why its not gonna work even for build in simple converters like BoolToZeroOneConverter.

The problem here is that you are calling when converting the new ProductReference(s). This is method where you can do whatever you want in it. For example if use it in a Select statement it will again fail. For example:

await dbcontext.Products
.Select(x=>new ProductReference(x.Value))
.toArrayAsync();

The reason is obvious, it won't be able to translate. But why it cant transform it to a query?

Because you are passing a constructor. Inside this constructor you could be doing API calls or using Reflections to set the variables to your object, pretty much anything. That of course is not able to be translated in an SQL query.

Converters are generally used for in memory but they can be used for databse operations as well. This would mean that you will need something like this:

await dbcontext.Products
.Select(x=>new ProductReference() // empty constructor that does nothing
{
    Property1 = x.Property1 // I don't know how the constructor maps them
})
.toArrayAsync();

Using this type of expression allow you to actually transalte the expression to an SQL statement and not making the conversion on the SQL DB and not in memory.

Now in your specific case using:

.HasConversion(
                        a => a.Value,
                        s => new ProductReference (){};
 });

Should fix your issues but I fail to understand why would you want to initialize or convert a ProductReference to a ProductReference.

How to convert a method of a ValueObject to SQL with Entity Framework Core 3.1

2 Answers2