CODEWITHSUNDEEP
c#entity-framework-core
Shamshiel
Shamshiel

Reputation: 2211

EF Core - Query with many to many relationship very slow

I want to do a "simple" query with Entity Framework Core that involves a many to many relationship.

Currently the execution time of the query is very slow because most of it seems to be run locally. I get the following warnings in my logs:

 The LINQ expression 'where ([categoryId] == [categoryRelationEntity].CategoryId)' could not be translated and will be evaluated locally.
 The LINQ expression 'Any()' could not be translated and will be evaluated locally.
 ...

The warnings basically tell me that nothing could be done in SQL and everything was done locally.

Here is my query in LINQ:

var categoryIds = new int[] { 1 , 2 , 3 };
var result = await _dbSet // MyTable
                    .Include(x => x.Categories) // CategoriesRelation
                    .ThenInclude(x => x.CategoryEntity)
                    .AsNoTracking()
                    .Where(x=>  x.Categories
                        .All(categoryRelationEntity => categoryIds.Any(categoryId => categoryId == categoryRelationEntity.CategoryId)))
                    .ToListAsync();

If I write the SQL myself it should look like this:

SELECT * FROM MyTable
LEFT OUTER JOIN CategoriesRelation ON MyTable.Id = CategoriesRelation.MyTableId
WHERE "CategoryId" IN (1,2,3)

Is something wrong with how I wrote my LINQ? I did some research and all resources that I found suggested I had to do All() and Any().

I tried many different things and nothing worked... I'm so frustrated that I'm very close to just writing it in raw sql...

Upvotes: 1

Views: 1368

Answers (2)

Asherguru
Asherguru

Reputation: 1741

Download Z.EntityFramework.Plus.EFCore from nuget. ToListAsync() causes slower performance. Use AsQueryableAsync() instead for better performance. And use code below

var result = await _dbSet
                   .IncludeFilter(x => x.Categories.Where(x => categoryIds.Contains(x.categoryId)))
                   .ThenInclude(x => x.CategoryEntity).AsQueryableAsync();

enter image description here

Upvotes: 0

Alexbogs
Alexbogs

Reputation: 400

Try to use categoryIds.Contains(categoryId) method instead .Any()

Upvotes: 3

Related Questions