Set IGNORE_DUP_KEY = ON with EF6 Code First

Question

Hello, I want to use EF6 Code first to set IGNORE_DUP_KEY = ON for a primary key. I find that annotations and fluent API can't achieve it. Since this should be a initialized process, I'm not prefer to use the Code First Migration. Can I execute custom sql script in OnModelCreating method or some where else? How? Thanks!

script may like this: ALTER TABLE [TableName] REBUILD WITH (IGNORE_DUP_KEY = ON)

---

Johnny

Answer 1

If you're not using Migrations, then you can do this with a custom initializer.  see eg http://stackoverflow.com/questions/5040795/adding-index-to-a-table

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 2

Thanks for your response.

Actually, I have already tried this. Code like this:

    public class WeiboContextInitializer : DropCreateDatabaseIfModelChanges<WeiboContext>
    {
        protected override void Seed(WeiboContext context)
        {
            context.Database.ExecuteSqlCommand("ALTER TABLE [Weibo] REBUILD WITH (IGNORE_DUP_KEY = ON)");
 
            context.SaveChanges();
        }
    }
}

And I registered this initializer in Application_Start , but I got the following error:

"Model compatibility cannot be checked because the database does not contain model metadata.Model compatibility can only be checked for databases created using Code First or Code First Migrations.

I think this is because the "ALTER TABLE REUILD" command is unlike other DDL/DML commands, since REBUILD a table my caues some internal table identity changed that lead to a model validation failed.

What I thinked a perfect solution is that to define the IGNORE_DUP_KEY before create a table. Indeed, in database first mode, We define this setting while we define the table.

Code my like this:

CREATE TABLE [dbo].[Weibo] (
    [ID]          BIGINT           NOT NULL,
    [User]        NVARCHAR (50)    NULL,
    [Text]        NTEXT            NULL,
    [CreatedTime] DATETIME         NULL, 
    CONSTRAINT [PK_Weibo] PRIMARY KEY ([ID]) WITH (IGNORE_DUP_KEY=ON)
);

So, I wonder that since EF has released to version 6, is there a convenient and direct way to do this? Like we can Switching off Identity for Numeric Primary Keys in this way:

modelBuilder.Entity<Weibo>().Property(t => t.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
base.OnModelCreating(modelBuilder);

---

Johnny

Answer 3

There's not a first-class way to set IGNORE_DUP_KEY because it's an obscure and rarely-used feature.  Silently ignoring PK violations is not usually good behavior.

The custom initializer should work for you.  Here's a sample that works for me:

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;

namespace test
{
  class Program
  {
    public class Weibo
    {
      public int ID {get;set;}
      public string Name {get;set;}
    }
    public class WeiboContext : DbContext
    {
      public WeiboContext() : base()
      {
        
      }
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
        modelBuilder.Entity<Weibo>().ToTable("Weibo");
        modelBuilder.Entity<Weibo>().Property(e => e.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
        base.OnModelCreating(modelBuilder);
      }
      public DbSet<Weibo> Weibos {get;set;}

    }

    public class WeiboContextInitializer : DropCreateDatabaseIfModelChanges<WeiboContext>
    {
        protected override void Seed(WeiboContext context)
        {
            context.Database.ExecuteSqlCommand("ALTER TABLE [Weibo] REBUILD WITH (IGNORE_DUP_KEY = ON)");
 
            context.SaveChanges();
        }
    }

    static void Main(string[] args)
    {
        Database.SetInitializer<WeiboContext>( new WeiboContextInitializer());

      using (var db = new WeiboContext())
      {
        db.Weibos.FirstOrDefault();
      }

    }
  }
}

Also unless you map a stored procedure to the entity insert, you will get an error when duplicate is ignored:

"Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries"

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 4

This works for me.

It's very kind of you to do this hands-on example and much appreciate for your help!

---

Johnny