C# Linq datatable动态groupby

最新推荐文章于 2024-09-02 22:51:32 发布

编程压力大

最新推荐文章于 2024-09-02 22:51:32 发布

阅读量9.2k

点赞数 2

分类专栏： LinQ 文章标签： LinQ datatable 动态 c#

本文链接：https://blog.csdn.net/duan1311/article/details/51769119

版权

这篇博客介绍了如何在C#中扩展Linq的GroupBy方法，以支持DataTable的动态分组。作者针对网上找到的不支持DataTable动态分组的方法进行了修改，使得可以传入分组列名，自动生成相应的Linq表达式。通过一个具体的例子展示了如何使用这个扩展方法，从而简化了对DataTable进行复杂分组的操作。

摘要由CSDN通过智能技术生成

声明：个人技术水平有限，欢迎指正。不喜勿喷
问题：可否传入分组列与合计列，自动进行分组合计
在网上找了很多，发现Scott大神写了一个动态的LinQ拼装方法，但是不支持DataTable的动态方法。个人做了一些修改。先看结果

对groupby 方法进行拓展，入参为一个字符串。（扩展方法在下面，非常长，大神写的源码，自己做了些修改）

举例：groupby 的入参为字符串：new(Field("col1") as col1,Field("col2") as col2 ),调用结果就相当于一个linq:

var query = from p in source group p by new{ col1 = p.field("col1"),col2 = p.field("col2")}

上述的groupby 方法实现了之后，只要我实现一个动态拼接linq字符串的方法就可以啦！

//拼写动态的group 字符串

                string vDmyLinq = "";
                for (int i = 0; i < vGroupColumns.Count; i++)
                {
                    if (string.IsNullOrEmpty(vDmyLinq))
                    {
                        vDmyLinq = @"new (Field(@" + i.ToString() + @") as " + vGroupColumns[i];
                    }
                    else
                    {
                        vDmyLinq = vDmyLinq + @" , Field(@" + i.ToString() + @") as " + vGroupColumns[i];
                    }
                }
                if (!string.IsNullOrEmpty(vDmyLinq))
                {
                    vDmyLinq = vDmyLinq + ")";
                }
                //获取group结果
                var queryList = source.AsEnumerable().GroupBy(vDmyLinq, vGroupColumns.ToArray(), types.ToArray());

//拼装查询的动态linq
                string vSelector = "";
                //获取分组列
                foreach (string item in vGroupColumns)
                {
                    if (string.IsNullOrEmpty(vSelector))
                    {
                        vSelector = "new(Key." + item + " as " + item;
                    }
                    else
                    {
                        vSelector = vSelector + ",Key." + item + " as " + item;
                    }
                }
                //匹配计算方法，获取要合计的列
                for (int i = 0; i < expColumns.Count; i++)
                {
                    var item = expColumns[i];
                    #region 匹配计算方法
                    string expreesionStr = item.ExpressionStr;
                    string vMethodName = expreesionStr.Substring(0, expreesionStr.IndexOf("("));
                    string linqExpreeMethod = "";
                    CellExpCommon.eExpression sumType;
                    bool l_bIsSumType = Enum.TryParse<CellExpCommon.eExpression>(vMethodName, out sumType);
                    if (l_bIsSumType)
                    {
                        if (sumType == CellExpCommon.eExpression.Avg)
                        {
                            linqExpreeMethod = "Average";


                            vSelector = vSelector + "," + linqExpreeMethod + "(Field(@" + i.ToString() + ")) as " + item.ColumnName;
                        }
                        else if (sumType == CellExpCommon.eExpression.Sum)
                        {
                            linqExpreeMethod = "Sum";


                            vSelector = vSelector + "," + linqExpreeMethod + "(Field(@" + i.ToString() + ")) as " + item.ColumnName;
                        }




                        else if (sumType == CellExpCommon.eExpression.Max)
                        {
                            linqExpreeMethod = "Max";
                            vSelector = vSelector + "," + linqExpreeMethod + "() as " + item.ColumnName;
                        }
                        else if (sumType == CellExpCommon.eExpression.Min)
                        {
                            linqExpreeMethod = "Min";
                            vSelector = vSelector + "," + linqExpreeMethod + "() as " + item.ColumnName;
                        }
                        else if (sumType == CellExpCommon.eExpression.Count)
                        {
                            linqExpreeMethod = "Count";
                            vSelector = vSelector + "," + linqExpreeMethod + "() as " + item.ColumnName;
                        }


                    }
                    #endregion
                    vSumCols.Add(item.ColumnName);
                    sumTypes.Add(table.Columns[item.ColumnName].DataType);
                }
                vSelector = vSelector + ")";


                var queryLIst2 = queryList.Select(vSelector, vSumCols.ToArray(), sumTypes.ToArray());

