linq to sql:如何用group by塑造数据?

我有一个示例数据库,它包含电影、人物和人物的表格。movie表包含一个标题和一个id。people表包含一个名称和一个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'表示此人是 演员 在那部电影里,或者 主任 .

我想对一个特定的人执行一个查询,返回这个人的名字,加上这个人工作过的所有电影的列表,以及这些电影中的角色。

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

那不太对。如你所见,每部电影都有一个副本,伊斯特伍德扮演了多个角色。我希望如此,因为电影+人物组合的credits表中有多行,每个角色一行。

试试“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

如果我使用 小组通过 包括电影标题,如下所示:

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

…然后它将无法编译,因为

错误CS0103:当前上下文中不存在名称“m”

我怎样才能按照我想要的方式塑造输出?