Why is Select().Contains() ok while Any() is not?

Question

Hi all;

I first tried the following code:

var statesIndirectDelete = await States
    .Where(s => deletedCountries.Any(c => c.Id == s.CountryId))
    .ToListAsync();

and that gave me the error:

System.InvalidOperationException
The LINQ expression 'c => c.Id == EntityShaperExpression: 
    LouisHowe.core.Models.State
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.CountryId' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

so I then changed it to:

var statesIndirectDelete = await States
    .Where(s => deletedCountries.Select(c => c.Id)
    .Contains(s.CountryId))
    .ToListAsync();

and that works. But why does the first fail?

And is this (Select.Contains) the best way to:

1. I have a list of Country objects
2. From that I want to get a list of all State objects where they are all the states that have any of the countries as their parent.

In other words, if my country list is { "USA", "CANADA" } I want to get "California", "Quebec", ... but I do not want to get any states/provinces from any other country.

thanks - dave

Answer 1

the translation of the linq expression tree into sql is performed by the database provider, and varies by provider:

sql server:

https://learn.microsoft.com/en-us/ef/core/providers/sql-server/functions

sqlite:

https://learn.microsoft.com/en-us/ef/core/providers/sqlite/functions

Answer 2

@David Thielen, thanks for the feedback,

Based on the last comment, it seems that you have known the specific reason: some linq query can be translate into sql query correctly, such as Any() method.

Are always turned into SQL for the query

For example, where, Select, OrderBy, Skip,Count, Max.

Are sometimes (depending on how it's used and/or the underlying DB vendor) turned into SQL for the query

a.

var result = dbContext.Users.Where(u => u.Name.StartsWith("J"));

StartsWith may be converted to like in sql query, but it depends on the level of support from the database provider.

b. Any method

Are never turned into SQL for the query

1. Client-side evaluation in method chains: Some LINQ methods are evaluated on the client side when executing queries, rather than in the database. This means that these methods cannot be directly converted to SQL statements. Examples: AsEnumerable(), ToList(), ToArray(), AsParallel(), etc.
2. Custom functions or methods: If a custom function or method is used in a LINQ query, EF Core may not be able to convert it to the corresponding SQL statement.
3. Nested queries: Complex nested queries or use of subqueries may not be converted into valid SQL statements by EF Core.

And is there an easy way to see what a given EF query is turned into.

I also provided the related method in the question.

Hope my explanation could help you.

Best Regards,

Jack

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.