Allow FKs to exist in the model but avoid creating them in the database

[chrsas]

Note: This issue is about only not creating the constraint in the database. EF will still treat the relationship as constrained. If you want an unconstrained relationship--that is, a relationship where an FK value is non-null but there is no corresponding PK in the database--then please vote for #13146.

---

I try to use RemoveForeignKey to remove all foreign keys from my DbContext, but it is not usable. Test DbContext

public class Order
{
    public Guid Id { get; set; }

    public string Code { get; set; }

    public IList<OrderDetail> OrderDetails { get; set; }
}
public class OrderDetail
{
    public Guid Id { get; set; }

    public Guid OrderId { get; set; }

    public int Quantity { get; set; }
}
public class BloggingContextFactory : IDesignTimeDbContextFactory<ConsoleDbContext>
{
    public ConsoleDbContext CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<ConsoleDbContext>();
        optionsBuilder.UseSqlServer("Server=.;Database=Blogging;Integrated Security=True");

        return new ConsoleDbContext(optionsBuilder.Options);
    }
}

public class ConsoleDbContext : DbContext
{
    public DbSet<Order> Orders { get; set; }

    public DbSet<OrderDetail> OrderDetails { get; set; }

    public ConsoleDbContext(DbContextOptions<ConsoleDbContext> dbContextOptions) : base(dbContextOptions)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        foreach (var mutableEntityType in modelBuilder.Model.GetEntityTypes())
        {
            if (mutableEntityType.ClrType == null)
                continue;
            // delete all foreign key
            foreach (var foreignKey in mutableEntityType.GetForeignKeys().ToList())
            {
                foreignKey.DeclaringEntityType.RemoveForeignKey(foreignKey.Properties, foreignKey.PrincipalKey,
                    foreignKey.PrincipalEntityType);
            }
        }       
    }
}

The foreign key is still in the generated Snapshot.

[DbContext(typeof(ConsoleDbContext))]
partial class ConsoleDbContextModelSnapshot : ModelSnapshot
{
    protected override void BuildModel(ModelBuilder modelBuilder)
    {
gma warning disable 612, 618
        modelBuilder
            .HasAnnotation("ProductVersion", "2.2.4-servicing-10062")
            .HasAnnotation("Relational:MaxIdentifierLength", 128)
            .HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

        modelBuilder.Entity("ConsoleApp.Order", b =>
            {
                b.Property<Guid>("Id")
                    .ValueGeneratedOnAdd();

                b.Property<string>("Code");

                b.HasKey("Id");

                b.ToTable("Orders");
            });

        modelBuilder.Entity("ConsoleApp.OrderDetail", b =>
            {
                b.Property<Guid>("Id")
                    .ValueGeneratedOnAdd();

                b.Property<Guid>("OrderId");

                b.Property<int>("Quantity");

                b.HasKey("Id");

                b.HasIndex("OrderId");

                b.ToTable("OrderDetails");
            });

        modelBuilder.Entity("ConsoleApp.OrderDetail", b =>
            {
                b.HasOne("ConsoleApp.Order")
                    .WithMany("OrderDetails")
                    .HasForeignKey("OrderId")
                    .OnDelete(DeleteBehavior.Cascade);
            });
gma warning restore 612, 618
    }
}

Further technical details

EF Core version: .Net Core 2.2 Database Provider: Microsoft.EntityFrameworkCore.SqlServer 2.2.4 Operating system: IDE: Visual Studio 2019 16.1.1

[ajcvickers]

@chrsas Can you give some details as to why you want to remove all foreign keys from the model?

[chrsas]

@ajcvickers The project I am working on was an upgrade of an existing system, which have more than 500 tables and lots of data in the database. The original model is dbfirst, and no foreign keys have been established. In addition to non-standard operations, many data in the system have been unable to establish foreign key constraints. The upgrade cycle is so short that we don't have enough time to clean up the data. Besides, some data are not easy to clean up. For example, product A has been deleted, but in order to prevent audit problems, all relevant sales records cannot be cleared. So now the fastest way to update the model is to delete the generated foreign keys.

[ajcvickers]

@chrsas "The original model is dbfirst, and no foreign keys have been established." Does this mean there are no foreign key constraints defined in the database, or that they are in the database but not in the EF model?

[chrsas]

There are no foreign key constraints defined in the database

[ajcvickers]

@chrsas Thanks for the additional information. So, if I can clarify, the database has no foreign key constraints, but when this was scaffolded into an EF Core model there still ended up being FKs in the model? Or did you create the EF Core model in some other way?

