Maybe you are surprised, maybe not but the fact is that you can increase the performance by reading and writing the data with the explicit database connections.

The simple test method GetEmployees is reading the employees and projecting them by Id.

private static IEnumerable GetEmployees(IEnumerable ids, CompanyDbContext context)
{
	IList employees = new List(ids.Count());
	foreach (var idToSelect in ids)
	{
		Employee foundEmployee = context.Employees.Single(employee => employee.Id == idToSelect);
		employees.Add(foundEmployee);
	}

	return employees;
}

The GetEmployeesWithImplicitConnection is calling the method above with the implicit connections managed by Entity Framework.

private static IEnumerable GetEmployeesWithImplicitConnection(IEnumerable ids)
{
	using (var context = new CompanyDbContext())
	{
		return GetEmployees(ids, context);
	}
}

The method GetEmployeesWithExplicitConnection is calling the method GetEmployees by setting the explicit connection.

private static IEnumerable GetEmployeesWithExplicitConnection(IEnumerable ids)
{
	string connectionString = ConfigurationManager.ConnectionStrings["CompanyDb"].ConnectionString;
	using (var connection = new SqlConnection(connectionString))
	{
		connection.Open();
		using (var context = new CompanyDbContext(connection, false))
		{
			return GetEmployees(ids, context);
		}
	}
}

This is only small detail which makes huge difference for the purpose of performance. Why? The Entity Framework create for each command own connection.

image

Audit Login means the connection was open and the Audit Logout stays for the connection closed. Calling the method GetEmployeesWithImplicitConnection with the list of ten thousand ids, is opening and closing the connection ten thousand times!

Calling the method GetEmployeesWithExplicitConnection is opening and closing the connection only one time because the one connection is shared for all commands.

image

The performance comparison

GetEmployeesWithImplicitConnection GetEmployeesWithExplicitConnection
10 000 entries 00:00:06.1619521 00:00:03.6564162
100 000 entries 00:00:43.2776995 00:00:33.8298577
1 000 000 entries 00:08:19.1952462 00:06:35.9054971

The performance improvement isn’t huge but opening and closing the connections consume SQL Server resources as well.