C#•11mo ago

❔ EF Core: Owned property with converter expression cannot be converted

Hey guys, we're having this issue. ShoppingCart is an owned entity and Items is a List of objects, which has a specified converters. No idea what could be causing this.

11 Replies

Tvde1•11mo ago

It isn't able to convert x => DoDateRangesIntersect(... to SQL If you can make a You can try making an

Expression<Func<ShoppingCartItem, bool>> doDateRangesIntersectExpression = ...

Expression<Func<ShoppingCartItem, bool>> doDateRangesIntersectExpression = ...

and pass it like

    .Where(...)
    .CountAsync(doDateRangesIntersectExpression);

    .Where(...)
    .CountAsync(doDateRangesIntersectExpression);

then it might translate it to SQL what is the content of DoDateRangesIntersect? are you familiar with what an Expression is? It's sort of one level higher than code. A normal Func<> would be like

Func<int, int> timesTwo = x => x * 2;

Func<int, int> timesTwo = x => x * 2;

This becomes like a method you can call, it's almost the same as

int TimesTwo(int x) { return x * 2; }

int TimesTwo(int x) { return x * 2; }

an Expression however, is kind of code before it's turned into code.

Expression<Func<int, int>> timesTwoExpression = x => x * 2;

Expression<Func<int, int>> timesTwoExpression = x => x * 2;

this variable is of type expression which means you can call .Compile() to get the Func but more importantly, you can inspect it. If you inspect this, it will say, this expression is a lambda expression with a variable and a body when you inspect the body, it will say it's a return expression which returns the value of a binary expression, the binary expression is made up of x, the multiply operation and 2. Using this power, you can write "code" which Entity Framework will inspect. It recognizes that if you have a binary expression with multiply, it can turn that into SQL So only if you turn DoDateRangesIntersect from a "normal function" into an expression, can Entity Framework inspect what the expression is made of, and translate that to SQL another example, the

.Where(x => x.VehicleId == vehicleId)

.Where(x => x.VehicleId == vehicleId)

this secretly is creating an expression, even though it looks like code. EF will see, a .Where and will inspect whether it can turn the expression into SQL. It encounters an EqualsExpression which it knows it can turn into

WHERE ... = ...

WHERE ... = ...

Then it sees the left side is a MemberExpression which accesses the VehicleId property from x. It knows it can translate that like

WHERE VehicleId = ...

WHERE VehicleId = ...

and the right side is a VariableExpression and it knows it can do that by introducing a SQL parameter

DEFINE PARAM @vehicleId INT (...)

WHERE VehicleId = @vehicleId

DEFINE PARAM @vehicleId INT (...)

WHERE VehicleId = @vehicleId

it's kind of magic, but really cool

FusedQyou•11mo ago

Unrelated to the problem, but do you have example of how something like that is actually inspected and parsed like that? Expressions is one of those things I never wrapped my head around.

Tvde1•11mo ago

This is kind of an example from the EF code itself https://github.com/dotnet/efcore/blob/main/src/EFCore.SqlServer/Query/Internal/SqlServerQuerySqlGenerator.cs#L115 It inspects a custom made SelectExpression, and adds to the SQL query depending on the structure of the expression

GitHub

efcore/src/EFCore.SqlServer/Query/Internal/SqlServerQuerySqlGenerat...

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations. - dotnet/efcore

Tvde1•11mo ago

Notably

if (selectExpression.Predicate != null)
{
    Sql.AppendLine().Append("WHERE ");
    Visit(selectExpression.Predicate);
}

if (selectExpression.Predicate != null)
{
    Sql.AppendLine().Append("WHERE ");
    Visit(selectExpression.Predicate);
}

@rese7948 we can help turning your method into an expression :) if you want to share it

Rese•11mo ago

@tvde1 thanks for the effort provided into your response 🙏 , but I'm familiar with expressions. Don't mind sharing the contents of the method (will do in the next message), but from what I understand, EF Core already converts some expressions as lambda expressions that are then translated into SQL as you've mentioned and I've already used this function in an EF Core query with no issues before. Additionally, if that were the issue, wouldn't it point to that part as the problematic one instead of simply the x => x.ShoppingCart.Items lambda?

public static bool DoDateRangesIntersect(
        DateTime range1Start,
        DateTime range1End,
        DateTime range2Start,
        DateTime range2End
    ) =>
        range1Start.IsWithinRange(range2Start, range2End)
        || range1End.IsWithinRange(range2Start, range2End)
        || range2Start.IsWithinRange(range1Start, range1End)
        || range2End.IsWithinRange(range1Start, range1End);

public static bool DoDateRangesIntersect(
        DateTime range1Start,
        DateTime range1End,
        DateTime range2Start,
        DateTime range2End
    ) =>
        range1Start.IsWithinRange(range2Start, range2End)
        || range1End.IsWithinRange(range2Start, range2End)
        || range2Start.IsWithinRange(range1Start, range1End)
        || range2End.IsWithinRange(range1Start, range1End);

public static bool IsWithinRange(
            this DateTime target,
            DateTime rangeStart,
            DateTime rangeEnd
        ) => target >= rangeStart && target <= rangeEnd;

public static bool IsWithinRange(
            this DateTime target,
            DateTime rangeStart,
            DateTime rangeEnd
        ) => target >= rangeStart && target <= rangeEnd;

as you can see, they're all functions, but they're essentially just simple expressions and as I mentioned, they did work in the past with EF Core queries just fine

Tvde1•11mo ago

How far do you get with

public static Expression<Func<DateTime, DateTime, DateTime, DateTime, bool>> DoDateRangesIntersectExpression =
         (range1Start, range1End, range2Start, range2End) => 
            IsWithinRange(range1Start, range2Start, range2End)
            || IsWithinRange(range1End, range2Start, range2End)
            || IsWithinRange(range2Start, range1Start, range1End)
            || IsWithinRange(range2End, range1Start, range1End);

public static bool IsWithinRange(DateTime target, DateTime rangeStart, DateTime rangeEnd)
        => target >= rangeStart && target <= rangeEnd;

public static Expression<Func<DateTime, DateTime, DateTime, DateTime, bool>> DoDateRangesIntersectExpression =
         (range1Start, range1End, range2Start, range2End) => 
            IsWithinRange(range1Start, range2Start, range2End)
            || IsWithinRange(range1End, range2Start, range2End)
            || IsWithinRange(range2Start, range1Start, range1End)
            || IsWithinRange(range2End, range1Start, range1End);

public static bool IsWithinRange(DateTime target, DateTime rangeStart, DateTime rangeEnd)
        => target >= rangeStart && target <= rangeEnd;

You might need to inline IsWithinRange

Rese•11mo ago

I'll try it out once I'm home from work, but why would this be an issue if the error states it's elsewhere?

Tvde1•11mo ago

oh that's true, the ShoppingCart.Items is the issue. What kind of property is that?

Accord•11mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Rese•11mo ago

Sorry for the lack of response, I had my plate full. Putting the main issue of this thread aside for a second, I actually did find out the IsWithinRange function is actually not translatable by EF Core for some reason? Even though when I simply extract it's code, it seems to work just fine.

Accord•11mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ EF Core: Owned property with converter expression cannot be converted