0

Has anyone been able to create temporary tables or triggers using Microsoft's Entity Framework and SQLite? I have a working application that can create permanent tables and triggers, but not temporary ones. Listing sqlite_temp_master turns up no entries, and any attempts to interact with the temporary tables fail with "no table" errors. These interactions are taking place through a single SQLiteConnection though there is at least one other connection active in the application at the time.

I am using Database.ExecuteSqlCommand() to create the tables and triggers. If the TEMPORARY keyword is supplied, there are no errors and no tables/triggers. If the TEMPORARY keyword is not supplied, there are no errors, and permanent tables/triggers are created.

Any help would be appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JBJB
  • 21
  • 4

1 Answers1

1

The System.Data.Entity.Database object opens and closes the connection as it deems appropriate. In the way I was using ExecuteSqlCommand, it will open and close the connection for each command. So temporary tables will be discarded as soon as they are created.

Manually opening and closing Database.Connection won't work because of an apparent problem in the DbContext class. However, the internal ObjectContext object can do the job.

Here's the best summary that I've found for the solution (many thanks to Brent McKendrick)

(dbContext as IObjectContextAdapter).ObjectContext.Connection.Open();

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
    {
        // perform a list of queries
        // The connection will not close!
        scope.Complete();
        (dbContext as IObjectContextAdapter).ObjectContext.Connection.Close();
    }

I used that technique in conjunction with SQLiteCommand to create a set of temporary tables and triggers, perform my operations, use LINQ to get the results, then end the transaction, and close the connection. The temporary objects were dropped only at the end, as expected.

I did not check to see if Database.ExecuteSqlCommand can be used in place of SQLiteCommand when using this technique.

Edit: The TransactionScope is not necessary, and certainly adds to the overhead of any operation. The critical part is the opening and closing of the connection through the dbContext's ObjectContext.

JBJB
  • 21
  • 4