CODEWITHSUNDEEP
c#linqazuregenericsazure-cosmosdb
Mark Redman
Mark Redman

Reputation: 24515

How do I use Generics to create multiple/nested SelectMany joins in Linq for DocumentDB

I am trying to add a method to a Generic Repository for Document Db.

I would like it produce SQL that looks similar to this (but generic):

SELECT VALUE c FROM c
JOIN versions IN c["versions"] 
JOIN groups IN versions["userGroups"] 
JOIN users IN groups["users"] 
WHERE c.type = "Approval" and users["userId"] = "xxx"

I have tried this...

public async Task<ListResult<TResult>> SelectManyAsync<TEntity1, TEntity2, TEntity3, TEntity4, TResult>(Expression<Func<TEntity1, bool>> condition01, Expression<Func<TEntity1, IEnumerable<TEntity2>>> condition02, Expression<Func<TEntity2, IEnumerable<TEntity3>>> condition03, Expression<Func<TEntity3, IEnumerable<TEntity4>>> condition04, Expression<Func<TEntity4, bool>> condition05) where TEntity1:class
        {
            var feedOptions = GetFeedOptions();

            var query = DocumentDbContext.Client.CreateDocumentQuery<TEntity1>(DocumentDbContext.DocumentCollection.DocumentsLink, feedOptions)
                .Where(condition01)
                .SelectMany(condition02)
                .SelectMany(condition03)
                .SelectMany(condition04)
                .Where(condition05)
                .AsDocumentQuery();

// .. removed code for brevity

}

which results in something like this:

SELECT VALUE tmp FROM root 
JOIN tmp IN root["versions"] 
JOIN tmp IN tmp["userGroups"] 
WHERE (true AND (tmp["userId"] = "xxx"))

but I get the error:

The input set name or alias 'tmp' is specified more than once in the FROM clause.

Is it possible to create something like this using generics and how could we specify different values for "tmp". or is this not the correct approach?

Upvotes: 0

Views: 259

Answers (1)

Olha Shumeliuk
Olha Shumeliuk

Reputation: 740

Not sure if this bug or not, bug such query wont work. You need to use nested selectMany.

Following example should work:

public async Task<IEnumerable<TRes>> Query<T, TRes>(
        string docCollection,
        string partitionKey,
        Func<IOrderedQueryable<T>, IQueryable<TRes>> func,
        string continuationToken = null)
    {
        var queryable =
            CosmosClient.CreateDocumentQuery<T>(
                CollectionUri(docCollection),
                new FeedOptions
                {
                    MaxItemCount = 100,
                    RequestContinuation = continuationToken,
                    EnableCrossPartitionQuery = false,
                    PartitionKey = new PartitionKey(partitionKey),
                    PopulateQueryMetrics = true,
                });

        return await func(queryable).AsDocumentQuery().ExecuteNextAsync<TRes>();
    }


await Query<Doc, string>("collection", "pk",
            (IOrderedQueryable<Doc> x) => x.SelectMany(p => p.Versions.SelectMany(d => d.UserGroups.SelectMany(k => k.Users.Select(u => u.Name)))));

Upvotes: 1

Related Questions