[chrsas]

@ajcvickers Thanks for your patience, Sorry for my poor English, My problem is that:

1. No foreign keys are built in the existing database;
2. When the db was scaffolded into an EF Core model there was no FK in the model;
3. In the programing stage, I added some navigate properties in the model, and changed some entities;
4. When the migration was generated, some FKs were there, and they must be removed;
5. I tried to remove these FKs by RemoveForeignKey, but it was not usable, I had to remove them from the migration manually.

[ajcvickers]

@chrsas For navigation properties to work EF needs there to be FKs in the model. This is because the relationship is represented by the FK, with the navigations acting as a kind of view over the FK.

However, EF doesn't care if these FKs really exist in the database or not. So you can keep them in the model to allow navigation properties to work, but then avoid creating them in the database. There currently isn't any way to configure EF to not create FKs in the migration if they are in the model, but you can manually remove them from the migration, like you said above. So I think what you are doing is the best approach available right now.

[chrsas]

@ajcvickers Thanks for your help.

[ajcvickers]

See also dotnet/efcore#2725, but leaving this as a separate issue since FKs may require a different mechanism.

[zejji]

We are in the same position of having a legacy database with EF Core retrofitted. The requirement for foreign keys removes the ability to use navigation properties with this data, which significantly reduces the benefit of using EF Core.

It would be helpful to have a fluent API method to specify that no foreign keys are required for a given relationship, so that migrations never attempt to create them in the first place.

[ajcvickers]

@zejji I'm curious; if you have a legacy database without FK constraints, but you're using migrations to evolve the schema, then why not add FK constraints as you update the schema?

[zejji]

@ajcvickers - it's a large, monolithic database with many years of data and we don't control the client deployments (i.e. there are multiple installs not under our direct control). Accordingly, right now, we don't want to change the performance characteristics or be forced to go through a data sanitization process - we simply don't have the time budget at the moment as there are hundreds of tables in the database - and we just want to be able to use navigation properties in our code rather than ugly explicit joins. Furthermore, we'd be willing to handle data integrity errors at runtime pending a more complete solution. In an ideal world we would begin from a different starting position but it's very much a brownfield project.

[ajcvickers]

@zejji So what kinds of changes are you making to the database?

[zejji]

