List,DataTable实现行转列的通用方案

原文连接：https://www.cnblogs.com/yanweidie/p/6485957.html

　　最近在做报表统计方面的需求，涉及到行转列报表。根据以往经验使用SQL可以比较容易完成，这次决定挑战一下直接通过代码方式完成行转列。期间遇到几个问题和用到的新知识这里整理记录一下。

阅读目录

• 问题介绍
• 动态Linq
• System.Linq.Dynamic其它用法
• DataTable行转列
• 总结

回到顶部

问题介绍

　　以家庭月度费用为例，可以在[Name,Area,Month]三个维度上随意组合进行分组，三个维度中选择一个做为列显示。

    /// <summary>
    /// 家庭费用情况
    /// </summary>
    public class House
    {
        /// <summary>
        /// 户主姓名
        /// </summary>
        public string Name { get; set; }
    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;summary&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">///</span><span style="color: rgba(0, 128, 0, 1)"> 所属行政区域
    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;/summary&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Area { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }

    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;summary&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">///</span><span style="color: rgba(0, 128, 0, 1)"> 月份
    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;/summary&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Month { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }

    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;summary&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">///</span><span style="color: rgba(0, 128, 0, 1)"> 电费金额
    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;/summary&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">double</span> DfMoney { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }

    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;summary&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">///</span><span style="color: rgba(0, 128, 0, 1)"> 水费金额
    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;/summary&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">double</span> SfMoney { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }

    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;summary&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">///</span><span style="color: rgba(0, 128, 0, 1)"> 燃气金额
    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;/summary&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">double</span> RqfMoney { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }
}</span></pre>

户主-月明细报表
户主姓名	2016-01			2016-02
	电费	水费	燃气费	电费	水费	燃气费
张三	240.9	30	25	167	24.5	17.9
李四	56.7	24.7	13.2	65.2	18.9	14.9

区域-月明细报表
区域	2016-01			2016-02
	电费	水费	燃气费	电费	水费	燃气费
江夏区	2240.9	330	425	5167	264.5	177.9
洪山区	576.7	264.7	173.2	665.2	108.9	184.9

区域月份-户明细报表
区域	月份	张三			李四
		燃气费	电费	水费	燃气费	电费	水费
江夏区	2016-01	2240.9	330	425	5167	264.5	177.9
洪山区	2016-01	576.7	264.7	173.2	665.2	108.9	184.9
江夏区	2016-02	3240.9	430	525	6167	364.5	277.9
洪山区	2016-02	676.7	364.7	273.2	765.2	208.9	284.9

  

  现在后台查出来的数据是List<House>类型，前台传过来分组维度和动态列字段。

  第1个表格前台传给后台参数

{DimensionList:['Name'],DynamicColumn:'Month'}

 第2个表格前台传给后台参数

{DimensionList:['Area'],DynamicColumn:'Month'}

第3个表格前台传给后台参数

{DimensionList:['Area','Month'],DynamicColumn:'Name'}

问题描述清楚后，仔细分析后你就会发现这里的难题在于动态分组，也就是怎么根据前台传过来的多个维度对List进行分组。

回到顶部

 

