Introduction

The goal of this helper is to develop a generic - so reusable - data access helper using entity framework. While the motivation of it is to eliminate redundant code using Generics and Delegates.

The helper divided into two sections, the first is querying (Retrieval) business, the second is transact (Update/Insert/Delete) database witch are implemented synchronously and asynchronously

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 input witch in turn takes an input of type DBContext as a base class inherited by your concrete class

Each method - depends on its role - encapsulates:

• 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 consist of a set of transactions
• Result returning, boolean type indicates querying / transaction state, or output type that is specified as generic
• Asynchronous logic
• Log exceptions

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

Motivation

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 redundant this code with a new business (e.g. GetEmployeeOrders). Also, in case I have to access another database witch means another DBContext I have to redundant this logic!!

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

1. Retrieval

All retrieval's methods could also be used for transacting database.

1. Locking tables

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;
    }
}

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;
}

2. With a Generic Result

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);
    }
}

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;
}

3. Asynchrouncly

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);
    }
}

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();
    });
}

4. Long With no locking tables Asynchrouncly

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);
    }
}

5. 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;
}

}

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
        };
    });

}

2. Saving

6. Transactions as atom

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;
            }
        }
    }
}

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();
    });
}

7. Asyncrounsly

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;
            }
        }
    }
}

Usage

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