No coercion operator is defined between types 'System.Guid' and 'System.String'

Question

I am building Expression tree for Linq-to-sql. In database on some of tables relevant columns are stored as string while some are stored as Guid. I have solved similar problem with int and int? by wrapping lambda constant with Expression.Convert(Expression.Constant(search.PolicyNumber), policyNumberColumnLambda.Type) (where PolicyNumber sometimes was nullable) which worked just fine. But it does not fly for Guid to string conversion apparently.

Code looks following:

public static IQueryable<IRetrieveGuid> SearchByRetrieveGuid<IRetrieveGuid>(this IQueryable<IRetrieveGuid> queryable, SearchModel search)
    {
        var paramLambda = Expression.Parameter(typeof(IRetrieveGuid));
        var columnLambda = Expression.Property(paramLambda, "retrieveguid");
        var lambda = Expression.Lambda<Func<IRetrieveGuid, bool>>(
            Expression.Equal(columnLambda, Expression.Convert(Expression.Constant(search.RetrieveGuid), columnLambda.Type)), paramLambda);
        return queryable.Where(lambda);
    }

How do I convert types to match in expression tree?

So the database column is a guid? Why not just pull it in as a quid and convert to string in linq-to-objects? — D Stanley, Jun 16 '16 at 14:19
@DStanley in some tables it's GUID while in some it's string. I am writing sorta aspect oriented thing via interfaces to query massive disorganized databases (MSSQL and Oracle), and interfaces that I am putting on are `Interface { T Property}` so I don't know type beforehand (this might explain it :) http://stackoverflow.com/questions/37812584/putting-interface-behind-properties-with-same-name-but-different-types) — Matas Vaitkevicius, Jun 16 '16 at 14:27

score 1 · Accepted Answer · edited May 23 '17 at 11:58

SOLUTION1:

This is by a magnitude faster than solution2, however if you have variety of possibilities might result in long if else or switch statement

var retrieveGuidAsString =  search.RetrieveGuid.ToString();
var constantLambda = columnLambda.Type.Name == "Guid"  ? Expression.Constant(search.RetrieveGuid) : Expression.Constant(retrieveGuidAsString);
var lambda = Expression.Lambda<Func<IRetrieveGuid, bool>>(Expression.Equal(columnLambda, constantLambda), paramLambda);

SOLUTION2:

This did work

public static IQueryable<IRetrieveGuid> SearchByRetrieveGuid<IRetrieveGuid>(this IQueryable<IRetrieveGuid> queryable, SearchModel search)
    {
        var paramLambda = Expression.Parameter(typeof (IRetrieveGuid));
        var columnLambda = Expression.Property(paramLambda, "retrieveguid");
        var lambda = Expression.Lambda<Func<IRetrieveGuid, bool>>(
            Expression.Equal(columnLambda, Expression.Call(Expression.Convert(Expression.Constant(search.RetrieveGuid), typeof (object)), typeof (object).GetMethod("ToString"))), paramLambda);
        return queryable.Where(lambda);
    }

but is incredibly slow as it produces following sql

([Extent1].[retrieveguid] = 'c87d1234-46ad-47bf-9a9c-d9a35a454bd5' as uniqueidentifier) AS nvarchar(max))))) OR (([Extent1].[retrieveguid] IS NULL) AND (LOWER( CAST( cast('c87d1234-46ad-47bf-9a9c-d9a35a454bd5' as uniqueidentifier) AS nvarchar(max))) IS NULL)))

No coercion operator is defined between types 'System.Guid' and 'System.String'

1 Answers1