How to create a custom key number using C # in ASP.NET core

This solution is based on the use of SQL Server Sequence (available with SQL Server 2012 and for Azure SQL Database). If you cannot use them, you can move on to another answer.

This solution consists of creating a sequence that will automatically calculate the FileId. But you will need to reset the sequence every day at midnight to achieve what you want. This is how you can create a sequence:

Set @YY = (RIGHT(CONVERT(VARCHAR(8), GETDATE(), 1),2))
Set @MM =  SUBSTRING(CONVERT(nvarchar(6),getdate(), 112),5,2)
Set @DD =  RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(DAY, GETDATE())), 2)

DROP SEQUENCE IF EXISTS dbo.DailyFileId;
CREATE SEQUENCE dbo.DailyFileId
    START WITH CAST(CONCAT(@YY, @MM, @DD, '0001') AS int)
    INCREMENT BY 1;  
GO  

      

        
        
        
      

    

(Or something like that, I don't have a SQL Server mechanism to test them. Please don't use it to fix it in the comments if necessary)

To run your script every day, you can use SQL agent. This is the best option in my opinion, but you can also start a new thread in your application that will run the script every day.

If you prefer this option, here's how you can do it. I'll let you decide where you need to put this code in your application:

// Call that method as close as you can from the startup of your application
Task.Run(() => DailyResetSequence());

private async void DailyResetSequence()
{
    while (true)
    {
        using (var dbContext = new DbContext())
        {
            var tomorrow = DateTime.Now.AddDays(1);
            var sleepingTime = tomorrow - DateTime.Now;

            // waiting until tomorrow morning
            await Task.Delay(sleepingTime);

            // See below
            dbContext.ResetSequence();
        }
    }
}

      

        
        
        
      

    

(Please note that I am not handling closing your application. You probably need to cancel the task at this point and maybe some other similar stuff)

Once your sequence has been created, you just need to request that sequence to get your new file ID. SQL Engine automatically handles concurrent calls and ensures that each returned identifier is unique.

It looks like we cannot execute raw queries with EF Core like we could use EF6 ( dbContext.Data.SqlQuery

). One solution would be to manually execute the sql command. I don't know how these operations (get a connection, open it, etc.) are thread safe, so I prefer to be safe and use mecanism lock:

static class DbContextExtensions
{
    private static object DbContextLock = new object();

    public static void ResetSquence(this DbContext dbContext)
    {
        lock (DbContextLock)
        {
            using (var command = dbContext.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = @"Set @YY = (RIGHT(CONVERT(VARCHAR(8), GETDATE(), 1),2))
Set @MM =  SUBSTRING(CONVERT(nvarchar(6),getdate(), 112),5,2)
Set @DD =  RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(DAY, GETDATE())), 2)

DROP SEQUENCE IF EXISTS dbo.DailyFileId;
CREATE SEQUENCE dbo.DailyFileId
START WITH CAST(CONCAT(@YY, @MM, @DD, '0001') AS int)
INCREMENT BY 1;  
GO  ";
                command.CommandType = CommandType.Text;

                dbContext.Database.OpenConnection();

                command.ExecuteNonQuery();

                dbContext.Database.CloseConnection();
            }
        }
    }

    public static long GetNextFileId(this DbContext dbContext)
    {
        long fileId;

        lock (DbContextLock)
        {
            using (var command = dbContext.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = "SELECT NEXT VALUE FOR dbo.DailyFileId;";
                command.CommandType = CommandType.Text;

                dbContext.Database.OpenConnection();

                fileId = (long)command.ExecuteScalar();

                dbContext.Database.CloseConnection();
            }
        }

        return fileId;
    }
}

      

        
        
        
      

    

(Ditto, I can't test it, so don't use it to share fixes / improvements in comments if needed)

Method are extension methods, so you just need to call it like this:

var newFileId = dbContext.GetNextFileId();

      

        
        
        
      

    

To do this, you will need to install: Microsoft.EntityFrameworkCore.Relational .