I'm using .NET Core 2.0
with Entity Framework Code First. I have a table "HData" with these few attributes: Id(PK), Value(Index with ignoring duplicate key setting) and Time. This table may have 10,000,000+ entries.
I insert thousand of entries like this:
dataList.ForEach(i => db.HData.Add(new HData() { Value = i, Time = some.CreateDateTime }));
db.SaveChanges();
I've already set the IGNORE_DUP_KEY = ON
with SQL command, but when the code run until above db.SaveChanges
, it shows me the duplicate error message as:
Cannot insert duplicate key row in object 'dbo.HData' with unique index 'IX_HData_Value'. The duplicate key value is (abcde).
How can I solve it or anyway can catch this exception and skip to next data insert?
Update HData Models Information
public class HData
{
public int Id { get; set; }
public string Value { get; set; }
public DateTime Time { get; set; }
}
OnModelCreating:
builder.Entity<HData>(b => {
b.Property(p => p.Time).HasDefaultValueSql("GETUTCDATE()");
b.HasIndex(p => p.Value).IsUnique(true).HasFilter(null);});
3rd Update:
I set the IGNORE_DUP_KEY = ON
in EnsureSeedData
function, the code below
context.Database.ExecuteSqlCommand("ALTER INDEX [IX_HData_Value] ON [dbo].[HData] REBUILD WITH (IGNORE_DUP_KEY = ON)");
context.SaveChanges();
If you only want the unique values inserted, you can do it this was
dataList.Distinct(l => l.value).ForEach(i => db.HData.Add(new HData() { Value = i, Time = some.CreateDateTime }));