none
Set IGNORE_DUP_KEY = ON with EF6 Code First RRS feed

  • 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

    Saturday, November 30, 2013 8:06 AM

Answers

  • 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/


    Sunday, December 1, 2013 3:23 PM

All replies

  • 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/

    Saturday, November 30, 2013 2:18 PM
  • 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

    Sunday, December 1, 2013 5:43 AM
  • 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/


    Sunday, December 1, 2013 3:23 PM
  • This works for me.

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


    Johnny

    Monday, December 2, 2013 6:52 AM