Linq-to-SQL：如何使用group by来塑造数据？

Question

我有一个示例数据库，其中包含电影、人员和演职员表。电影表包含标题和ID，人员表包含名称和ID。演职员表将参与电影制作的人员与电影及其所扮演角色相关联。该表格如下：

CREATE TABLE [dbo].[Credits] (
    [Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [PersonId]  [int] NOT NULL FOREIGN KEY REFERENCES People(Id),
    [MovieId]  [int] NOT NULL  FOREIGN KEY REFERENCES Movies(Id),
    [Role]  [char] (1) NULL

在这个简单的例子中，[Role] 列是一个单字符，按照我的惯例，要么是 'A' 表示该人在特定电影中是演员，要么是 'D' 表示导演。

我想对某个人执行查询，返回该人的姓名，以及该人参与过的所有电影和电影中的角色列表。

如果我将其序列化为 json，则可能如下所示：

{
  "name" : "Clint Eastwood",
  "movies" : [
     { "title": "Unforgiven",        "roles": ["actor", "director"] },
     { "title": "Sands of Iwo Jima", "roles": ["director"] },
     { "title": "Dirty Harry",       "roles": ["actor"] },
     ...
  ]
}

如何编写一个 LINQ-to-SQL 查询以使输出呈现这样的形式？

我正在努力高效地完成它。

---

尝试 #1

如果我使用这个查询：

  int personId = 10007;
  var persons =
      from p in db.People
      where p.Id == personId
      select new
      {
          name   = p.Name,
          movies =
                (from m in db.Movies
                 join c in db.Credits on m.Id equals c.MovieId
                 where (c.PersonId == personId)
                 select new {
                         title = m.Title,
                         role = (c.Role=="D"?"director":"actor")
                 })
      };

我收到了这样的信息：

{
  "name" : "Clint Eastwood",
  "movies" : [
     { "title": "Unforgiven",        "role": "actor" },
     { "title": "Unforgiven",        "role": "director" },
     { "title": "Sands of Iwo Jima", "role": "director" },
     { "title": "Dirty Harry",       "role": "actor" },
     ...
  ]
}

这并不完全正确。您可以看到，每个由Eastwood扮演多个角色的电影都有一个副本。我预计这是因为在该电影+人物组合的credits表中有多行记录，每行记录代表一个角色。

---

尝试 #2

我想使用group by来实现，就像这样：

  var persons =
      from p in db.People
      where p.Id == personId
      select new
      {
          name   = p.Name,
          movies =
                (from m in db.Movies
                 join c in db.Credits  on m.Id equals c.MovieId
                 where (c.PersonId == personId)
                 orderby m.Year
                 group ((c.Role == "A")? "actor":"director")
                 by m.Id
                 into g
                 select new {roles = g })
      };

输出结果与我所需的相当接近。它看起来像这样：

{
  "name" : "Clint Eastwood",
  "movies" : [
     { "roles": ["actor", "director"]}, 
     { "roles": ["director"]}, 
     { "roles": ["actor"]},
     ...
  ]
}

接近了，但是我没有电影标题。

---

尝试 #3

如果我使用group by并包含电影标题，就像这样：

  var persons =
      from p in db.People
      where p.Id == personId
      select new
      {
          name   = p.Name,
          movies =
                (from m in db.Movies
                 join c in db.Credits  on m.Id equals c.MovieId
                 where (c.PersonId == personId)
                 orderby m.Year
                 group ((c.Role == "A")? "actor":"director")
                 by m.Id
                 into g
                 select new { title = m.Title, roles = g })
      };

如果出现以下错误，则无法编译：

error CS0103: 当前上下文中不存在名称'm'

---

我该如何按照自己的意愿塑造输出结果？

- Cheeso

Answer 1

如果你从关系表（credits）开始考虑，就会变得更容易理解：

var query =
    from c in context.Credits
    where c.PersonId == 1
    group c by c.Person into g
    select new
    {
        PersonName = g.Key.Name,
        Credits = from cr in g
                  group cr by cr.Movie into g2
                  select new
                  {
                      MovieTitle = g2.Key.Name,
                      Roles = g2.Select(ci =>
                          (ci.Role == 'A') ? "Actor" : "Director")
                  }
    };

这里是能够显示结果的代码：

foreach (var result in query)
{
    Console.WriteLine(result.PersonName);
    foreach (var credit in result.Credits)
    {
        string roles = string.Join(",", credit.Roles.ToArray());
        Console.WriteLine("  " + credit.MovieTitle + ": " + roles);
    }
}

- Aaronaught

---

我有一种感觉应该把它翻转过来，但我没有完全做到......虽然我仍然认为它存在问题。读取“group c by c.Person”的部分-它是如何满足的？我不清楚，因为Credits表有一个PersonId列，但没有Person列。重要的是要获取人的姓名，但如果不加入Person表，我将无法获得它。也许你只是省略了那部分。 - Cheeso

@Cheeso: 它没有一个 Person 列但是应该有一个 Person 关联。当你通过这个关联进行分组时，它知道如何在翻译成SQL时使用关联列 (PersonId)。如果数据库中有FK，则自动创建关联。你可以将表拖到设计器中（这正是我在这里所做的）。实际上，我从测试项目中直接复制和粘贴了这段代码。如果你缺少关联，那么你会让自己的生活变得更加困难... - Aaronaught

是的，我没有这个关联；我猜那是某种生成代码的结果。我没有使用VS来生成ORM代码；我使用命令行中的sqlmetal.exe。我想我将不得不显式地使用来自sqlmetal的代码来执行这些连接。这引出了一个新的问题 - 是否有一种方法可以使用sqlmetal自动创建这些关联？我将单独研究这个问题。无论如何，感谢您对语法的帮助。我使用的实际查询在这里：https://dev59.com/3UzSa4cB1Zd3GeqPoq5u#2481546 - Cheeso

@Cheeso: 奇怪，我以前用过 SQLMetal，它应该自动生成关联，没有特殊的开关之类的东西。我能想到的唯一原因是你实际上没有像原问题中那样的外键。可能当你第一次运行 SQLMetal 时关系就已经丢失了？ - Aaronaught

是的。我在处理表定义时不小心删除了一个外键。我已经重新创建了表和代码，现在它可以正常工作（实体名称为Movies和People，而不是Movie和Person）。感谢您提供的指导，非常有帮助。 - Cheeso

Answer 2

我相信你需要将查询实例化，然后按名称和标题分组，并使用string.Join来汇总角色。

  int personId = 10007;
  var persons = db.People.Where( p => p.Id == personId );
  var movies = db.Movies
                 .Join( db.Credits.Where( c => c.PersonId == personId),
                        m => m.Id,
                        c => c.MovieId,
                       (m,c) => new {
                   personid = c.PersonId,
                   title = m.title,
                   role = c.Role == "D" : "director", "actor"
                  })
                 .GroupBy( g => new { g.personid, g.title } )
                 .ToList()
                 .Select( g => new {
                     personid = g.Key.personid,
                     title = g.Key.title
                     roles = string.Join( ",", g.Select( g => g.role ).ToArray() )
                  });

  var personsWithMovies = people.Join( movies, p => p.PersonId, m => m.personid, (p,m) => new {
                            name = p.Name,
                            movies = m 
                          });

- tvanfosson

---

我认为那个不行 - 它无法编译通过，告诉我 'System.Linq.IQueryable<AnonymousType#1>' 没有定义 'title'，也没有扩展方法 'title' 等等。 - Cheeso

比我想象的要稍微复杂一些。我认为这应该可以工作。 - tvanfosson

Answer 3

感谢tvanfosson的提示，我能够想出这个方法，对我很有效！

var persons =
     from p in db.People
     where p.Id == personId
     select new
     {
         name   = p.Name,
         movies =
               (from m in db.Movies
                join c in db.Credits on m.Id equals c.MovieId
                where (c.PersonId == personId)
                group ((c.Role =="A")?"actor":"director") by m into sg
                orderby sg.Key.year
                select new { title = sg.Key.Title, roles = sg } )
     };

---

我还从Aaronaught那里得到了一些建议，并尝试使用 Credits 表以及生成的关联来开始编写代码。这样做使事情变得更简单。以下是这段代码的实现：

var persons =
    from c in db.Credits
    where c.PersonId == arg
    group c by c.People into g
    select new
    {
        name = g.Key.Name,
        credits = from cr in g
            group ((cr.Role == "A") ? "actor" : "director")
            by cr.Movies into g2
            orderby g2.Key.Year
            select new { title = g2.Key.Title, roles = g2 }
    };

...并且在使用JavaScriptSerializer进行序列化时，它会生成相同的（或等效的）输出。

---

对我来说，关键的领悟是我可以为组使用复合键，并且可以在键内选择字段。第二个关键的领悟是我应该使用生成的关联。

- Cheeso