Proper way to insert a record and assign the id to the Entity object

Question

Scenario: I need to insert a record in a database and use the id resulting from that operation for every other operation needed.

So I've created a class that contains all details of the object that needs to be persisted in the database and added an Id field to store the record id returned by the database operation.

public class MetadataObject {
    protected long Id { get; private set; }
    protected long RegistrationDate { get; private set; }
    protected long FileName { get; private set; }

    public MetadataObject(string fileName)
    {
        FileName = fileName;
        RegistrationDate = DateTime.Now;
    }
}

1) Once I create a MetadataObject, the Id property is 0 (not initialized). Then I make the call to the database.

metadata.Id = RegisterMetadataAndReturnId(metadata);

2) Another thing that I could do is to assign a value to the Id field immediately after I get it back from the database. The method that handles this would look like this

public void RegisterMetadata(MetadataObject metadata)
{
    long recordId;
    var metadataInsert = CreateStoredProcedure("RegisterMetadata");
    metadataInsert.Execute();

    [assign recordId with value received from stored procedure]

    metadata.Id = recordId;
}

Second version looks better in my opinion, the only doubt I have is if the method calling the stored procedure must make sure the metadata object Id property is set before exiting. Or is it better if I just return the Id value and let the calling procedure take care of that.

Is there a better/cleaner way of getting this done?

Answer 1

I would use a slightly modified form of your option #2, which, instead of returning a modified version of the object, returns a new instance of that same entity.

1. There are two main reasons for this. The first is that you can't be entirely sure if the data stored in your DB is exactly that of your original item. The auto-incremented ID field is new, of course, but there could be triggers, constraints or login in stored procedures that might make the data subtly different. It's best to return the newly-stored entity from the DB call and have it fully synced with the DB.

2. The second reason is immutability. If your logic relies on updating a field in your entity after it's created, it means the ID field is mutable and can be changed elsewhere in your code. Having the class be immutable - unchangeable after instantiation - can make a lot of your logic simpler. You can have two threads operate on the same instance without worrying about race conditions between them, and other benefits beside.

Reason #2 is enough, in my opinion, to always clone your object, whether it's cloning-through-database-query or just cloning the instance before returning it.

Answer 2

option 1:

assign the Id in code, not in the database

public class MetaData
{
    public MetaData()
    {
        this.Id = Guid.NewGuid().ToString();

option 2:

return the fully populated object from the insert call on your repository

public class Repository()
{
     public MetaData Insert(MetaData md)
     {
         ///insert to db
         md.Id = returnedId
         return md;