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

David Thielen 2,796 Reputation points
2023-06-05T03:30:01.6166667+00:00

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

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
726 questions
{count} votes

1 additional answer

Sort by: Most helpful
  1. Jack J Jun 24,496 Reputation points Microsoft Vendor
    2023-06-06T08:51:47.39+00:00

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