How to force Entity Framework not to lock the database

Question

Hi All,

I'm using EF4 and .Net 4 to load some XML from a file into a database.

I have a class the wraps around the ObjectContext and has methods that add the marshalled objects from the XML file to the various EntityCollections that represent my tables.

Each XML file contains around 200,000 objects on average, the wrapper class creates the ObjectContext on construction and stores the reference in a local private class variable which is then used by the methods.

When I have finished creating the entities I call:

entities.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);

This creates a transaction on the server which is as per EntityFramework design. However this transaction is completely locking down my DB even on tables that are not being added to.

I have tried various things to try and get round this including wrapping save changes in a TransactionScope like this:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress,
       new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
       {
            entities.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);

            transaction.Complete();
       }

I have also tried creating the TransactionScope before creating the ObjectContext in an attempt to influence the underlying transaction being used during SaveChanges.

Ideally I would want to load multiple files at once, but this will be impossible if the DB is locked during save changes.

Does anyone know a work around to this issue? Is there away to force the EntityFramework not to use a transaction?

Thanks for any help in advance.

James

Answer 1

Just to put this to bed im going to post what my solution was.

I was basically looking at this from the wrong end of the problem, a transaction is used when calling SaveChanges() but you can still read the database by using a method like this:

private static FrameEntities GetEntities()
    {
        FrameEntities entities = new FrameEntities();
        entities.ExecuteStoreCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
        return entities;
    }

This sets the isolation level before you try to read.

This is not normally recommend since you can read "dirty" of partial data but for what im doing its fine.