Expression tree to sql, Guid constants are translated to string in database and can't be read as Guid

87 Views Asked by At

I have a method that returns some dynamic grouping keys:

private Expression<Func<T, object>> GetKeySelector<T>(Request req,
    Expression<Func<T, Guid>> domainKey, ...

// Gets called on a Queryable q.   
q.GroupBy(GetKeySelector<Order>(req, x => x.DomainId, ...

// Inside GekKeySelector, based on the request a key is picked:
if (...)
{
    return Expression.Lambda<Func<T, object>>(Expression.Convert(domainKey.Body, typeof(object)), domainKey.Parameters);
}

There can be different key types so the return type is ExpressionFunc<T, object>.

I now want to add another grouping based on the domainKey which uses a translation list to build a if-then-else tree:

if (...)
{
    // List is of type 'a new { id, OrganizationId }
    var list = ...;

    Expression expr = Expression.Constant(Guid.Empty, typeof(Guid)); // Default value

    foreach (var item in list)
    {
        expr = Expression.Condition(Expression.Equal(domainKey.Body, Expression.Constant(item.Id, typeof(Guid))),
            Expression.Constant(item.OrganizationId, typeof(Guid)), expr);
    }

    expr = Expression.Convert(expr, typeof(Guid)); // Debugging attempt - did not help.
    
    return Expression.Lambda<Func<T, object>>(Expression.Convert(expr, typeof(object)), domainKey.Parameters);
}

This kinda works and produces the following sql and expression:

    SELECT ... CASE
        WHEN [a].[DomainId] = '8255b5a1-b52f-4661-2571-08db5b57a880' THEN '32795fb3-27cf-45f1-c629-08db584be10a'
        ...
        WHEN [a].[DomainId] = 'cd9ad94d-1fcf-4738-46f2-08d94d0733d4' THEN 'eaf738b8-2c15-4d65-c225-08d94dbb2fb0'
        ELSE '00000000-0000-0000-0000-000000000000'
    END AS [Key]
.GroupBy(
        keySelector: x => (object)(Guid)x.DomainId == 8255b5a1-b52f-4661-2571-08db5b57a880 
? 32795fb3-27cf-45f1-c629-08db584be10a : x.DomainId == 93964c6b-7428-46e4-a0fd-08da38abb4e9 ... 
? eaf738b8-2c15-4d65-c225-08d94dbb2fb0 : 00000000-0000-0000-0000-000000000000, 
        elementSelector: ...

When running the queryable we get an exception:

System.InvalidOperationException
  HResult=0x80131509
  Message=An error occurred while reading a database value. The expected type was 'System.Object' but the actual value was of type 'System.String'.
  Source=Microsoft.EntityFrameworkCore.Relational
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.ThrowReadValueException[TValue](Exception exception, Object value, Type expectedType, IPropertyBase property)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.<MoveNextAsync>d__20.MoveNext()
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__65`1.MoveNext()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__65`1.MoveNext()
   at [REDACTED]
  This exception was originally thrown at this call stack:
    [External Code]

Inner Exception 1:
InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Guid'.

I think the culprits here are the constants: Expression.Constant(..., typeof(Guid)) which are translated to string (they should be something like CAST(... as uniqueidentifier) to match the expected value).

How can i solve this exception and have the generated sql return proper guids?

0

There are 0 best solutions below