Linq2sql and transactional contexts

See more:

SQLite

Linq2Sql

I've been fussing with Linq2Sql with SQLite (don't go there!) but one thing in my investigations was that people say that the DataContext should be transactional -- you should create it, do your stuff, then dispose of it.

What I don't get is, how do you actually then use a DataContext? Say I have an object that I read in from the database. The object binds to some UI controls, the user changes them, then I want to simply save the changes with context.SubmitChanges().

I totally get that this may be dangerous because the record may have become stale (updated by someone else) but in my case, that's not the case.

If you're curious, it's particularly pathetic with SQLite because when you insert a record, the ID field in the model doesn't update, and if you set it programmatically afterwards it creates a change entry in the data context, which then complains later on that you've updated the primary key field! And of course, you can't just remove changes because the change logs are now read only collections.

The whole Linq2Sql thing seems pathetic. I suppose I could use Entity Framework, ugh.

What I have tried:

At this point, I've got some working stuff by creating a new data context based on the old one, using an extension method:

public static void Update<T>(this DataContext context, T data) where T : class, IEntity
{
  DataContext newContext = (DataContext)Activator.CreateInstance(context.GetType(), new object[] { context.Connection });
  T record = newContext.GetTable<T>().Where(t => (int)t.Id == data.Id).Single();	 // Cast to (int) is required because there's no mapping for int? and we need int? for SQLite to autopopulate the auto-incrementing PK.
  record.CopyFrom(data);
  newContext.SubmitChanges();
}

The above works, creates a transactional DataContext, but this seems like a pathetic way to do things, as I'm instantiating a new context, reading the record from the DB, then copying the new record into the old one (with another extension method.)

Ideas?