Pivoting POCO generic lists using reflection

by martin 7. November 2011 22:05
I recently had to pivot data that I had in a list of POCO objects.  I had to do this for a couple of different object types and it just felt wrong to have to recode something so generic over and over again.  So... I figured I'd write a little helper class that utilizes reflection and allows me to pivot on any list of POCO objects, and specify what I want to pivot on (X and Y columns) and what I should be summing.  This is all done using System.Collections.Generic and System.Reflection so it may not be the ideal solution from a performance perspective for huge datasets.  On the other hand, for most datasets and modern hardware it isn't a problem at all, and it is really flexible and very highly reuseable for cranking out reports quickly.
 
Code Snippet
  1. public class PivotResult
  2. {
  3.  
  4.     public String[] ColumnNames;
  5.     public String[] RowNames;
  6.     public float[,] ValuesTable;
  7.     public int Count;
  8. }
  9. public class ReflectionPivot
  10. {
  11.     public PivotResult Pivot(List<Object> ObjectList, String ColumnProperty, String RowProperty, String ValueProperty)
  12.     {
  13.         if (ObjectList == null)
  14.             return null;
  15.         if (ObjectList.Count == 0)
  16.             return null;
  17.         Object first = ObjectList[0];
  18.         Type type = first.GetType();
  19.         PropertyInfo[] properties = type.GetProperties();
  20.         PropertyInfo colPropInfo = null;
  21.         PropertyInfo rowPropInfo = null;
  22.         PropertyInfo valPropInfo = null;
  23.         foreach (PropertyInfo pInfo in properties)
  24.         {
  25.             if (pInfo.Name.Equals(ColumnProperty))
  26.                 colPropInfo = pInfo;
  27.             if (pInfo.Name.Equals(RowProperty))
  28.                 rowPropInfo = pInfo;
  29.             if (pInfo.Name.Equals(ValueProperty))
  30.                 valPropInfo = pInfo;
  31.         }
  32.         if (colPropInfo == null)
  33.         {
  34.             throw new ArgumentException("Column Property invalid.");
  35.         }
  36.         if (rowPropInfo == null)
  37.         {
  38.             throw new ArgumentException("Row Property invalid.");
  39.         }
  40.         if (valPropInfo == null)
  41.         {
  42.             if (!(ValueProperty.Equals("1")))
  43.             {
  44.                 throw new ArgumentException("Value Property invalid.");
  45.             }
  46.         }
  47.         List<String> lstColNames = new List<string>();
  48.         List<String> lstRowNames = new List<string>();
  49.         foreach (Object obj in ObjectList)
  50.         {
  51.             string strCol = colPropInfo.GetValue(obj, null).ToString();
  52.             string strRow = rowPropInfo.GetValue(obj, null).ToString();
  53.             if (!(lstColNames.Contains(strCol)))
  54.             {
  55.                 lstColNames.Add(strCol);
  56.             }
  57.             if (!(lstRowNames.Contains(strRow)))
  58.             {
  59.                 lstRowNames.Add(strRow);
  60.             }
  61.         }
  62.         PivotResult pr = new PivotResult();
  63.         pr.Count = 0;
  64.         pr.ValuesTable = new float[lstRowNames.Count, lstColNames.Count];
  65.         pr.RowNames = lstRowNames.ToArray();
  66.         pr.ColumnNames = lstColNames.ToArray();
  67.         foreach (Object obj in ObjectList)
  68.         {
  69.             float fVal = 0;
  70.             if (ValueProperty.Equals("1"))
  71.             {
  72.                 fVal = 1;
  73.             }
  74.             else
  75.             {
  76.                 fVal = (float)valPropInfo.GetValue(obj, null);
  77.             }
  78.             pr.Count++;
  79.             string strCol = colPropInfo.GetValue(obj, null).ToString();
  80.             string strRow = rowPropInfo.GetValue(obj, null).ToString();
  81.             int intColName = lstColNames.FindIndex(x => x == strCol);
  82.             int intRowName = lstRowNames.FindIndex(x => x == strRow);
  83.             pr.ValuesTable[intRowName, intColName] += fVal;
  84.         }
  85.         return pr;
  86.     }
  87. }
 
 
And you would use it something like this: 
 
ReflectionPivot reflectionPivot = new ReflectionPivot();
PivotResult pivotResult = reflectionPivot.Pivot(intermediate.ToList<Object>(), "Team", "Grade", "1"); 
PivotResult pivotResult2 = reflectionPivot.Pivot(lstSales, "Division", "State", "Sales"); 
 
And then you can simply pass the PivotResult instance to a MVC view and use it to display a graph or to just show in a table list like this:
 
Code Snippet
  1. <%@ Page Title="Sales Report" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<PivotResult>" %>

  3. <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
  4.  
  5.     <script src="<%: Url.Content("~/Scripts/visualize.jQuery.js") %>" type="text/javascript"></script>
  6.     <script type="text/javascript">
  7. //<![CDATA[
  8.             $(function () {
  9.                 $(".jqvisualizesimple").visualize();
  10.             });
  11.         });
  12. //]]>
  13.     </script>
  14.  
  15. <h2>Sales Report</h2>
  16.  
  17. <table class="jqvisualizesimple">
  18. <thead>
  19. <td>&nbsp;</td>
  20. <% foreach(var strCol in Model.ColumnNames) { %>
  21. <th scope="col"><%: strCol %></th>
  22. <% } %>
  23. </thead>
  24. <tbody>
  25.     <% for (int i = 0; i < Model.ValuesTable.GetLength(0); i++)
  26.        {
  27.            var strRow =  Model.RowNames[i]; %>
  28.            <tr><th scope="row"><%: strRow %></th>
  29.        <%
  30.            for (int j = 0; j < Model.ValuesTable.GetLength(1); j++)
  31.            { %>
  32.        <td><%:Model.ValuesTable[i, j]%></td>
  33.     <%     }
  34.        }%>
  35. </tr>
  36. </tbody>
  37. </table>
  38. </asp:Content>
  
 
I was really hoping to do this using pure LINQ, but it turned out to be the much more difficult route so I ended up backing off and using reflection instead.  If someone has seen something like this done in LINQ, please do send me a message or a comment and point towards that solution as I would very much enjoy seeing it and learning from it. 

Currently rated 1.5 by 284 people

  • Currently 1.517606/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags: ,

C#

E-mail
Permalink | Comments (0) | Post RSSRSS comment feed

Welcome

Please contact me if you have a great idea for a project and need technical expertise in designing, developing, or integrating a custom software solution.

Recent Comments

Comment RSS