PetaPoco - Partial Record Updates

This post is about PetaPoco - "a tiny ORMish thing for your POCOs". Read more on the PetaPoco Project Page.

By default, when PetaPoco updates a record it updates all columns that have been mapped to properties in the associated POCO object. Depending on the use case, this is often fine but there are times where it can inadvertently overwrite fields that have been updated by other recent transactions.

Take this case for example:

var u = user.SingleOrDefault("WHERE name=@0", username);
u.last_login = DateTime.UtcNow;
u.Update();

The problem with this update is that all fields - the user's name, email address, password, everything are re-written to the database. What would be preferable is if just the last_login field was updated. We'll now we can do this like this:

u.Update(new string[] { "last_login" });

or similarly:

db.Update<user>(u, new string[] { "last_login" });

All of the old Update methods now have new overloads that accept this new parameter, which is defined as IEnumerable<string> and should specify the names of the columns (not the properties) that should be updated.

So that's useful except it's a pain to keep track of which columns need to be updated. Well the POCO classes generated by the T4 templates can now track modified properties automatically. To enable this, there's a new option that can be set in the Database.tt file.

TrackModifiedColumns = true;

When false, POCO properties are implemented the old way:

[Column] string title { get; set; }

When true, it generates accessor methods that track the modified columns:

[Column] 
public string title 
{ 
    get
    {
        return _title;
    }
    set
    {
        _title = value;
        MarkColumnModified("title");
    }
}
string _title;

And the base Record class gets a few new methods:

private Dictionary<string,bool> ModifiedColumns;
private void OnLoaded()
{
    ModifiedColumns = new Dictionary<string,bool>();
}
protected void MarkColumnModified(string column_name)
{
    if (ModifiedColumns!=null)
        ModifiedColumns[column_name]=true;
}
public int Update() 
{ 
    if (ModifiedColumns==null)
        return repo.Update(this); 

    int retv = repo.Update(this, ModifiedColumns.Keys);
    ModifiedColumns.Clear();
    return retv;
}
public void Save() 
{ 
    if (repo.IsNew(this))
        repo.Insert(this);
    else
        Update();
}

To explain this:

• OnLoaded - is a new method that if any POCO implements will be called by PetaPoco immediately after populating it from the DB. We use this as an indicator to start tracking modified columns.
• MarkColumnsModified - simply records the names of columns whose value has been changed since OnLoaded has been called.
• Update and Save have been updated to pass through the list of modified columns to PetaPoco when doing an update.

One thing to note about the set accessors is that they mark the column as modified even if the value didn't actually change. This is intentional for two reasons:

1. It makes sure that value actually gets sent to the database no matter what, helping to ensure data consistency.
2. It means the set of queries the database sees isn't dependent on the data entered by users. eg: if two users use the same form to change their profile and one changes their email address and one changes their display name, both will result in the same update query at the database - and presumably the DB can optimize it just once.

These changes are available now in the github master branch now, NuGet package coming soon.