Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Connection Scope for the Entity Framework

5.00/5 (1 vote)
7 Apr 2010CPOL2 min read 1   496  
A Connection Scope for the Entity Framework that allows to execute commands in a single connection
Attention: This code is for VS 2010 and EF v4.

Introduction

This code gives you the possibility to group entity framework database operations in the business layer so that they will be executed in one single open connection, even if they are separated commands in the backend layer.  

Background

If you use the TransactionScope class, then there will still be different connections (or at least the same, but opened and closed several times), but they will run in a distributed transaction. I wanted for e.g. an importer application, that my import runs in one open connection for performance issues.

Using the Code

There are two interesting classes: EntityConnectionScope and EntityConnectionScopeHelper. The EntityConnectionScope is the one you use in your business logic. On creation, it creates a variable in the thread store to store itself there.

C#
public EntityConnectionScope()
{
	LocalDataStoreSlot slot = Thread.GetNamedDataSlot(Key);
	Thread.SetData(slot, this);
}

With the property Current, you can retrieve the current scope.

C#
/// <summary>
/// Gets a reference to the EntityConnectionScope object for the current thread.
/// </summary>
public static EntityConnectionScope Current
{
	get
	{
		EntityConnectionScope scope = null;

		LocalDataStoreSlot slot = Thread.GetNamedDataSlot(Key);
		scope = Thread.GetData(slot) as EntityConnectionScope;

		return scope;
	}
}

The next interesting part is the property EntityConnection. At the first call, it initializes an entity connection and opens it. Because I don't know the name of the connection string, I made a new tag in the app.config file named EFConnectionStringName, that holds the connection tag name.

C#
public EntityConnection EntityConnection
{
	get
	{
		if (entityConnection == null)
		{
			entityConnection = new EntityConnection
				(Configuration.EFConnectionStringName);
		}

		if (entityConnection.State == ConnectionState.Closed)
		{
			entityConnection.Open();
		}

		return entityConnection;
	}
}

The class implements IDisposable. Without that, you can't use the using keyword. It ensures that after the usage of the EntityConnectionScope, the connection is closed and released, even in case of exceptions.

C#
public void Dispose()
{
	if (entityConnection != null)
	{
		entityConnection.Close();
		entityConnection.Dispose();
	}

	Thread.FreeNamedDataSlot(Key);
}

The helper class is used in the backend. There you check if a ConnectionScope is active. If yes, take its connection (that is open) and pass it to the ObjectContext. If not, create a new connection (don't open it) and pass this to the ObjectContext.

C#
/// <summary>
/// If we are in a ConnectionScope we return that 
/// (open) connection, otherwise a new closed one
/// </summary>
/// <returns>EnityConnection</returns>
internal static EntityConnection GetEntityConnection()
{
	if (EntityConnectionScope.Current != null)
	{
		return EntityConnectionScope.Current.EntityConnection;
	}
	else
	{
		return new EntityConnection(Configuration.EFConnectionStringName);
	}
}

The ObjectContext (this is where the EntityFramework magic happens) is clever enough, that an open connection is not closed at the end. On the other hand if it gets a closed connection, it closes it at the end.

C#
using (ModelContainer proxy = 
	new ModelContainer(EntityConnectionScopeHelper.GetEntityConnection()))
{
	return proxy.Customers.Include("Orders").SingleOrDefault(c => c.Id == id);
}

The usage of the EntityScope is quite simple, like:

C#
using (EntityConnectionScope scope = new EntityConnectionScope())
{
	//call backend methods here
}  

In my small sample, I have a simple data model. Customer : Order = 1 : n. I create a customer, then an order and in the end retrieve that customer from the backend. As you can see in the profiler, it opens and closes the connection three times (first red rectangle). When doing the same in the EntityConnectionScope, the connection is held open for all three commands (second red rectangle).

Image 1

History

  • V 1.0 2010/04/06 Initial version 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)