Re-update

Question

Suppose I have a very simple class:

public class State
{
    public int StateId { get; set; }
    public string StateName { get; set; }
    public string Country { get; set; }
}

where StateId is the automatically generated primary key. StateName and County are a unique key. Suppose you want to insert a new state for a given StateName and Country, if it is not already in db. Various solutions offered in other similar issues do not work here:

State state = new State{StateName="New York", Country="US"};

db.States.add(state);  //Does not work because we can get a unique key violation.

db.States.addOrUpdate(state); does not work because the primary key is not known.

finally, the most promising:

var stateQuery = from state in db.States
                 where StateName = "New York"
                 and Country = "US"
                 select state;
State newState = stateQuery.FirstOrDefault();
if(newState != null) return newState;
newState = new State{StateName="New York", Country="US"}
db.States.add(newState)
return newState;
//does not work because it can generate a unique key violoation if there are
//concurrent upserts.

I have considered other questions regarding upserts within the framework of the entity, and there is still not a satisfactory answer to this question. Is there a way to do this without getting a unique key violation associated with simultaneously improving performance from clients on different machines? How can this be handled so as not to throw an exception?

Answer 1

"AddOrUpdate", , , /, "SaveChanges", , .

, SaveChanges:

(-)

, , 2 . , .

lock(stateLock)
{
    using(var db = new MyContext)
    {
        var state = (from state in db.States
                         where StateName = "New York"
                         and Country = "US"
                         select state).FirstOrDefault();

        if(state == null)
        {
            State newState = new State{StateName="New York", Country="US"}
            db.States.add(newState)
            db.SaveChanges();
        }
    }
}

• SQL
• /
  • ,
• + (-).
  • ,
• BulkMerge " ", : http://entityframework-extensions.net/
  • PAID,

. Entity Framework Extensions.

1

AddOrUpdate , "" "" . ( ) AddOrUpdate SaveChanges.

-, , .

3 ( , ):

• SQL ()
• /
• BulkMerge

2:

, 2

using(var ctx = new EntitiesContext()) 
{
    State state = new State{StateName="New York", Country="US"};

    // SELECT TOP (2) * FROM States WHERE (N'New York' = StateName) AND (N'US' = Country)
    ctx.States.AddOrUpdate(x => new {x.StateName, x.Country }, state);

    // INSERT: INSERT INTO States VALUES (...); SELECT ID 
    // UPDATE: Perform an update on different column value retrieved from AddOrUpdate
    ctx.SaveChanges();
}

1:

,

• UserA: AddOrUpdate()// = > ADD
• UserA: SaveChanges()// PK = 10
• UserB: AddOrUpdate()// , SET PK to 10 = > UPDATE
• UserB: SaveChanges()//

2:

, -

• UserA: AddOrUpdate()// = > ADD
• UserB: AddOrUpdate()//Nothing found = > ADD
• UserA: SaveChanges()// PK = 10
• UserB: SaveChanges()//! .

Merge/BulkMerge

SQL UPSERT:

https://msdn.microsoft.com/en-CA/library/bb510625.aspx

BulkMerge ( SQL) UPSERT - .

using(var ctx = new EntitiesContext()) 
{
    List<State> states = new List<State>();
    states.Add(new State{StateName="New York", Country="US"});
    // ... add thousands of states and more! ...

    ctx.BulkMerge(states, operation => 
        operation.ColumnPrimaryKeyExpression = x => new {x.StateName, x.Country});
}

3

, .

. "AddOrUpdate" , , 3-4 , , .

using (var ctx = new TestContext())
{
    // ... code ...

    var state = AddOrUpdateState(ctx, "New York", "US");

    // ... code ...

    // Save other entities
    ctx.SaveChanges();
}

public State AddOrUpdateState(TestContext context, string stateName, string countryName)
{
    State state = new State{StateName = stateName, Country = countryName};

    using (var ctx = new TestContext())
    {
        // WORK 99,9% of times
        ctx.States.AddOrUpdate(x => new {x.StateName, x.Country }, state);

        try
        {
            ctx.SaveChanges();
        }
        catch (Exception ex)
        {
            // WORK for the 0.1% time left 
            // Call AddOrUpdate to get properties modified
            ctx.States.AddOrUpdate(x => new {x.StateName, x.Country }, state);
            ctx.SaveChanges();

            // There is still have a chance of concurrent access if 
            // A thread delete this state then a thread add it before this one,
            // But you probably have better chance to have GUID collision then this...
        }
    }

    // Attach entity to current context if necessary
    context.States.Attach(state);
    context.Entry(state).State = EntityState.Unchanged;

    return state;
}