动态Linq

   下面使用System.Linq.Dynamic完成行转列功能，Nuget上搜索System.Linq.Dynamic即可下载该包。

   代码进行了封装，实现了通用的List<T>行转列功能。

        /// <summary>
        /// 动态Linq方式实现行转列
        /// </summary>
        /// <param name="list">数据</param>
        /// <param name="DimensionList">维度列</param>
        /// <param name="DynamicColumn">动态列</param>
        /// <returns>行转列后数据</returns>
        private static List<dynamic> DynamicLinq<T>(List<T> list, List<string> DimensionList, string DynamicColumn, out List<string> AllDynamicColumn) where T : class
        {
            //获取所有动态列
            var columnGroup = list.GroupBy(DynamicColumn, "new(it as Vm)") as IEnumerable<IGrouping<dynamic, dynamic>>;
            List<string> AllColumnList = new List<string>();
            foreach (var item in columnGroup)
            {
                if (!string.IsNullOrEmpty(item.Key))
                {
                    AllColumnList.Add(item.Key);
                }
            }
            AllDynamicColumn = AllColumnList;
            var dictFunc = new Dictionary<string, Func<T, bool>>();
            foreach (var column in AllColumnList)
            {
                var func = DynamicExpression.ParseLambda<T, bool>(string.Format("{0}==\"{1}\"", DynamicColumn, column)).Compile();
                dictFunc[column] = func;
            }
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">获取实体所有属性</span>
        Dictionary&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>, PropertyInfo&gt; PropertyInfoDict = <span style="color: rgba(0, 0, 255, 1)">new</span> Dictionary&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>, PropertyInfo&gt;<span style="color: rgba(0, 0, 0, 1)">();
        Type type </span>= <span style="color: rgba(0, 0, 255, 1)">typeof</span><span style="color: rgba(0, 0, 0, 1)">(T);
        </span><span style="color: rgba(0, 0, 255, 1)">var</span> propertyInfos = type.GetProperties(BindingFlags.Instance |<span style="color: rgba(0, 0, 0, 1)"> BindingFlags.Public);
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">数值列</span>
        List&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>&gt; AllNumberField = <span style="color: rgba(0, 0, 255, 1)">new</span> List&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>&gt;<span style="color: rgba(0, 0, 0, 1)">();
        </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> item <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> propertyInfos)
        {
            PropertyInfoDict[item.Name] </span>=<span style="color: rgba(0, 0, 0, 1)"> item;
            </span><span style="color: rgba(0, 0, 255, 1)">if</span> (item.PropertyType == <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">int</span>) || item.PropertyType == <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">double</span>) || item.PropertyType == <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">float</span><span style="color: rgba(0, 0, 0, 1)">))
            {
                AllNumberField.Add(item.Name);
            }
        }

        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">分组</span>
    <strong><span style="color: rgba(255, 0, 0, 1)">    var dataGroup = list.GroupBy(string.Format("new ({0})", string.Join(",", DimensionList)), "new(it as Vm)") as IEnumerable&lt;IGrouping&lt;dynamic, dynamic&gt;&gt;</span></strong><span style="color: rgba(0, 0, 0, 1)"><strong><span style="color: rgba(255, 0, 0, 1)">;</span></strong>
        List</span>&lt;<span style="color: rgba(0, 0, 255, 1)">dynamic</span>&gt; listResult = <span style="color: rgba(0, 0, 255, 1)">new</span> List&lt;<span style="color: rgba(0, 0, 255, 1)">dynamic</span>&gt;<span style="color: rgba(0, 0, 0, 1)">();
        IDictionary</span>&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>, <span style="color: rgba(0, 0, 255, 1)">object</span>&gt; itemObj = <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;
        T vm2 </span>= <span style="color: rgba(0, 0, 255, 1)">default</span><span style="color: rgba(0, 0, 0, 1)">(T);
        </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> group <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> dataGroup)
        {
            itemObj </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> ExpandoObject();
            </span><span style="color: rgba(0, 0, 255, 1)">var</span> listVm = group.Select(e =&gt; e.Vm <span style="color: rgba(0, 0, 255, 1)">as</span><span style="color: rgba(0, 0, 0, 1)"> T).ToList();
            </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">维度列赋值</span>
            vm2 =<span style="color: rgba(0, 0, 0, 1)"> listVm.FirstOrDefault();
            </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> key <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> DimensionList)
            {
                itemObj[key] </span>=<span style="color: rgba(0, 0, 0, 1)"> PropertyInfoDict[key].GetValue(vm2);
            }

            </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> column <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> AllColumnList)
            {
                vm2 </span>=<span style="color: rgba(0, 0, 0, 1)"> listVm.FirstOrDefault(dictFunc[column]);
                </span><span style="color: rgba(0, 0, 255, 1)">if</span> (vm2 != <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">)
                {
                    </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">string</span> name <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> AllNumberField)
                    {
                        itemObj[name </span>+ column] =<span style="color: rgba(0, 0, 0, 1)"> PropertyInfoDict[name].GetValue(vm2);
                    }
                }
            }
            listResult.Add(itemObj);
        }
        </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> listResult;
    }</span></pre>

   

   标红部分使用了System.Linq.Dynamic动态分组功能，传入字符串即可分组。使用了dynamic类型，关于dynamic介绍可以参考其它文章介绍哦。