以上是拼装和调用GroupBy的方法，是不是很简单，只要传入分组列与合计列就OK了！

下面是对Scott大神的代码修改之后的动态拼装修改！也就是实现DataTable的GroupBy拓展方法！

using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Reflection.Emit;
using System.Threading;
using System.Data;
using System.Collections;

namespace System.Data
{
    public static class DynamicQueryable
    {

       
        public static IEnumerable Select(this IEnumerable sourceEnum, string keySelector, string[] values, Type[] types)
        {
            if (sourceEnum == null) throw new ArgumentNullException("source");
            if (keySelector == null) throw new ArgumentNullException("keySelector");
            IQueryable source = sourceEnum.AsQueryable();

            LambdaExpression keyLambda = DynamicExpression.ParseLambda(source.ElementType, null, keySelector, values, types);

            return source.Provider.Execute<IEnumerable>(
                 Expression.Call(
                     typeof(Enumerable), "Select",
                     new Type[] { source.ElementType, keyLambda.Body.Type },
                     source.Expression, keyLambda
                     )
                     );
        }
       
        public static IEnumerable GroupBy(this IEnumerable<DataRow> sourceEnum, string keySelector, string[] values,Type[] types)
        {
            if (sourceEnum == null) throw new ArgumentNullException("source");
            if (keySelector == null) throw new ArgumentNullException("keySelector");
            IQueryable<DataRow> source = sourceEnum.AsQueryable();

            LambdaExpression keyLambda = DynamicExpression.ParseLambda(source.ElementType, null, keySelector, values,types);

            return source.Provider.Execute<IEnumerable>(
                 Expression.Call(
                     typeof(Enumerable), "GroupBy",
                     new Type[] { source.ElementType, keyLambda.Body.Type },
                     source.Expression, keyLambda
                     )
                     );
        }
        public static IQueryable<T> Where<T>(this IQueryable<T> source, string predicate, params object[] values)
        {
            return (IQueryable<T>)Where((IQueryable)source, predicate, values);
        }

