C#list common sample code sharing for row-to-column implementations

This article has introduced the System.Linq.Dynamic through row to column, and introduces the filter function, has the very good reference value. Let's take a look at the little series.

Recently in the report statistics requirements, involving row to column report. Using SQL based on previous experience is easier to accomplish, and this decision challenges you to complete the row-to-column directly through code. During the period encountered several problems and the use of new knowledge here to collate records.

Read Catalogue

• Problem description

• Dynamic LINQ

• System.Linq.Dynamic Other uses

• Summarize

Problem description

Take the family monthly fee as an example, you can group at random in [Name,area,month] three dimensions, select one of the three dimensions as the column display.

///<summary>//Family fee///</summary> public class House {///<summary>// /home name////</summary> public string name {get; set;}///<summary>//Administrative///</summary> Public St Ring area {get; set;}//<summary>//month///</summary> public string Month {get; set;}//<summary& Gt  Electricity amount//</summary> public double Dfmoney {get; set;}//<summary>////</summary> Public Double Sfmoney {get; set;}///<summary>//Gas amount///</summary> public double Rqfmoney {get; set;}} 

Water Water

Head of household-monthly detail report
Name of Householder	2016-01			2016-02
	Electricity	Charges	Gas Charges	Electricity	Charges	Gas Charges
Tom	240.9	30	25	167	24.5	17.9
John doe	56.7	24.7	13.2	65.2	18.9	14.9

Water Water

Regional-monthly Detail report
Name of Householder	2016-01			2016-02
	Electricity	Charges	Gas Charges	Electricity	Charges	Gas Charges
Jiangxia	2240.9	330	425	5167	264.5	177.9
Hongshan district	576.7	264.7	173.2	665.2	108.9	184.9

Water Water

Regional month-Account Detail report
Area	Month	Zhang San			John Doe
		Gas Charges	Electricity	Charges	Gas Charges	Electricity	Charges
Jiangxia	2016-01	2240.9	330	425	5167	264.5	177.9
Hongshan district	2016-01	576.7	264.7	173.2	665.2	108.9	184.9
Jiangxia	2016-02	3240.9	430	525	6167	364.5	277.9
Hongshan district	2016-02	676.7	364.7	273.2	765.2	208.9	284.9

Now the data behind the background is list

1th table foreground pass to background parameters

{dimensionlist:[' Name '],dynamiccolumn: ' Month '}

2nd table foreground pass to background parameters

{dimensionlist:[' area '],dynamiccolumn: ' Month '}

3rd table foreground pass to background parameters

{dimensionlist:[' area ', ' Month '],dynamiccolumn: ' Name '}

After careful analysis of the problem, you will find that the problem here is dynamic grouping, that is, how to group the list according to the multiple dimensions that the foreground passes over.

Dynamic LINQ

Using System.Linq.Dynamic to complete the row-to-column function below, NuGet searches for System.Linq.Dynamic to download the package.

The code is encapsulated to achieve a common list<t> row-to-column function.

<summary>///Dynamic LINQ method for row to column///</summary>/<param name= "list" > Data </param>//<param NA Me= "Dimensionlist" > Dimension columns </param>//<param name= "Dynamiccolumn" > Dynamic columns </param>//<returns> Row-to-column data </returns> private static list<dynamic> dynamiclinq<t> (list<t> List, list<string > Dimensionlist, String dynamiccolumn, out list<string> alldynamiccolumn) where T:class {//Get all dynamic columns var column Group = 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; }//Get entity All Properties dictionary<string, propertyinfo> propertyinfodict = new dictionary<string, propertyinfo> (); Type type = typeof (T); var Propertyinfos = type. GetProperties (BindingFlags.Instance | BindingFlags.Public); Value column 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); }}//group 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 (); Dimension column Assignment 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; }

The red section uses the System.Linq.Dynamic dynamic grouping feature, which can be grouped by passing in a string. Using the dynamic type, the introduction to the dynamic can refer to other articles.

System.Linq.Dynamic Other uses

Top row to column code to see the strength of the System.Linq.Dynamic, the following will be introduced in the development of the method used.

where filter

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

The above uses the parameterized query, realizes the search name is Zhang San's data, through this code you may not feel its benefit. However, combined with entityframework, we can realize the function of dynamic splicing of SQL.

<summary>//EF Entity Query Package///</summary>//<typeparam name= "T" > Entity type </typeparam>///<param Name= "Query" >iqueryable object </param>///<param Name= "Gridparam" > Filter conditions </param>//<returns> Query results </returns> public static efpaginationresult<t> pagequery<t> (this iqueryable<t> query, Querycondition gridparam) {//Query condition Effilter filter = getparametersql<t> (gridparam); var query = Query.where (filter. filter, filter. Listargs.toarray ()); Query results efpaginationresult<t> result = new efpaginationresult<t> (); if (gridparam.ispagination) {int PageSize = gridparam.pagesize; int PageIndex = Gridparam.pageindex < 0? 0:gridpara M.pageindex; Gets the sort information string sort = Getsort (Gridparam, typeof (T). FullName); Result. Data = query. (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>////Search by query conditions, get parameterized Query SQL///</summary>//<param name= "Gridparam" > Filter conditions </param>// <returns> Filter Conditional characters </returns> private static Effilter getparametersql<t> (Querycondition gridparam) { Effilter result = new Effilter (); Parameter Value collection List<object> Listargs = new list<object> (); String filter = "1=1";  #region "Handling Dynamic filter Conditions" if (gridparam.filterlist! = null && gridParam.FilterList.Count > 0) {StringBuilder sb = new StringBuilder (); int paramcount = 0; datetime datetime; The operator string stroperator = string. Empty; foreach (var item in gridparam.filterlist) {//Field name is empty then skip if (string. IsNullOrEmpty (item.  FieldName)) {continue; }//Match enumeration to prevent SQL injection Operator Operatorenum = (Operator) enum.parse (typeof (Operator), item.  Operator, True); Skips the field value to NULL if (operatorenum! = Operator.null && operatorenum! = Operator.notnull && string. IsNullOrEmpty (item.  Fieldvalue)) {continue; } Stroperator = Operatorenum.getdescription (); if (item. IgnoreCase &&!item. Isdatetime) {//2016-07-19 When adding a query ignores case comparison item. Fieldvalue = Item.  Fieldvalue.tolower (); Item. FieldName = string. Format ("{0}". ToLower () ", item.  FieldName); } switch (Operatorenum) {//equals, not equal, less than, greater than, less than equals, greater than or equal to case Operator.EQ:case Operator.NE:case Operator.GT:case Opera Tor. 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; }

Summary