How to gen a sequential unique number by difference app type

Question

I've built a MVC web application and need to generate a unique sequential serial number by application type and year when users submit a form.

The serial number will be stored in the database table MyAppCodeSEQ and the column "AppCode" is a PK of MyAppCodeSEQ.

This is my code below:

public MyAppCodeSEQ GenNewAppCode(string appType)
{
    MyAppCodeSEQ model = null;
    try
    {
        var today = DateTime.Now;

        int maxSeq = _db.MyAppCodeSEQ.Where(x => x.AppType == appType && x.Year == today.Year).Select(x => x.SEQ).DefaultIfEmpty(0).Max();

        maxSeq = maxSeq + 1;
        var appCode = "XX" + appType + today.Year % 1000 + string.Format("{0:000000}", maxSeq);

        model = new MyAppCodeSEQ()
        {
            AppCode = appCode,
            AppType = appType,
            Year = today.Year,
            SEQ = maxSeq,                        
        };

        _db.MyAppCodeSEQ.Add(model);
        _db.SaveChanges();             
    }
    catch(Exception e)
    {      
        Thread.Sleep(100);
        GenNewAppCode(appType);
    }

    return model;
}

The function will be recall while a duplicated key is inserted. however, the below error was occurred again and again.

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. 
---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. 
---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_AppCodeSEQ'. 
Cannot insert duplicate key in object 'dbo.MyAppCodeSEQ'. The duplicate key value is (XXYY18002193).
The statement has been terminated.
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
 System.Data.SqlClient.SqlDataReader.get_MetaData()
 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
 System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
 System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
 System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
 System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
 System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
 System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
 System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
 System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
 System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
 System.Data.Entity.Internal.InternalContext.SaveChanges()

Is there any better solution to generate a serial number?

Let the database do it for you by using the primary key as part of the serial number. — Uwe Keim, Jun 11 '18 at 09:59
Possible duplicate of [C# how to create a Guid value?](https://stackoverflow.com/questions/2344098/c-sharp-how-to-create-a-guid-value) — Søren D. Ptæus, Jun 11 '18 at 10:01
Possibly store all the components of your serial number in their own columns, that way the auto incrementing `int` portion can be the primary key? You can have a `[NotMapped]` column that assembles the ref number for you to use in code. — Wurd, Jun 11 '18 at 10:14
Generating your own IDs is generally not necessary. Just let the database create it for you using an auto_increment field. Then it removes all problems relating to generating duplicates etc. — ADyson, Jun 11 '18 at 10:29

score 1 · Accepted Answer · answered Jun 11 '18 at 19:08

a) NEVER have a primary key that is editable, let the database generate the primary key

b) The fields you are talking about is a "candidate" key and the purpose is usually for "sorting" into a default sequence or for guaranteeing uniqueness

c) A sequential key is pretty much a meaningless piece of data

That said, here's one possibility:

Alter your class (MyAppCodeSEQ) to include an Added field as DateTime. Unique Index on AppCode, AppType, Year to prevent duplicates. Index on AppCode, AppType, Year, Added to preserve sequence.

How to gen a sequential unique number by difference app type

1 Answers1