(精华)2020年6月26日 C#类库 数据库统计帮助类(扩展方法)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Reflection;
using System.Text;

namespace Core.Util
{
    /// <summary>
    /// 数据库查询帮助类
    /// </summary>
    public static class DbSearchHelper
    {
        /// <summary>
        /// 获取数据库统计数据
        /// </summary>
        /// <param name="dataSource">数据源</param>
        /// <param name="groupColumn">分组的列</param>
        /// <param name="statisColumn">统计的列</param>
        /// <param name="funcName">统计方法名(Max,Min,Average,Count())</param>
        /// <returns></returns>
        public static List<DbStatisData> GetDbStatisData(this IQueryable dataSource, string groupColumn, string statisColumn, string funcName)
        {
            List<DbStatisData> resData = new List<DbStatisData>();
            var q = dataSource.GroupBy(groupColumn, "it")
            .Select($"new (it.Key as Key,{funcName}(it.{statisColumn}) as Value)")
            .CastToList<dynamic>();
            foreach (dynamic aData in q)
            {
                DbStatisData newData = new DbStatisData();
                resData.Add(newData);
                
                newData.Key = aData.Key;
                newData.Value = aData.Value;
            }

            return resData;
        }

        /// <summary>
        /// 获取数据库统计数据
        /// </summary>
        /// <param name="dataSource">数据源</param>
        /// <param name="groupColumn">分组的列</param>
        /// <param name="searchEntris">查询的配置项</param>
        /// <returns></returns>
        public static List<DynamicModel> GetDbStatisData(this IQueryable dataSource, string groupColumn, SearchEntry[] searchEntris)
        {
            if (searchEntris.Count() == 0)
                throw new Exception("请输入至少一个查询配置项");
            StringBuilder selectStr = new StringBuilder();
            selectStr.Append("new (it.Key as Key");
            int count = searchEntris.Count();
            searchEntris.ForEach((item, index) => {
                string end = "";
                if (index == count - 1)
                    end = ")";
                selectStr.Append($",{item.FuncName}(it.{item.StatisColoum}) as {item.ResultName}{end}");
            });

            List<DynamicModel> resData = new List<DynamicModel>();
            var q = dataSource.GroupBy(groupColumn, "it")
            .Select(selectStr.ToString())
            .CastToList<dynamic>();
            foreach (dynamic aData in q)
            {
                DynamicModel newData = new DynamicModel();
                resData.Add(newData);
                object obj = (object)aData;
                Type type = obj.GetType();
                newData.AddProperty("Key", type.GetProperty("Key").GetValue(obj));
                searchEntris.ForEach(item =>
                {
                    newData.AddProperty(item.ResultName, type.GetProperty(item.ResultName).GetValue(obj));
                });
            }

            return resData;
        }

        /// <summary>
        /// 获取IQueryable
        /// </summary>
        /// <param name="obj">包含获取IQueryable方法的对象</param>
        /// <param name="funcName">获取IQueryable的方法名</param>
        /// <param name="entityName">实体名</param>
        /// <param name="nameSpace">命名空间</param>
        /// <returns></returns>
        public static IQueryable GetIQueryable(object obj, string funcName, string entityName, string nameSpace)
        {
            Type type = obj.GetType();
            MethodInfo method = type.GetMethod(funcName);
            var entityType = Assembly.Load(nameSpace).GetTypes().Where(x => x.Name.ToLower().Contains(entityName.ToLower())).FirstOrDefault();
            if (entityType.IsNullOrEmpty())
                throw new Exception("请输入有效的实体名！");

            var iQueryable = (IQueryable)method.MakeGenericMethod(entityType).Invoke(obj, null);

            return iQueryable;
        }
    }

    /// <summary>
    /// 统计数据模型
    /// </summary>
    public class DbStatisData
    {
        /// <summary>
        /// 分组查询的列
        /// </summary>
        public string Key { get; set;}

        /// <summary>
        /// 统计后的数值
        /// </summary>
        public double? Value { get; set; }
    }

    /// <summary>
    /// 统计查询配置项
    /// </summary>
    public struct SearchEntry
    {
        /// <summary>
        /// 统计的列
        /// </summary>
        public string StatisColoum { get; set; }

        /// <summary>
        /// 返回数据列名
        /// </summary>
        public string ResultName { get; set; }

        /// <summary>
        /// 统计方法名（Max,Min,Average,Count()等）
        /// </summary>
        public string FuncName { get; set; }
    }
}

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
  
34
  
35
  
36
  
37
  
38
  
39
  
40
  
41
  
42
  
43
  
44
  
45
  
46
  
47
  
48
  
49
  
50
  
51
  
52
  
53
  
54
  
55
  
56
  
57
  
58
  
59
  
60
  
61
  
62
  
63
  
64
  
65
  
66
  
67
  
68
  
69
  
70
  
71
  
72
  
73
  
74
  
75
  
76
  
77
  
78
  
79
  
80
  
81
  
82
  
83
  
84
  
85
  
86
  
87
  
88
  
89
  
90
  
91
  
92
  
93
  
94
  
95
  
96
  
97
  
98
  
99
  
100
  
101
  
102
  
103
  
104
  
105
  
106
  
107
  
108
  
109
  
110
  
111
  
112
  
113
  
114
  
115
  
116
  
117
  
118
  
119
  
120
  
121
  
122
  
123
  
124
  
125
  
126
  
127
  
128
  
129
  
130
  
131
  
132
  
133
  
134
  
135
  
136
  
137
  
138
  
139
  
140
 

文章来源: codeboy.blog.csdn.net，作者：愚公搬代码，版权归原作者所有，如需转载，请联系作者。

原文链接：codeboy.blog.csdn.net/article/details/106966591