Using the SQL Server transport with Entity Framework

To avoid escalating transactions to the Distributed Transaction Coordinator (DTC), operations using Entity Framework must share their connection string with the SQL Server transport.

However, the connection string used for the SQL Server transport cannot be used directly by Entity Framework when the Database/Model First approach is used. In this case, Entity Framework requires a special connection string containing specific metadata.

The metadata can be added using EntityConnectionStringBuilder. The modified connection string can then be used to create an EntityConnection, which can then be used to create an instance of the generated DbContext type:

var entityBuilder = new EntityConnectionStringBuilder
{
    Provider = "System.Data.SqlClient",
    ProviderConnectionString = "the database connection string",
    Metadata = "res://*/MySample.csdl|res://*/MySample.ssdl|res://*/MySample.msl"
};

var entityConn = new EntityConnection(entityBuilder.ToString());

using (var mySampleContainer = new MySampleContainer(entityConn))
{
    // use the DbContext as required
}

The DbContext generated Entity Framework does not have a constructor with an EntityConnection, but since it is a partial class, the constructor can be added:

partial class MySampleContainer
{
    public MySampleContainer(EntityConnection dbConnection)
        : base(dbConnection, true)
    {
    }
}