Entity Framework FAQ: Connections and Transactions
Can the container name and the connection string name be different?
Unfortunately, if you want to use the parameterless constructor on the strongly typed context, these two things are tied. The container name and the connection string name must be the same. What you can do, though, is pass the connection string into the constructor of your context (either the strongly typed, generated context or the base class ObjectContext), and then you could hard code it, determine it dynamically, get it out of the config file, etc. From http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2349979&SiteID=1
How can I create an ObjectContext using a database connection that has already been created?
Sometimes there are security or other reasons why you would want to create a database connection and have EntityClient and the ObjectContext use it rather than creating the connection based on the connection string. This can be done, but you must build the metadata workspace, use it and the database connection to build an entity connection, and then pass that to the ObjectContext. Here's a sample:
string connectionString = @"Data Source=jeffreed-dev6\sqlexpress;" +
@"Initial Catalog=Northwind;Integrated Security=True;" +
@"MultipleActiveResultSets=True";
SqlConnection sqlConnection = new SqlConnection(connectionString);
MetadataWorkspace workspace = new MetadataWorkspace(
new string[] { "res://*/" },
new Assembly[] { Assembly.GetExecutingAssembly() });
EntityConnection entityConnection = new EntityConnection(workspace,
sqlConnection);
NorthwindEntities context = new NorthwindEntities(entityConnection);
From: http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3715593&SiteID=1&mode=1
How are connection strings managed in the Entity Framework? How can I create a connection string at run time?
When you use the designer or EdmGen.exe to generate classes from your model, part of what gets generated is a strongly typed ObjectContext that provides a convenient façade for working with your model. Part of the standard pattern is that the connection string that was originally used to access the database to generate the model is embedded in an EntityClient connection string, which is placed into the app.config file under the same name as your entity container (specified in the CSDL and used as the name of the strongly typed ObjectContext type in the generated code). If the app.config file exists and has a connection string whose name matches the container, then the generated classes have a parameterless constructor that will automatically pick up that connection string and use it.
This mechanism is great for many scenarios--it makes for clean code, and it follows a best practice of putting the connection string into the config file where it's easier to swap out, for example, in situations where you might use one connection string for development, another for testing/staging, and a third for final deployment. That said, there are cases where you might want to dynamically generate the connection string--for example, you might have multiple database servers for different environments and want to allow the user to specify the server as well as credential information at run time. So, it's important to understand what the parts of the connection string are and how they go together.
If you create a connection string at run time, then it's still quite straightforward to use. Instead of using the parameterless constructor for the strongly typed context, you can just use the constructor overload that takes a connection string. This connection string is the same connection string that you use either with an ObjectContext (the base type or one of the strongly typed classes that inherit from it) or directly with EntityClient. It follows the same model as all other connection strings, which is a set of name/value pairs separated by semicolons, and it has three parts:
- A specification for where to find the metadata (CSDL, MSL, and SSDL) that EntityClient will use for mapping between the conceptual model and the database. The keyword for this section is "metadata=", and you can give it several different kinds of values. A common practice is to specify that the metadata lives in resources within the assembly and that the system should look through all the resources looking for appropriate ones. You can do this with `metadata=res://*/;`, or you can specify a pipe-delimited list of directories or files (either absolute or relative to your app directory) that contain the metadata.
- The name of the database provider that EntityClient should use to talk to the database. For example, `Provider=System.Data.SqlClient;`.
- The connection string that you would use with the database provider to access your database. Because this value has a series of keywords separated by semicolons, the string is enclosed in double quotes. An example is:
string ProviderConnection =
"MultipleActiveResultSets=true;database=dpmr;server=.;" +
"Integrated Security=true;"
If you are going to build up these parts dynamically, then the recommended way to do so is by using a connection string builder, and EntityConnectionBuilder makes this easy. For the provider connection string, you can do a similar thing by using something like SqlConnectionBuilder. So, the whole thing together would look like this:
SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
sqlBuilder.MultipleActiveResultSets = true;
sqlBuilder.DataSource = ".";
sqlBuilder.InitialCatalog = "dpmr";
sqlBuilder.IntegratedSecurity = true;
EntityConnectionStringBuilder entityBuilder =
new EntityConnectionStringBuilder();
entityBuilder.ProviderConnectionString = sqlBuilder.ToString();
entityBuilder.Metadata = "res://*/";
entityBuilder.Provider = "System.Data.SqlClient";</P>
For more information, see [Connection Strings (Entity Framework)](http://msdn.microsoft.com/en-us/library/78d516bc-c99f-4865-8ff1-d856bc1a01c0).
Can I access the underlying database connection given an EntityConnection?
Given an EntityConnection you can easily access the database connection. In most cases, however, you don't need to because the Entity Framework is designed to cover most of the scenarios required for application building through its public API. For more information, see [Managing Connections (Entity Framework)](http://msdn.microsoft.com/en-us/library/bb896325.aspx).
What is the recommended way to manage transactions and connections with the Entity Framework?
See [Managing Transactions and Connections (Entity Framework)](http://msdn.microsoft.com/en-us/library/bb896325.aspx).
How do I set IsolationLevel/Lock Hints on single queries? Is it possible to set `NOLOCK` on a query?
It is recommended that you use `READ COMMITTED` transactions, and use `READ COMMITTED SNAPSHOT ISOLATION` if you need to have readers not block writers and writers not block readers. `READ UNCOMMITTED` provides no guarantees about the correctness of your data, and can fail unpredictably.
However, if you still decide to use `READ UNCOMMITTED` with the EF, there is a way to do it. While you can't apply a `NOLOCK` hint to a LINQ to Entities query, you can drop the isolation level of your transaction to `READ UNCOMMITTED` before running your query. You do this by issuing `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED` in the scope of your transaction, as in the following example:
TransactionOptions topt = new TransactionOptions();
topt.IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead;
topt.Timeout = TimeSpan.MaxValue;
using (var tran = new TransactionScope(TransactionScopeOption.Required,
topt))
using (var context = new SchoolEntities())
{
context.Connection.Open();
var con = (SqlConnection)((EntityConnection)
context.Connection).StoreConnection;
SqlCommand cmd = new SqlCommand(
"set transaction isolation level read uncommitted", con);
cmd.ExecuteNonQuery();
var qCustomer1 = from c in context.StudentGrades
where c.StudentID == 1
select c;
}
Another way to do it is by setting the isolation level on a nested TransactionScope:
using (var context = new SchoolEntities())
{
using(TransactionScope scope1 = new TransactionScope())
{
// Do some modification processing
var query = (from g in ontext.StudentGrades
where g.EnrollmentID == 23
select g).FirstOrDefault();
Console.WriteLine(query.Grade);
query.Grade = 3;
context.SaveChanges;
using (TransactionScope scope2 = new TransactionScope
(TransactionScopeOption.RequiresNew,
new TransactionOptions()
{ IsolationLevel = IsolationLevel.ReadUncommitted }))
{
// execute a reading query
// with ReadUncommited isolation level.
var query2 =
(from g in context.StudentGrades
where g.EnrollmentID == 23
select g).FirstOrDefault();
Console.WriteLine(query.Grade);
scope2.Complete();
}
}
}
How do I avoid promotion of a transaction to a DTC?
Promotion of a transaction to a distributed transaction coordinator (DTC) may occur when a connection is closed and reopened within a single transaction. The latest versions of SQL Server have less trouble in this area, but if you are using SQL Server 2005 or before you may run into this kind of problem because the Entity Framework opens and closes the connection automatically for each operation. So, you should consider manually opening and closing the connection to avoid transaction promotion. For more information, see [Managing Transactions and Connections (Entity Framework)](http://msdn.microsoft.com/en-us/library/bb896325.aspx).