EF Core - Query with many to many relationship very slow

c# entity-framework-core

Question

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...

1
1
1/30/2020 9:53:47 AM

Popular Answer

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

3
1/30/2020 10:29:18 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow