Distinct and group by at the same time in LINQ?

Let say I have the following classes:

Product { ID, Name }

Meta { ID, Object, Key, Value }

Category { ID, Name }

Relation {ID, ChildID, ParentID } (Child = Product, Parent = Category)

and some sample data:

Product:

ID   Name

1    Chair
2    Table

Meta

ID   Object  Key     Value

1      1     Color   "Red"
2      1     Size    "Large"
3      2     Color   "Blue"
4      2     Size    "Small"

Category

ID   Name

1    Indoor
2    Outdoor

Relation

ID   ChildID   ParentID

1       1         1
2       1         2
3       2         1

Can we use Distinct and Group by to produce the following format (ProductDetail)

ID=1,
Name=Chair,
Parent=
{
  { ID=1, Name="Indoor" },
  { ID=2, Name="Outdoor" }
},
Properties { Color="Red", Size="Large" }

ID=2,
Name=Table,
Parent=
{
  { ID=1, 开发者_运维技巧Name="Indoor"}
},
Properties { Color = "Blue", Size = "Small" }

which we can get the "Color" value of the first item by using

ProductDetails[0].Properties.Color

Any helps would be appreciated!

No, you can't do this based on what you've said - because "Color" and "Size" are part of the data, rather than part of the model. They're only known at execution time, so unless you use dynamic typing, you're not going to be able to access it by Properties.Color.

You could, however, use Properties["Color"] potentially:

var query = from product in db.Products
            join meta in db.Meta 
                on product.ID equals meta.Object 
                into properties
            select new { Product = product,
                         Properties = properties.ToDictionary(m => m.Key,
                                                              m => m.Value) };

So for each product, you'll have a dictionary of properties. That works logically, but you may need to tweak it to get it to work in the entity framework - I don't know how well that supports ToDictionary.

EDIT: Okay, I'll leave the above up as the "ideal" solution, but if EF doesn't support ToDictionary, you'd have to do that part in-process:

var query = (from product in db.Products
             join meta in db.Meta 
                 on product.ID equals meta.Object 
                 into properties
             select new { product, properties })
            .AsEnumerable()
            .Select(p => new {
                    Product = p.product,
                    Properties = p.properties.ToDictionary(m => m.Key,
                                                           m => m.Value) });

I just came across this question while learning LINQ, but I wasn't satisfied that Jon's output matched the question (sorry Jon). The following code returns a List of anonymously-typed objects that better match the structure of your question:

var ProductDetails = (from p in Product
                      let Parents = from r in Relation
                                    where r.ChildID == p.ID
                                    join c in Category on r.ParentID equals c.ID
                                        into RelationCategory
                                    from rc in RelationCategory
                                    select new
                                    {
                                        rc.ID,
                                        rc.Name
                                    }
                      join m in Meta on p.ID equals m.Object into ProductMeta

                      select new
                      {
                          p.ID,
                          p.Name,
                          Parent = Parents.ToList(),
                          ProductMeta
                      })
                     .AsEnumerable()
                     .Select(p => new
                     {
                         p.ID,
                         p.Name,
                         p.Parent,
                         Properties = p.ProductMeta
                                       .ToDictionary(e => e.Key, e => e.Value)
                     }).ToList();

Credit goes mostly to Jon Skeet and the Visual Studio debugger ;)

I realise that you've probably moved on by now but hopefully this might help someone else looking to learn LINQ, as I was.