Entity Framework Code First with an existing database view

c# database entity-framework entity-framework-core migration

Question

My team is starting a new project using Entity Framework Code First. We have a new, empty database for the project; the schema will be managed through Code First migrations.

However, there is one piece of data that we will need to access that lives in an external database (managed by a different project). We were given access to this data through a view in our own database that maps to a table in the external database.

Because of this setup, we have two competing requirements:

  1. When creating migrations, Entity Framework should not try to create or edit the view.
  2. At runtime, we should still be able to query/insert/update/delete data through the view using Entity Framework as if it is a regular table.

We've looked at both the [NotMapped] attribute and the .Ignore() method, but both seems to only satisfy one of the two requirements above.

Is this configuration possible using Entity Framework Code First, or will we need to switch to Database First?

We're using Entity Framework Core version 2.1.1.

1
1
8/16/2018 5:45:24 PM

Accepted Answer

There is no configuration which satisfies both requirements, and I don't see how Database First (or Code Second) would help, since the problem is with migration, not mapping.

The requirement #2 can easily be satisfied by mapping the view as table (either conventionally, [Table] data annotation or ToTable fluent API). For EF CRUD operations it really doesn't matter if the actual db object with that name is a table or view, as soon as it supports the corresponding SQL commands.

The requirement #1 cannot be satisfied by the above solution, but can easily be workaround. Since EF Core requires pre created code migrations (no automatic migrations like EF6), you can manually edit the migrations which contains CreateTable in Up / DropTable in Down and simply remove them.

Actually there is also a solution based on custom MigrationSqlGenerator, but it's more complicated and doesn't worth the effort just for a single view.

1
8/16/2018 6:17:29 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow