CODEWITHSUNDEEP
c#asp.net-coreentity-framework-core
Stevie
Stevie

Reputation: 149

Filter data with EF Core in a single query

I'm trying to filter data in ASP.NET Core 2.1 using EF Core DbContext. The scenario is as follows: Our main entity is a Movie. A movie has one or more genres and a genre can belong to one or more movies. The same goes for movie and actor. So we have two many-to-many relationships(code first) and the code that describes these is:

public class Movie
{
    public int Id {get; set;}
    public string Title {get; set;}
    public ICollection<MovieActor> MovieActors {get; set;}
    public ICollection<MovieGenre> MovieGenres {get; set;}
}

public class Actor
{
    public int Id {get; set;}
    public string Name {get; set;}
    public ICollection<MovieActor> MovieActors {get; set;}
}

public class Genre
{
    public int Id {get; set;}
    public string Name {get; set;}
    public ICollection<MovieGenre> MovieGenres {get; set;}  
}

public class MovieActor 
{
    public int MovieId {get; set;}
    public Movie Movie {get; set;}

    public int ActorId {get; set;}
    public Actor Actor {get; set;}  
}

public class MovieGenre
{
    public int MovieId {get; set;}
    public Movie Movie {get; set;}

    public int GenreId {get; set;}
    public Genre Genre {get; set;}  
}

The context responsible for handling queries with the database is MoviesDbContext. I'm trying to filter all the data from the 'Movies' table based on two lists of ints, which represent the Ids of actors and genres in the database.

List<int> actorIds;
List<int> genreIds;

For filtering, we want to get all movies that simultaneously follow the following rules:

1) All movies whose list of actors contain at least one actor whose Id is found in the 'actorIds' list

2) All movies whose list of genres contain at least one genre whose Id is found in the 'genreIds' list

The solution that I found is as follows:

context.Movies
        .Include(m => m.MovieActors)
        .Include(m => m.MovieGenres)
        .Where(m => actorIds.Any(id => m.MovieActors.Any(ma => ma.Id == id)))
        .Where(m => genreIds.Any(id => m.MovieGenres.Any(mg => mg.Id == id)));

This does the filtering right, but the problem is that when EF Core translates the code into sql commands, it breaks the query into a lot of tiny queries and this causes severe performance issues, some queries taking tens of seconds. How can I refactor this so that it does the filtering in only one query?

Upvotes: 0

Views: 5484

Answers (1)

Stevie
Stevie

Reputation: 149

After some experiments with the extension methods, I've found something that does only N + 1 queries instead of thousands of queries, where N is the number of many-to-many relationships.

So, instead of using this lambda in the Where() extension method:

m => actorIds.Any(id => m.MovieActors.Any(ma => ma.Id == id))

You have to use this one:

m => m.MovieActors.Any(ma => actorIds.Contains(ma.ActorId))

After doing some research, I found that EF Core is still incomplete and cannot overcome the N + 1 problem. But still, N + 1 queries instead of a few thousands of queries is a huge improvement.

Upvotes: 3

Related Questions