How to use GroupBy properly in LINQ?

Question

I have 4 tables: Post, Category, Relation and Meta

A category can contains multiple posts, and the relation between them is stored in Relation table. A post then can has many extra info that are stored in Meta table. I want to list all post with categories and extra infos, then group them by post's ID.

I have the following query

select p.ID, p.Title, t.Name, m.Key, m.Value from Post p
left join Relation r on p.ID = r.Child
left join Category c on r.Parent = c.ID
left join Meta m on p.ID = m.Object
where m.Type = 'news'
order by p.ID

and with these sample data:

Post

ID    Title

1     A

Category

ID    Name

1     Tips
2     Tricks

Meta

ID    Object  Key      Value

1       1     Key1     Value 1
2       1     Key2     Value 2

Relation

ID    Child Parent

1       1     1
2       1     2

then the result will be

PostID      Title     Category       Key      Value

  1           A         Tips         Key1     Value1
  1           A         Tips         Key2     Value2
  1           A        Tricks        Key1     Value1
  1           A        Tricks        Key2     Value2

and I expected the result to be

PostID      Title     Categories               Meta

  1           A      Tips, Tricks    Key1=Value1, Key2=Value2

I wonder if we can convert the query from SQL to LINQ to Entities with EF v4 and the result is stored in a class like this

class Result
{
  long ID,
  string Title,
  List<string> Categories,
  Dictionary<string, string> Meta
}

Any helps would be appreciated.

Answer 1

What's the final result you expect from the query I personally prefer to write the query like

 var q = from r in Relation
         join p in Post on r.Child equals p.ID
         join t in Term on r.Parent equals t.ID
         let x = new { p.ID, p.Title, t.Name }
         group x by x.ID into g
         select g;

this way I think (not sure) the sql generated will be simpler