System.Data.SQLite

 My setup:

I am using the entity framework as my DAL to SQLite v1.0.60.0.  I will create a new EF context at my root operation (Say Invoice insert) use it then pass it to my child operations (line items).  I want to protect all this in a transaction for obvious reasons.  If I manage transactions manually it will work as expected, but if I try to use a transaction scope then I get the null reference exception when I tell the EF to commit the changes.

Stack trace:

   at System.Data.SQLite.SQLiteCommand.set_Transaction(SQLiteTransaction value)
   at System.Data.SQLite.SQLiteCommand.set_DbTransaction(DbTransaction value)
   at System.Data.Common.DbCommand.set_Transaction(DbTransaction value)
   at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
   at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
   at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
   at System.Data.Objects.ObjectContext.SaveChanges(Boolean acceptChangesDuringSave)
   at System.Data.Objects.ObjectContext.SaveChanges()
   at SharpInvoice.Core.Invoice.DataPortal_Insert(MyContext context) in D:\source\Working\Stenco\CslaNew\src\SharpInvoice.Core\Invoice.cs:line 243

I should note that I have enlist=false.  If I set enlist=true then I get an ArgumentException: Unable to enlist in transaction, a local transaction already exists with a stack trace of:

   at System.Data.EntityClient.EntityConnection.EnlistTransaction(Transaction transaction)
   at System.Data.Objects.ObjectContext.EnsureConnection()
   at System.Data.Objects.ObjectContext.SaveChanges(Boolean acceptChangesDuringSave)
   at System.Data.Objects.ObjectContext.SaveChanges()
   at SharpInvoice.Core.Invoice.DataPortal_Insert(MyContext context) in D:\source\Working\Stenco\CslaNew\src\SharpInvoice.Core\Invoice.cs:line 243

Am I doing something wrong?  I *REALLY* don't want to manage the transactions myself as I have no clean way of passing them back and forth and will need to do some re-architecting.  Please advise.

 More information.  I spent the morning getting SQLite to compile and it turns out that the EF sets null transactions on commands BEFORE the connection has been set.  This causes the else block in SQLiteCommand.cs(469-470)::set_Transaction to run.  Because no connection is set line 469:

Connection = value.Connection;

is ran which is what causes the NullReferenceException.  I added a null check around just this line (so line 470, where _transaction is set) still executes.  And now everything appears to be peachy and working as expected with TransactionScope.  Can you comment on the potential side effects of this change Robert??

 I should also note for those that are interested that the EF will *NOT* work with Enlist=true (If you are pre-opening your connections).  EF will attempt to enlist your connection into the transactionScope which was already enlisted on open and thus cause the other error I stated above.  Because EF enlists for you enlist=false will allow it to work as intended.

 Not to bump my own post AGAIN but I was forced to make yet another change, this time to support nested TransactionScopes and would like Robert to comment on the side effects of my changes (i have noticed none).

 The situation I was in was UnitTesting.  I was testting my insert / retrive cycle of my objects and so I put my test inside of a TransactionScope as I didn't want the data to stay in the DB after testing.  Each of my inserts (Invoice, LineItems) was also protected by a TS.  What was happening is that SQLiteConnection.EnlistTransaction(743) was being called multiple times; however the transaction was the same transaction that we were already enlisted in.  So I added the following check to simple exit EnlistTransaction if we were being asked to Enlist in something that we were already enlisted in:

//Check nested TransactionScopes
if (_enlistment != null && _enlistment._scope == transaction)
    return; //just requesting that we enlist in the transaction we are already enlisted in.

Any feedback would be appreciated.

I'll add these changes to the library for the next release, which I hope will be soon.  Things have been crazy the last month.

 No need to appologize to me Robert, you have given MUCH more of your time writing the library than I.  I am glad to hear that they will be included and that there are no known side effects.  If you have not yet made the changes I would be happy to provide diffs.

Take care.

Andrew

 Was this problem ever fixed?  I am having what appears to be the same issue, and looking at the code the change Andrew suggested doesn't seem to have been applied in the current 1.0.63 version.  I tried making the change myself but I can't seem to get it to build properly for some reason (the version I built is giving me an "Unable to find an entry point named 'sqlite3_open_interop' in DLL 'System.Data.SQLite.DLL'." error).

Sorry this slipped through the cracks -- I'll fix this for the 64 release.

 still not working, same error
Version: 1.0.66
:(