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)
{
}
}
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)
{
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)
{
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)
{
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)
{
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)
{
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();
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();
return null;
}
}
}
}
The usage
return await DALHelper.GenericSafeTransactionAsync<NorthwindDBContext>( async (northwindContext) =>
{
northwindContext.Employees.Add(newEmp);
});