Grouping Linq and Converting columns to rows

Question

I have a list that will currently return something like this. the Att column can be anything because a user can enter in a Att and Value at anytime.

var attr_vals = (from mpav in _db.MemberProductAttributeValues
                             select mpav);

Results

Id        Att        Value
1         Color      Blue
1         Size       30
1         Special    Slim
3         Color      Blue
4         Size       30
2         Special    Slim
2         Random     Foo Foo

The conversion I am looking for would be similar to this

Converted results

Id    Color    Size    Special    Random
1     Blue     30      Slim       null
2     null     null    null       Foo Foo
3     Blue     null    null       null
4     null     52      null       null

Class looks like this so far.

public class MyMappProducts
{
    public int? id { get; set; }
    public Dictionary<string, string> Attributes { get; set; }

    string GetAttribute(string aName)
    {
        return Attributes[aName];
    }

    void setAttribute(string aName, string aValue)
    {
        Attributes[aName] = aValue;
    }
}

what you are doing is a little different - but this will help - https://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq — Ctznkane525, Jul 02 '18 at 14:14
And you want the missing attributes to have null? They have to also be in the dictionary? — Haytam, Jul 02 '18 at 14:20
@haytam I just put null because they will be blank since there is no value. It does not need to be a dictionary this was just my poor attempt at trying to figure out where to start. — Alex D, Jul 02 '18 at 14:21

score 4 · Accepted Answer · answered Jul 02 '18 at 14:27

So giving that your list of attributes might change, creating a class with each attribute as a property would not be good as you'll have to know all the attributes before hand, thus working with a dictionary is easier.
Here's a way of doing what you want (Note that the missing attributes of each row aren't present in the dictionary):

var list = new List<AttributeValue>
{
    new AttributeValue(1, "Color", "Blue"),
    new AttributeValue(1, "Size", "30"),
    new AttributeValue(1, "Special", "Slim"),
    new AttributeValue(3, "Color", "Blue"),
    new AttributeValue(4, "Size", "30"),
    new AttributeValue(2, "Special", "Slim"),
    new AttributeValue(2, "Random", "Foo Foo")
};

// First we groupby the id and then for each group (which is essentialy a row now)
// we'll create a new MyMappProducts containing the id and its attributes
var result = list.GroupBy(av => av.Id)
                    .Select(g => new MyMappProducts
                    {
                        id = g.Key,
                        Attributes = g.ToDictionary(av => av.Attribute, av => av.Value)
                    })
                    .ToList();

This results in (pretty printed):

[
  {
    "id": 1,
    "Attributes": {
      "Color": "Blue",
      "Size": "30",
      "Special": "Slim"
    }
  },
  {
    "id": 3,
    "Attributes": {
      "Color": "Blue"
    }
  },
  {
    "id": 4,
    "Attributes": {
      "Size": "30"
    }
  },
  {
    "id": 2,
    "Attributes": {
      "Special": "Slim",
      "Random": "Foo Foo"
    }
  }
]

one step closer for sure. I am getting the following error. System.NotSupportedException: 'LINQ to Entities does not recognize the method 'System.Collections.Generic.Dictionary`2[System.String,System.String] ToDictionary[<>f__AnonymousType45`3,String,String](System.Collections.Generic.IEnumerable`1 — Alex D, Jul 02 '18 at 15:17

Grouping Linq and Converting columns to rows

1 Answers1