Linq-to-SQL: How to shape the data with group by?
- by Cheeso
I have an example database, it contains tables for Movies, People and Credits. The Movie table contains a Title and an Id. The People table contains a Name and an Id. The Credits table relates Movies to the People that worked on those Movies, in a particular 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 convention either 'A' to indicate the person was an actor on that particular movie, or 'D' for director.
I'd like to perform a query on a particular person that returns the person's name, plus a list of all the movies the person has worked on, and the roles in those movies.
If I were to serialize it to 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 shapes the output like that?
I'm having trouble doing it efficiently.
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" },
...
]
}
...but as you can see there's a duplicate of each movie for which Eastwood played multiple roles.
How can I shape the output the way I want?
