Geek asks and answers

LINQ query in EF - many for many and cross connect

A simple stored procedure that I want to process using 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 that would be a general requirement, but I can’t figure it out, and haven’t found a solution through an Internet search.

The application is in WPF, supported by the EF-model. Since EF hides the join table, I need LINQ syntax, which can deal with the absence of a staging table.

Classic many-to-many with a simple join table: table 1: movies, table 2: genres, Join table: MovieGenres. In the user interface, the user selects a specfic movie. For this film, I want to return ALL genres and a bool value indicating whether the genre was assigned to the film. The hours of trying this in LINQ didn't let me down, so the solution now is to have the stored procedure generate values ​​for me above. I will not always avoid this with a stored procedure and would like to see a LINQ solution.

Here are 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
+5
source share
1 answer

That should do the trick.

  • use fromand inusing the navigation property tojoin
  • DefaultIfEmpty,
  • ? : case

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
            };
+5

More articles:


All Articles