使用linq的IEnumerable<dynamic>

Question

我尝试构建动态数据上下文，但Linq不支持动态类型。我在这里找到了解决方法： http://jrwren.wrenfam.com/blog/2010/03/04/linq-abuse-with-the-c-4-dynamic-type/。

public static class ObjectExtensionMethod
{
    public static IEnumerable<dynamic> Select(this object source, Func<dynamic, dynamic> map)
    {
        foreach (dynamic item in source as dynamic)
        {
            yield return map(item);
        }
    }

    public static IEnumerable<dynamic> Where(this object source, Func<dynamic, dynamic> predicate)
    {
        foreach (dynamic item in source as dynamic)
        {
            if (predicate(item))
                yield return item;
        }
    }
}

这种解决方案的问题在于需要从数据库中获取所有数据，然后再应用where语句。有没有办法在从数据库获取数据之前使用动态类型应用where语句？

- Bakri

---

我不确定你是如何与数据库交互的，但使用动态数据库查询会使你的应用程序性能变差。此外，.net已经提供了Entity Framework来与数据库交互，无需额外努力。 - Rohit Prakash

LINQ检查编译时表达式树，将C#代码转换为SQL语句。一旦您输入“dynamic”，它就不再是编译时，因此无法工作。因此，不能使用“dynamic”查询数据库。 - Enigmativity

我需要使用动态查询构建查询，因为数据库表是在运行时构建的。之后，我会在运行时构建linq to sql类。然后，我需要维护该表的查询。 - Bakri

这并不意味着你需要“动态”。你可以使用IDataRecord处理数据行或者使用object处理固定的对象。 - DarkWanderer

@Bakri - 如果你需要使用动态数据，那么你应该使用标准的数据读取器，而不是试图让LINQ工作。将数据加载到内存中，然后使用LINQ-to-objects。 - Enigmativity

Answer 1

这个解决方案的问题在于需要从数据库中获取所有数据，然后再应用 where 语句。

这里的问题不在于 dynamic，而在于您迭代源的方式。您使用了 foreach，并期望它被转换为 SQL 或类似的东西，但这是错误的假设。一旦通过 GetEnumerator() 方法调用创建了迭代器，查询将被“实现”，即使 source 的实际类型实现了 IQueryable，所有其他操作也将在内存中执行。

如果您希望将条件翻译为 SQL，则需要实现 IQueryableProvider。

或者，至少您可以尝试调用底层的 IQueryableProvider。但我不确定它是否会起作用。

public static class ObjectExtensionMethod
{
    public static IQueryable Select<T>(this IQueryable source, Expression<Func<dynamic, dynamic>> map)
    {
        var method = new Func<IQueryable<dynamic>, Expression<Func<dynamic, dynamic>>, IQueryable<dynamic>>(Queryable.Select).Method;

        var call = Expression.Call(null, method, source.Expression, Expression.Quote(map));

        return source.Provider.CreateQuery(call);
    }

    public static IQueryable Where(this IQueryable source, Expression<Func<dynamic, bool>> predicate)
    {
        var method = new Func<IQueryable<dynamic>, Expression<Func<dynamic, bool>>, IQueryable<dynamic>>(Queryable.Where).Method;

        var call = Expression.Call(null, method, source.Expression, Expression.Quote(predicate));

        return source.Provider.CreateQuery(call);
    }
}

请注意，source 参数的类型已从 object 更改为 IQueryable，map 和 predicate 参数的类型也已更改为 Expression<Func<,>>。

- hazzik

---

谢谢@hazzik，你的解决方案非常有帮助，我补充了完整答案。 - Bakri

Answer 2

经过长时间的搜索，我找到了适合我的解决方案

public static class ObjectExtensionMethod
{
    public static IQueryable Select(this IQueryable source, Expression<Func<dynamic, dynamic>> map)
    {
        try
        {
            var method = new Func<IQueryable<dynamic>, Expression<Func<dynamic, dynamic>>, IQueryable<dynamic>>(Queryable.Select).Method;

            Expression conversion = Expression.Convert(source.Expression, typeof(System.Linq.IQueryable<dynamic>));

            var call = Expression.Call(null, method, conversion, Expression.Quote(map));

            return source.Provider.CreateQuery(call);
        }
        catch (Exception ex)
        {
            return null;
        }
    }

