Linq query for two tables + N Tier

Question

I have two tables that have some Foreign Keys. When writing a SELECT query i would write something like

SELECT     
Movie.MovieName, 
Director.Name, 
Director.Notes, 
Director.FirstMovieDate

FROM         
Movie 

INNER JOIN Director ON Movie.MovieID = Director.MovieID

In an N Tier structure, how would i write this query using Linq and separate layers, including classes/DTO etc i.e. 

For one table this is what i do. I have a class called Movie (DTO)

Public Class MovieDTO
Public Property MovieID As Integer
Public Property MovieName As String
End Class

Then i have my Interface

Public Interface IMovie
Function GetMovies As IQueryable (of MovieDTO)
End Interface

I then implement the interface in another class

Public Class MovieOperations
Implements IMovie

Public Function GetMovies As IQueryable (Of MovieDTO)
Dim ctx as New MovieDataContext

dim query = from m in ctx.Movies            
Select new MovieDTO With {.MovieName = m.MovieName}    
Return Query
End Function
End Class

So this works neatly with one table but how would i use the same pattern when i have two tables (or more) to get certain columns back etc? Would i still have two DTO classes and then somehow link them? Just looking for some examples or links to explain the above in more depth.

Thanks

Answer 1

If you are using Linq I would suggest using the designer in VS.  If you have FK defined the classes created will know about that.  If you need another level then I would suggest that you simply reference the Linq generated classes or you will lose pretty much all the Linq generated goodness.  If you don't then you will spend a lot of time in syncing the two versions of the data (the Linq version and your DTO version).

If you do this then using Linq will indeed make your life easier.  Using the Linq classes for example would allow you to have the FK define within the classes.  Supposing that ctx.Movies returns a list of movies (a class) then if you had a class (from a table) named ActorInMovie when you access the movie class there would be a property that would give you access to the list of actor in that movie.  There would be no code to write to get the information.  As well if you were storing a new movie then you would fill in the movie class (and this is where it gets good).  You also create ActorInMovie items and add them to the collection that gives you the list of actors in the movie.  Without writing any code (other than asking Linq to store the data) it will insert the new movie row, get back the generated PK and then for each item in the actor collection add it using that PK.

Hope this helps

LS

---

Lloyd Sheen

Answer 2

If I could understand correctly you need to implement inner join operation on LINQ layer?

Use the LINQ designer to drop the tables (as sqlguy has explained) and here is how can you do inner join with LINQ

http://msguy.net/post/2013/01/04/LINQ-Join-Operations.aspx#inner-join

---

Please Mark as Reply and Vote as Helpful if I helped.

Also please visit my blog http://msguy.net/