回到顶部

System.Linq.Dynamic其它用法

    上面行转列代码见识了System.Linq.Dynamic的强大，下面再介绍一下会在开发中用到的方法。

    Where过滤

list.Where("Name=@0", "张三")

 

上面用到了参数化查询，实现了查找姓名是张三的数据，通过这段代码你或许感受不到它的好处。但是和EntityFramework结合起来就可以实现动态拼接SQL的功能了。

        /// <summary>
        /// EF实体查询封装 
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="Query">IQueryable对象</param>
        /// <param name="gridParam">过滤条件</param>
        /// <returns>查询结果</returns>
        public static EFPaginationResult<T> PageQuery<T>(this IQueryable<T> Query, QueryCondition gridParam)
        {
            //查询条件
            EFFilter filter = GetParameterSQL<T>(gridParam);
            var query = Query.Where(filter.Filter, filter.ListArgs.ToArray());
            //查询结果
            EFPaginationResult<T> result = new EFPaginationResult<T>();
            if (gridParam.IsPagination)
            {
                int PageSize = gridParam.PageSize;
                int PageIndex = gridParam.PageIndex < 0 ? 0 : gridParam.PageIndex;
                //获取排序信息
                string sort = GetSort(gridParam, typeof(T).FullName);
                result.Data = query.OrderBy(sort).Skip(PageIndex * PageSize).Take(PageSize).ToList<T>();
                if (gridParam.IsCalcTotal)
                {
                    result.Total = query.Count();
                    result.TotalPage = Convert.ToInt32(Math.Ceiling(result.Total * 1.0 / PageSize));
                }
                else
                {
                    result.Total = result.Data.Count();
                }
            }
            else
            {
                result.Data = query.ToList();
                result.Total = result.Data.Count();
            }
            return result;
        }

