I'm a little stuck with some linq queries (linq to entity). I'm building an API with Web Api 2 and I use the Entity Framework for database/model.
For simplicity's sake lets say Table A, B and C. Table C is a junction table for A and B, and contains the primary keys for Table A and B.
When presented with the primary key of A, I want to get all the entries in B relating to A, and return a new object {item from A, [data from B]}.
I could do this with two linq request, first look up the item in A's related data in B, then look up the item I want from a A, and create the object.
Any way to achieve this in one linq request?
In actuality I have:
Table A --> Junction table C ---> Table B
Table A --> Junction table D ---> Table E
I need to get several items from table A at the time, so first I need to create {item from A, [data from B], [data from E]}, then then a collection of A items..
I've experimented with joining all the tables, and on the other hand getting the results step by step, but my knowledge isn't good enough (Noob) to tell what's 'the best/proper' way
Edit:
Table A
pk
. . . . . . . . . . . . .
MovId | data | data |
. . . . . . . . . . . . .
MovId | data | data |
Table b
pk
. . . . . . . . . . . . .
GenId | data | data |
. . . . . . . . . . . . .
GenId | data | data |
Table c
pk pk
. . . . . . . . . . . . .
MovId | GenId
. . . . . . . . . . . . .
MovId | GenId
My current approach:
var combined = from a in A
join c in C on a.MovId equals c.MovId
join b in B on c.GenId equals b.GenId
where a.MovId == some value
group b by a into obj
select obj;
The problem starts when I need to fetch from yet another set of tables (Same structure as Table B and C)
var combined = from a in A
join c in C on a.MovId equals c.MovId
join b in B on c.GenId_b equals b.GenId
join d in D on a.MovId equals d.MovId
join e in E on d.DirId equals d.DirId
where a.id == some
--> ?
Is it possible to do this in one go without 4 joins?
. . . . . . . . . . UPDATE . . . . . . . .
Hi, EF6. Solved with implicit Code-First junction tables between Movies-Director and Movies-Genre:
var movies = await db.Movies
.OrderByDescending(m => m.Rating)
.Take(num).Select(m => new {
MovieId = m.MovieId,
Title = m.Title,
Rating = m.Rating,
Description = m.Description,
Poster = m.PosterUrl,
BackDrop = m.BackDropUrl,
Genres = m.Genres.Select(g => g.Name),
Directors = m.Directors.Select(d => d.Name)
})
.ToListAsync();
-- JSON --
[{"MovieId":5,
"Title":"Four Rooms",
"Rating":6.3,
"Description":"It's Ted the...",
"Poster":"/eQs5hh9rxrk1m4xHsIz1w11Ngqb.jpg",
"BackDrop":"/3EqYpbGCE9S5GddU2K4cYzP5UmI.jpg",
"Release":"1995-12-09T00:00:00",
"Genres":["Comedy","Crime"],
"Directors":["Quentin Tarantino","Robert Rodriguez","Allison
Anders","Alexandre Rockwell"]
}]