@ajcvickers - Features are still being added to the application, which means that any new migrations generally create new tables (which can happily include foreign + unique key constraints because they don't contain any legacy data). The issue is that we want to use EF Core effectively with the old database tables.

[ajcvickers]

@zejji Thanks, understood.

[dahovey]

Any updates on this? I am in a similar position. Existing database uses not-null int properties as a sort of optional foreign key, with default value of 0. I would like to establish a relationship with EF Core, but a foreign key should not be created to match the monolithic centralized database.

[ajcvickers]

@dahovey Sounds like you will need dotnet/efcore#13146, since key value zero violates the normal constraint. Both these issues are in the Backlog milestone. This means that it is not planned for the next release (EF Core 6.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.

[OpenSpacesAndPlaces]

I have a related concern for SQLite - that case there's supposed to be a way to just turn off FK constraints globally: https://stackoverflow.com/questions/40273184/how-to-ignore-foreign-key-constraints-in-entity-framework-core-sqlite-database https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#pragma

I was following the SO sample from @bricelam: ";Foreign Keys=False" in the Sqlite Dbconn

But when I try to run it with that set I get: {"Keyword not supported: 'foreign keys'."}

Any Help appreciated!

Running: .Net Framework 4.8, .Net Standard 2.0 Microsoft.EntityFrameworkCore and Related - 3.1.15 SQLitePCLRaw and related 2.0.5-pre20210521085756

---

For now I'm hacking around this with like:

..Open..
db.Database.ExecuteSqlCommand("PRAGMA foreign_keys=OFF;");
db.Database.ExecuteSqlCommand("PRAGMA ignore_check_constraints=true;");
..Create  DB...
..Close..

[KamenRiderKuuga]

So, can we navigate without any real database foreign key constraints now? I'm from dotnet/efcore#20744

[simo9000]

+1 to this

[CorporateActionMan]

+1 for this feature

[ajayvikas]

+1

[reservoir-dogs]

+1

[roji]

Everyone, to support this feature please add your vote on the top-most issue (via :+1:), and refrain from additional +1 posts - these aren't taken into account when we look at issues and their vote counts.

[Ludogo]

+1 ! please !

[bricelam]

+1 Our infrastructure runs on MySQL 4.1 so foreign keys aren't supported. :trollface: (Just kidding. Every time I see this issue though, it reminds me how awesome it was to switch from MyISAM to InnoDB.)

[joutvhu]

+1

[drronnie85]

+1

[nillkitty]

+1

[roji]

Everyone, please upvote (:+1:) the top-most comment above, posting +1 isn't taken into account and doesn't really help us prioritize...

[mehmetuken]

+1

[atrauzzi]

Just wanna add that I am extremely blocked by this. I need to be able to map at least one side of polymorphic relations. But as soon as two entities target the same property on another entity, I get an "impossible" FK in the owning-table.

[ajcvickers]

@atrauzzi You should be able to edit the migration so that the FK constraint is not created in the database.

[atrauzzi]

@ajcvickers - It's a bit more complicated than that unfortunately as I'm using db.Database.EnsureCreatedAsync() (in my tests).

My actual production DBs are not currently being managed by EF (legacy stuff, like so many others here).

The fact that this is hard-wired at such a low level is a bit problematic. I really just need a flag that I can set on my context which tells EF to not try and create these FKs.

[okramarenko]

are there any updates on this? that would've been extremely useful!

[ajcvickers]

@okramarenko This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 7.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. Make sure to vote (👍) for this issue if it is important to you.

[lwestfall]

For those who are tired of manually editing every migration that might create an unwanted FK constraint - do note that you can create custom subclasses of CSharpMigrationOperationGenerator and your DB provider's SqlGenerator class that overrides the Generate method, stripping out FK's conditionally from your migrations. I did this for my company and it works well as a workaround. Here's a decent stackoverflow answer I used to get started (along with studying this part of EF Core source code)

Sadly I can't share my exact implementation publicly, but I could potentially help answer questions about how I did this. In my case I am stripping out any FKs that would end up outside of a given schema.

[ajcvickers]

Note from triage: consider also not configuring the unique constraint on the principal key by convention--see dotnet/efcore#28226

[qsdfplkj]

I don't see why this isn't easily implemented in ef core. Normally entity has a navigation property that we like to include (join). Now we have a tabel for which we don't want the foreignkey but we still need to use include /join. Isn't this just the definition of a right join? With an addition deletebehavior that allows orphans.

[lwestfall]

I don't see why this isn't easily implemented in ef core. Normally entity has a navigation property that we like to include (join). Now we have a tabel for which we don't want the foreignkey but we still need to use include /join. Isn't this just the definition of a right join? With an addition deletebehavior that allows orphans.

I'm sure Microsoft is hiring. Send in your resume since you've figured it out

[ajcvickers]

@qsdfplkj I don't think anyone said this was difficult.

[qsdfplkj]

It was just me thinking out loud because I find all those work arounds... and I would prefer a framework solution over any of them. Each time I run into this. Although I don't like orphan data the case I usually need it is when the orphans are updated in a separate process (usually some long running calculations, for which we need the joins but not the FK). So we would prefer the orphans to stay "disconnected" from the main graph.

[myozawlatt]

up

[AvrahamYBock]

+1

[cos55ap]

Hi Conclusion (if you do not plan to read details):

• Fk as well indexes must be optional;
• Db normalisation can be done keeping Entity Relaiton in child table as well in parent table. EF design is compatible only when keeping relaitons in ChildTable.
• Db FK Constraints are out of date techniques turned into "bad practice" today, not recommended for large database (my case between 1.5 & 4 Terra Bytes) because db's are not horisontally scallable, but EF instances yes (work with constraints in memory, caching & dbReaders will horisontally scale your db, there you can check all the constraints you need).

Details: to understand the reason why developers are asking of this, you need to corelate "db Execution Plans" with the idea that a relation can be designed int two ways (not one as EF force you).

Initial case Order/OrderDetail EF works as design because the child (OrderDetail) keeps the reference for his parent (Order), having an index on OrderDetail.OrderId is correct (db constraints for this relation should be optional; different topic but happy to share my knowledge)

Db Normalisation can be done with a second type of relation, when Parent keeps the reference to his child. An axample can be when Person keeps the AddressId as reference for Address. Or when Address is normalized with Country or Region. So one....

public class Person
{
        public long? AddressId { get; set; }
        public Address Address { get; set; }
}
public class Address
{
        public long? CountryId { get; set; }
        public Country Country { get; set; }

        public long? RegionId { get; set; }
        public Region Region { get; set; }
}

The SQL query will be something like...

SELECT 1 
FROM ContactPerson p
JOIN Address a on a.Id = p.AddressId
JOIN Country c on c.Id = a.CountryId
JOIN Region r on r.Id = a.RegionId
Where p.Id = 1

In this case EF generates an Index over ContactPerson.AddressId, Address.CountryId, Address.RegionId All 3 indexes are bad design from database perspective, because they will never be in use, but the index maintanability cost exists. We all know db writers are not horisontally scale.

If you check the Execution plan you will see on above queries that: ContactPerson.PK is used to filter the records from ContactPerson table Address.PK is used to satisfy the join syntax Country.PK is used to satisfy the join syntax Region.PK is used to satisfy the join syntax

In all 3 cases AddressId, CountryId, RegionId are Included columns (columns part of the SELECT statement, not part of the WHERE/ON statement).

I have verified multiple options to keep db performant:

• modelBuilder.Entity().Ignore(e => e.Address); will make .Include() to not work
• modelBuilder.Entity().Navigation(b => b.Address).UsePropertyAccessMode(PropertyAccessMode.Property); does nothing

[atrauzzi]

Encountering this again currently when working with "polymorphic" columns (a column that will reference IDs from more than one table).

I realize this prevents referential integrity, but I still think the best thing here would be the option to create a navigation property that doesn't result in EF trying to enforce a foreign key in the schema.

It's fine if internally EF still uses some notion of a foreign key to model the navigation property. But there just needs to be a way to prevent that from propagating through to what gets created in the DB:

        modelBuilder
            .Entity<MyOwningSide>()
            .HasMany((owner) => owner.Owneds)
            .WithOne()
            .HasNavigationPropertyWithoutForeignKey((owned) => owned.DifferentTypesOfOwnerIds);

(obviously the .HasNavigationPropertyWithoutForeignKey needs a better name!)

And again, while I can handle this when manually editing migrations, it will not work for .EnsureCreated... as that creates and applies everything from the EF model in one go.

[nh43de]

Also encountering this - we have a "hot" reporting data table where data is updated from another system and data points can move in and out of this table. We have a "meta" table with a compound key that points to our reporting data so we can assign attributes to data points that come in. Having an FK that points to the hot table prevents the data from being removed from the table, so we have resorted to coding LINQ joins instead of nav properites.

[qsdfplkj]

I was having the same questions but I think we could try to solve it differently. Of course we can just write joins ourselves but the data integrity will have to be managed in another layer and that is just error prone. We should have different designs of our systems and databases such that we don't have these concerns. Having out of sync data that could or used to be linked to other data is just a "smell" rooted in the monolithic designs we have for the database. I think having distributed databases that have copies of the data that are kept in sync using messaging might help us in keeping foreign keys in the database.

[nh43de]

I was having the same questions but I think we could try to solve it differently. Of course we can just write joins ourselves but the data integrity will have to be managed in another layer and that is just error prone. We should have different designs of our systems and databases such that we don't have these concerns. Having out of sync data that could or used to be linked to other data is just a "smell" rooted in the monolithic designs we have for the database. I think having distributed databases that have copies of the data that are kept in sync using messaging might help us in keeping foreign keys in the database.

Consider a nullable navigation property in a 1:1 relationship with the dependent entity. Currently, a properly specified FK meets that requirement in the sense that if the FK column has a value defined, then the dep entity exists suredly because there is no such thing as an optional FK (barring nocheck), and when the FK column is null then the dep entity doesn't exist as expected. Because FKs must be defined on a unique column or combination of columns, you are guaranteed a 1:1 relationship. Having alternate / unique keys defined on both sides of the relationship to meet the definition of an optional 1:1 relationship is a valid use case of a dbms system imo. An FK is just a stricter implementation of this requirement (dep entity guaranteed to exist if value present). Dbs are kept in sync with messaging as you prescribe but at the end of the day reports are generated by joining these data together, and an FK is too strict for this use case.

Basically, creating an optional 1:1 relationship defined as an AK on both sides should be valid within the context of EF. No need for deleting or disabling FKs manually, if the user doesn't need the existence of data to be guaranteed. This would solve the OP's problem.

That being said, an FK does even more, in that it makes sure the data types and columns in the relationship are identical (e.g. value length / precision).

[houbi56]

@ajcvickers Is this planned for 8.0?