是否可以在Lambda表达式中包含SqlFunctions.StringConvert？ [英] Is it possible to include SqlFunctions.StringConvert in a Lambda Expression?

问题描述

我一直在学习表达式，并使用下面的代码为数据库模型添加建立表达式（EF4 - ORACLE not SQL！）

I have been learning Expressions and using the code below to add build up an expression against a database model (EF4 - ORACLE not SQL!)

这对于Oracle，并允许我动态建立谓词，如CustomerId，Contains，2 到 f => f.CustomerId.ToString （）.ToLower（）。包含（2）

This works perfectly against Oracle, and allows me to dynamically build up predicates such as "CustomerId", "Contains", 2 into f=>f.CustomerId.ToString().ToLower().Contains("2")

但是，如果我尝试反对SQL Server，那么它失败，因为我需要调用 SqlFunctions.StringConvert - 但是我不知道如何把它包含在Lambda中？

However, if I try against SQL Server then it fails because I need to call SqlFunctions.StringConvert - but I don't know how to get that included in the Lambda?

我的结束结果将是如下：

My end result would be something like:

f=> SqlFunctions.StringConvert(f.CustomerId).ToLower().Contains("2")

Thx： ）

编辑：添加了我尝试过的示例

这段代码看起来好像几乎可以工作，这样一来！_
但是，它会在 var sqlExpression上引发错误行

This code looks like it almost works, sort of!
However, it throws an error on the var sqlExpression line

Expression of type 'System.Double' cannot be used for parameter of type 'System.Nullable`1[System.Double]' of method 'System.String StringConvert(System.Nullable`1[System.Double])'

_

MethodInfo convertDouble = typeof(Convert).GetMethod("ToDouble",new Type[]{typeof(int)});
                    var cExp = Expression.Call(convertDouble, left.Body);

                    var entityParam = Expression.Parameter(typeof(TModel), "f");
                    MethodInfo sqlFunc = typeof(SqlFunctions).GetMethod("StringConvert", new Type[] { typeof(double) });
                    var sqlExpression = Expression.Call(sqlFunc, cExp);


                    MethodInfo contains = typeof(string).GetMethod("Contains", new[] { typeof(string) });
                    right = Expression.Constant(value.ToString(), typeof(string));

                    var result = left.AddToString().AddToLower().AddContains(value.ToString());
                    return result;

---

---

public static Expression<Func<T, string>> AddToString<T, U>(this Expression<Func<T, U>> expression)
        {

            return Expression.Lambda<Func<T, string>>(
                Expression.Call(expression.Body,
                "ToString",
                null,
                null),
                expression.Parameters);
        }

        public static Expression<Func<T, string>> AddToLower<T>(this Expression<Func<T, string>> expression)
        {

            return Expression.Lambda<Func<T, string>>(
                Expression.Call(expression.Body,
                "ToLower",
                null,
                null),
                expression.Parameters);
        }

        public static Expression<Func<T, bool>> AddContains<T>(this Expression<Func<T, string>> expression, string searchValue)
        {
            return Expression.Lambda<Func<T, bool>>(
                Expression.Call(
                    expression.Body,
                    "Contains",
                    null,
                    Expression.Constant(searchValue)),
                expression.Parameters);
        }

推荐答案

我相信你基本上需要构建以下lambda表达式的等效表达式：

I believe you basically need to build an equivalent expression of the following lambda expression:

e => SqlFunctions.StringConvert((double?) e.Number).Contains("6"))

是一个完整的复制粘贴示例。它使用CodeFirst，所以应该工作，而不必创建数据库或类似的东西。只需添加实体框架nuget包（我使用EF6，但它应该适用于EF5）。构建lambda是你真正的后期。

Here is a full copy-paste example. It uses CodeFirst so should work without having to create database or anything like that. Just add the Entity Framework nuget package (I used EF6 but it should work for EF5 as well). Build lambda is what you are really after.

namespace ConsoleApplication8
{
    public class MyEntity
    {
        public int Id { get; set; }
        public int Number { get; set; }
    }


    public class MyContext : DbContext
    {
        public DbSet<MyEntity> Entities { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new MyContext())
            {
                if (!ctx.Entities.Any())
                {
                    ctx.Entities.Add(new MyEntity() {Number = 123});
                    ctx.Entities.Add(new MyEntity() {Number = 1893});
                    ctx.Entities.Add(new MyEntity() {Number = 46});
                    ctx.SaveChanges();
                }

                foreach(var entity in ctx.Entities.Where(e => SqlFunctions.StringConvert((double?) e.Number).Contains("6")))
                {
                    Console.WriteLine("{0} {1}", entity.Id, entity.Number);
                }

                foreach (var entity in ctx.Entities.Where(BuildLambda<MyEntity>("Number", "6")))
                {
                    Console.WriteLine("{0} {1}", entity.Id, entity.Number);
                }

            }
        }

        private static Expression<Func<T, bool>> BuildLambda<T>(string propertyName, string value)
        {
            var parameterExpression = Expression.Parameter(typeof(T), "e");

            var stringConvertMethodInfo = 
                typeof(SqlFunctions).GetMethod("StringConvert", new Type[] {typeof (double?)});

            var stringContainsMethodInfo =
                typeof (String).GetMethod("Contains");

            return 
                Expression.Lambda<Func<T, bool>>(
                Expression.Call(
                    Expression.Call(
                        stringConvertMethodInfo,
                        Expression.Convert(
                            Expression.Property(parameterExpression, "Number"),
                            typeof (double?))),
                    stringContainsMethodInfo,
                    Expression.Constant(value)),
                parameterExpression);
        }
    }
}

这篇关于是否可以在Lambda表达式中包含SqlFunctions.StringConvert？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！