如何在LINQ中正确使用GroupBy？

Question

我有 4 个表：Post、Category、Relation 和 Meta

一个 category 可以包含多个 post，它们之间的关系存储在 Relation 表中。然后，帖子可以包含许多存储在 Meta 表中的额外信息。我想列出所有带有类别和额外信息的帖子，然后按帖子 ID 对它们进行分组。

我有以下查询

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

和这些示例数据：

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

那么结果将是

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

，我期望的结果是

PostID      Title     Categories               Meta

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

我想知道我们是否可以使用 EF v4 和结果将查询从 SQL 转换为 LINQ to Entities存储在这样的类中

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

任何帮助将不胜感激。

Answer 1

您期望查询的最终结果是什么
我个人更喜欢这样编写查询

 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;

我认为（不确定）生成的sql会更简单

Answer 2

现在您想要使用 EntityFramework，您只需要设置数据库、edmx 以及带有 ID 和标题的结果表，然后设置类别和元表。然后将结果表中的一对多关系添加到每个类别和元表中。

Answer 3

我不是 100% 确定你要做什么，但显然如果你要分组，结果必须按结果集中的任何内容进行分组，或者是聚合数据。此查询将检索结果并按 PostId、PostTitle 和 CategoryName 进行分组，生成单个 SQL 语句：

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 };

这是此语句生成的 SQL：

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]

这是原始语句生成的 SQL：

 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]))