Create and Calling SqlFunctions in Linq to Entities C#?

See more:

SQL

LINQ

C#5.0

EF6.0

We wanna create the following code dynamically with expression:

TestDatabaseEntities entities = new TestDatabaseEntities();
entities.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); // to show generated tsql
// We wanna create the following code dynamically
List<Employee> employees = entities.Employees.Where(c => SqlFunctions.StringConvert((double?)c.SomeNumber).Trim().Contains("123")).ToList();

Above code works great. now we wanna create it with expression dynamically.

We done it as follows :

Type entityType = typeof(TEntity);
ParameterExpression parameterExpression = Expression.Parameter(entityType, "entity");

Type nullableDoubleType = typeof (double?);
//leftSile == {entity.SomeNumber}, it's OK
UnaryExpression unaryExpression  = Expression.Convert(leftSile, nullableDoubleType);
MethodInfo stringConvertMethodInfo = typeof(SqlFunctions).GetMethod("StringConvert", new[] { nullableDoubleType });
MethodInfo trimMethodInfo = typeof(string).GetMethod("Trim", new Type[] {});
ConstantExpression newRightSide = Expression.Constant(valueObject.ToString());

var traceVariable1 = Expression.Call(stringConvertMethodInfo, unaryExpression);
var traceVariable2 = Expression.Call(traceVariable1, trimMethodInfo);
Expression resultExpression = Expression.Call(traceVariable2, methodInfo, newRightSide);
Expression<Func<Employee, bool>> expression = Expression.Lambda<Func<TEntity, bool>>(resultExpression, parameterExpression);

// expression == {entity => (StringConvert(Convert(entity.SomeNumber)).Trim().Contains("123"))}

The expression created successfully, but when we use it within EntityFramework it throws an exception:

List<Employee> employees2 = entities.Employees.Where(expression.Compile()).ToList();
//Exception :
//An unhandled exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll
//Additional information: This function can only be invoked from LINQ to Entities.