Dynamically Pivot in LINQ: Pivoted Columns Determined at Runtime

Question

Let's say I have three tables. Basically, one table of things, one table describing the possible attributes of the things, and a bridge table providing the values for those attributes for a particular thing. This lets you do dynamic metadata.

Users can add metadata properties at any time, and then they can provide values for those properties for any thing in the "things" table.

So like this:

Table: Persons
PersonID | FirstName | LastName 
1        | Bob       | Jones
2        | Fred      | Smith
3        | Sally     | Doe

Table: Properties
PropertyID | Name
1          | SupervisorName
2          | Age
3          | Birthday
4          | EmployeeNumber
5          | Hometown

Table: PropertyValues
PersonID | PropertyID | PropertyValue
1        | 1          | Frank Grimes
1        | 2          | 47
2        | 2          | 35
2        | 4          | 1983738
2        | 3          | 5/5/1978
3        | 3          | 4/4/1937
3        | 5          | Chicago, IL

So, users want to be able to view a report of these properties. Maybe I want to see a table containing the age and birthday of all employees. There would be blanks in the table if those values aren't populated for those users. Then maybe I want to see a report that includes supervisor, age and birthday--I should be able to generate that table on the fly as well.

Now, in order to do this with SQL, I would dynamically construct a query and add a join to that query for each property that I want to pivot up to the top. That's how it works now.

If I wanted to do this in LINQ, and I knew which properties to pivot while writing the code, I could do that too--I'd just use GroupJoin().

What I don't know is how to dynamically construct a LINQ query that would allow me to pivot any number of properties at runtime, without knowing what they are ahead of time.

Any ideas?

(Before you knee-jerk mark this as a duplicate, know that I did a fair amount of StackOverflow research before posting this question, and if this exact question has been asked before, I couldn't find it.)

Answer 1

You can build linq expression trees dynamically. This topic is covered (including example) in the following MSDN article: http://msdn.microsoft.com/en-us/library/bb882637.aspx

My suggestion would be to write an example Linq query for your task and rebuild it programmatically with expression trees. Once it works you adapt it and inject your dynamic parts.

Answer 2

My thought is you could join with a where condition as follows:

Person Class

public class Person
{
    public int Id {get; set;}
    public string FirstName {get; set;}
    public string LastName {get; set;}
}

Property Class

public class Property
{
    public int Id {get; set;}
    public string Name {get; set;}
}

Value Class

public class Value
{
    public int PersonId {get; set;} 
    public int PropertyId {get; set;}   
    public string Val {get; set;}
}

Code

void Main()
{
    var selectBy = "Birthday";

    var persons = new List<Person>() { new Person {Id = 1, FirstName = "Bob", LastName = "Jones"}, new Person {Id = 2, FirstName = "Fred", LastName = "Smith"}, new Person {Id = 3,FirstName = "Sally", LastName = "Doe"}};
    var properties = new List<Property>() { new Property {Id = 1, Name = "SupervisorName"}, new Property {Id = 2, Name = "Age"}, new Property {Id = 3, Name = "Birthday"}, new Property {Id = 4, Name = "EmployeeNumber"}, new Property {Id = 5, Name = "Hometown"}};
    var values = new List<Value>() { new Value {PersonId = 1, PropertyId = 1, Val="Frank Grimes"}, new Value {PersonId = 1, PropertyId = 2, Val="47"}, new Value {PersonId = 2, PropertyId = 2, Val="35"}, new Value {PersonId = 2, PropertyId = 4, Val="1983738"}, new Value {PersonId = 2, PropertyId = 3, Val="5/5/1978"}, new Value {PersonId = 3, PropertyId = 3, Val="4/4/1937"}, new Value {PersonId = 3, PropertyId = 5, Val="Chicago, IL"}};


    var result = from v in values
                join p in persons on v.PersonId equals p.Id
                join p2 in properties on v.PropertyId equals p2.Id
                where p2.Name.Equals(selectBy)
                select new { Name = p.FirstName + " " + p.LastName,
                            Value = v.Val
                            };

    result.Dump();
}

Results

Name, Value

Fred Smith 5/5/1978
Sally Doe 4/4/1937

Revised Answer

void Main()
{
    var selectBy = "Birthday";

    var persons = new List<Person>() { new Person {Id = 1, FirstName = "Bob", LastName = "Jones"}, new Person {Id = 2, FirstName = "Fred", LastName = "Smith"}, new Person {Id = 3,FirstName = "Sally", LastName = "Doe"}};
    var properties = new List<Property>() { new Property {Id = 1, Name = "SupervisorName"}, new Property {Id = 2, Name = "Age"}, new Property {Id = 3, Name = "Birthday"}, new Property {Id = 4, Name = "EmployeeNumber"}, new Property {Id = 5, Name = "Hometown"}};
    var values = new List<Value>() { new Value {PersonId = 1, PropertyId = 1, Val="Frank Grimes"}, new Value {PersonId = 1, PropertyId = 2, Val="47"}, new Value {PersonId = 2, PropertyId = 2, Val="35"}, new Value {PersonId = 2, PropertyId = 4, Val="1983738"}, new Value {PersonId = 2, PropertyId = 3, Val="5/5/1978"}, new Value {PersonId = 3, PropertyId = 3, Val="4/4/1937"}, new Value {PersonId = 3, PropertyId = 5, Val="Chicago, IL"}};


    // Default Values for the Cartesian Product
    var defaultValues = new string[]{"","","","",""};

    // propertyKeys are used to filter values generated for pivot table
    var propertyKeys = new List<Property> { new Property{Id=1}, new Property{Id=2}, new Property{Id=3}};

    // Generate default values for every person and each property
    var cartesianProduct = from ppl in persons
                            from prop in properties
                            join pk in propertyKeys on prop.Id equals pk.Id
                            select new {PersonId = ppl.Id, PropertyId = prop.Id, Val = defaultValues[prop.Id-1]};

    // Create Pivot Values based on selected PropertyIds
    var newValues = from cp in cartesianProduct 
                    join v in values on new {cp.PersonId, cp.PropertyId} equals new { v.PersonId, v.PropertyId } into gj
                    from x in gj.DefaultIfEmpty()
                    select new {
                        PersonId = (x == null ? cp.PersonId : x.PersonId),
                        PropertyId = (x == null ? cp.PropertyId: x.PropertyId),
                        Val = ( x == null ? cp.Val : x.Val )
                    };


    foreach( var y in newValues )
    {
        var aPerson = persons.Where( r=> r.Id == y.PersonId ).First().FirstName;
        var aProperty = properties.Where( r=> r.Id == y.PropertyId ).First().Name;
        Console.WriteLine(string.Format("{0:12}          {1:12}           {2:12}", aPerson, aProperty, y.Val));
    }
}

Results:

Bob       |   SupervisorName  |   Frank Grimes
Bob       |   Age             |   47
Bob       |   Birthday        |   
Fred      |   SupervisorName  |         
Fred      |   Age             |   35
Fred      |   Birthday        |   5/5/1978
Sally     |   SupervisorName  |         
Sally     |   Age             |
Sally     |   Birthday        |   4/4/1937