How to prevent retrieving duplicate values from the db using entityframework

Question

I have a situation where i have a counter field in a table named Profile, and on form submit, i will retrieve the counter field and +1 to the counter and update profile table. The incremented counter will be stored in a variable where i will then use to create new records in another table [Bidder]. The problem is when there are multiple form submit at the same time, duplicate record values will be created in the Bidder table

Profile profile = db.Profile.Where(w => w.TenderId == tender_Id && w.IsDeleted == false).FirstOrDefault();
int submission = profile.TotalSubmission + 1;

if (profile != null) {
    profile.TotalSubmission = submission;
    profile.ModifiedBy = user_id;
    profile.ModifiedOn = DateTime.Now;
    db.Entry(profile).State = EntityState.Modified;
    db.SaveChanges();
}

bid.ROId = string.Format("RO{0}", submission);
db.Entry(bid).State = EntityState.Modified;
db.SaveChanges();

How do i prevent duplicate ROId to be created?

Answer 1

You can not rely only on entity framework for your solution. Only the database has a full picture of the stored data. Your different entity context instances don't even know if other instances exist, so coordinating sequence numbers on a global scale is extremely difficult on EF level.

Depending on the frequency of conflicts, two options come to my mind to enforce the uniqueness of the sequence number:

1. Unique constraint
2. Stored procedure for writing the data

Unique constraint

You can create a UNIQUE constraint over the ProfileId and Sequence columns. When you store the data with a duplicate sequence number, you will get an exception. Either the exception itself or one of its inner exceptions will be an SqlException. You can examine the error number of that exception and if it's error number 2627 (if your DBMS is SQL Server; if it is not, check for the similar error in your DBMS), you know it's a unique key constraint violation. In this case you get the current sequence number from the DB and write the data again with a new sequence. You have to repeat that until the insert was successful.

In case you're using SQL server, you can selectively handle a UNIQUE KEY constraint violation like this (using C# 6.0 exception filters):

private bool IsUniqueKeyViolation(Exception exception) {
    Exception currentException = exception;
    while (currentException != null) {
        SqlException sqlException = exception as SqlException;
        if (sqlException != null) {
            return sqlException.Errors.Cast<SqlError>().Any(error => error.Number == 2627);
        }
        currentException = currentException.InnerException;
    }
    return false;
}

//...

//...Code to set up the POCOs before Save...
while(true) {
    try {
        context.SaveChanges();
    }
    catch(Exception exc) when (IsUniqueKeyViolation(exc)) {
        //...Code to update the sequence number...
        continue;
    }
    break;
}

This solution is only practical if the number of conflicts is expected to be small. If the number of conflicts is large, you will see a lot of unsuccessful UPDATE requests to the DB, which can become a performance issue.

EDIT:

As some other answers suggested, you could also use optimistic concurrency with a timestamp column. As long as you only update the DB from your own code, this works fine. However, a UNIQUE KEY constraint will protect the integrity of your data also from changes that don't originate from your application (like migration scripts etc.). Optimistic concurrency does not give you the same guarantee.

Stored procedure

You can create a stored procedure that will set the new sequence number from the last existing number in the same INSERT or UPDATE statement. The stored procedure can return the new sequence number back to the client and you can process it accordingly.

Since this solution will always update the DB in a single statement, it works well for a larger amount of conflicting updates. The disadvantage is that you have to write a part of your program logic in SQL on the DB level.

Answer 2

The uniqueness should be enforced using a unique index or a unique constraint.

You can create these using code first (from MSDN):

public class User
{
    public int UserId { get; set; }

    [Index(IsUnique = true)]
    public string Username { get; set; }

    public string DisplayName { get; set; }
}

or directly via the database.

The counter should be protected using optimistic concurrency:

public class MyEntity
{
    [Key]
    public Guid Id { get; set; }

    // Add a timestamp property to your class
    [Timestamp]
    [Required]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    [ConcurrencyCheck]
    public byte[] VersionTimestamp { get; set; }

    public int Counter { get; set; }
}

If you try to update the row with the VersionTimestamp after it has been changed without re-reading it from the database, you'll get an OptimisiticConcurrencyException e.g. in this test scenario:

// Read the entity
MyEntity entity;
using (var context = new MyContext())
{
    entity = context.MyEntities.Single(e => e.Id == id1);
}

// Read and update the entity
using (var context = new MyContext())
{
    var entity2 = context.MyEntities.Single(e => e.Id == id1);
    entity2.Counter++;
    context.SaveChanges();
}

// Try to update stale data
// - an OptimisticConcurrencyException will be thrown
using (var context = new MyContext())
{
    entity.Counter++;
    context.SaveChanges();
}

Answer 3

If you are using SQL Server 2012 or newer, you can use a Sequence to accomplish this. You would also want to enforce uniqueness through a unique constraint.

public partial class YourEfContext : DbContext 
{
    .... (other EF stuff) ......

    // get your EF context
    public int GetNextSequenceValue()
    {
        var rawQuery = Database.SqlQuery<int>("SELECT NEXT VALUE FOR dbo.SomeSequence;");
        var task = rawQuery.SingleAsync();
        int nextVal = task.Result;

        return nextVal;
    }
}

Another option, if you don't have a version that supports sequences, is to use a stored procedure on the database to issue Id numbers. The stored proc can work in conjunction with an ID table, which it can place an explicit lock on. This means you can request an id from the proc, it can lock the table, read the current number, increment it, store it back in the table, release the lock, and return the id. You would need to call your proc from code to get the new id to assign. The lock on the db side ensures that you are only ever assigned unique values. As long as your id column is only ever given a value assigned by the proc, you will have unique values. You will still be able to assign arbitrary numbers though, which could include duplicates, that can be solved with a unique constraint.

None of this in Entity-Framework specific, though you can still access all this through entity-framework in one way or another.