CODEWITHSUNDEEP
linqlinq-to-entitiesgroup-by
ByulTaeng
ByulTaeng

Reputation: 1269

How to use GroupBy properly in LINQ?

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.

Upvotes: 13

Views: 1024

Answers (3)

DShultz
DShultz

Reputation: 4541

Now that you're wanting to use EntityFramework, you would merely need to set up you database, edmx with a Result table with an ID and a Title, then Category and Meta tables. Then add one-to-many relationships from the Result table to each the Category and Meta tables.

Upvotes: 1

DShultz
DShultz

Reputation: 4541

I'm not 100% sure what you're trying to do, but obviously if you're grouping, the results have to be grouped by anything in the resultset, or be aggregated data. This query will retrieve your results and group by PostId, PostTitle, and CategoryName, generating a single SQL Statement:

var query = from p in Posts
from r in Relations
.Where(r => p.ID == r.Child)
.DefaultIfEmpty()
from c in Categories
.Where(c => r.Parent == c.ID)
.DefaultIfEmpty()
group p by new {ID = p.ID, Title = p.Title, Name = c.Name} into z
select new { ID = z.Key.ID, Title = z.Key.Title, Name = z.Key.Name };

Here is the SQL Generated by this statement:

SELECT [t3].[ID], [t3].[Title], [t3].[value] AS [Name]
FROM (
SELECT [t0].[ID], [t0].[Title], [t2].[Name] AS [value]
FROM [Post] AS [t0]
LEFT OUTER JOIN [Relation] AS [t1] ON [t0].[ID] = [t1].[Child]
LEFT OUTER JOIN [Category] AS [t2] ON [t1].[Parent] = [t2].[ID]
) AS [t3]
GROUP BY [t3].[ID], [t3].[Title], [t3].[value]

Here is the SQL Generated by your original statement:

 SELECT [t0].[ID] AS [Key]
FROM [Post] AS [t0]
INNER JOIN [Relation] AS [t1] ON [t0].[ID] = [t1].[Child]
INNER JOIN [Category] AS [t2] ON [t1].[Parent] = [t2].[ID]
    GROUP BY [t0].[ID]
GO

-- Region Parameters
DECLARE @x1 Int SET @x1 = 1
-- EndRegion
SELECT [t0].[ID], [t0].[Title], [t2].[Name]
FROM [Post] AS [t0]
INNER JOIN [Relation] AS [t1] ON [t0].[ID] = [t1].[Child]
INNER JOIN [Category] AS [t2] ON [t1].[Parent] = [t2].[ID]
WHERE ((@x1 IS NULL) AND ([t0].[ID] IS NULL)) OR ((@x1 IS NOT NULL) AND         ([t0].[ID]         IS     NOT NULL) AND (@x1 = [t0].[ID]))
    GO

-- Region Parameters
DECLARE @x1 Int SET @x1 = 2
-- EndRegion
SELECT [t0].[ID], [t0].[Title], [t2].[Name]
FROM [Post] AS [t0]
INNER JOIN [Relation] AS [t1] ON [t0].[ID] = [t1].[Child]
INNER JOIN [Category] AS [t2] ON [t1].[Parent] = [t2].[ID]
WHERE ((@x1 IS NULL) AND ([t0].[ID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[ID] IS     NOT NULL) AND (@x1 = [t0].[ID]))

Upvotes: 0

Nasmi Sabeer
Nasmi Sabeer

Reputation: 1380

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

Upvotes: 1

Related Questions