Entity Framework 3.0 Contains cannot be translated in SQL as it was in EF Core 2.2

Question

I am trying to migrate a Web API from .NET Core 2.2 to .NET Core 3.0 and I have stumbled across the following:

public Dictionary<int, Tag> GetTagMap(IList<int> tagIds = null)
{
    var tags = context.Tag.AsNoTracking();
    if (tagIds != null)
        tags = tags.Where(t => tagIds.Contains(t.TagId));

    return tags
       .ToList()       // explicit client evaluation in 3.0
       .ToDictionary(t => t.TagId, t => t);
}

This used to generate a SQL statement similar to this one:

SELECT TagId, Name FROM Tag WHERE TagId IN (1, 2, 3)

which worked very well for correctly indexed column and a small number of IN values.

Now I receive the following error suggesting that List<>.Contains translation is not supported anymore:

System.InvalidOperationException: 'The LINQ expression 'Where( source: DbSet, predicate: (t) => (Unhandled parameter: __tagIds_0).Contains(t.TagId))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See Client vs. Server Evaluation - EF Core for more information.'

This suggests the LINQ queries are no longer evaluated on the client breaking change, but AFAIK Contains was not evaluated on the client.

Liar

learn.microsoft.com/en-us/ef/core/querying/client-eval, so Any keywords also not work? – Upswell

@BeiBeiZHU - It is useful and I have just realized what is wrong with my code and why explicit client evaluation is such a good improvement. I will post my answer asap. Thanks. – Liar

Answer 1

It's a 3.0 bug, tracked by #17342: Contains on generic IList/HashSet/ImmutableHashSet will throw exception.

Already fixed in 3.1. The workaround (if you can't wait) is to force the usage of Enumerable.Contains, for instance

t => tagIds.AsEnumerable().Contains(t.TagId)

or changing the type of the variable.

Quinsy

hi, can you answer this question? thanks #62802839 – Crosscheck

hi, I don't think the bug has been fixed – Crosscheck

@AlanSmith5482 My tests show that it is indeed fixed. – Quinsy

The bug history shows it was marked as closed/fixed on 3 Sep 2019, reopened and closed again on october and last activity on the bug was on december 2019. If the bugfix is wrong, the EF team may not be aware of it. – Stochmal

I'm getting the same behavior from EF Core 5 – Maleeny

@Maleeny I am on EFC 5.0.7 and not getting the same behavior. IList<int>, ICollection<int>, HashSet<it> - all these work. May be you have something else specific, feel free to create new post (question) and provide repro. – Quinsy

I can confirm this behavior is still present in 5.0.8, with the exact same fix using AsEnumerable(). The bug fix was apparently extremely narrow and does not extend to HashSet<SomeEntityClass>. I do not think simply changing the type of T warrants a new question. The usage, problem, and solution are all exactly the same, and I would bet a hefty sum that such a question would be closed as a duplicate of this one. – Couscous

Answer 2

I understood what was happening and why my code was not behaving as expected (server side evaluation) and why the explicit client side evaluation is actually a very good thing:

IList<T>.Contains was actually evaluated on the client, but List<T>.Contains is evaluated on the server side. Simply replacing IList with List made the code work without explicit client evaluation:

public Dictionary<int, Tag> GetTagMap(List<int> tagIds = null)
{
    var tags = context.Tag.AsNoTracking();
    if (tagIds != null)
        tags = tags.Where(t => tagIds.Contains(t.TagId));

    return tags
       .ToList()       // explicit client evaluation in 3.0
       .ToDictionary(t => t.TagId, t => t);
}

Liar

O so interesting.. IList and List have different behavior.. learning.. – Upswell

That's a sign of a bug - a List is an IList and should behave the same way. Client-side evaluation is a very bad idea no matter how you look at it, which is why it was removed when EF 1.0 replaced LINQ-to-SQL. Too many people loaded entire tables in memory without realizing it.. Why people thought it was a good idea to bring it back, is one of the mysteries of the Universe – Animalism

@PanagiotisKanavos - I think the main advantage to have client-side evaluation is to allow a more natural code (just fetch some data + make some transformation). However, making it implicit was clearly not OK since many failed to see the potential performance issues. – Liar

hi, can you answer this question? thanks #62802839 – Crosscheck