System Design Considerations - Pro SQL Server Internals - page 356

The code that works with DbContext class looks similar to the code that uses the ADO.Net SqlConnection class.
There is a major difference in how database connections are handled, however. The SqlConnection class requires you to
explicitly open and close connections. The DbContext class, on the other hand, implicitly opens and closes connections
when corresponding objects need to access the data. In the example shown in Listing 16-5, Entity Framework opens a
database connection at the beginning and closes it at the end of the context.Customers.ToLists() method call. After
that, a connection would be opened and closed every time the application loads orders for a specific customer; that is,
during each foreach (var order in context.Orders.Where(o => o.Customer == customer.Id)) call. This behavior
can introduce the significant overhead of establishing database connections if connection pooling is not enabled. The
EFDbContext class in the example in Listing 16-5 is inherited from the DbContext class.

Listing 16-5. Working with DbContext class

using (var context = new EFDbContext())
{
var customers = context.Customers.ToList();
foreach (var customer in customers)
{
Trace.WriteLine(string.Format(
"Customer Id: {0}", customer.CustomerId));
foreach (var order in context.Orders.Where(o => o.Customer == customer.Id))
Trace.WriteLine(string.Format(
"Customer Id: {0}", customer.CustomerId));
}
}

■ You can read more about connection management in entity Framework at:
http://msdn.microsoft.com/en-us/data/dn456849 .

Note

You can explicitly control transactions in Entity Framework by using either .Net TransactionScope or Entity
Framework DbContextTransaction classes. Listing 16-6 illustrates the latter approach. It is also worth mentioning that
explicit transactions force Entity Framework to keep a database connection open for the duration of the transaction.

Listing 16-6. Working with the DbContextTransaction class

using (var context = new EFDbContext())
{
using (var transaciton =
context.Database.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
context.SaveChanges();
transaciton.Commit();
}
catch
{
transaciton.Rollback();
}
}
}