How to use Pivot table with Entity Framework?

Question

I have a table something like this:

I would like to use EF which will use a stored procedure which will return a unpivot result set back. But the problem is, how would I model it so that I can use it in RIA services to push the data to client. I was going for something like this

public class RegionModel {
    public string Name { get; set; }
    public List<string> Quarter { get; set; }
    public List<int> Sales { get; set; }
}

Same way there will be a QuarterModel as well. Based on the user selection, I can return the proper collection for binding. Currently we have solved this by creating dynamic class at the client side. But interested to know is it possible to achieve this without client side code and leverage the EF and SQL Server unpivot.

Answer 1

Answer :

The pivot feature enables transforming a collection of objects into a new collection of objects flattening the original hierarchy. In the following example The Pivot extension method is used to transform a collection as follow:

Using the folowing Link:

LINQ Extensions Library (Pivot Extensions)

Using the following line to pivot the data:

contacts.Pivot(X => X.Phones, X => X.PhoneType, X => string.Concat("(", X.AreaCode, ") ", X.PhoneNumber), true)