Generic Data Access Helper using Entity Framework 6.1 (Code First)

• Download source code - 10.9 MB

Introduction

The goal of this helper is to develop a generic - so reusable - data access helper using entity framework (CodeFirst) while its motivation is to eliminate redundant code using Generics and Delegates.

The helper is divided into two sections, the first is querying (Retrieval) business, the second is saving changes to database which are implemented synchronously and asynchronously.

Background

This helper proposed using those technologies .NET 4.5 (C#5), Entity Framework 6 (CodeFirst). Also, you need to have a moderate knowledge about async / await code.

In Details

The generic here means apply Generics over methods that takes a delegate - of type Action in case it doesn't return, or of type Func in case it returns result - as a parameter which in turn takes a parameter of type DBContext as a base class inherited by your concrete class.

Each method encapsulates the following logic depending on its role:

• Initiating a concrete entity framework context with using
• Begin the transaction scope with locking (Default) or unlocking (IsolationLevel.ReadUncommitted) tables
• try / catch body
• Delegate execution
• The Commit and Roll-back logic, in case it is an atomic transaction that consists of a set of transactions
• Result returning type boolean that indicates querying / saving state, or of a type predefined using generics.
• Asynchronous logic
• Log exceptions

All those logics are not going to be written into your business-based data access logic, so the redundant code is eliminated.

Motivation

When we write a simple logic that accesses database using EF & Linq, it simply looks like this:

public static List<Employee> GeAllEmployees()
{
    try
    {
        using (var northwindContext = new NorthwindDBContext())
        {
            var query = from e in northwindContext.Employees select e;
            return query.ToList();
        }
    }
    catch (Exception ex)
    {
        // Log Error
    }
}

So, I have to make this code redundant with a new business (e.g. GetEmployeeOrders). Also, in case I have to access another database which means another DBContext, I have to make this logic redundant !!

Here, the Generics and Delegates come as a solution for those two issues. So I created a public static class called DALHelper containing the following seven static methods.

1 Querying

All retrieval's methods could also be used for saving changes to the database.

1.1 Default

The following snippet code is locking tables and this is the default behavior of initiating a new DbContext.

public static bool GenericRetrival<T>(Action<T> action) where T : DbContext, new()
{
    try
    {
        using (var context = new T())
        {
            action(context);
            return true;
        }
    }
    catch (Exception ex)
    {
	// Log Error
        return false;
    }
}

The usage

public List<Employee> GeAllEmployees()
{
	List<Employee> result= null;
	bool success = DALHelper.GenericRetrival<NorthwindDBContext>((northwindContext) =>
	{
		result = (from e in northwindContext.Employees select e).ToList();
	});
	return result;
}

1.2 Querying with a generic result

Here we identify TResult as generics of type DBContext which a delegate of type Func is going to return an object of TResult type.

public static TResult GenericResultRetrival<T, TResult>(Func<T, TResult> func) where T : DbContext, new()
    where TResult : new()
{
    try
    {
        using (var context = new T())
        {
            TResult res = func(context);
            return res;
        }
    }
    catch (Exception ex)
    {
        // Log Error
        return default(TResult);
    }
}

The usage

public List<Employee> GeAllEmployees()
{
	List<Employee> result = DALHelper.GenericResultRetrival<NorthwindDBContext,List<Employee>>((northwindContext) =>
	{
		return (from e in northwindContext.Employees select e).ToList();
	});
	return result;
}

1.3 Querying asynchronously

public static async Task<TResult> GenericRetrivalAsync<T, 
	TResult>(Func<T, Task<TResult>> func)
    where T : DbContext, new()
    where TResult : new()
{
    try
    {
        using (var context = new T())
        {
            return await func(context);
        }
    }
    catch (Exception ex)
    {
	// Log Error
        return default(TResult);
    }
}

The usage

public async Task<List<Employee>> GetAllEmployeesAsync()
{
    return await DALHelper.GenericRetrivalAsync<NorthwindDBContext, List<Employee>>(async (northwindContext) =>
    {
        return await (from e in northwindContext.Employees select e).ToListAsync();
    });
}

1.4 A long query with no locking tables asynchronously

public static async Task<TResult> 
GenericResultNoLockLongRetrivalAsync<T,TResult>(Func<T, Task<TResult>> func)
    where T : DbContext, new()
    where TResult : new()
{
    try
    {
        using (var context = new T())
        {
            ((IObjectContextAdapter)context).ObjectContext.CommandTimeout = 0;
            using (var dbContextTransaction = 
            	context.Database.BeginTransaction(IsolationLevel.ReadUncommitted))
            {
                return await func(context);
            }
        }
    }
    catch (Exception exception)
    {
        // Log Error
        return default(TResult);
    }
}

1.5 Querying from twice contexts asynchronously

public static async Task<object> 
	GenericTwiceContextsRetrivalAsync<T1, T2>(Func<T1, T2, Task<object>> func)
            where T1 : DbContext, new()
            where T2 : DbContext, new()
{
    try
    {
        using (var context1 = new T1())
        {
            using (
                var dbContextTransaction1 = context1.Database.BeginTransaction(IsolationLevel.ReadUncommitted))
            {
                using (var context2 = new T2())
                {
                    using (
                        var dbContextTransaction2 =
                            context2.Database.BeginTransaction(IsolationLevel.ReadUncommitted)
                        )
                    {
                        return await func(context1, context2);
                    }
                }
            }
        }
    }
    catch (Exception exception)
    {
        // Log Error

        return null;
    }
}

The usage

public async Task<object> GetDistributedDataAsync()
{
    return await DALHelper.GenericTwiceContextsRetrivalAsync<NorthwindDBContext, AdventureWorkDBContext>(async
        (northwindContext, advantureContext) =>
        {
            var employees = (from e in northwindContext.Employees select e).ToListAsync();
            var cutomers = (from c in advantureContext.Customers select c).ToListAsync();

            await Task.WhenAll(employees, cutomers);
            return new
            {
                EmployeeList = employees.Result,
                PersonList = cutomers.Result
            };
        });
}

So the design is going to be:

2 Saving

2.1 Generic safe saving

I called it safe as it could treat a set of transactions as an atom with commit / rollback logic. 

public static bool GenericSafeTransaction<T>(Action<T> action) where T : DbContext, new()
{
    using (var context = new T())
    {
        using (var dbContextTransaction = context.Database.BeginTransaction())
        {
            try
            {
                action(context);
                dbContextTransaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                dbContextTransaction.Rollback();
                // Log Error
                return false;
            }
        }
    }
}

The usage

public bool AddMultipleRecords(Employee newEmp, Supplier newSup)
{
    return DALHelper.GenericSafeTransaction<NorthwindDBContextgt;(northwindContext =>
    {
        northwindContext.Employees.Add(newEmp);
        northwindContext.SaveChanges();
        northwindContext.Suppliers.Add(newSup);
        northwindContext.SaveChanges();
    });
}

2.2 Saving asynchronously

public static async Task<int?> GenericSafeTransactionAsync<T>(Action<T> action)
            where T : DbContext, new()
{
    using (var context = new T())
    {
        using (var dbContextTransaction = context.Database.BeginTransaction())
        {
            try
            {
                action(context);
                int affectedRecords = await context.SaveChangesAsync();
                dbContextTransaction.Commit();
                return affectedRecords;
            }
            catch (Exception ex)
            {
                dbContextTransaction.Rollback();
		// Log Error
                return null;
            }
        }
    }
}

The usage

return await DALHelper.GenericSafeTransactionAsync<NorthwindDBContext>( async (northwindContext) =>
{
	northwindContext.Employees.Add(newEmp);
});