C#List实现行转列的通用示例代码分享（图）【C#.Net教程】,list,行转列，C#

本篇经由过程行转列引出了System.Linq.Dynamic，而且引见了过滤功用，具有很好的参考价值。下面随着小编一起来看下吧

近来在做报表统计方面的需求，涉及到行转列报表。依据以往履历运用SQL能够比较轻易完成，此次决议应战一下直接经由过程代码体式格局完成行转列。时期碰到几个题目和用到的新知识这里整顿纪录一下。

浏览目次

• 题目引见

• 动态Linq

• System.Linq.Dynamic别的用法

• 总结

题目引见

以家庭月度用度为例，能够在[Name,Area,Month]三个维度上随便组合举行分组，三个维度中挑选一个做为列显现。

/// <summary>
 /// 家庭用度状况
 /// </summary>
 public class House
 {
 /// <summary>
 /// 户主姓名
 /// </summary>
 public string Name { get; set; }
 /// <summary>
 /// 所属行政地区
 /// </summary>
 public string Area { get; set; }
 /// <summary>
 /// 月份
 /// </summary>
 public string Month { get; set; }
 /// <summary>
 /// 电费金额
 /// </summary>
 public double DfMoney { get; set; }
 /// <summary>
 /// 水脚金额
 /// </summary>
 public double SfMoney { get; set; }
 /// <summary>
 /// 燃气金额
 /// </summary>
 public double RqfMoney { get; set; }
 }

户主-月明细报表
户主姓名	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;
 }
 //猎取实体一切属性
 Dictionary<string, PropertyInfo> PropertyInfoDict = new Dictionary<string, PropertyInfo>();
 Type type = typeof(T);
 var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
 //数值列
 List<string> AllNumberField = new List<string>();
 foreach (var item in propertyInfos)
 {
 PropertyInfoDict[item.Name] = item;
 if (item.PropertyType == typeof(int) || item.PropertyType == typeof(double) || item.PropertyType == typeof(float))
 {
  AllNumberField.Add(item.Name);
 }
 }
 //分组
 var dataGroup = list.GroupBy(string.Format("new ({0})", 
 string.Join(",", DimensionList)), "new(it as Vm)") as IEnumerable<IGrouping<dynamic, dynamic>>;
 List<dynamic> listResult = new List<dynamic>();
 IDictionary<string, object> itemObj = null;
 T vm2 = default(T);
 foreach (var group in dataGroup)
 {
 itemObj = new ExpandoObject();
 var listVm = group.Select(e => e.Vm as T).ToList();
 //维度列赋值
 vm2 = listVm.FirstOrDefault();
 foreach (var key in DimensionList)
 {
  itemObj[key] = PropertyInfoDict[key].GetValue(vm2);
 }
 foreach (var column in AllColumnList)
 {
  vm2 = listVm.FirstOrDefault(dictFunc[column]);
  if (vm2 != null)
  {
  foreach (string name in AllNumberField)
  {
  itemObj[name + column] = PropertyInfoDict[name].GetValue(vm2);
  }
  }
 }
 listResult.Add(itemObj);
 }
 return listResult;
 }

标红部份运用了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";
 #region "处置惩罚动态过滤前提"
 if (gridParam.FilterList != null && gridParam.FilterList.Count > 0)
 {
 StringBuilder sb = new StringBuilder();
 int paramCount = 0;
 DateTime dateTime;
 //操作符
 string strOperator = string.Empty;
 foreach (var item in gridParam.FilterList)
 {
  //字段名称为空则跳过
  if (string.IsNullOrEmpty(item.FieldName))
  {
  continue;
  }
  //婚配罗列，防备SQL注入
  Operator operatorEnum = (Operator)Enum.Parse(typeof(Operator), item.Operator, true);

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

总结

以上就是C#List完成行转列的通用示例代码分享（图）的细致内容，更多请关注ki4网别的相干文章！