Support "unconstrained" foreign key relationships

[ajcvickers]

EF Core uses a "foreign key" property that is matched to a "principal key" to define a relationship, where one or both of these may be shadow properties. For relational databases, these relationships are almost always backed by a constraint which means that if an FK property has a value, then there must exist a principal key that matches. We use this information to make decisions/optimizations on which queries to generate. In #9470, we decided to maintain these semantics across providers.

However, there are some cases where the FK and PK exist and define the relationship, but are not constrained in the same way as a relational constraint would do. Specifically, this means that just because an FK property has a value doesn't mean that there is necessarily a matching PK value. This means:

• the relationship is always optional, even if the FK isn't nullable
• we can't assume that because the FK is set that it means a principal must exist in the database
• and therefore we can't assume that getting all the principals will bring in all dependents (same as for optional relationships today)
• fixup needs to decide what to do if a relationship is severed but the FK is non-nullable

[mariusGundersen]

Since there is no responses to this, can I assume there is no solution to this problem? I have this exact requirement but no way to implement it in EF Core. Every migration I now add I have to remove the constraints added in the migration scripts

[AndriySvyryd]

@mariusGundersen That's the best workaround until #2725 is implemented

[Basssiiie]

Since it's mentioned a few times in different issues, are there any plans to support something like this in a future release? It's quite frustrating having no control over created constraints.

Thank you for your time! :)

[ajcvickers]

@Basssiiie This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 5.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

Please vote (👍) for the issue to indicate your interest; based on these votes, this issue is currently 73rd in the list of most voted for features not yet implemented.)

[TonyValenti]

I've needed a solution for this a number of times. This is particularly necessary when synchronizing data between a local and remote database where you can't make a global snapshot to ensure consistent state across everything.

[lixaotec]

@TonyValenti how did you workedaround?

[TonyValenti]

I haven't had a chance to test this but an associate recommended just running a sql script to delete the foreign key constraint and using EF core as is. Not sure if That will work though.

[qsdfplkj]

As a workaround we consider using placeholders for this. Tough implications of this could be that all values should be nullable.

[drewberk]

What is the status of this issue? It is the exact problem I am running into.

I have records from a 3rd party system that are mapped to entities in our application.

The 3rd party system tracks employee hours against projects. That system has its own project ids and user ids needed for invoicing in that system. When we import those hour logs into our system they map to our internal Users and projects via the project and user id of the the 3rd party system.

Our tables:
Project

• id : int
• ImportProjectId : string
• Other project columns...

User

• Id : int
• ImportProjectId : string
• Other user columns...

3rdPartyHours

• Date : datetime
• UserId : string
• ProjectId : string
• Other 3rd party columns...

Since the 3rd party hours are imported programmatically, there will be times when a new employee or new project is added to the 3rd party system that has not been created in our system yet. When that happens we have runtime code that identifies the missing User or Project.

The problem is that we need navigation properties from the imported hours to our projects and users. We use the 3rd party project and user ids as navigation properties to our projects and users. Since EF creates foreign key constraints we get errors when the 3rd party hours are imported and the new user or project has not been created and associated in our system yet.

The functionality would be much more useful if the 3rd party hour entities allowed for navigation properties that could be null. As stated before, we can handle that situation at runtime and notify the user that a new project or user needs to be added in our system to map to the 3rd party project or user.

[ajcvickers]

@lwestfall Note that this issue is not about EF Core creating the constraints in the database--that is tracked by #15854. Rather this issue is about allowing EF to work with different foreign key semantics. Simply removing the database constraint, while required for this, is not sufficient to make EF Core work with these different semantics.

[lwestfall]

@ajcvickers I appreciate the correction, I've moved my comment to #15854 where it's more relevant

[ajcvickers]

Note for team: When we use FKs between Cosmos documents, they are essentially unconstrained, since Cosmos does not have FK constraints. See https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/modeling-data#what-about-foreign-keys

[rjlacanlaled]

Happy new year!! Any update on this?

[AndriySvyryd]

@rjlacanlaled It's on our radar, but unlikely to make it into EF 10

[ta1H3n]

We have a number of use cases where this would be very helpful.

For example, we have a "loose" navigation between a person, and a phone call history, where we use the phone number as the key to figure out which calls were made by which person. There is no constraint on the database, and I am having great success using EFCore navigations for the relationship.

builder.Entity<Person>()
    .HasMany(x => x.Calls)
    .WithOne()
    .HasPrincipalKey(x => x.NormalizedPhoneNumber)
    .HasForeignKey(x => x.NormalizedPhoneNumber);

However, there are problems with it today:

1. EFCore does not allow me to change the phone number of the person, because it is "the principal key".
   • I can workaround this by making a separate DbContext without the navigation, and use it to update the number
2. Migrations keep trying to change my schema