Answered by:
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
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/
- Edited by davidbaxterbrowneMicrosoft employee Sunday, December 1, 2013 3:26 PM
- Marked as answer by Johnny Qian Monday, December 2, 2013 6:53 AM
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/
- Edited by davidbaxterbrowneMicrosoft employee Sunday, December 1, 2013 3:26 PM
- Marked as answer by Johnny Qian Monday, December 2, 2013 6:53 AM
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