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

墨蓝 2022-08-07 16:58 13阅读 0赞

原文：[http://www.codeproject.com/Articles/898206/Generic-Data-Access-Helper-using-Entity-Framework?msg=5044112\#xx5044112xx][http_www.codeproject.com_Articles_898206_Generic-Data-Access-Helper-using-Entity-Framework_msg_5044112_xx5044112xx]

### Motivation ###

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

Hide    Copy Code

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`.

Hide    Copy Code

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 #####

Hide    Copy Code

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.

Hide    Copy Code

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 #####

Hide    Copy Code

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 ####

Hide    Copy Code

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 #####

Hide    Copy Code

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 ####

Hide    Copy Code

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 ####

Hide    Shrink  ![arrow-up-16.png][]    Copy Code

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 #####

Hide    Copy Code

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:  
![Twice_Contexts_Design.png][]

## 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.

Hide    Copy Code

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 #####

Hide    Copy Code

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 ####

Hide    Copy Code

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 #####

Hide    Copy Code

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

[http_www.codeproject.com_Articles_898206_Generic-Data-Access-Helper-using-Entity-Framework_msg_5044112_xx5044112xx]: http://www.codeproject.com/Articles/898206/Generic-Data-Access-Helper-using-Entity-Framework?msg=5044112#xx5044112xx
[arrow-up-16.png]: /images/20220805/359780bdb2c747bfbb0f9275610671a7.png
[Twice_Contexts_Design.png]: /images/20220805/45df7fa9fe2a4a11ba37cd58fb368416.png