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

[chrsas]

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...