EF Core问题：Any()方法无法筛选全部数据项

Hey there, let's break down why your EF Core LINQ query's Any() method isn't filtering data as expected, and fix it step by step!

First, let's clean up your truncated query for better readability (I'll fill in the obvious missing part for context):

var query = (from supply in context.Supplies 
             where supply.AccountId == accountId 
                && supply.Product.Requests.Any(r => 
                    r.WantToTrade && r.PotentialTradeSupplies.Any(p=>p.CanBeTraded)) 
             select supply)
    .Include(s => s.Product)
        .ThenInclude(p=>p.Requests)
            .ThenInclude(r=>r.PotentialTradeSupplies)
                .ThenInclude(pts=>pts.Sizes)
    .Include(s=>s.Product)
        .ThenInclude(p=>p.Requests)
            .ThenInclude(r=>r.Account)
    .Include(s=>s.Product)
        .ThenInclude(p=>p.Genders); // Assumed truncated property

Common Reasons & Fixes #

1. You mixed up Any() with All() (most likely culprit) #

Your current Any() condition means: "Include this Supply if its Product has at least one Request where WantToTrade is true, and that Request has at least one PotentialTradeSupply where CanBeTraded is true."

If your actual goal is to include Supplies only when all their associated Requests meet that condition, you need to swap Any() for All() in the where clause:

where supply.AccountId == accountId 
   && supply.Product.Requests.All(r => 
       r.WantToTrade && r.PotentialTradeSupplies.Any(p=>p.CanBeTraded))

Double-check your business logic here—this is the most common mistake with these methods.

2. EF Core is doing client-side evaluation (instead of database-level filtering) #

Sometimes complex nested Any() expressions can trip up EF Core's SQL translator, causing part of the filter to run in memory instead of on the database. This leads to unexpected results because not all data is being filtered at the source.

To verify this, enable client evaluation warnings in your DbContext—it'll throw an error if EF Core falls back to client-side processing:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer("your_connection_string") // Swap for your DB provider
        .ConfigureWarnings(warnings => 
            warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));
}

If you get an error, rewrite your query using method-chain syntax (EF Core often handles this better than query expressions):

var query = context.Supplies
    .Where(s => s.AccountId == accountId 
        && s.Product.Requests.Any(r => 
            r.WantToTrade && r.PotentialTradeSupplies.Any(p => p.CanBeTraded)))
    .Include(s => s.Product)
        .ThenInclude(p => p.Requests)
            .ThenInclude(r => r.PotentialTradeSupplies)
                .ThenInclude(pts => pts.Sizes)
    .Include(s => s.Product)
        .ThenInclude(p => p.Requests)
            .ThenInclude(r => r.Account)
    .Include(s => s.Product)
        .ThenInclude(p => p.Genders);

3. Your Include statements are loading unfiltered related data #

Note that your Include calls load all related Requests/PotentialTradeSupplies, not just the ones that match your Any() filter. If you thought the Any() would also filter the loaded related data, that's not how Include works by default.

If you need to load only the matching related data (EF Core 5+ required), use filtered includes:

var query = context.Supplies
    .Where(s => s.AccountId == accountId 
        && s.Product.Requests.Any(r => 
            r.WantToTrade && r.PotentialTradeSupplies.Any(p => p.CanBeTraded)))
    .Include(s => s.Product)
        .ThenInclude(p => p.Requests.Where(r => 
            r.WantToTrade && r.PotentialTradeSupplies.Any(p => p.CanBeTraded)))
            .ThenInclude(r => r.PotentialTradeSupplies.Where(p => p.CanBeTraded))
                .ThenInclude(pts => pts.Sizes)
    .Include(s => s.Product)
        .ThenInclude(p => p.Requests.Where(r => 
            r.WantToTrade && r.PotentialTradeSupplies.Any(p => p.CanBeTraded)))
            .ThenInclude(r => r.Account)
    .Include(s => s.Product)
        .ThenInclude(p => p.Genders);

4. Verify your database data matches expectations #

Sometimes the issue isn't code—it's data. Export the SQL that EF Core generates (use logging or your database's profiler tool) and run it directly against your database. This will tell you if the problem is with the query logic or if your underlying data doesn't match what you expect.

内容的提问来源于stack exchange，提问作者Willem de Jong