How to create LINQ query to show all movies only once?

Question

I have 3 tables and for simplicity I will use IMDB example (users rating movies).

Table 1: Movie
Table 2: MovieUserRating
Table 3: User.

Movie (3 movies):

ID  MOVIE_NAME  
1   Movie1  
2   Movie2  
3   Movie3

MovieUserRating (both users rated only movie 1):

MOVIE_ID    USER_ID RATING  
1             1       10  
1             2        9

User (2 users):

ID  USER_NAME  
1   User1  
2   User2

So, if any user is logged in (for example User1), I need to execute LINQ query that will return list of all movies and logged user’s rating for them. If movie is not rated by logged user, it would show 0.

So, I need results to look like this (if user1 is logged in):

MOVIE       RATING  
Movie1        10  
Movie2         0

And if user2 is logged in:

MOVIE       RATING  
Movie1        9  
Movie2        0

So, this is my query (not good) in the ASP.NET controller:

var results =   
    from m in db.Movies  
    from mur in db.MoveUserRatings.Where( mur => mur.MOVIE_ID == m.ID).DefaultIfEmpty()  
    from u in db.Users.Where( u=>u.ID == mur.USER_ID).DefaultIfEmpty()  
        select new MyModelViewClass  
        {  
            MovieName = m.MOVIE_NAME,         
            MovieRating = (u.Id == “user ID that was supplied”) ? mur.RATING : 0  
        };

Unfortunately, this query returns Movie1 twice since it has been rated by both users. So, results look like this:

(if user 1 is logged in):

MOVIE       RATING  
Movie1        10  
Movie1         0  
Movie2         0

(if 2 is logged in):

MOVIE       RATING  
Movie1        9  
Movie1        0  
Movie2        0

How can I fix this to get results I need (Movie 1 shows only once)?

Thanks in advance for any help.

you need to `join` your entries http://stackoverflow.com/q/5537995/81053 — Chris Moutray, Apr 19 '17 at 13:27

score 2 · Answer 1 · answered Apr 19 '17 at 13:38

2

Your Query is not filtering results by logged user, but only changing selected values. You can try this:

var results =
   from m in db.Movies
   join mur in db.MoveUserRatings.Where(k=> k.USER_ID == “user ID that was supplied”) on m.ID equals mur.MOVIE_ID into murs
   from mmur in murs.DefaultIfEmpty()
   select new MyModelViewClass
   {
       MovieName = m.MOVIE_NAME,
       MovieRating = mmur.RATING ?? 0
   };

Note: '??' is like SQL COALESCE operator.

answered Apr 19 '17 at 13:38

Seididieci

404
4
14

1

You could also set the filter on the `on` clause using an anonymous type to check the `ID` and the `USER_ID` – Cleptus Apr 19 '17 at 13:53
anyone else hate how the syntax leads you to have 3 aliases for the joined entity eg `mur`, `murs` and `mmur` – Chris Moutray Apr 19 '17 at 14:08
1

@ChrisMoutray It's not necessary though - I don't know why people tend to use different aliases. After `into` clause, the `mur` range variable is out of scope, so it can be used instead of `mmur`. – Ivan Stoev Apr 19 '17 at 14:12
@ChrisMoutray I know it's a mess of aliases, but i thought it would be more explicative for a beginner (who could not know scopes and I would been ambiguous). – Seididieci Apr 19 '17 at 14:23
@Ivan ah thanks, didn't appreciate that you could reuse the aliases - I've been sticking to method syntax over query syntax lately – Chris Moutray Apr 20 '17 at 13:11

How to create LINQ query to show all movies only once?

1 Answers1