Efcore: Cosmos: Add Include support

[AndriySvyryd]

[ajcvickers]

@AndriySvyryd 3.0? Can you do in-memory first?

This is cross-document Include--backlogging.

However...

can you do in-memory Include?

[AndriySvyryd]

Note to implementor: Consider https://github.com/aspnet/EntityFrameworkCore/issues/12776 and https://github.com/aspnet/EntityFrameworkCore/issues/18022

[TeaBaerd]

Preview 5 appears to have this functioning. Is there a reason it was removed in later work?

[AndriySvyryd]

@TeaBaerd The query pipeline had to be rewritten to remove the Relinq dependency

[TeaBaerd]

Bummer. Thank you for the explanation @AndriySvyryd.

[AndriySvyryd]

Don't forget to vote (👍) for the features that are most important for you so we can prioritize better.

[TheFanatr]

If I am missing something please let me know, and I mean no offense by this.

I would like to mention that, in my experience, not having Include support is very detrimental to the usefulness of the Cosmos DB provider; instead of being able to write queries "organically" using the expected APIs, the query must now be done somehow else when attempting to use Include, which, in my case, is a lot. This effectively translates into having to use separate operations, which increases cost. The worse part, however, is that the exception being thrown when the query is executed does not make it obvious as to why the operation failed, so this functionality can lead to wild goose chases, as in the following case.

I was attempting to query an inverse collection property and then filter it into a dictionary with each element key being a property of a navigation property on an element of the aforementioned collection, which would require Include to do efficiently, but instead of it working or me receiving a message in the exception stating that Include is not available in the Cosmos DB provider, I received the following exception when executing ToDictionaryAsync on an IQueryable, far away from the call to Include.

InvalidOperationException: The LINQ expression 'LeftJoin<Alias, Platform, Nullable<Guid>, TransparentIdentifier<Alias, Platform>>(
outer: Where<Alias>(
source: DbSet<Alias>,
predicate: (a) => Property<Nullable<Guid>>(a, "ProfileID") == (Unhandled parameter: __p_0)),
inner: DbSet<Platform>,
outerKeySelector: (a) => Property<Nullable<Guid>>(a, "PlatformID"),
innerKeySelector: (p) => Property<Nullable<Guid>>(p, "ID"),
resultSelector: (o, i) => new TransparentIdentifier<Alias, Platform>(
Outer = o,
Inner = i
))' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

This initially led me to believe that my problem was related to #16730, but as I continued to change the rest of my query to eventually use ToListAsync, I suddenly came to the realization hours later that the one thing I need in order for the processing that needs to be done to not be unwieldy was not working. This means that in order to actually build the dictionary I was attempting to out of this data, I have to individually load the navigation property whose property is needed for the key, using nothing other than separate connections to the database.

Cosmos DB is paid for in RU/s; if people are legitimately expected to use this provider, then it would be appreciated if this basic quality of life feature, that can do what would otherwise require n requests more or raw SQL which is the exact thing being avoided by using supposedly built-in Entity Framework Core functionality, could be implemented.

The following is what was attempted before finding true culprit.

await entry.Collection(profile => profile.Aliases).Query().Include(alias => alias.Platform).ToDictionaryAsync(alias => alias.Platform.Name, alias => alias.Identification);

```csharp
await entry.Collection(profile => profile.Aliases).Query().Include(alias => alias.Platform).Select(alias => new { Platform = alias.Platform.Name, Identification = alias.Identification }).ToDictionaryAsync(alias => alias.Platform, alias => alias.Identification);

```csharp
(await entry.Collection(profile => profile.Aliases).Query().Include(alias => alias.Platform).Select(alias => new { Platform = alias.Platform.Name, Identification = alias.Identification }).ToListAsync()).ToDictionary(alias => alias.Platform, alias => alias.Identification);

The final solution is a lot more wasteful clunky than it would have been with include, but it works.

aliases = new Dictionary<string, string> { };
await foreach (Alias alias in entry.Collection(profile => profile.Aliases).Query().AsAsyncEnumerable())
{
    await context.Entry(alias).Reference(alias => alias.Platform).LoadAsync();
    aliases[alias.Platform.Name] = alias.Identification;
}

I've seen somewhere that Cosmos DB may not even support Include operations, but even in that case it is a nuisance to not be able to complete the operation at least semantically efficiently, as in not having to use an await foreach loop, and just being able to use a one-liner instead.

I would appreciate any help to make what I ended up with more efficient.

[AndriySvyryd]

@TheFanatr Your understanding is mostly correct. Include is indeed a useful feature, but EF will only be able to issue a more efficient query if the included data is in the same collection and the same partition. It's unlikely that we would support other kind of Include, as it would be equivalent to the code you posted above.

Consider embedding the related data by using owned types if performance is a concern.

[MariaCobretti]

can't wait for this feature

[kevinchatham]

Please add support for this 🙏

[braidenstiller]

Is there any news if this feature will be supported in / by v6?

[ajcvickers]

@braidenstiller We're currently figuring that out.