Combine inner join and left join in Entity Framework query

Question

I have 3 classes / tables: Car, User and UserVote (which is a bridging table between the other two).

For example:

Car (id / name):
1 / Ford Focus
2 / Honda Civic
3 / Citroen C2

User (id / ignoreUser):
1 / false
2 / true
3 / false

UserVote (UserId / CarId)
1 / 2
1 / 3
2 / 1
3 / 2

I have a method which gets the top x cars based on the number of votes.

public IQueryable<Car> GetTopCars(int count)
{
    return context.Car
        .Include(car => car.UserVote)
        .Where(car => car.UserVote.Count > 0)
        .OrderByDescending(car => car.UserVote.Count)
        .Take(count);
}

This code only looks at the Cars and UserVote table and doesn't look at the ignoreUser field on User. I am trying to update my Entity Framework code to join to the User table, but I'm not having any success. Ultimately I am looking to get a list of Cars ordered by the number of UserVotes, excluding those with IgnoreUser set to true. In the above example, I would like the method to return IQueryable in the correct order (Honda Civic then Citroen C2).

I think I want to take Cars and then left join this to User inner joined with UserVote? Is this possible with Entity Framework?

This question follows on from the advice given in a comment to a previous question.

ocuenca · Accepted Answer · 2016-06-18 18:57:55Z

2

If you have an User navigation property in your UserVote entity then you query could be this way:

public IQueryable<Car> GetTopCars(int count)
{
    return (from c in context.Car
            let userVotes=car.UserVote.Where(uv=>!uv.User.IgnoreUser).Count()
            where userVotes > 0
            orderby userVotes
            select c)
           .Take(count);
}

If you want to do it using just join operations then you could do this:

public IQueryable<Car> GetTopCars(int count)
{
    return (from c in context.Car
            let userVotes=(from uv in c.UserVotes
                           join u in context.Users on uv.UserId equals u.Id into users
                           where u=>!u.IgnoreUser
                           select u).Count()
            where userVotes > 0
            orderby userVotes
            select c
           )
           .Take(count);
}

edited Jun 18, 2016 at 18:57

answered Jun 18, 2016 at 17:55

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

infojolt Over a year ago

When I use the second option with the join operations, I get duplicate cars returned - one for each vote. Also, userVotes is always equal to one which I believe is due to the inner join?

ocuenca Over a year ago

I will try to fix the second variant, Can you not use the first solution, I'm sure it works.

ocuenca Over a year ago

@psych, I fixed it but I strongly suggest use the first solution if you can. IMHO it is cleaner

Collectives™ on Stack Overflow

Combine inner join and left join in Entity Framework query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related