Share connection between DbContext objects in Entity Framework 6 Database first approach

Question

I am trying to share a connection in between 2 different DB context objects using EF 6 . I have got the code mentioned here https://msdn.microsoft.com/en-us/data/dn456843.aspx to work for a single DB Context but as soon as I try to share the connection with another DB Context object I am running into trouble. Below is what I have done so for.

public class MyUnitOfWork
{
              // BAD CODE : having static connection / transaction is bad design , i  use DI to implement  this properly without need for static fields , this code is used here to avoid having to mention my DI configuration; 
                public static EntityConnection _commonConnection ;
                public static System.Data.Entity.DbContextTransaction _commonTransaction;
                // Generic function to create DBContext object based on T supplied.
            public static T GetDbContext<T>()
            {
                 if(_commonConnection  == null)

                  {
                     // generates a generic connection string 
                        _commonConnection = new EntityConnection(DbContextBase.GenerateConnectionString()); 

                        _connection.Open();

                        _commonTransaction = _connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
                  }
                        T myContextObject = (T)Activator.CreateInstance(typeof(T), new object[1] { _connection });
                        myContextObject .Database.UseTransaction(_transaction);
                       return myContextObject;
            }
}

The code for generation of connection string is mentioned below:

string GenerateConnectionString()
{
            var entityBuilder = new EntityConnectionStringBuilder
            {
                Provider = "System.Data.SqlClient",                     
                ProviderConnectionString = SharedDatabase.SQLBase.DBconnection + "multipleactiveresultsets=True;" ,
                Metadata = @"res://*/;"
            };

       return entityBuilder ;
}

I can then call the GetDbContext from different places in my function like so

GetById(int id)
{
   var dbcontext = MyUnitOfWork.GetDbContext<OrdersDbContext>();
  return dbcontext.Orders.Where(.......);


}

GetCustomerInfo( int id)
{
   var dbcontext = MyUnitOfWork.GetDbContext<CustomerDbContext>();
  return dbcontext.Customer.Where(.......);

}

Because I am using the generic metadata portion , I am getting errors in entity framework "cannot have more than one entity with same name regardless of the namespace"

However if I specify the name of the .csdl/.ssdl file , the connection ( and hence the transaction ) can no more be common and I have to create a connection and Transaction for each DBContext ( this is what i wanted to avoid )

It seems I have hit a block . Is there a way for me to use the same connection without getting the duplicate entities error ? Changing the names of entities to be different is not an option for me as it will be a very time consuming change which I would have to do across 30 + db context / EDMX files with huge production impact.

Why don't you take Metadata as an argument of GenerateConnectionString ? What are the type names of your context and the names of respectives csdl/ssdl files ? — Guillaume
– Guillaume, Commented Feb 22, 2016 at 9:54
@Guillaume : I can take metadata name as an object. but then the connection string becomes unique to that context and cannot be reused in another context. What i want to achieve is the ability to have multiple DBContext using the same connection string and hence being a part of the same transaction. — Pratik
– Pratik, Commented Feb 22, 2016 at 9:56
Why do you want the same EntityConnectionString? They are sharing the same PorivderConnectionString... they may already share the same ConnectionPool (I'm not 100% sure) — Guillaume
– Guillaume, Commented Feb 22, 2016 at 9:59
Having a static transaction is a bad design (thread safety...). — Guillaume
– Guillaume, Commented Feb 22, 2016 at 11:04
@Guillaume : Appretiate that and actually my transactions are not static , they are implemented using the a Transient lifecycle using Structure map which means the object for UnitOfWork class is created only once per http request . It is however not possible to write and explain the entire code here as it will be too long and its also not relevant to the problem I am facing. — Pratik
– Pratik, Commented Feb 22, 2016 at 11:10

Guillaume · Accepted Answer · 2016-02-23 10:40:30Z

4

As seen in Working with Transactions (EF6 Onwards), use the following code to provide an existing transaction to your context :

string GenerateConnectionString()
{
    return SharedDatabase.SQLBase.DBconnection + "multipleactiveresultsets=True;";
}


public class MyUnitOfWork
{
    SqlConnection _commonConnection;
    DbTransaction _commonTransaction;
    // Generic function to create DBContext object based on T supplied.
    public T GetDbContext<T>()
    {
        if (_commonConnection == null)
        {
            // generates a generic connection string 
            _commonConnection = new SqlConnection(DbContextBase.GenerateConnectionString());

            _commonConnection.Open();

            _commonTransaction = _connection.BeginTransaction(IsolationLevel.Snapshot);
        }

        MetadataWorkspace workspace = new MetadataWorkspace(
          string.Format("res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl;", typeof(T).Name).Split('|'), 
          new Assembly[] { Assembly.GetExecutingAssembly() });

        var connection = new EntityConnection(workspace, _commonConnection);
        T myContextObject = (T)Activator.CreateInstance(typeof(T), new object[] { connection });
        myContextObject.Database.UseTransaction(_commonTransaction);
        return myContextObject;
    }
}

edited Feb 23, 2016 at 10:40

answered Feb 22, 2016 at 13:18

Guillaume

13.2k3 gold badges44 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Pratik Over a year ago

Unfortunately this doenst work , it throws a UnintentionalCodeFirstException . Another stack overflow post (stackoverflow.com/questions/24605535/…) mentions using entityConnection to avoid this error . But using EntityConnection causes the error i meantioned in the orignal question.

Guillaume Over a year ago

@Pratik I edited my answer, not sure it works but that may lead you to a working solution...

Pratik Over a year ago

Sorry , My question is probably not clear enough but if I use the code you have mentioned it will error. When the GetDbContext<OrdersDbContext>(); function is called , it will set the metadata in the _connection to .csdl/ssdl for OrdersDbContext. When the GetDbContext<CustomerDbContext>() part mentioned in the question is executed , the _connection object will contain metadata for OrdersDbContext and hence cant be used by the CutstomersDbContext. I am searching for a way in which 2 DBContext object can use a common connection without the issue with Metadata.

Guillaume Over a year ago

@Pratik In my code, _commonConnection is a SqlConnection and doesn't keep any reference to Metadata. The EntityConnection and associated metadata is changed while keeping the same connection and transaction. If that doesn't work you may try with a TransactionScope but it's not advised with EF6.

Pratik Over a year ago

You are right , this code does work. I overlooked the smart SQLConnection to EntityConnection conversion . That was exactly what i was looking for. Thanks a lot , you are a genius!

|

Collectives™ on Stack Overflow

Share connection between DbContext objects in Entity Framework 6 Database first approach

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related