SqlSugar列转行扩展方法

参考

C# 行列互转 List、DataTable和匿名对象 行转列
SQL Server 列转行的实现
MemberExpression 类
MemberInfo 类

代码

点击查看代码-扩展方法

 public static class UnPivotQueryMethod
    {
        public static List<TRow> ToUnPivotList<T, TRow, TCol, TColName, TData>(
            this ISugarQueryable<T> queryable,
            Func<T, TRow> rowSelector,
            Expression<Func<TRow, TData>> dataSelector,
            Expression<Func<TRow, TColName>> colNameSelector,
            Expression<Func<T, TCol>> colSelector)
        {
            var dataList = queryable.ToList();
            var resList = new List<TRow>();

            var tDataName = string.Empty;
            if(dataSelector.Body is MemberExpression)
            {
                tDataName = ((MemberExpression)dataSelector.Body).Member.Name;
            }
            else
            {
                throw new Exception("dataSelector表达式错误，需要dataSelector.Body is MemberExpression判断成立");
            }
            var colLabelName = string.Empty;
            if (colNameSelector.Body is MemberExpression)
            {
                var colLabelInfo = ((MemberExpression)colNameSelector.Body).Member;

                var colLabelPType = (PropertyInfo)colLabelInfo;
                if (colLabelPType.PropertyType != typeof(string))
                {
                    throw new Exception("colNameSelector表达式错误，类型必须是string");
                }

                colLabelName = colLabelInfo.Name;
            }
            else
            {
                throw new Exception("colNameSelector表达式错误，需要colNameSelector.Body is MemberExpression判断成立");
            }




            var colNames = new List<string>();
            if (colSelector.Body is MemberExpression)
            {
                colNames.Add(((MemberExpression)colSelector.Body).Member.Name);
            }
            else
            {
                colNames.AddRange(((NewExpression)colSelector.Body).Arguments.Select(it => it as MemberExpression).Select(it => it.Member.Name));
            }

            var tType = typeof(T);
            var tProperties = tType.GetProperties();
            tProperties = tProperties.Where(x => colNames.Contains(x.Name)).ToArray();

            var rowType = typeof(TRow);
            var tDataPropert = rowType.GetProperty(tDataName);
            var colLabePropert = rowType.GetProperty(colLabelName);


            foreach (var data in dataList)
            {
                foreach (var tProperty in tProperties)
                {
                    var row = rowSelector(data);
                    tDataPropert.SetValue(row, tProperty.GetValue(data));
                    colLabePropert.SetValue(row, tProperty.Name);
                    resList.Add(row);
                }
            }


            return resList;
        }

        public static List<TRow> ToUnPivotList<T, TRow, TCol, TColName, TData>(
            this ISugarQueryable<T> queryable,
            Func<T, TRow> rowSelector,
            Expression<Func<TRow, TData>> dataSelector,
            Expression<Func<T, TCol>> colSelector)
        {
            var dataList = queryable.ToList();
            var resList = new List<TRow>();

            var tDataName = string.Empty;
            if (dataSelector.Body is MemberExpression)
            {
                tDataName = ((MemberExpression)dataSelector.Body).Member.Name;
            }
            else
            {
                throw new Exception("dataSelector表达式错误，需要dataSelector.Body is MemberExpression判断成立");
            }
            




            var colNames = new List<string>();
            if (colSelector.Body is MemberExpression)
            {
                colNames.Add(((MemberExpression)colSelector.Body).Member.Name);
            }
            else
            {
                colNames.AddRange(((NewExpression)colSelector.Body).Arguments.Select(it => it as MemberExpression).Select(it => it.Member.Name));
            }

            var tType = typeof(T);
            var tProperties = tType.GetProperties();
            tProperties = tProperties.Where(x => colNames.Contains(x.Name)).ToArray();

            var rowType = typeof(TRow);
            var tDataPropert = rowType.GetProperty(tDataName);
            

            foreach (var data in dataList)
            {
                foreach (var tProperty in tProperties)
                {
                    var row = rowSelector(data);
                    tDataPropert.SetValue(row, tProperty.GetValue(data));
                    
                    resList.Add(row);
                }
            }


            return resList;
        }
    }

点击查看代码-使用方式

var resList = sql.ToUnPivotList(
                (it) => new DeptAmountData()
                {
                    salerId = it.F_PAEZ_XSY01,
                    dateStrYear = it.F_BGP_ND,
                    deptId = it.deptId,
                    salerName = it.salerName,
                    salerStaffId = it.salerStaffId
                }
                , itr => itr.amount
                , itr => itr.amountLabel
                , it => new
                {
                    it.F_PAEZ_MBZ_01,
                    it.F_PAEZ_MBZ_02,
                    it.F_PAEZ_MBZ_03,
                    it.F_PAEZ_MBZ_04,
                    it.F_PAEZ_MBZ_05,
                    it.F_PAEZ_MBZ_06,
                    it.F_PAEZ_MBZ_07,
                    it.F_PAEZ_MBZ_08,
                    it.F_PAEZ_MBZ_09,
                    it.F_PAEZ_MBZ_10,
                    it.F_PAEZ_MBZ_11,
                    it.F_PAEZ_MBZ_12
                });