System.Data.SQLite

I use a TransactionScope to ensure a transaction. If I invoke ObjectContext.SaveChanges more than once within the transaction, I get an exception that reports that the database file is locked. Here is a sample:

using (AdventureWorksLTEntities objectContext = new AdventureWorksLTEntities(connectionString))
{
   using (TransactionScope transactionScope = new TransactionScope())
   {
      ProductCategory newProductCategory = new ProductCategory
      {
         Name = "Test",
         ModifiedDate = DateTime.Now
      };
      objectContext.AddToProductCategories(newProductCategory);
      objectContext.SaveChanges();

      Product newProduct = new Product
      {
         Name = "Test1",
         ModifiedDate = DateTime.Now,
         ProductCategory = newProductCategory,
         ProductNumber = "x1001",
         SellStartDate = DateTime.Now,
      };
      objectContext.AddToProducts(newProduct);
      objectContext.SaveChanges(); // Here I get the exception
   }
}

As a workaround I use an explicit transaction, but I'd rather like to use TransactionScope. Any suggestions?

UPDATE:  The database locked exception occurs not only if SaveChanges is invoked a second time. It also occurs if the program reads entities from the object context after SaveChanges was invoked.

I found the reason for the error. The Entity Framework implicitly (re)opens the database connection by default before sending a query or saving the data. It closes the connection directly after querying and saving. SQLite or the provider seems to have problems with reopened connections. If the connection is closed and reopened, the connection is not able to access the locked database file (which is locked because of the transaction). This may be caused by the fact that ADO.NET connections are taken from a connection pool. 

Anyway, the workaround is to open the connection explicitly before the TransactionScope and close it directly after:

bool connectionWasOpened = false;
try
{
   // Open the connection explicitly if it was not yet opened (in the event that the transaction is executed within another transaction)
   if (objectContext.Connection.State != System.Data.ConnectionState.Open)
   {
       connectionWasOpened = true;
       objectContext.Connection.Open();
    }

   using (TransactionScope transactionScope = new TransactionScope())
   {
      ...

      transactionScope.Complete();
   }
}
finally
{
   if (connectionWasOpened )
   {
      objectContext.Connection.Close();
   }
}

This gets the EF to not open/close the connection implicitly.

I'm not sure if this workaround works in all cases. In complex situations (involving sub transactions) the connection problem may still occur.