/// <summary>
        /// 通过查询条件,获取参数化查询SQL
        /// </summary>
        /// <param name="gridParam">过滤条件</param>
        /// <returns>过滤条件字符</returns>
        private static EFFilter GetParameterSQL<T>(QueryCondition gridParam)
        {
            EFFilter result = new EFFilter();
            //参数值集合
            List<object> listArgs = new List<object>();
            string filter = "1=1";
        </span><span style="color: rgba(0, 0, 255, 1)">#region</span> "处理动态过滤条件"
        <span style="color: rgba(0, 0, 255, 1)">if</span> (gridParam.FilterList != <span style="color: rgba(0, 0, 255, 1)">null</span> &amp;&amp; gridParam.FilterList.Count &gt; <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">)
        {
            StringBuilder sb </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> StringBuilder();

            </span><span style="color: rgba(0, 0, 255, 1)">int</span> paramCount = <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;
            DateTime dateTime;
            </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">操作符</span>
            <span style="color: rgba(0, 0, 255, 1)">string</span> strOperator = <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">.Empty;
            </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> item <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> gridParam.FilterList)
            {
                </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">字段名称为空则跳过</span>
                <span style="color: rgba(0, 0, 255, 1)">if</span> (<span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">.IsNullOrEmpty(item.FieldName))
                {
                    </span><span style="color: rgba(0, 0, 255, 1)">continue</span><span style="color: rgba(0, 0, 0, 1)">;
                }
                </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">匹配枚举，防止SQL注入</span>
                Operator operatorEnum = (Operator)Enum.Parse(<span style="color: rgba(0, 0, 255, 1)">typeof</span>(Operator), item.Operator, <span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">);

                </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">跳过字段值为空的</span>
                <span style="color: rgba(0, 0, 255, 1)">if</span> (operatorEnum != Operator.Null &amp;&amp; operatorEnum != Operator.NotNull &amp;&amp; <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">.IsNullOrEmpty(item.FieldValue))
                {
                    </span><span style="color: rgba(0, 0, 255, 1)">continue</span><span style="color: rgba(0, 0, 0, 1)">;
                }
                strOperator </span>=<span style="color: rgba(0, 0, 0, 1)"> operatorEnum.GetDescription();
                </span><span style="color: rgba(0, 0, 255, 1)">if</span> (item.IgnoreCase &amp;&amp; !<span style="color: rgba(0, 0, 0, 1)">item.IsDateTime)
                {
                    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">2016-07-19添加查询时忽略大小写比较</span>
                    item.FieldValue =<span style="color: rgba(0, 0, 0, 1)"> item.FieldValue.ToLower();
                    item.FieldName </span>= <span style="color: rgba(0, 0, 255, 1)">string</span>.Format(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">{0}.ToLower()</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, item.FieldName);
                }
                </span><span style="color: rgba(0, 0, 255, 1)">switch</span><span style="color: rgba(0, 0, 0, 1)"> (operatorEnum)
                {
                    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">等于,不等于，小于，大于，小于等于，大于等于</span>
                    <span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.EQ:
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.NE:
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.GT:
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.GE:
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.LT:
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.LE:
                        </span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> (item.IsDateTime)
                        {
                            </span><span style="color: rgba(0, 0, 255, 1)">if</span> (DateTime.TryParse(item.FieldValue, <span style="color: rgba(0, 0, 255, 1)">out</span><span style="color: rgba(0, 0, 0, 1)"> dateTime))
                            {
                                </span><span style="color: rgba(0, 0, 255, 1)">if</span> (!item.FieldValue.Contains(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">00:00:00</span><span style="color: rgba(128, 0, 0, 1)">"</span>) &amp;&amp; dateTime.ToString(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">HH:mm:ss</span><span style="color: rgba(128, 0, 0, 1)">"</span>) == <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">00:00:00</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">)
                                {
                                    </span><span style="color: rgba(0, 0, 255, 1)">if</span> (operatorEnum ==<span style="color: rgba(0, 0, 0, 1)"> Operator.LE)
                                    {
                                        listArgs.Add(DateTime.Parse(dateTime.ToString(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">yyyy-MM-dd</span><span style="color: rgba(128, 0, 0, 1)">"</span>) + <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> 23:59:59</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">));
                                    }
                                    </span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">
                                    {
                                        listArgs.Add(dateTime);
                                    }
                                }
                                </span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">
                                {
                                    listArgs.Add(dateTime);
                                }
                                sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> AND {0} {1} @{2}</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, item.FieldName, strOperator, paramCount);
                            }
                        }
                        </span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">
                        {
                            listArgs.Add(ConvertToType(item.FieldValue, GetPropType</span>&lt;T&gt;<span style="color: rgba(0, 0, 0, 1)">(item.FieldName)));
                            sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> AND {0} {1} @{2}</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, item.FieldName, strOperator, paramCount);
                        }
                        paramCount</span>++<span style="color: rgba(0, 0, 0, 1)">;
                        </span><span style="color: rgba(0, 0, 255, 1)">break</span><span style="color: rgba(0, 0, 0, 1)">;
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.Like:
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.NotLike:
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.LLike:
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.RLike:
                        listArgs.Add(item.FieldValue);
                        </span><span style="color: rgba(0, 0, 255, 1)">if</span> (operatorEnum ==<span style="color: rgba(0, 0, 0, 1)"> Operator.Like)
                        {
                            sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> AND {0}.Contains(@{1})</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, item.FieldName, paramCount);
                        }
                        </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span> (operatorEnum ==<span style="color: rgba(0, 0, 0, 1)"> Operator.NotLike)
                        {
                            sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> AND !{0}.Contains(@{1})</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, item.FieldName, paramCount);
                        }
                        </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span> (operatorEnum ==<span style="color: rgba(0, 0, 0, 1)"> Operator.LLike)
                        {
                            sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> AND {0}.EndsWith(@{1})</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, item.FieldName, paramCount);
                        }
                        </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span> (operatorEnum ==<span style="color: rgba(0, 0, 0, 1)"> Operator.RLike)
                        {
                            sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> AND {0}.StartsWith(@{1})</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, item.FieldName, paramCount);
                        }
                        paramCount</span>++<span style="color: rgba(0, 0, 0, 1)">;
                        </span><span style="color: rgba(0, 0, 255, 1)">break</span><span style="color: rgba(0, 0, 0, 1)">;
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.Null:
                        listArgs.Add(item.FieldValue);
                        sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> AND {0}=null</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, item.FieldName);
                        paramCount</span>++<span style="color: rgba(0, 0, 0, 1)">;
                        </span><span style="color: rgba(0, 0, 255, 1)">break</span><span style="color: rgba(0, 0, 0, 1)">;
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.NotNull:
                        listArgs.Add(item.FieldValue);
                        sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> AND {0}!=null</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, item.FieldName);
                        paramCount</span>++<span style="color: rgba(0, 0, 0, 1)">;
                        </span><span style="color: rgba(0, 0, 255, 1)">break</span><span style="color: rgba(0, 0, 0, 1)">;
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.In:
                        sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> AND (</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
                        </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> schar <span style="color: rgba(0, 0, 255, 1)">in</span> item.FieldValue.Split(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">,</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">))
                        {
                            listArgs.Add(schar);
                            sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">{0}=@{1} or </span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, item.FieldName, paramCount);
                            paramCount</span>++<span style="color: rgba(0, 0, 0, 1)">;
                        }
                        sb.Remove(sb.Length </span>- <span style="color: rgba(128, 0, 128, 1)">3</span>, <span style="color: rgba(128, 0, 128, 1)">3</span><span style="color: rgba(0, 0, 0, 1)">);
                        sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> )</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
                        </span><span style="color: rgba(0, 0, 255, 1)">break</span><span style="color: rgba(0, 0, 0, 1)">;
                    </span><span style="color: rgba(0, 0, 255, 1)">case</span><span style="color: rgba(0, 0, 0, 1)"> Operator.NotIn:
                        sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> AND (</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
                        </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> schar <span style="color: rgba(0, 0, 255, 1)">in</span> item.FieldValue.Split(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">,</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">))
                        {
                            listArgs.Add(schar);
                            sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">{0}!=@{1} and </span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, item.FieldName, paramCount);
                            paramCount</span>++<span style="color: rgba(0, 0, 0, 1)">;
                        }
                        sb.Remove(sb.Length </span>- <span style="color: rgba(128, 0, 128, 1)">3</span>, <span style="color: rgba(128, 0, 128, 1)">3</span><span style="color: rgba(0, 0, 0, 1)">);
                        sb.AppendFormat(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> )</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
                        </span><span style="color: rgba(0, 0, 255, 1)">break</span><span style="color: rgba(0, 0, 0, 1)">;
                }
                </span><span style="color: rgba(0, 0, 255, 1)">if</span> (sb.ToString().Length &gt; <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">)
                {
                    filter </span>= sb.ToString().Substring(<span style="color: rgba(128, 0, 128, 1)">4</span>, sb.Length - <span style="color: rgba(128, 0, 128, 1)">4</span><span style="color: rgba(0, 0, 0, 1)">);
                }
            }
            </span><span style="color: rgba(0, 0, 255, 1)">#endregion</span><span style="color: rgba(0, 0, 0, 1)">

        }
        result.Filter </span>=<span style="color: rgba(0, 0, 0, 1)"> filter;
        result.ListArgs </span>=<span style="color: rgba(0, 0, 0, 1)"> listArgs;
        </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> result;
    }</span></pre>

 

