Is there a better SQLite update / insert pattern than this?

Question

I've got a function which updates an existing record or inserts a new one, but it looks very clunky to me.

using (SQLiteConnection db = new SQLiteConnection(AppSettings.DbPath))
{
 db.RunInTransaction(() =>
 {
 var y = db.Table<MyClass>().Where(x => x.GUID == this.GUID && x.VersionNumber == this.VersionNumber).FirstOrDefault();
 bool insert = false;
 if (y == null)
 {
 y = new MyClass();
 y.GUID = this.GUID;
 insert = true;
 }
 y.VersionNumber = this.VersionNumber;
 y.WordCount = this.WordCount;
 y.Updated = DateTime.Now;
 y.Checksum = this.Checksum;
 y.Deleted = this.Deleted;
 y.Source = this.Source;
 if (insert)
 {
 db.Insert(y);
 }
 else
 {
 db.Update(y);
 }
 });
}

The only alternative I can see is a bigger if else, which looks neater but has some argument repitition

using (SQLiteConnection db = new SQLiteConnection(AppSettings.DbPath))
{
 db.RunInTransaction(() =>
 {
 var y = db.Table<PoeFileVersion>().Where(x => x.GUID == this.GUID && x.VersionNumber == this.VersionNumber).FirstOrDefault();
 if (y == null)
 {
 y = new PoeFileVersion();
 y.GUID = this.GUID;
 y.VersionNumber = this.VersionNumber;
 y.WordCount = this.WordCount;
 y.Updated = DateTime.Now;
 y.Checksum = this.Checksum;
 y.Deleted = this.Deleted;
 y.Source = this.Source;
 db.Insert(y);
 }
 else
 { 
 y.VersionNumber = this.VersionNumber;
 y.WordCount = this.WordCount;
 y.Updated = DateTime.Now;
 y.Checksum = this.Checksum;
 y.Deleted = this.Deleted;
 y.Source = this.Source;
 db.Update(y);
 }
 });
}

This is not unique to SQLite of course - I've come up against the same ugly code in Linq-to-SQL. Is there a better pattern?

• Snowbody: Whenever you have common code, factor it out to a new routine. CopyThisToY()
• Mario: I assume simply using REPLACE is not an option? That way you could just skip the query/branch.
• Pimgd: What you may and may not do after receiving answers - Basically, please do not put new code in the question. If you want the new code reviewed, make a new question. If you want to say that you went a completely different direction and feel like sharing, make an answer reviewing your own code. If you just want to say "I applied the changes", maybe make a comment.

Answer 1

The second code block is the clunky one, it has repetition which the first one doesn't. If you wanted to refactor the first example you would need to go higher up and do the check for existing record before you make the call to save or update then have a method for save and one for update, but this would lead to some duplicate code.

From what you have posted I'd stick with the first one.

Answer 2

I've accepted matt's answer, and gone with my original block of code. I ended up modifying it slightly, so I thought I'd post my updated version.

using (SQLiteConnection db = new SQLiteConnection(AppSettings.DbPath))
{
 db.RunInTransaction(() =>
 {
 var y = db.Table<MyClass>().Where(x => x.GUID == this.GUID && x.VersionNumber == this.VersionNumber).FirstOrDefault();
 bool insert = (y == null);
 if (insert) 
 y = new MyClass{ GUID = this.GUID, VersionNumber = this.VersionNumber };
 y.WordCount = this.WordCount;
 y.Updated = DateTime.Now;
 y.Checksum = this.Checksum;
 y.Deleted = this.Deleted;
 y.Source = this.Source;
 if (insert)
 db.Insert(y);
 else
 db.Update(y);
 });
}

I thought about doing this

y = (insert ? new MyClass{ GUID = this.GUID, VersionNumber = this.VersionNumber } : y); 

But I think what I have is more readable.