System.Data.SQLite

Hi,

I have a pretty annoying problem when using transactions with the entityframework...

In my application, I'm using transactions as follows :
- when I connect to the database, I immediately begin a transaction
- when the user clicks "save", I commit the transaction and begin a new one

This way, changes made to the databases are only saved when the user wants to (similar to a document-editing application)

It was working fine when I was accessing the database directly, without the entity framework. But now, when I try to commit, I get the following error :

EntityException : The underlying provider failed on Commit.
InnerException : SQLiteException : SQLite error\r\ncannot commit transaction - SQL statements in progress

Apparently it means that there are open DataReaders (I managed to produce the same error when trying to commit on a connection with open readers).

My problem here is that I can't close those readers, since they are handled by the Entity Framework, and I have no access to them !

Is it a normal behaviour for EF to keep the readers open after reading the data ??

To make things clearer, here is a bit of code :

db = new DvdEntities(connectionString);
trx = db.Connection.BeginTransaction();

....

foreach(Movie m in db.Movies) // this opens a cursor in SQLite
{
    // do something
}

...

// the user clicks "save"
trx.Commit(); // the exception happens here
trx = db.BeginTransaction();

Am I doing something wrong ? I know this is not exactly the usual way of using transactions, but I don't see why it doesn't work... Could it be a bug in the provider ?

 Any help would be greatly appreciated :-)

Ugh, that's a major issue.  One thing about Linq however, is that all those changes are memory resident anyway ... can't you just call a Cancel() method on the EF connection to abort those changes?  Afterall, you have to call SaveChanges() for all those changes to get written to the database to begin with.

Hi Robert,

Thanks for your answer.

Initially I intended to take advantage of SQLite's transactional capabilities, but your suggestion is probably a better approach. Since my changes are present in memory anyway, I could simply call SaveChanges when I want to ... well, save the changes ;-). This way I don't need to worry about transactions.

Thanks again !

Regards,
Thomas

While it's probably true that this sort of thing shouldn't generate an error, it's probably not a good practice, especially for databases where opening a transaction can block other connections.  The transaction system isn't intended to be a "save the user's work while she's thinking" system. :)

Hi Robert,

Actually the solution you suggested doesn't work either, for two reasons :

- Deleted objects are not removed from the entity set until I call SaveChanges (that's a serious issue, but not as serious as the next one...)

- When I call SaveChanges, I also get an exception :

SQLite error
cannot rollback transaction - SQL statements in progress

I don't really understand why it wants to do a rollback... anyway, this error also seems to be caused by open cursors... I can easily reproduce the issue by doing something like that :

foreach (Movie m in entities.Movies)
{
    entities.SaveChanges();
}

(I know this code doesn't make any sense, it is just to demonstrate the fact that saving changes while a cursor is open doesn't work...)

If I move the call to SaveChanges after the loop (when the cursor is closed), it works fine.

The trouble is that my application is a WPF application, and the entity sets are bound to ListView controls, so I don't handle the enumeration of entity sets myself. I tried to unbind the ListViews from the entity sets before saving, but it doesn't change anything...

Do you have any idea how to solve this issue ?

I eventually found a workaround for this issue...

Actually, it was probably more a WPF issue than a SQLite or EF issue

Instead of binding the ListViews to the entity sets (directly, or indirectly via a CollectionView), I bind them to Lists obtained by calling ToList on the entity sets... This way, I don't keep open cursors that forbid me to save changes.

Also, I switched back to using transactions, because added or deleted items are not reflected in the entity sets until SaveChanges is called.