NoSql query results in The LINQ expression could not be translated. Either rewrite the query in a form that... exception

Question

I am writing a Core 6 application and to query data from Cosmos DB, and have written the following LINQ query upon the Entity Framework database context object:

var assets = _context.Assets
     .SelectMany(asset => asset.Cards
         .Where(card => card.CardType == "dummy"))
     .Select(a => a.Asset)
     .Distinct()
     .ToList();

I am trying to fetch all Asset objects, that have a Card child object with CardType property set to "dummy". When I try to run the application and execute the code, the following exception is thrown:

The LINQ expression 'DbSet<DbAsset>()
    .SelectMany(
        collectionSelector: d => EF.Property<ICollection<DbCard>>(d, "Cards")
            .AsQueryable()
            .Where(o => o.CardType == "dummy"), 
        resultSelector: (d, c) => new TransparentIdentifier<DbAsset, DbCard>(
            Outer = d, 
            Inner = c
        ))' 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.

I followed the documentation from https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/linq-to-sql and used Nesting, example 3 as a base reference to make the selection of Cards I require. I'm then trying to fetch their parent Assets in a Distinct and aggregate them into a list.

The database is several gigabytes and client evaluation would severely impact the performance of this application. I'm sure there is a better option, server side, but don't see what I'm missing.

Answer 1

After checking many resources regarding the subject, I've come to the conclusion that this is impossible to query with EF. I've implemented the Azure DocumentClient and rewritten the query using the SqlQuerySpec to pass a parameterized SQL query, passing the cardNumber parameter and value, instead of doing this using LINQ.

The query I used was (some excessive brackets but I've added a couple of extra parameters that I cut from this example):

SELECT * FROM a WHERE (IS_NULL(@cardNumber) = true OR EXISTS (SELECT VALUE card FROM card IN a.cards WHERE card.cardNumber LIKE @cardNumber))