本篇通过行转列引出了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-012016-02
电费水费燃气费电费水费燃气费
张三 240.9 30 25 167 24.5 17.9
李四 56.7 24.7 13.2 65.2 18.9 14.9
区域-月明细报表
户主姓名2016-012016-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实现行转列的通用示例代码分享（图）的详细内容。