1

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"]
}]
Derrick
  • 71
  • 6
  • 1
    Can you share your current linq code and your table's structures? – Emre Savcı Dec 23 '18 at 08:26
  • 2
    Navigation properties will greatly simplify what are you trying to achieve (and in general are the preferred EF approach over manual joins). Are you using model with implicit or explicit junction table? And what EF is this - EF6 or EF Core? – Ivan Stoev Dec 23 '18 at 11:06
  • If this is EF-core (please use the appropriate EF version tag) then see [here](https://stackoverflow.com/q/39672601/861716) just one example of how to deal with many to many in EF core properly. Also for EF6 there are many examples out there. – Gert Arnold Dec 23 '18 at 12:21

0 Answers0