In my controller I am returning a db query result (list of objects) defined by my model. I'm displaying this in the view. For example:

I would like to pivot this result to look like below:

What's the best way to achieve this? I had initially pivoted in the SQL query and had a model representing the bottom result , however it became difficult to manage as I had frequent changes and requests to add new fields calculated from other columns etc.

CodePudding user response：

See below for an example implementation extension method below that uses reflection to pivot an IEnumerable<T>.

Example Usage Pivot()

// Test Type
public record MyObj(string Prop1, int Prop2, int Prop3);

// test data
var list = new List<MyObj> {
        new MyObj("AAA", 1123, 400),
        new MyObj("BBB", 2123, 500),
        new MyObj("CCC", 3123, 600) 
    };

Console.Write(list.Pivot());

Example Usage Pivot() w/ ColumnSelector

Example 1

Console.Write(list.Pivot( pivotOn: (f)=>f.Prop1));

Example 2

Console.Write(list.Pivot( pivotOn: (f)=>f.Prop2));

Implementation

public static class PivotExtension
{
    public static IEnumerable<ExpandoObject> Pivot<T>(this IEnumerable<T> objs)
    {
        var objArray = objs.ToArray();
        var properties = typeof(T).GetProperties();
        foreach (var property in properties)
        {
            var obj = new ExpandoObject();
            var objDic = (IDictionary<string, object>)obj;
            objDic.Add("Name", property.Name);
            for (int i = 1; i < objs.Count(); i  )
                objDic.Add($"Row_{i.ToString()}", property.GetValue(objArray[i]));
                
            yield return obj;
        }
    }
    public static IEnumerable<ExpandoObject> Pivot<T, TValue>(this IEnumerable<T> objs, Expression<Func<T, TValue>> pivotOn)
    {
        var objArray = objs.ToList();
        var properties = typeof(T).GetProperties();
        var pivotProperty = pivotOn.GetProperty();
        
        //header
        var cnt = 1;
        var pivotHeader = new Dictionary<int, string>{{0, pivotProperty.Name}};
        objArray.ForEach(r=> pivotHeader.Add( cnt  , pivotProperty.GetValue(r).ToString()));
        
        //rotate
        foreach (var property in properties.Where(r=> !pivotHeader.ContainsValue(r.Name)))
        {
            var obj = new ExpandoObject();
            var objDic = (IDictionary<string, object>)obj;
            objDic.Add(pivotHeader[0], property.Name);
            for (int i = 0; i < objs.Count(); i  )
                objDic.Add($"{pivotHeader[i 1]}", property.GetValue(objArray[i]));

            yield return obj;
        }
    }
    
    public static PropertyInfo GetProperty<T, TValue>(this Expression<Func<T, TValue>> expression)
    {
        return GetProperty(expression.Body);
    }
    
    private static PropertyInfo GetProperty(Expression body)
    {
        MemberExpression memberExpression;
        switch (body.NodeType)
        {
            case ExpressionType.Convert:
                memberExpression = (MemberExpression)((UnaryExpression)body).Operand;
                break;
            case ExpressionType.MemberAccess:
                memberExpression = (MemberExpression)body;
                break;
            default:
                throw new ArgumentOutOfRangeException();
        }

        return (PropertyInfo)memberExpression.Member;
    }
}