    public static IQueryable Where<T>(this IQueryable source, Expression<Func<T, bool>> predicate)
    {
        try
        {
            var method = new Func<IQueryable<T>, Expression<Func<T, bool>>, IQueryable<T>>(Queryable.Where).Method;

            Expression conversion = Expression.Convert(source.Expression, typeof(System.Linq.IQueryable<T>));

            var call = Expression.Call(null, method, conversion, predicate);

            return source.Provider.CreateQuery(call);
        }
        catch (Exception ex)
        {
            return null;
        }
    }
    public static IEnumerable<dynamic> ToList(this IQueryable source)
    {
        return source as dynamic;
    }
}

这个问题是关于传递 Expression<Func<dynamic, dynamic>> 的知识点。
为了解决这个问题，您可以尝试以下方法：

    public Expression<Func<T, Boolean>> GetWhereFunc<T>(List<WhereCondition> searchFieldList,T item)
    {
        try
        {
            if (searchFieldList == null || searchFieldList.Count == 0)
            {
                return null;
            }
            ParameterExpression pe = Expression.Parameter(item.GetType(), "c");

            //ParameterExpression pe = Expression.Parameter(typeof(object), "c");
            Type itemType = item.GetType();
            //combine them with and 1=1 Like no expression
            Expression combined = null;
            Type tempPropType;
            if (searchFieldList != null)
            {
                foreach (WhereCondition fieldItem in searchFieldList)
                {
                    if (string.IsNullOrEmpty(fieldItem.Value))
                        continue;
                    if (!string.IsNullOrEmpty(fieldItem.GridTblName))
                        continue;
                    //Expression for accessing Fields name property
                    Expression columnNameProperty = Expression.Property(pe, fieldItem.ColumName);

                    //the name constant to match 
                    Expression columnValue = null;
                    tempPropType = itemType.GetProperty(fieldItem.ColumName).PropertyType;

                    if (tempPropType == typeof(DateTime) || tempPropType == typeof(DateTime?))
                    {
                        if (string.IsNullOrEmpty(fieldItem.Value))
                        {
                        }
                        else
                        {

                            DateTime tempdate = DateTime.Parse(fieldItem.Value);
                            TimeZone zoneclient = TimeZone.CurrentTimeZone;
                            TimeSpan spclient = zoneclient.GetUtcOffset(tempdate);
                            tempdate = tempdate.AddMinutes(-1 * spclient.TotalMinutes);
                            fieldItem.Value = tempdate.ToString();
                        }
                    }
                    if (tempPropType == typeof(Guid) || tempPropType == typeof(Guid?))
                    {
                        if (string.IsNullOrEmpty(fieldItem.Value))
                        {
                            columnValue = Expression.Constant(null);
                        }
                        else
                        {
                            if (tempPropType == typeof(Guid?))
                            {
                                columnValue = Expression.Constant((Guid?)Guid.Parse(fieldItem.Value), typeof(Guid?));
                            }
                            else
                            {
                                columnValue = Expression.Constant(Guid.Parse(fieldItem.Value));

                            }
                        }
                    }


                    else if (tempPropType.IsGenericType && tempPropType.GetGenericTypeDefinition() == typeof(Nullable<>))
                    {
                        if (string.IsNullOrEmpty(fieldItem.Value))
                        {
                            columnValue = Expression.Constant(null);
                        }
                        else
                        {

                            columnValue = Expression.Constant(Convert.ChangeType(fieldItem.Value, tempPropType.GetGenericArguments()[0])
                                , tempPropType);
                        }
                    }
                    else
                    {
                        columnValue = Expression.Constant(Convert.ChangeType(fieldItem.Value, tempPropType));
                    }
                    Expression e1 = null;
                    MethodInfo method;
                    switch (fieldItem.Cond)
                    {
                        case Condetion.Equal:
                            e1 = Expression.Equal(columnNameProperty, columnValue);
                            break;
                        case Condetion.Greater:
                            e1 = Expression.GreaterThan(columnNameProperty, columnValue);
                            break;
                        case Condetion.GreaterOrEqual:
                            e1 = Expression.GreaterThanOrEqual(columnNameProperty, columnValue);
                            break;
                        case Condetion.Lower:
                            e1 = Expression.LessThan(columnNameProperty, columnValue);
                            break;
                        case Condetion.LowerOrEqual:
                            e1 = Expression.LessThanOrEqual(columnNameProperty, columnValue);
                            break;
                        case Condetion.NotEqual:
                            e1 = Expression.NotEqual(columnNameProperty, columnValue);
                            break;
                        case Condetion.Contaiens:
                            if (fieldItem.IsContaien)
                            {

                                Type tt = fieldItem.Values.GetType();

                                if (tt == typeof(List<dynamic>))
                                {
                                    IEnumerable<dynamic> val = fieldItem.Values.Cast<dynamic>().ToList();
                                    var someValueContain = Expression.Constant(val, val.GetType());
                                    var convertExpression = Expression.Convert(columnNameProperty, typeof(object));
                                    e1 = Expression.Call(someValueContain, "Contains", new Type[] { }, convertExpression);
                                }
                                else
                                {

                                    var mval = fieldItem.Values.AsQueryable().Cast<dynamic>();

                                    var someValueContain = Expression.Constant(mval, mval.GetType());
                                    var convertExpression = Expression.Convert(columnNameProperty, typeof(object));
                                    e1 = Expression.Call((
                                        ((Expression<Func<bool>>)
                                        (() => Queryable.Contains(default(IQueryable<dynamic>), default(object))))
                                        .Body as MethodCallExpression).Method,
                                        someValueContain,
                                        convertExpression);
                                }

                            }
                            else
                            {
                                method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
                                var someValueContain = Expression.Constant(fieldItem.Value, columnValue.Type);
                                e1 = Expression.Call(columnNameProperty, method, someValueContain);
                            }
                            break;
                        case Condetion.StartWith:
                            method = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
                            var someValueStartWith = Expression.Constant(fieldItem.Value, columnValue.Type);
                            e1 = Expression.Call(columnNameProperty, method, someValueStartWith);
                            break;
                        case Condetion.EndWith:
                            method = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });
                            var someValueEndWith = Expression.Constant(fieldItem.Value, columnValue.Type);
                            e1 = Expression.Call(columnNameProperty, method, someValueEndWith);
                            break;
                        case Condetion.NotContaiens:
                            if (fieldItem.IsContaien)
                            {
                                Type tt = fieldItem.Values.GetType();
                                if (tt == typeof(List<dynamic>))
                                {
                                    IEnumerable<dynamic> val = fieldItem.Values.Cast<dynamic>().ToList();
                                    var someValueContain = Expression.Constant(val, val.GetType());
                                    var convertExpression = Expression.Convert(columnNameProperty, typeof(object));
                                    e1 = Expression.Call(someValueContain, "Contains", new Type[] { }, convertExpression);
                                    e1 = Expression.Not(e1);
                                }
                                else
                                {

                                    var mval = fieldItem.Values.AsQueryable().Cast<dynamic>();

                                    var someValueContain = Expression.Constant(mval, mval.GetType());
                                    var convertExpression = Expression.Convert(columnNameProperty, typeof(object));
                                    e1 = Expression.Call((
                                        ((Expression<Func<bool>>)
                                        (() => Queryable.Contains(default(IQueryable<dynamic>), default(object))))
                                        .Body as MethodCallExpression).Method,
                                        someValueContain,
                                        convertExpression);
                                    e1 = Expression.Not(e1);
                                }


                            }
                            else
                            {
                                method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
                                var someValueContain = Expression.Constant(fieldItem.Value, columnValue.Type);
                                e1 = Expression.Call(columnNameProperty, method, someValueContain);
                                e1 = Expression.Not(e1);
                            }
                            break;
                    }
                    if (combined == null)
                    {
                        combined = e1;
                    }
                    else
                    {
                        combined = Expression.And(combined, e1);
                    }
                }
            }
            if (combined == null)
            {
                return null;
            }
            var mm = Expression.Lambda<Func<T, bool>>(combined, pe);


            return mm;//.Compile();
        }
        catch (Exception ex)
        {
            Logs.Log(ex);
            return null;
        }
    }


public class WhereCondition
{
    public string ColumName { set; get; }
    public string Value { set; get; }
    public Condetion Cond { set; get; }
    public string GridTblName { set; get; }
    public IEnumerable<dynamic> Values { set; get; }
    public bool IsContaien { set; get; }
    public WhereCondition(string columName, string value, Condetion cond)
    {
        ColumName = columName;
        Value = value;
        Cond = cond;
    }
    public WhereCondition()
    {
        ColumName = "";
        Value = "";
        Cond = Condetion.Equal;
    }
}
public enum Condetion { Equal, Greater, GreaterOrEqual, Lower, LowerOrEqual, NotEqual, Contaiens, NotContaiens, StartWith,EndWith }

我们现在可以这样调用查询。

WhereCondition whereCondition = new WhereCondition();
            whereCondition.ColumName = "Id";
            whereCondition.Cond = Condetion.Equal;
            whereCondition.Value = id.ToString();
            Expression<Func<T, bool>> whereEx = GetWhereFunc(new List<WhereCondition>(){whereCondition}, GetType());

            return (from c in RDaynamicContext.GetTable(tbl)
                    select c).Where(whereEx).FirstOrDefault();

- Bakri