CODEWITHSUNDEEP
c#.netentity-frameworkef-code-first
Milos Mrdovic
Milos Mrdovic

Reputation: 1471

Prevent duplicate insert without unique constraints using Entity Framework

This is the classical "upsert" problem. I know how to achieve this using pure SQL, but wasn't able to find the equivalent using Entity Framework (v6, code first).

Given the following entity:

public class Product
{
    [Key]
    public int Id { get; set; }

    public string Sku { get; set; } // Should be unique

    // Other properties
}

I want to create the Save(Product p) method which creates new record if the given Sku does not exist, or updates the existing record if that Sku already exist in the database. The Save method must not create records with duplicate Skus. Also, no unique constraints are defined on the Sku column in the DB.

Checking for duplicates before inserting the record obviously won't work in multi-process environment. I tried wrapping the check in a transaction but that didn't work:

using (var db = new MyDbContext())
using (var transaction = db.Database.BeginTransaction())
{
    // if exists, retrieve and update
    // otherwise insert

    db.SaveChanges();
    transaction.Commit();
}

Any clever ideas?

Thanks!

Upvotes: 3

Views: 3732

Answers (1)

Adem Catamak
Adem Catamak

Reputation: 2009

If you use Entity Framework 6.1+, Index annotation's IsUnique property can be used.

In question, you mention insert without unique constraint, however, in this uniqueness is achieved via code first. I hope this is your want.

public class Product
{
    [Key]
    public int Id { get; set; }

    [StringLength(450)]
    [Index(IsUnique = true)]
    public string Sku { get; set; }

    // Other properties
}

When I run this code, SaveChanges throws DbUpdateExceptionsas expected.

using (DataContext dataContext = new DataContext())
{
    MyClass myClass1 = new MyClass() {Sku = "test1"};
    MyClass myClass2 = new MyClass() {Sku = "test2"};
    MyClass myClass3 = new MyClass() {Sku = "test2"};
    MyClass myClass4 = new MyClass() {Sku = "test3"};

    dataContext.TestTable.Add(myClass1);
    dataContext.TestTable.Add(myClass2);
    dataContext.TestTable.Add(myClass3);
    dataContext.TestTable.Add(myClass4);

    dataContext.SaveChanges();
}

Upvotes: 2

Related Questions