Linq-to-SQL: how to generate data using a group?

Question

I have an example database, it contains tables for films, people and credits. The movie table contains a title and an identifier. The People table contains the name and identifier. The Credits table refers to films for people who have worked on these films in a specific role. The table looks like this:

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 

In this simple example, the [Role] column is a single character, by my agreement, either “A” to indicate that the person was an actor in that particular film, or “D” for the director.

I would like to fulfill a query for a specific person who returns the person’s name, as well as a list of all the films the person worked on and their roles in these films.

If I serialized it for json, it might look like this:

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

How can I write a LINQ-to-SQL query that generates this output?

I have problems with efficiency.

---

Try # 1

if i use this query:

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

I get something like this:

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

This is not entirely correct. As you can see there is a duplicate of each film, for which Eastwood played several roles. I would expect, because in the credit table there are several rows for this film + person combination, one for each role.

---

Try # 2

I thought I would use a group, for example:

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

The result is pretty close to what I want. It looks like this:

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

This is close, but, of course, I have no movie titles.

---

Try # 3

If I use a group and include the name of the movie, for example:

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

... then it will not compile due

error CS0103: name "m" does not exist in the current context

---

How can I form the output the way I want?

Answer 1

It’s much easier to talk about whether you start with a table of relationships (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") } }; 

Here is the code that displays the results:

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

Answer 2

I believe that you need to materialize the request, and then group by name and title and use string.Join to sort the roles.

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

Answer 3

Thanks to the hint of tvanfosson , I was able to come up with this that works for me!

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

---

I also got some tips from Aaronaught and tried to start with the Credits table and use the generated associations. This simplified the situation. This code also works:

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

... and it produces the same (or equivalent) output when serializing the JavaScriptSerializer.

---

The main implementation for me that allowed me to do this was that I could use a composite key for the group and that I could select in the fields inside the key. The second key implementation was that I have to use the created associations.