CODEWITHSUNDEEP
entity-frameworkentity-framework-6entity-framework-migrations
Eric J.
Eric J.

Reputation: 150118

Undesired Migration for Object Mapped to a View

The Problem

We have an object

public class Foo
{
    [Key, Column(Order = 0)]public virtual int A { get; set; }
    [Key, Column(Order = 1)]public virtual int B { get; set; }
}

that needs to be mapped to an indexed view in SQL Server. Building on the approaches

EF Code First : Mapping nontable objects with Fluent API and https://stackoverflow.com/a/20887064/141172

we first created an initial migration

public override void Up()
{
    CreateTable("dbo.Foos",
        c => new { A = c.Int(nullable:false), B = c.Int(nullable:false) })
        .PrimaryKey(t => new { t.A, t.B });
}

Then an empty migration into which we added SQL to drop the auto-generated table, and then add the index

public override void Up()
{
    Sql(@"DROP TABLE Foos");
    Sql(@"CREATE VIEW dbo.Foos As....");
}

finally in our DbContext, Foo is mapped to the view:

modelBuilder.Entity<Foo>().ToTable("Foos");

This worked just fine, until we added another property to Foo:

[Key, Column(Order = 2)]public int C { get; set; }

We added a new migration to redefine the view

public override void Up()
{
    Sql(@"ALTER VIEW Foos ....");
}

The Alter View migration is correctly applied, but EF believes that it must create a migration to account for the new property.

Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration.

When I run

Add-Migration WhatIsPending

EF generates

public override void Up()
{
    DropPrimaryKey("dbo.Foos");
    AddColumn("dbo.Foos", "C", c => c.Int(nullable: false));
    AddPrimaryKey("dbo.Foos", new[] {"A", "B", "C" });
}

Question

Is there a better approach to mapping an object to a view, such that changes to the object are painless?

If this is the best approach, how can I inform EF Migrations that it does not need to generate the migration?

Upvotes: 3

Views: 142

Answers (1)

DrewJordan
DrewJordan

Reputation: 5314

We couldn't find a way to tell EF to not create a migration, but this approach might help:

  1. When you create the POCO, don't CreateTable() in the migration. if EF wants to create a migration file, fine, but you can comment the code out so only your Sql(@"Create View ..."); runs.

  2. All we had to do then was create the DbSet for the view, not a modelBuilder.Entity entry.

  3. When you need to make changes, start the same way as any other table. Make changes to the POCO, then run Add-Migration and let it create the migration for you. comment out whatever it wants to do, and add your Alter View script in. make sure you make a corresponding one in the Down.

This way you will just have a single migration file for each change.

Upvotes: 1

Related Questions