How to generate an unique number while saving the data in MVC using Entity Framework as per the max Identity key of the table?

Question

I have to short the below code.

I need to generate and save a unique reference number in the column as R00001, R00002, R00003, etc. Here 1, 2 & 3 are based on the identity key values.

For this, I have written db.SaveChanges() twice. First I save the data and select that saved data and update it by generating that unique key. Is it possible to generate that key and passing while saving at once?

So, I do not need to select the data after saving and updating it by again calling db.SaveChanges() twice. Here is my MVC Controller (MVC action) code.

[HttpPost]
public ActionResult Create(RateTable model, HttpPostedFileBase[] files)
{
  RateTable objRC = null;
  if (model.RatecontractId >= 1)
  {
      objRC = db.RateTables.FirstOrDefault(rc => rc.RatecontractId == model.RatecontractId);
  }

  if (objRC == null && model.RatecontractId <= 0)
  {
      objRC = new RateTable();

      objRC.CountryCode = model.CountryCode;
      objRC.CompanyCode = model.CompanyCode;
      objRC.CustomerName = model.CustomerName;
      objRC.Remarks = model.Remarks;
      objRC.CreatedDate = DateTime.Now;
      objRC.CreatedBy = User.Identity.Name;

      //Saving data into the database table.
      db.RateTables.Add(objRC);
      db.SaveChanges();

      string uniqueRefNo = "R" + string.Format("{0:D5}", objRC.RatecontractId); 
      int rateContractId = objRC.RatecontractId;

      //For updating the unique reference number of the contract.
      RateTable result = (from rc in db.RateTables where rc.RatecontractId == rateContractId select rc).SingleOrDefault();
       if (result != null)
       {
          result.UniqueRefNo = uniqueRefNo;
          db.SaveChanges();
       }
   }
}

I save the data in UniqueRefNo column as shown below:

RatecontractId  CountryCd   CompanyCd   UniqueRefNo
---------------------------------------------------
1               C0U001      C0M001      R00001
2               C0U001      C0M001      R00002
3               C0U001      C0M001      R00003
4               C0U001      C0M001      R00004
5               C0U001      C0M001      R00005

I want to reduce this code because I am saving files also which needed to be saved in the format like R00001.pdf, R00002.pdf, R00003.pdf, etc.

Here is the file saving code with the renamed file name as per the unique reference number.

if (files != null)
{
  foreach (HttpPostedFileBase file in files)
  {
    if (file != null)
    {
      //To generate the file name of the rate contract.
      var fileName = "RC" + string.Format("{0:D5}", objRC.RatecontractId);
      var extension = Path.GetExtension(file.FileName);
      fileName = fileName + extension;

      string path = string.Format("~/Documents/PDF/{0}", fileName);

       if (System.IO.File.Exists(Server.MapPath(path)))
          System.IO.File.Delete(Server.MapPath(path));
       file.SaveAs(Server.MapPath(path));

       RateTable resultForfiles = (from rc in dbTender.RateTables where 
       rc.RatecontractId == objRC.RatecontractId select rc).SingleOrDefault();
       if (resultForfiles != null && (extension.ToLower() == "pdf" || extension.ToLower() == ".pdf"))
       {
          resultForfiles.PdfFilePath = fileName;
       }
       if (resultForfiles != null && extension.ToLower() != "pdf" && extension.ToLower() != ".pdf")
       {
          resultForfiles.WordExcelFilePath = fileName;
       }
       dbTender.SaveChanges(); //Third thime calling `SaveChanges()`
     }
   }
}

Answer 1

As an option, you can create an identity column and a computed column, in the computed column, calculate the desired value based on identity column. For example:

CREATE TABLE [dbo].[Table1](
    [Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(50) NOT NULL,
    [Code]  AS (('R'+ REPLICATE('0',(5) - LEN(CONVERT([NVARCHAR](50),[Id])))) +
        CONVERT([NVARCHAR](50),[Id]))
)

Then when you insert into table using EF or a query like this:

INSERT INTO [dbo].[Table1]([Name]) VALUES (N'Something')

The result is:

| Id   | Name           | Code              |
.............................................
| 1    | Something      | R00001            |

Important Note

Before choosing any solution, no matter computed column, sequence, or trigger, make sure you need to store such computed code in database. I think you don't need to generate and store the reference code at database side, it's enough to use the key of the entity as a reference number / foreign key wherever a logical or physical relation is needed.

Using Identity column: You can define an identity Id column. Then after you insert yourEnitty in db, after SaveChanges(), yourEntity.Id will contain the new inserted identity value and you don't need an additional call to db. Then you can use the same id as reference (for example as file name).

Using Guid column: You can define a Guid Id column. Then before you insert yourEnitty in db assign Guid.NewId() to the Id column and then you can use the same guid as reference (for example as file name).

Consider the following points:

• The name or key of the file in your storage and the key of the entity in your database is your territory and end user has nothing to do with it.
• You may want to use a storage like Azure Blob, SQL Database, File System and so on and in any of these storage technologies, you may use different key/name for the file.
• The only thing that end use can say is, "I'd like to see the link of files with a friendly name", or "I'd like to have a friendly name when downloading a file" and its really easy for you to provide such friendly name in grids or when download.

My advice is store and retrieve the file using a key, like a guid key. Then in your download action you can easily send the file with a different name for download, for example:

public ActionResult Download(Guid id)
{
    var file = Server.MapPath($"~/Content/uploads/{id}.pdf";
    return File(file, "application/pdf", "something.pdf");
}