Pivot List of Object C# MVC Solution

Question

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:

prop1	prop2	prop3	prop4	prop5
AAA	1123	400	35%	$600
BBB	3444	23	45%	$235
CCC	5000	55	15%	$555
DDD	2500	264	70%	$243

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

prop1	AAA	BBB	CCC	DDD
prop2	1123	3444	5000	2500
prop3	400	23	55	264
prop4	35%	45%	15%	70%
prop5	$600	$235	$555	$243

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.

Take a look at this post for possible solution: https://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq — David Tansey, Sep 27 '22 at 01:04
If you ready to pay there are controls which can do that. For example [DevExpress ASP.NET MVC Pivot Grid](https://demos.devexpress.com/MVCxPivotGridDemos/DataProcessing/DataBindingToLargeDatabase). You just simply bind to plain `IQueryable` (without PIVOT) and grid takes care which queries to execute. — Svyatoslav Danyliv, Sep 27 '22 at 06:09
@Adam - Based on you example it looks like your attempting to do a matrix rotation common in data science tasks - without any grouping. Let me know if this is what you're looking for or if you want the first column in the pivot to be the header row. — , Sep 27 '22 at 19:26
Thank you everyone for your responses! @AdamCohen - I was looking to rotate the data (rows to columns essentially, no grouping). Or yes, the first column as headers. I was actually able to work with your pivot extension solution below to get the data displayed properly. Appreciate your input very much — Adam, Sep 27 '22 at 19:41

score 1 · Accepted Answer · 2022-09-27T20:32:21.530

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;
    }
}

Thank you! This got me very close to a final solution. – Adam Sep 27 '22 at 19:43 — Adam, Sep 27 '22 at 19:43

Pivot List of Object C# MVC Solution

1 Answers1