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 doubt. 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
Idcolumn. Then after you insertyourEnittyin db, afterSaveChanges(),yourEntity.Idwill 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
Idcolumn. Then before you insertyourEnittyin db assignGuid.NewId()to theIdcolumn 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");
}
Although to get the database table's identity key value I need second call must after SaveChanges() in EF. Apart from that this is an exact alternative of my requirement.
Assuming you have a
myEntityvariable and doing so to save it:db.MyEntities.Add(myEntity); db.SaveChanges();then without any additional request to db,myEntity.Idwill contain the inserted identity for your entity.Anyway, you know your requirements better :)