Search code examples
entity-framework-coreazure-cosmosdb

EF core cosmos Foreign key

I am working with EF core and Azure Cosmos DB. I have a problem with the foreign keys. When I load an entity, the related classes are not being loaded. For example, I want to select a user with its roles. It's connected to Users, Tenants, and Roles containers. The data is loaded, but the relations are not loaded:

when loading data from Cosmos DB

I tried different ways of one-one/one-many relations. The last config for the relations is:

var tenantUserModel = modelBuilder.Entity<TenantUser>();
            tenantUserModel.ToContainer(nameof(TenantUsers))
                .HasNoDiscriminator()
                .HasPartitionKey(tu => tu.TenantId)
                .HasKey(tu => new {tu.TenantId,tu.UserId,tu.RoleId});
tenantUserModel.HasOne(tu => tu.User).WithMany().HasForeignKey(tu=>tu.UserId);

But it doesn't load the relation.

Solution

  • Cosmos DB is not a relational database. Despite having a SQL API with a query syntax that's familiar to users of relational databases it's not possible to join entities. There are no such concept as relations and foreign keys which is why EF Core features like Include are not supported when using Cosmos DB.

    Cosmos DB stores JSON documents. The documents are stored in containers and containers are organized into databases. The documents does not have a fixed schema so you can store different document types in the same container. EF Core supports this by using a discriminator in the JSON document that specifies the type of the document.

    There are physical servers storing the contents of a container, i.e. the JSON documents. However, to support horizontal scalability a container can be distributed over multiple physical servers (actually clusters of servers). Each JSON document has a partition key and this key determines which server partition a given JSON document is stored on.

    You typically start out with a single server partition but as your data grows it can be split into multiple server partitions. This happens completely transparent with no downtime so the rule is that if two documents does not have the same partition key they live in separate worlds so to speak. You can't request Cosmos DB to join them because they might live on different servers and the scalability and performance features of Cosmos DB depend on operations being limited to running on a single server (well, single cluster of servers).

    On the other hand, if two or more documents have the same partition key they will always be stored on the same server and in that case Cosmos DB supports some additional features like stored procedures accessing multiple documents (tedious to write) and batch operations (sort of like transactions). However, this is not available in EF Core.

    Bottom line: You can't retrofit a relation model to Cosmos DB. If you need the features of Cosmos DB you will have to design your entire application to work with it. How? Well, it depends heavily on your application. If your data can be organized into hierarchical models (e.g. parent, child, grandchild) then you can store each graph as a single document avoiding the complexity of normalizing your data and introducing relation only to satisfy the needs of the database. However, if your data is truly relational and you need to build many different graphs and aggregates then you can build a system where you capture the incoming changes in one container and then project this source data to materialized views in other containers using the Cosmos DB change feed. If you squint a bit it might look like event sourcing and real-time streaming.