LINQ query in EF - many to many and a cross join

Question

A simple stored procedure that I want to handle with LINQ instead:

SELECT 
CASE  WHEN mg.MovieID IS NULL THEN 0 else 1 end as Selected ,
g.genreID, g.GenreName
FROM dbo.Genres g LEFT JOIN 
(
    SELECT MovieID, GenreID FROM [dbo].[MovieGenre] m 
    WHERE m.MovieID = @Movie
) MG 
ON g.[GenreID]  = mg.[GenreID]
ORDER BY g.GenreName

I think this should be simple and I think it would be a common requirement, yet I can't figure it out nor have I found a solution via searching the web.

The app is in WPF backed by an EF model. Since EF hides the join table I need LINQ syntax that can deal with the absence of the intermediary table.

Classic many-to-many with a simple join table: table 1:Movies, table 2: Genres, Join table: MovieGenres. In the UI the user selects a specfic movie. For that movie I want to bring back ALL the genres and a bool value indicating whether the genre has been assigned to the movie. Hours of attempting this in LINQ have failed me, so the solution is currently to have the stored procedure above generate the values for me. I won't always be abe to do this with a stored procedure and would love to see a LINQ solution.

Here's the actual SQL table structures

CREATE TABLE [dbo].[Genres](
    [GenreID] [int] IDENTITY(1,1) NOT NULL,
    [GenreName] [nvarchar](15) NOT NULL,
 CONSTRAINT [PK_Genres] PRIMARY KEY CLUSTERED 
(
    [GenreID] ASC
)) ON [PRIMARY]

GO  

CREATE TABLE [dbo].[Movies](
    [MovieID] [int] IDENTITY(1,1) NOT NULL,
    [MovieTitle] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED 
(
    [MovieID] ASC
))
ON [PRIMARY]
GO

CREATE TABLE [dbo].[MovieGenre](
    [MovieID] [int] NOT NULL,
    [GenreID] [int] NOT NULL,
 CONSTRAINT [PK_MovieGenre] PRIMARY KEY CLUSTERED 
(
    [MovieID] ASC,
    [GenreID] ASC
)) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MovieGenre]  WITH CHECK ADD  CONSTRAINT [FK_Genres] FOREIGN KEY([GenreID])
REFERENCES [dbo].[Genres] ([GenreID])
GO
ALTER TABLE [dbo].[MovieGenre] CHECK CONSTRAINT [FK_Genres]
GO
ALTER TABLE [dbo].[MovieGenre]  WITH CHECK ADD  CONSTRAINT [FK_Movies] FOREIGN KEY([MovieID])
REFERENCES [dbo].[Movies] ([MovieID])
GO
ALTER TABLE [dbo].[MovieGenre] CHECK CONSTRAINT [FK_Movies]
GO

Answer 1

This should do the trick.

• use from and in with the navigation property for a join
• DefaultIfEmpty to make it a left join
• Using a ternary operator ? : for the case statement

Query:

var query = from g in context.Genres
            from m in g.Movies.Where(x => x.MovieID == movieId)
                               .DefaultIfEmpty()
            orderby g.GenreName
            select new {
              Selected = m == null ? 0 : 1,
              g.genreID, 
              g.GenreName
            };