LINQ to CosmosDB数组交集查询问题：SDK不支持Any方法

Hey there! I’ve bumped into this exact frustration with ORM SDKs before—they often choke when trying to translate nested Any() checks for array intersections into valid SQL. Let’s walk through why this happens and how to fix it without ditching LINQ entirely.

Why the error happens #

When you query a single category (e.g., book.Categories.Contains("Fiction")), your SDK can easily translate that to database-specific array checks (like PostgreSQL’s @> operator or SQL Server’s JSON_CONTAINS). But when you nest Any() to check for overlap between two arrays, most SDKs can’t map that logic to a SQL query they understand—hence the "Method 'Any' is not supported" error.

Practical fixes #

1. Build a predicate with Contains() (great for small target arrays)

If your code’s target array isn’t too large, you can build a LINQ expression that checks if the database array contains any of your target values, using a combination of OR conditions. You’ll need a way to combine expression trees (many developers use PredicateBuilder, or you can roll a simple extension):

// First, define an extension for combining expression trees
public static class ExpressionExtensions
{
    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)
    {
        var invokedExpr = Expression.Invoke(right, left.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>(Expression.OrElse(left.Body, invokedExpr), left.Parameters);
    }
}

// Then use it to build your query
var targetCategories = new[] { "Fiction", "Science" };
Expression<Func<Book, bool>> filter = b => false;

foreach (var category in targetCategories)
{
    var currentCat = category; // Capture loop variable to avoid closure issues
    filter = filter.Or(b => b.Categories.Contains(currentCat));
}

var matchingBooks = dbContext.Books.Where(filter).ToList();

This gets translated to a series of OR conditions that your SDK can handle, e.g., WHERE Categories @> '{"Fiction"}' OR Categories @> '{"Science"}' for PostgreSQL.

2. Embed raw SQL in your LINQ query

If your target array is large or your SDK struggles with the expression tree approach, you can use raw SQL for the intersection check while keeping LINQ for object mapping. Here’s an example for PostgreSQL:

var targetCategories = new[] { "Fiction", "Science" };
var matchingBooks = dbContext.Books
    .FromSqlRaw(@"
        SELECT * FROM Books
        WHERE EXISTS (
            SELECT 1 FROM UNNEST(Categories) AS cat
            WHERE cat = ANY(@targets)
        )",
        new NpgsqlParameter("@targets", targetCategories))
    .ToList();

Adjust the SQL to match your database (e.g., use OPENJSON for SQL Server arrays stored as JSON). This leverages the database’s native array handling while letting you work with strongly-typed Book objects afterward.

3. Check for SDK-specific array methods

Some newer ORM versions have added support for array intersection checks. For example, EF Core 6+ has better support for Enumerable.Intersect() in certain scenarios. You can give this a try (though note it might not work for all SDKs):

var targetCategories = new[] { "Fiction", "Science" };
var matchingBooks = dbContext.Books
    .Where(b => b.Categories.Intersect(targetCategories).Any())
    .ToList();

If your SDK still throws an error here, stick with the first two methods—they’re more reliable across different ORMs and versions.

Wrap-up #

The key is to work with your SDK’s capabilities instead of fighting it. For small arrays, the expression tree approach keeps your code clean and LINQ-native. For larger datasets or stubborn SDKs, embedding raw SQL gets the job done while preserving type safety.

内容的提问来源于stack exchange，提问作者James Hyot