c# - Linq-to-SQL:如何使用 group by 对数据进行整形？

Question

我有一个示例数据库，它包含电影、人物和演职员表。 Movie 表包含一个 Title 和一个 Id。 People 表包含一个 Name 和一个 Id。 Credits 表将电影与以特定角色参与制作这些电影的人员相关联。该表如下所示:

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”表示此人是该特定电影的 Actor ，或者“D”表示 < em>导演。

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

如果我将它序列化为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" },
     ...
  ]
}

这不太对。如您所见，伊斯特伍德扮演多个角色的每部电影都有一个副本。我希望如此，因为该电影+人物组合的演职员表中有多行，每个角色对应一行。

---

尝试#2

我想我会使用分组方式，像这样:

  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: The name 'm' does not exist in the current context

---

如何按照我想要的方式调整输出？

Answer 1

如果你从关系表开始推理会容易得多(学分):

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