回到顶部

DataTable行转列

　　该部分是根据网友反馈后期再补充上的内容，意在完善行转列。下面给出实现代码

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace DataTable_RowToColumn

{

class Program

{

static void Main(string[] args)

{

DataTable dt = InitTable();

List<string> DimensionList = new List<string>() { "Area", "Month" };

string DynamicColumn = "Name";

List<string> AllDynamicColumn = null;

DataTable dtResult = RowToColumn(dt, DimensionList, DynamicColumn, out AllDynamicColumn);

Console.WriteLine(JsonConvert.SerializeObject(dtResult, Formatting.Indented));

Console.Read();

}
    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;summary&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">///</span><span style="color: rgba(0, 128, 0, 1)"> 动态Linq方式实现行转列
    </span><span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;/summary&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;param name="list"&gt;</span><span style="color: rgba(0, 128, 0, 1)">数据</span><span style="color: rgba(128, 128, 128, 1)">&lt;/param&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;param name="DimensionList"&gt;</span><span style="color: rgba(0, 128, 0, 1)">维度列</span><span style="color: rgba(128, 128, 128, 1)">&lt;/param&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;param name="DynamicColumn"&gt;</span><span style="color: rgba(0, 128, 0, 1)">动态列</span><span style="color: rgba(128, 128, 128, 1)">&lt;/param&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">///</span> <span style="color: rgba(128, 128, 128, 1)">&lt;returns&gt;</span><span style="color: rgba(0, 128, 0, 1)">行转列后数据</span><span style="color: rgba(128, 128, 128, 1)">&lt;/returns&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> DataTable RowToColumn(DataTable dt, List&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>&gt; DimensionList, <span style="color: rgba(0, 0, 255, 1)">string</span> DynamicColumn, <span style="color: rgba(0, 0, 255, 1)">out</span> List&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>&gt;<span style="color: rgba(0, 0, 0, 1)"> AllDynamicColumn)
    {
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">获取所有动态列</span>
        AllDynamicColumn = <span style="color: rgba(0, 0, 255, 1)">new</span> List&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>&gt;<span style="color: rgba(0, 0, 0, 1)">();
        </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (DataRow dr <span style="color: rgba(0, 0, 255, 1)">in</span> dt.DefaultView.ToTable(<span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">, DynamicColumn).Rows)
        {
            </span><span style="color: rgba(0, 0, 255, 1)">if</span> (dr[DynamicColumn] != <span style="color: rgba(0, 0, 255, 1)">null</span> &amp;&amp; !<span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">.IsNullOrEmpty(dr[DynamicColumn].ToString()))
            {
                AllDynamicColumn.Add(dr[DynamicColumn].ToString());
            }
        }

        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">数值列</span>
        Dictionary&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>, Type&gt; AllNumberColumn = <span style="color: rgba(0, 0, 255, 1)">new</span> Dictionary&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>, Type&gt;<span style="color: rgba(0, 0, 0, 1)">();
        </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (DataColumn item <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> dt.Columns)
        {
            </span><span style="color: rgba(0, 0, 255, 1)">if</span> (item.DataType == <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">int</span>) || item.DataType == <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">double</span>) || item.DataType == <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">float</span><span style="color: rgba(0, 0, 0, 1)">))
            {
                AllNumberColumn.Add(item.ColumnName, item.DataType);
            }
        }

        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">结果DataTable创建</span>
        DataTable dtResult = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> DataTable();
        </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> item <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> DimensionList)
        {
            dtResult.Columns.Add(item, </span><span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">));
        }
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">动态列</span>
        <span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> dynamicValue <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> AllDynamicColumn)
        {
            </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> item <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> AllNumberColumn.Keys)
            {
                dtResult.Columns.Add(item </span>+<span style="color: rgba(0, 0, 0, 1)"> dynamicValue, AllNumberColumn[item]);
            }
        }

        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">分组-优化性能</span>
        Dictionary&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>, List&lt;DataRow&gt;&gt; dict = <span style="color: rgba(0, 0, 255, 1)">new</span> Dictionary&lt;<span style="color: rgba(0, 0, 255, 1)">string</span>, List&lt;DataRow&gt;&gt;<span style="color: rgba(0, 0, 0, 1)">();
        List</span>&lt;DataRow&gt; drList = <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;
        </span><span style="color: rgba(0, 0, 255, 1)">string</span> groupKey = <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">;
        </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (DataRow dr <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> dt.Rows)
        {
            groupKey </span>= <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">;
            </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> item <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> DimensionList)
            {
                groupKey </span>+= dr[item] + <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">#</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
            }
            </span><span style="color: rgba(0, 0, 255, 1)">if</span> (!dict.TryGetValue(groupKey, <span style="color: rgba(0, 0, 255, 1)">out</span><span style="color: rgba(0, 0, 0, 1)"> drList))
            {
                drList </span>= <span style="color: rgba(0, 0, 255, 1)">new</span> List&lt;DataRow&gt;<span style="color: rgba(0, 0, 0, 1)">();
                dict[groupKey] </span>=<span style="color: rgba(0, 0, 0, 1)"> drList;
            }
            drList.Add(dr);
        }

        DataRow drReult </span>= <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;
        DataTable dtTemp </span>= <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;
        Dictionary</span>&lt;<span style="color: rgba(0, 0, 255, 1)">object</span>, DataTable&gt; dictTable = <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;
        </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> kv <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> dict)
        {
            drReult </span>=<span style="color: rgba(0, 0, 0, 1)"> dtResult.NewRow();
            </span><span style="color: rgba(0, 0, 255, 1)">var</span> arrKey = kv.Key.Split(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">#</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">);
            </span><span style="color: rgba(0, 0, 255, 1)">int</span> i = <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;
            </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> key <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> DimensionList)
            {
                drReult[key] </span>=<span style="color: rgba(0, 0, 0, 1)"> arrKey[i];
                i</span>++<span style="color: rgba(0, 0, 0, 1)">;
            }
            dictTable </span>= (<span style="color: rgba(0, 0, 255, 1)">from</span> p <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> kv.Value.AsEnumerable()
                         group p by p.Field</span>&lt;<span style="color: rgba(0, 0, 255, 1)">object</span>&gt;<span style="color: rgba(0, 0, 0, 1)">(DynamicColumn) into g
                         </span><span style="color: rgba(0, 0, 255, 1)">select</span> g).ToDictionary(e =&gt; e.Key, e =&gt;<span style="color: rgba(0, 0, 0, 1)"> e.CopyToDataTable());
            </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> dynamicValue <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> AllDynamicColumn)
            {
                </span><span style="color: rgba(0, 0, 255, 1)">if</span> (dictTable.TryGetValue(dynamicValue, <span style="color: rgba(0, 0, 255, 1)">out</span><span style="color: rgba(0, 0, 0, 1)"> dtTemp))
                {
                    </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> numColumn <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> AllNumberColumn.Keys)
                    {
                        drReult[numColumn </span>+ dynamicValue] = dtTemp.Compute(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">sum(</span><span style="color: rgba(128, 0, 0, 1)">"</span> + numColumn + <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">)</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">);
                    }
                }
                </span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">
                {
                    </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> numColumn <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> AllNumberColumn.Keys)
                    {
                        drReult[numColumn </span>+ dynamicValue] = <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;
                    }
                }
            }
            dtResult.Rows.Add(drReult);
        }
        </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> dtResult;
    }

    </span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span><span style="color: rgba(0, 0, 0, 1)"> DataTable InitTable()
    {
        DataTable dt </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> DataTable();
        dt.Columns.Add(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Name</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">));
        dt.Columns.Add(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Area</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">));
        dt.Columns.Add(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Month</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">));
        dt.Columns.Add(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">DfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">double</span><span style="color: rgba(0, 0, 0, 1)">));
        dt.Columns.Add(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">double</span><span style="color: rgba(0, 0, 0, 1)">));
        dt.Columns.Add(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">RqfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">double</span><span style="color: rgba(0, 0, 0, 1)">));

        DataRow row </span>=<span style="color: rgba(0, 0, 0, 1)"> dt.NewRow();
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Name</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">张三</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Month</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">2016-01</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Area</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">江夏区</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">DfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">240.9</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">30</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">RqfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">25</span><span style="color: rgba(0, 0, 0, 1)">;
        dt.Rows.Add(row);

        row </span>=<span style="color: rgba(0, 0, 0, 1)"> dt.NewRow();
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Name</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">张三</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Month</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">2016-02</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Area</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">江夏区</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">DfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">167</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">24.5</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">RqfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">17.9</span><span style="color: rgba(0, 0, 0, 1)">;
        dt.Rows.Add(row);

        row </span>=<span style="color: rgba(0, 0, 0, 1)"> dt.NewRow();
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Name</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">小燕子</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Month</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">2016-01</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Area</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">江夏区</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">DfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">340.9</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">RqfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">55</span><span style="color: rgba(0, 0, 0, 1)">;
        dt.Rows.Add(row);

        row </span>=<span style="color: rgba(0, 0, 0, 1)"> dt.NewRow();
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Name</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">小燕子</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Month</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">2016-02</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Area</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">江夏区</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">DfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">67</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">64.5</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">RqfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">77.9</span><span style="color: rgba(0, 0, 0, 1)">;
        dt.Rows.Add(row);

        row </span>=<span style="color: rgba(0, 0, 0, 1)"> dt.NewRow();
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Name</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">李四</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Month</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">2016-01</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Area</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">洪山区</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">DfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">56.7</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">24.7</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">RqfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">13.2</span><span style="color: rgba(0, 0, 0, 1)">;
        dt.Rows.Add(row);

        row </span>=<span style="color: rgba(0, 0, 0, 1)"> dt.NewRow();
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Name</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">李四</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Month</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">2016-02</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Area</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">洪山区</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">DfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">65.2</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">18.9</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">RqfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">14.9</span><span style="color: rgba(0, 0, 0, 1)">;
        dt.Rows.Add(row);

        row </span>=<span style="color: rgba(0, 0, 0, 1)"> dt.NewRow();
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Name</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">尔康</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Month</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">2016-01</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Area</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">洪山区</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">DfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">156.7</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">124.7</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">RqfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">33.2</span><span style="color: rgba(0, 0, 0, 1)">;
        dt.Rows.Add(row);

        row </span>=<span style="color: rgba(0, 0, 0, 1)"> dt.NewRow();
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Name</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">尔康</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Month</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">2016-02</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Area</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">洪山区</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">DfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">35.2</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">28.9</span><span style="color: rgba(0, 0, 0, 1)">;
        row[</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">RqfMoney</span><span style="color: rgba(128, 0, 0, 1)">"</span>] = <span style="color: rgba(128, 0, 128, 1)">44.9</span><span style="color: rgba(0, 0, 0, 1)">;
        dt.Rows.Add(row);
        </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> dt;
    }
}

}

 

回到顶部

总结

      本篇通过行转列引出了System.Linq.Dynamic，并且介绍了过滤功能，其实它的用处还有很多，等待大家发掘。下面给出本文示例代码：DynamicLinq

 　  我的博客即将搬运同步至腾讯云+社区，邀请大家一同入驻：https://cloud.tencent.com/developer/support-plan

如果，您认为阅读这篇博客让您有些收获，不妨点击一下右下角的【推荐】按钮。
如果，您希望更容易地发现我的新博客，不妨点击一下绿色通道的【关注我】。

如果，想给予我更多的鼓励，求打

因为，我的写作热情也离不开您的肯定支持。

感谢您的阅读，如果您对我的博客所讲述的内容有兴趣，请继续关注我的后续博客，我是焰尾迭 。