Recent demand for reporting statistics involves row-to-row reporting. Based on past experience, using SQL can be relatively easy to accomplish, this decision challenges the direct completion of line-to-column through code. During this period, I encountered several problems and used new knowledge. Here, I'll sort out and record them.
Reading catalogue
Introduction to problems
For example, family monthly expenses can be grouped randomly in the three dimensions of [Name,Area,Month], and one of the three dimensions can be selected as a column display.
/// <summary> /// Family expenses /// </summary> public class House { /// <summary> /// name of householder /// </summary> public string Name { get; set; } /// <summary> /// Administrative Regions Belonging to /// </summary> public string Area { get; set; } /// <summary> /// Month /// </summary> public string Month { get; set; } /// <summary> /// Electricity bill /// </summary> public double DfMoney { get; set; } /// <summary> /// Amount of water charges /// </summary> public double SfMoney { get; set; } /// <summary> /// Amount of gas /// </summary> public double RqfMoney { get; set; } }
| Household Head-Monthly Detailed Statement | ||||||
| name of householder | 2016-01 | 2016-02 | ||||
|---|---|---|---|---|---|---|
| Electricity fees | Charge for water | Gas fee | Electricity fees | Charge for water | Gas fee | |
| Zhang San | 240.9 | 30 | 25 | 167 | 24.5 | 17.9 |
| Li Si | 56.7 | 24.7 | 13.2 | 65.2 | 18.9 | 14.9 |
| Regional-Monthly Detailed Statement | ||||||
| name of householder | 2016-01 | 2016-02 | ||||
|---|---|---|---|---|---|---|
| Electricity fees | Charge for water | Gas fee | Electricity fees | Charge for water | Gas fee | |
| jiangxia district | 2240.9 | 330 | 425 | 5167 | 264.5 | 177.9 |
| Hongshan District | 576.7 | 264.7 | 173.2 | 665.2 | 108.9 | 184.9 |
| Regional Monthly-Household Detailed Statement | |||||||
| region | Month | Zhang San | Li Si | ||||
|---|---|---|---|---|---|---|---|
| Gas fee | Electricity fees | Charge for water | Gas fee | Electricity fees | Charge for water | ||
| jiangxia district | 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 district | 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 |
The data found in the background is List < House > type, and the grouping dimension and dynamic column fields are passed from the foreground.
Form 1 Front Desk to Background Parameters
{DimensionList:['Name'],DynamicColumn:'Month'}
Form 2 Front Desk to Background Parameters
{DimensionList:['Area'],DynamicColumn:'Month'}
Form 3 Front to Background Parameters
{DimensionList:['Area','Month'],DynamicColumn:'Name'}
After a clear description of the problem, after careful analysis, you will find that the problem here is dynamic grouping, that is, how to group lists according to the multiple dimensions passed from the front desk.
Dynamic Linq
Next, use System.Linq.Dynamic to complete the row-column function, and Nuget can search System.Linq.Dynamic to download the package.
The code is encapsulated and the general line-to-column function of List<T> is realized.
/// <summary> /// dynamic Linq Way to achieve row-to-column conversion /// </summary> /// <param name="list">data</param> /// <param name="DimensionList">Dimension column</param> /// <param name="DynamicColumn">Dynamic column</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 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; } //Get all attributes of an entity Dictionary<string, PropertyInfo> PropertyInfoDict = new Dictionary<string, PropertyInfo>(); Type type = typeof(T); var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public); //Numerical 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); } } //Grouping 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(); //Dimensional 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 System.Linq.Dynamic dynamic grouping function is used in the label red part, which can be grouped by passing in strings. Use the dynamic type, about the introduction of dynamic can refer to other articles.
Other uses of System.Linq.Dynamic
The line-to-column code above shows the power of System.Linq.Dynamic. Here's how it will be used in development.
Where filtering
list.Where("Name=@0", "Zhang San")
Parametric queries are used to find the data whose name is Zhang San. You may not feel the benefits of this code. But with the Entity Framework, we can realize the function of splicing SQL dynamically.
/// <summary> /// EF Entity query encapsulation /// </summary> /// <typeparam name="T">Entity type</typeparam> /// <param name="Query">IQueryable object</param> /// <param name="gridParam">Filter condition</param> /// <returns>Query results</returns> public static EFPaginationResult<T> PageQuery<T>(this IQueryable<T> Query, QueryCondition gridParam) { //query criteria 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 : gridParam.PageIndex; //Getting sort information 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> /// By querying conditions,Getting parameterized queries SQL /// </summary> /// <param name="gridParam">Filter condition</param> /// <returns>Filter conditional characters</returns> private static EFFilter GetParameterSQL<T>(QueryCondition gridParam) { EFFilter result = new EFFilter(); //Set of parameter values List<object> listArgs = new List<object>(); string filter = "1=1"; #region "Processing Dynamic Filtration Conditions" if (gridParam.FilterList != null && gridParam.FilterList.Count > 0) { StringBuilder sb = new StringBuilder(); int paramCount = 0; DateTime dateTime; //Operator string strOperator = string.Empty; foreach (var item in gridParam.FilterList) { //If the field name is empty, skip if (string.IsNullOrEmpty(item.FieldName)) { continue; } //Match enumeration to prevent SQL injection Operator operatorEnum = (Operator)Enum.Parse(typeof(Operator), item.Operator, true); //Skipping fields with null values if (operatorEnum != Operator.Null && operatorEnum != Operator.NotNull && string.IsNullOrEmpty(item.FieldValue)) { continue; } strOperator = operatorEnum.GetDescription(); if (item.IgnoreCase && !item.IsDateTime) { //2016-07-19 Ignore case comparison when adding queries item.FieldValue = item.FieldValue.ToLower(); item.FieldName = string.Format("{0}.ToLower()", item.FieldName); } switch (operatorEnum) { //Be equal to,Not equal to, less than, greater than, less than or equal to, greater than or equal to 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; }
summary
This article introduces System.Linq.Dynamic through row and column, and introduces the function of filtering. In fact, there are many uses for it, waiting for you to discover. The following is an example code for this article: DynamicLinq
If you think reading this blog will give you some benefit, click on the bottom right corner. [recommendation] Button.
If you want to find my new blog more easily, click on the Green Channel [Pay attention to me].
Because, my enthusiasm for writing is also inseparable from your affirmative support.
Thank you for your reading. If you are interested in what my blog tells you, please continue to pay attention to my follow-up blog. I am Yantaidui.