使用LINQ组织数据库结果

Question

我在电影/电影管理系统中遇到问题。我有表，

表电影

+----+------------------+---------------------------+ 
| Id | Name    | Description    | 
+----+------------------+---------------------------+ 
| 1 | Kabaddi Kabaddi |Kabaddi Kabaddi is a... | 
| 2 | Kabaddi   |Kabaddi is a...   | 
| 3 | Jholay   |A man hopes to achieve... | 
| 4 | Nai Nabhannu La |       | 
+----+------------------+---------------------------+ 

表角色

+----+----------+ 
| Id | Name  | 
+----+----------+ 
| 1 | Actor | 
| 2 | Actress | 
| 3 | Director | 
+----+----------+ 

表人民

+----+--------------+---------------+-----------+ 
| Id | FirstName | MiddleName | LastName | 
+----+--------------+---------------+-----------+ 
| 1 | Dayahang  |    | Rai  | 
| 2 | Priyanka  |    | Karki  | 
| 3 | Nischal  |    | Basnet | 
+----+--------------+---------------+-----------+ 

表施放

+-----------+-----------+-----------+ 
| FilmId | PersonId | RoleId | 
+-----------+-----------+-----------+ 
| 1   | 1   | 1   | 
| 2   | 1   | 1   | 
| 3   | 1   | 1   | 
| 2   | 3   | 1   | 
| 1   | 3   | 1   | 
| 2   | 3   | 3   | 
| 1   | 3   | 3   | 
| 4   | 2   | 2   | 
+-----------+-----------+-----------+ 

我的目标是获得与使用单个查询各自的角色各自铸件的电影名单。

Kabaddi Kabaddi <FilmName> 
    Dayahang <CastFirstName> 
     Actor <RoleName> 
    Nischal <CastFirstName> 
     Actor <RoleName> 
     Director <RoleName> 
Jholay <FilmName> 
    Dayahang <CastFirstName> 
     Actor <RoleName> 
    Priyanka <CastFirstName> 
     Actress <RoleName> 

最好的我已经试过是

var result = from cast in entity.Casts 
      join film in entity.Films on cast.FilmId equals film.Id 
      join person in entity.People on cast.PersonId equals person.Id 
      join role in entity.Roles on cast.RoleId equals role.Id 

导致

+-------------------+---------------+-----------+ 
| FilmName   | CastFirstName | RoleName | 
+-------------------+---------------+-----------+ 
| Kabaddi Kabaddi | Dayahang  | Actor  | 
| Kabaddi   | Dayahang  | Actor  | 
| Jholay   | Dayahang  | Actor  | 
| Kabaddi   | Nischal  | Actor  | 
| Kabaddi Kabaddi | Nischal  | Actor  | 
| Kabaddi   | Nischal  | Director | 
| Kabaddi Kabaddi | Nischal  | Director | 
| Nai Nabhannu La | Nischal  | Actress | 
+-------------------+---------------+-----------+ 

是否有可能在一个单一的查询？怎么样？

Answer 1

我希望，这样的事情应该帮助上手（请原谅语法和拼写错误）：

from cast in entity.Casts 
      join film in entity.Films on cast.FilmId equals film.Id 
      join person in entity.People on cast.PersonId equals person.Id 
      join role in entity.Roles on cast.RoleId equals role.Id 
group new { cast, film, person, role} by film into grp 
        select new 
        { 
         Film = grp.Key.Name, 
         Persons= grp.Select(r => new People() 
         { 
          Id = r.People.Id 
         }), 
         Roles = grp.Select(r => new Role() 
         { 
          Id = r.role.Id 
         }) 
        };