Cosmos: Translate LeftJoin

[AndriySvyryd]

Note: the main issue tracking cross-document joins is #16920.

[xtellurian]

I think this is blocking AspNetCore Identity Model Customisation.

AFAIK it's trying to do a join, as you can see here which will throw an exception.

FYI you can use AspNetCore Identity without model customization (no roles)

[ajcvickers]

@xtellurian Can you explain in more detail how these things are connected. For example, exactly what model customization is being done and how does it fail?

[xtellurian]

@ajcvickers I can explain what I attempted. I hope this is helpful.

Working Scenario

I'm using Asp Net Core Identity w/ Entity Framework and the EF Cosmos DB provider. Asp Net Core Identity comes with built-in extensions of EF Core DbContext. The simplest use of this is to extend IdentityUser with the generic IdentityDbContext<> like so:

using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
public class MyContext : IdentityDbContext<ApplicationUser>
{
   // implementation 
}
...
// I'm setting up cosmos provider like so
services.AddDbContext<MyContext>(options =>
 {
   options.UseCosmos(cosmos.Endpoint, cosmos.PrimaryKey, cosmos.Database);
   options.UseLazyLoadingProxies();
});

This works as expected.

Failing Scenario

You can extend IdentityDbContext<> further by including custom implementations of Roles etc, like so:

// ApplicationUser and ApplicationRole implementations not shown
public class MyContext: IdentityDbContext<ApplicationUser, ApplicationRole, string> 
{
// implementation
}

This fails (I think) because the Identity Framework tries to do a join that is not supported by the Cosmos DB provider.

I've managed to avoid joins elsewhere, but the Asp Net Core Identity code is not mine and I'm not so keen to re-write it.

[ajcvickers]

@xtellurian It doesn't seem like this is related to the customization. That is, using the Identity model as is with the Cosmos provider likely won't work because of this query. Or am I again missing something that you're doing in the customization?

[xtellurian]

@ajcvickers ah, I think I might be confused regarding the term "customization".

I'm using it because of the title of this page: Identity model customization in ASP.NET Core

Really the issue is with using Roles (not with adding properties/ methods to the UserModel or RoleModel)

[ajcvickers]

@xtellurian Thanks--we'll discuss in triage.

[ajcvickers]

@xtellurian Can you provide some details as to why you would like to use ASP.NET Identity with a Cosmos back-end? (It doesn't immediately seem like a good fit.)

[xtellurian]

@ajcvickers why is it not a good fit? My guess is because other providers have stronger transaction support. I assume Azure SQL/ MS SQL Server is the most commonly used back-end.

We're already using Cosmos DB & EF Core for standard reasons. Cosmos is easy to manage & has lots of features. EF Core provides lots of useful stuff like In-Memory contexts, Lazy Loading etc.

There are two significant reasons we're including ASP.NET Identity with our Cosmos/ EF Core storage:

• Including Users in the model. e.g. the relationship User A is the owner of Dog D (both are entities) can be modeled in the same DbContext, providing all the useful navigation features etc.
• Simplifying infrastructure. We want to minimize the number of storage back-ends we need to provision. Spinning up a new (for example) Azure SQL just for identities feels unnecessary and adds complexity.

Is there a good reason to either keep your ASP.NET Identities in a separate DbContext? Or even a stronger separation - keep your ASP.NET Identities in a separate database?

[ajcvickers]

@xtellurian Thanks for the additional info. I filed dotnet/aspnetcore#15242 to consider updating the Identity model appropriately.

[tyeth]

@ajcvickers why is it not a good fit? My guess is because other providers have stronger transaction support. I assume Azure SQL/ MS SQL Server is the most commonly used back-end.

We're already using Cosmos DB & EF Core for standard reasons. Cosmos is easy to manage & has lots of features. EF Core provides lots of useful stuff like In-Memory contexts, Lazy Loading etc.

There are two significant reasons we're including ASP.NET Identity with our Cosmos/ EF Core storage:

• Including Users in the model. e.g. the relationship User A is the owner of Dog D (both are entities) can be modeled in the same DbContext, providing all the useful navigation features etc.
• Simplifying infrastructure. We want to minimize the number of storage back-ends we need to provision. Spinning up a new (for example) Azure SQL just for identities feels unnecessary and adds complexity.

Is there a good reason to either keep your ASP.NET Identities in a separate DbContext? Or even a stronger separation - keep your ASP.NET Identities in a separate database?

This. Please if someone (@ajcvickers?) knows the answers to these questions and assumptions that I also have please speak up.

[uranio-235]

finally how this end? They gave a good excuse and won't do it, or can we keep waiting and eventually will have join support?

[roji]

@xtellurian @uranio-235 and others, the crucial point here is that like most document databases, Cosmos doesn't support cross-document joins (see #16920 (comment) and below); this would make any sort of cross-document joining very expensive and slow, as it would need to be done client-side, after fetching all records. My personal opinion is that this would be a huge pit of failure, which the EF Core provider should not provide.

At the end of the day, ASP.NET Identity was designed for a relational databases and therefore models data in a relational way; it isn't appropriate as-is for use with a document database. However, there may be a way to make that ASP.NET Identity friendlier to document databases by reconfiguring it to use owned documents (this is just a possible direction to explore - I don't know at this point whether this would be feasible).

[roji]

Note: the main issue tracking cross-document joins is #16920.