        public static IQueryable Where(this IQueryable source, string predicate, params object[] values)
        {
            if (source == null) throw new ArgumentNullException("source");
            if (predicate == null) throw new ArgumentNullException("predicate");
            LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType, typeof(bool), predicate, values);
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Where",
                    new Type[] { source.ElementType },
                    source.Expression, Expression.Quote(lambda)));
        }

        public static IQueryable Select(this IQueryable source, string selector, params object[] values)
        {
            if (source == null) throw new ArgumentNullException("source");
            if (selector == null) throw new ArgumentNullException("selector");
            LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType, null, selector, values);
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Select",
                    new Type[] { source.ElementType, lambda.Body.Type },
                    source.Expression, Expression.Quote(lambda)));
        }

        public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string ordering, params object[] values)
        {
            return (IQueryable<T>)OrderBy((IQueryable)source, ordering, values);
        }

        public static IQueryable OrderBy(this IQueryable source, string ordering, params object[] values)
        {
            if (source == null) throw new ArgumentNullException("source");
            if (ordering == null) throw new ArgumentNullException("ordering");
            ParameterExpression[] parameters = new ParameterExpression[] {
                Expression.Parameter(source.ElementType, "") };
            ExpressionParser parser = new ExpressionParser(parameters, ordering, values);
            IEnumerable<DynamicOrdering> orderings = parser.ParseOrdering();
            Expression queryExpr = source.Expression;
            string methodAsc = "OrderBy";
            string methodDesc = "OrderByDescending";
            foreach (DynamicOrdering o in orderings)
            {
                queryExpr = Expression.Call(
                    typeof(Queryable), o.Ascending ? methodAsc : methodDesc,
                    new Type[] { source.ElementType, o.Selector.Type },
                    queryExpr, Expression.Quote(Expression.Lambda(o.Selector, parameters)));
                methodAsc = "ThenBy";
                methodDesc = "ThenByDescending";
            }
            return source.Provider.CreateQuery(queryExpr);
        }

        public static IQueryable Take(this IQueryable source, int count)
        {
            if (source == null) throw new ArgumentNullException("source");
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Take",
                    new Type[] { source.ElementType },
                    source.Expression, Expression.Constant(count)));
        }

        public static IQueryable Skip(this IQueryable source, int count)
        {
            if (source == null) throw new ArgumentNullException("source");
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Skip",
                    new Type[] { source.ElementType },
                    source.Expression, Expression.Constant(count)));
        }

        public static IQueryable GroupBy(this IQueryable source, string keySelector, string elementSelector, params object[] values)
        {
            if (source == null) throw new ArgumentNullException("source");
            if (keySelector == null) throw new ArgumentNullException("keySelector");
            if (elementSelector == null) throw new ArgumentNullException("elementSelector");
            LambdaExpression keyLambda = DynamicExpression.ParseLambda(source.ElementType, null, keySelector, values);
            LambdaExpression elementLambda = DynamicExpression.ParseLambda(source.ElementType, null, elementSelector, values);
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "GroupBy",
                    new Type[] { source.ElementType, keyLambda.Body.Type, elementLambda.Body.Type },
                    source.Expression, Expression.Quote(keyLambda), Expression.Quote(elementLambda)));
        }

        public static bool Any(this IQueryable source)
        {
            if (source == null) throw new ArgumentNullException("source");
            return (bool)source.Provider.Execute(
                Expression.Call(
                    typeof(Queryable), "Any",
                    new Type[] { source.ElementType }, source.Expression));
        }

        public static int Count(this IQueryable source)
        {
            if (source == null) throw new ArgumentNullException("source");
            return (int)source.Provider.Execute(
                Expression.Call(
                    typeof(Queryable), "Count",
                    new Type[] { source.ElementType }, source.Expression));
        }
    }

    public abstract class DynamicClass
    {
        public override string ToString()
        {
            PropertyInfo[] props = this.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
            StringBuilder sb = new StringBuilder();
            sb.Append("{");
            for (int i = 0; i < props.Length; i++)
            {
                if (i > 0) sb.Append(", ");
                sb.Append(props[i].Name);
                sb.Append("=");
                sb.Append(props[i].GetValue(this, null));
            }
            sb.Append("}");
            return sb.ToString();
        }
    }

    public class DynamicProperty
    {
        string name;
        Type type;

        public DynamicProperty(string name, Type type)
        {
            if (name == null) throw new ArgumentNullException("name");
            if (type == null) throw new ArgumentNullException("type");
            this.name = name;
            this.type = type;
        }

        public string Name
        {
            get { return name; }
        }

        public Type Type
        {
            get { return type; }
        }
    }

    public static class DynamicExpression
    {
        public static Expression Parse(Type resultType, string expression, params object[] values)
        {
            ExpressionParser parser = new ExpressionParser(null, expression, values);
            return parser.Parse(resultType);
        }

        public static LambdaExpression ParseLambda(Type itType, Type resultType, string expression, params object[] values)
        {
            return ParseLambda(new ParameterExpression[] { Expression.Parameter(itType, "") }, resultType, expression, values);
        }
      
        public static LambdaExpression ParseLambda(Type itType, Type resultType, string expression, string[] values,Type[] types)
        {
            return ParseLambda(new ParameterExpression[] { Expression.Parameter(itType, "") }, resultType, expression, values,types);
        }
        public static LambdaExpression ParseLambda(ParameterExpression[] parameters, Type resultType, string expression, params object[] values)
        {
            ExpressionParser parser = new ExpressionParser(parameters, expression, values);
            return Expression.Lambda(parser.Parse(resultType), parameters);
        }
        
        public static LambdaExpression ParseLambda(ParameterExpression[] parameters, Type resultType, string expression, string[] values,Type[] types)
        {
            ExpressionParser parser = new ExpressionParser(parameters, expression, values,types);
            return Expression.Lambda(parser.Parse(resultType), parameters);
        }
        public static Expression<Func<T, S>> ParseLambda<T, S>(string expression, params object[] values)
        {
            return (Expression<Func<T, S>>)ParseLambda(typeof(T), typeof(S), expression, values);
        }

        public static Type CreateClass(params DynamicProperty[] properties)
        {
            return ClassFactory.Instance.GetDynamicClass(properties);
        }

        public static Type CreateClass(IEnumerable<DynamicProperty> properties)
        {
            return ClassFactory.Instance.GetDynamicClass(properties);
        }
    }

    internal class DynamicOrdering
    {
        public Expression Selector;
        public bool Ascending;
    }

    internal class Signature : IEquatable<Signature>
    {
        public DynamicProperty[] properties;
        public int hashCode;

        public Signature(IEnumerable<DynamicProperty> properties)
        {
            this.properties = properties.ToArray();
            hashCode = 0;
            foreach (DynamicProperty p in properties)
            {
                hashCode ^= p.Name.GetHashCode() ^ p.Type.GetHashCode();
            }
        }

        public override int GetHashCode()
        {
            return hashCode;
        }

        public override bool Equals(object obj)
        {
            return obj is Signature ? Equals((Signature)obj) : false;
        }

        public bool Equals(Signature other)
        {
            if (properties.Length != other.properties.Length) return false;
            for (int i = 0; i < properties.Length; i++)
            {
                if (properties[i].Name != other.properties[i].Name ||
                    properties[i].Type != other.properties[i].Type) return false;
            }
            return true;
        }
    }

    internal class ClassFactory
    {
        public static readonly ClassFactory Instance = new ClassFactory();

        static ClassFactory() { }  // Trigger lazy initialization of static fields

        ModuleBuilder module;
        Dictionary<Signature, Type> classes;
        int classCount;
        ReaderWriterLock rwLock;

        private ClassFactory()
        {
            AssemblyName name = new AssemblyName("DynamicClasses");
            AssemblyBuilder assembly = AppDomain.CurrentDomain.DefineDynamicAssembly(name, AssemblyBuilderAccess.Run);
#if ENABLE_LINQ_PARTIAL_TRUST
            new ReflectionPermission(PermissionState.Unrestricted).Assert();
#endif
            try
            {
                module = assembly.DefineDynamicModule("Module");
            }
            finally
            {
#if ENABLE_LINQ_PARTIAL_TRUST
                PermissionSet.RevertAssert();
#endif
            }
            classes = new Dictionary<Signature, Type>();
            rwLock = new ReaderWriterLock();
        }

        public Type GetDynamicClass(IEnumerable<DynamicProperty> properties)
        {
            rwLock.AcquireReaderLock(Timeout.Infinite);
            try
            {
                Signature signature = new Signature(properties);
                Type type;
                if (!classes.TryGetValue(signature, out type))
                {
                    type = CreateDynamicClass(signature.properties);
                    classes.Add(signature, type);
                }
                return type;
            }
            finally
            {
                rwLock.ReleaseReaderLock();
            }
        }

        Type CreateDynamicClass(DynamicProperty[] properties)
        {
            LockCookie cookie = rwLock.UpgradeToWriterLock(Timeout.Infinite);
            try
            {
                string typeName = "DynamicClass" + (classCount + 1);
#if ENABLE_LINQ_PARTIAL_TRUST
                new ReflectionPermission(PermissionState.Unrestricted).Assert();
#endif
                try
                {
                    TypeBuilder tb = this.module.DefineType(typeName, TypeAttributes.Class |
                        TypeAttributes.Public, typeof(DynamicClass));
                    FieldInfo[] fields = GenerateProperties(tb, properties);
                    GenerateEquals(tb, fields);
                    GenerateGetHashCode(tb, fields);
                    Type result = tb.CreateType();
                    classCount++;
                    return result;
                }