EF Code-first, how to insert primary key to another field when inserting?

Question

This is my entity that I will insert into the database:

public sampleEntity
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int PrimaryKey { get; set; }

    [Required]
    public string Ticket { get; set; }
}

Ticket is in the format like

string.Format("{0:yyyyMMdd}-{1}", DateTime.Now, PrimaryKey);

So when I add this entity to the context, primaryKey always is 0, so Ticket value always be '20170315-0'

For now my solution is

// first add row and save
SampleEntity sample = new SampleEntity {
    Ticket=DateTime.Now.ToString("yyyyMMdd")
};
context.Samples.Add(sample);
context.SaveChanges();

// then find it out and update
var latest = context.Samples.OrderByDecending(p => p.PrimaryKey).First();
latest.Ticket += sample.PrimaryKey.ToString();
context.SaveChanges();

How can I set Ticket value according to primaryKey value when SaveChanges() without update?

Answer 1

You have DatabaseGeneratedOption.Identity option for primary key.

That means that only SQL Server know that ID, and that knowledge materializes only in the moment of the actual INSERT into database (as related column in database is some kind of IDENTITY column).

To understand that think, for example, of two applications which are inserting new records into database simultaneously - they will receive different keys, but you cannot know for sure which application receive which key.

Entity Framework will generate two requests for SaveChanges - first one is INSERT, and another one is SELECT to receive that generated key.

Only after that your code will know actual key and can be able to use it for your ticket calculations - so basically you cannot avoid another UPDATE with EF.

What you can do though is to change Primary Key type for something controlled by your code rather than by database - for example, random GUID; in this case you will know ID before insert and can use it in any way you want.

But using say GUID for primary key results in other complications, which in most cases won't worth it, like non-sequential insert results in often index rebuilds, still some probability of keys collision, more space to keep column etc.

Another option would be to have calculated column OR similar logic for ticket column in application, so you will have separate Date column and separate Id column, but for ticket you will either always apply concat logic whenever you need it, of create calculated column which will only return values (and thus will be read-only for database and EF).