Common sample code sharing for C#List to implement row to column conversion (picture)-C#.Net Tutorial-php.cn

Head of Household-Monthly Detailed Report

This article introduces System.Linq.Dynamic by converting rows to columns, and introduces the filtering function, which has a good reference value. Let's take a look at the following with the editor

The recent demand for report statistics involves row-to-column reports. Based on past experience, it can be done relatively easily using SQL. This time I decided to take the challenge of converting rows and columns directly through code. Several problems encountered during the period and new knowledge used are summarized and recorded here.

Reading Contents

Introduction to the problem
Dynamic Linq
##System.Linq.DynamicOther usage
Summary

Introduction to the problem

With family For example, monthly expenses can be grouped in any combination of the three dimensions [Name, Area, Month], and one of the three dimensions can be selected as a column display.

/// <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; }
 }

Copy after login

Name of Head of Household2016-01 2016-02 Electricity feeWater feeGas feeElectricity feeWater feeGas fee张三240.9302516724.517.9李四56.724.713.265.218.914.9

##Region-Monthly Detailed ReportHead of Household NameElectricity fee江xia区Hongshan District

2016-01	2016-02

2016-01	Water fee	Gas fee	Electricity fee	Water fee	Gas Fee
2240.9	330	425	5167	264.5	177.9
576.7	264.7	173.2	665.2	108.9	184.9

#area Month-Household Detail ReportMonthElectricity fee2016-012016-01##江xia区2016-023240.94305256167364.5277.9Hongshan District2016-02676.7364.7273.2765.2208.9284.9

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

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

{DimensionList:[&#39;Name&#39;],DynamicColumn:&#39;Month&#39;}

Copy after login

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

{DimensionList:[&#39;Area&#39;],DynamicColumn:&#39;Month&#39;}

Copy after login

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

{DimensionList:[&#39;Area&#39;,&#39;Month&#39;],DynamicColumn:&#39;Name&#39;}

Copy after login

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

动态Linq

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

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

/// <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;
 }

Copy after login

标红部分使用了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;
 }

Copy after login

/// <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(&#39;,&#39;))
  {
  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(&#39;,&#39;))
  {
  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;
 }

Copy after login

总结

The above is the detailed content of Common sample code sharing for C#List to implement row to column conversion (picture). For more information, please follow other related articles on the PHP Chinese website!

Related labels：

list

source：php.cn

Previous article：Introduction to C# basics-detailed explanation of constants Next article：C# creates example code sharing for reading and writing XML files

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

张三	李四				Gas fee
##Region
张三	李四	Water fee	Gas fee	Electricity fee	Water fee	江xia区
2240.9	330	425	5167	264.5	177.9	Hongshan District
576.7	264.7	173.2	665.2	108.9	184.9