using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Linq.Expressions;
namespace CompanyWeb.Content.Tools
{
/// <summary>
/// Entity FrameWork帮助类(提供各种对数据的操作方法)
/// </summary>
/// <typeparam name="TDbContext"></typeparam>
public class EFTools<TDbContext> where TDbContext : DbContext, new()
{
/// <summary>
/// 获取所有的实体 zhaolin
/// </summary>
/// <typeparam name="T"> 泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <returns>结果集</returns>
public List<T> GetAllEntity<T>() where T : class
{
try
{
using (TDbContext db = new TDbContext())
{
return db.Set<T>().ToList();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return new List<T>();
}
}
/// <summary>
/// 获取单个实体(条件的最后一条)
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <param name="whereProc">过滤的表达式</param>
/// <returns>实体</returns>
public T GetSingleEntityLast<T, Tkey>(Expression<Func<T, bool>> whereProc, Expression<Func<T, Tkey>> orderProc, string desc) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
if (desc == "desc")
{
return ef.Set<T>().Where(whereProc).OrderByDescending(orderProc).FirstOrDefault();
}
return ef.Set<T>().Where(whereProc).OrderBy(orderProc).FirstOrDefault();
// return ef.Set<T>().FirstOrDefault(whereProc);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return null;
}
}
/// <summary>
/// 获取所有的实体可排序
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <param name="orderProc"> 排序表达式</param>
/// <param name="orderByType">排序的类型 默认是升序,降序(desc)</param>
/// <returns>结果集</returns>
public List<T> GetAllEntityByOrder<T, TKey>(Expression<Func<T, TKey>> orderProc, string orderByType) where T : class
{
try
{
using (TDbContext db = new TDbContext())
{
if (orderByType == "desc")
{
return db.Set<T>().OrderByDescending(orderProc).ToList();
}
return db.Set<T>().OrderBy(orderProc).ToList();
}
}
catch (Exception)
{
return new List<T>();
}
}
/// <summary>
/// 获取所有的实体可排序
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <param name="whereProc"> 过滤的表达式</param>
/// <returns>结果集</returns>
public List<T> GetAllEntityByWhere<T>(Expression<Func<T, bool>> whereProc) where T : class
{
try
{
using (TDbContext db = new TDbContext())
{
return db.Set<T>().Where(whereProc).ToList();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return new List<T>();
}
}
/// <summary>
/// 获取所有的实体可排序
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <typeparam name="TKey">成员属性的类型</typeparam>
/// /// <param name="whereProc">过滤的表达式</param>
/// <param name="orderProc">排序的表达式</param>
/// <param name="orderByType">排序的类型</param>
/// <returns>结果集</returns>
public List<T> GetAllEntityOrderAndWhere<T, TKey>(Expression<Func<T, bool>> whereProc, Expression<Func<T, TKey>> orderProc, string orderByType) where T : class
{
try
{
using (TDbContext db = new TDbContext())
{
if (orderByType == "desc")
{
return db.Set<T>().Where(whereProc).OrderByDescending(orderProc).ToList();
}
return db.Set<T>().Where(whereProc).OrderBy(orderProc).ToList();
}
}
catch (Exception)
{
return new List<T>();
}
}
/// <summary>
/// 获取某一个实体类型的所有数据并排序
/// </summary>
/// <typeparam name="TEntity">待查询的实体的类型</typeparam>
/// <typeparam name="TKey">排序的条件的属性的数值类型</typeparam>
/// <param name="OrderByProc">排序的lambda表达式</param>
/// <param name="IsDesc">false 顺序 true 逆序</param>
/// <returns>失败或者没有数据 返回 空集合 否则返回 查询的到结果集</returns>
public List<TEntity> GetAllEntitysAndOrderBy<TEntity, TKey>(Expression<Func<TEntity, TKey>> OrderByProc, bool IsDesc) where TEntity : class
{
try
{
using (TDbContext db = new TDbContext())
{
if (IsDesc)
{
return db.Set<TEntity>().OrderByDescending(OrderByProc).ToList();
}
return db.Set<TEntity>().OrderBy(OrderByProc).ToList();
}
}
catch (Exception exp)
{
Console.WriteLine(exp.Message);
return new List<TEntity>();
}
}
/// <summary>
/// 获取单个实体
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <param name="whereProc">过滤的表达式</param>
/// <returns>实体</returns>
public T GetSingleEntity<T>(Expression<Func<T, bool>> whereProc) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
return ef.Set<T>().Where(whereProc).FirstOrDefault();
// return ef.Set<T>().FirstOrDefault(whereProc);
}
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// 按条件查询排序后获取最后一个
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <typeparam name="TKey"></typeparam>
/// <param name="WhereProc"></param>
/// <param name="OrderByProcDesc"></param>
/// <returns></returns>
public TEntity GetLastEntity<TEntity, TKey>(Expression<Func<TEntity, bool>> WhereProc, Expression<Func<TEntity, TKey>> OrderByProcDesc) where TEntity : class
{
try
{
using (TDbContext db = new TDbContext())
{
return db.Set<TEntity>().Where(WhereProc).OrderByDescending(OrderByProcDesc).FirstOrDefault();
}
}
catch (Exception exp)
{
Console.WriteLine(exp.Message);
return null;
}
}
/// <summary>
/// 获取分页的实体
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <typeparam name="TKey">成员属性的类型</typeparam>
/// <param name="pageIndex">当前的页码</param>
/// <param name="pageCount">每页显示的行数</param>
/// <param name="orderProc">排序的表达式</param>
/// <param name="orderByType">排序的类型</param>
/// <returns></returns>
public List<T> GetPageEntityByOrder<T, TKey>(int pageIndex, int pageCount, Expression<Func<T, TKey>> orderProc, string orderByType) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
if (orderByType == "desc")
{
return ef.Set<T>().OrderByDescending(orderProc).Skip((pageIndex - 1) * pageCount).Take(pageCount).ToList();
}
return ef.Set<T>().OrderBy(orderProc).Skip((pageIndex - 1) * pageCount).Take(pageCount).ToList();
}
}
catch (Exception)
{
return new List<T>();
}
}
/// <summary>
/// 获取分页的实体
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <typeparam name="TKey">成员属性的类型</typeparam>
/// <param name="pageIndex">当前的页码</param>
/// <param name="pageCount">每页显示的行数</param>
/// <param name="whereProc">过滤的表达式</param>
/// <param name="orderProc">排序的表达式</param>
/// <param name="orderByType">排序的类型</param>
/// <returns></returns>
public List<T> GetPageEntityByOrderAndWhere<T, TKey>(int pageIndex, int pageCount, Expression<Func<T, bool>> whereProc, Expression<Func<T, TKey>> orderProc, string orderByType) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
if (orderByType == "desc")
{
return ef.Set<T>().OrderByDescending(orderProc).Where(whereProc).Skip((pageIndex - 1) * pageCount).Take(pageCount).ToList();
}
return ef.Set<T>().OrderBy(orderProc).Where(whereProc).Skip((pageIndex - 1) * pageCount).Take(pageCount).ToList();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return new List<T>();
}
}
/// <summary>
///获取 按条件过滤排序分组 数据
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <typeparam name="TKey">成员属性的类型</typeparam>
/// <typeparam name="TKey2">成员属性的类型</typeparam>
/// <param name="whereProc">过滤的表达式</param>
/// <param name="orderProc">排序的表达式</param>
/// <param name="orderByType">排序的类型</param>
/// <param name="groupProc">分组的表达式</param>
/// <returns></returns>
public List<T> GetEntityAndGroup<T, TKey, TKey2>(Expression<Func<T, bool>> whereProc, Expression<Func<T, TKey>> orderProc, string orderByType, Expression<Func<T, TKey2>> groupProc) where T : class
{
try
{
List<T> list = new List<T>();
using (TDbContext ef = new TDbContext())
{
if (orderByType == "desc")
{
var Groups = ef.Set<T>().Where(whereProc).OrderByDescending(orderProc).GroupBy(groupProc).ToList();
Groups.ForEach(G => { list.AddRange(G); });
}
else
{
var Groups = ef.Set<T>().Where(whereProc).OrderBy(orderProc).GroupBy(groupProc).ToList();
Groups.ForEach(G => { list.AddRange(G); });
}
return list;
}
}
catch (Exception)
{
return new List<T>();
}
}
/// <summary>
/// 分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageIndex">当前是第几页</param>
/// <param name="pageCount">每一页的行数</param>
/// <returns></returns>
public List<T> GetEntityByPage<T, Tkey>(int pageIndex, int pageCount, Expression<Func<T, Tkey>> orderExp, Expression<Func<T, bool>> whereExp) where T : class
{
try
{
TDbContext db = new TDbContext();
if (whereExp == null)
{
return db.Set<T>().OrderBy(orderExp).Skip((pageIndex - 1) * pageCount).Take(pageCount).ToList();
}
else
{
return db.Set<T>().OrderBy(orderExp).Where(whereExp).Skip((pageIndex - 1) * pageCount).Take(pageCount).ToList();
}
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// 获取总条数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereExp"></param>
/// <returns></returns>
public int GetAllCount<T>(Expression<Func<T, bool>> whereExp) where T : class
{
TDbContext db = new TDbContext();
if (whereExp == null)
{
return db.Set<T>().ToList().Count;
}
else
{
return db.Set<T>().Where(whereExp).ToList().Count;
}
}
/// <summary>
/// 根据条件删除
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <param name="whereProc">删除的条件</param>
/// <returns>结果</returns>
public bool DeleteEntityByWhere<T>(Expression<Func<T, bool>> whereProc) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
List<T> list = ef.Set<T>().Where(whereProc).ToList();
if (list == null || list.Count == 0)
{
return true;
}
list.ForEach(R =>
{
ef.Set<T>().Remove(R);
});
return ef.SaveChanges() > 0;
}
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 删除单个实体
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <param name="t">待删除的实体</param>
/// <returns>结果</returns>
public bool DeleteEntity<T>(T t) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
ef.Set<T>().Attach(t);
ef.Set<T>().Remove(t);
//var entity = ef.Entry<T>(t);
//entity.State = System.Data.EntityState.Deleted;
return ef.SaveChanges() > 0;
}
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 删除多个
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <param name="tlist">待删除的集合</param>
/// <returns>结果</returns>
public bool DelEntity<T>(List<T> tlist) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
if (tlist.Count == 0)
{
return true;
}
foreach (var item in tlist)
{
ef.Set<T>().Attach(item);
ef.Set<T>().Remove(item);
}
return ef.SaveChanges() > 0;
}
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 根据条件删除权限
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <param name="whereProc">删除的条件</param>
/// <returns>结果</returns>
public bool DeleteClass<T>(Expression<Func<T, bool>> whereProc) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
List<T> list = ef.Set<T>().Where(whereProc).ToList();
if (list == null || list.Count == 0)
{
return true;
}
list.ForEach(R =>
{
ef.Set<T>().Remove(R);
});
return ef.SaveChanges() > 0;
}
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 修改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public bool UpdateEntity<T>(T t) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
if (ef.Entry<T>(t).State == System.Data.Entity.EntityState.Detached)
{
ef.Set<T>().Attach(t);
ef.Entry<T>(t).State = System.Data.Entity.EntityState.Modified;
}
return ef.SaveChanges() > 0;
}
}
catch (Exception ex)
{
string error = ex.Message;
return false;
}
}
/// <summary>
/// 添加实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public bool AddEntity<T>(T t) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
ef.Set<T>().Add(t);
return ef.SaveChanges() > 0;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
}
/// <summary>
/// 根据sql查询数据 (可以使自定义实体)
/// </summary>
/// <typeparam name="TModel">实体类型</typeparam>
/// <param name="sql">sql语句</param>
/// <returns>失败或者没有数据 返回 空集合 否则返回 查询的到结果集</returns>
public List<TModel> GetDataBySql<TModel>(string sql) where TModel : class
{
try
{
using (TDbContext db = new TDbContext())
{
return db.Database.SqlQuery<TModel>(sql).ToList();
}
}
catch (Exception exp)
{
Console.WriteLine(exp.Message);
return new List<TModel>();
}
}
/// <summary>
/// 执行sql 增加 删除 修改
/// </summary>
/// <param name="sql">增加 删除 修改 sql</param>
/// <returns>true 成功 false 失败</returns>
public bool ExcuteSql(string sql)
{
try
{
using (TDbContext db = new TDbContext())
{
return db.Database.ExecuteSqlCommand(sql) > 0;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
}
/// <summary>
/// 添加一组数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tlist"></param>
/// <returns></returns>
public bool AddClass<T>(List<T> tlist) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
if (tlist.Count == 0)
{
return true;
}
foreach (var item in tlist)
{
ef.Set<T>().Add(item);
}
return ef.SaveChanges() > 0;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
}
/// <summary>
/// 获取分页数据(不需要条件)
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <typeparam name="TN">实体的成员属性的类型</typeparam>
/// <param name="pageIndex">当前请求的页面</param>
/// <param name="pageCount">每页的行数</param>
/// <param name="orderByProc">排序的表达式</param>
/// <returns></returns>
public List<T> GetPageEntity<T, TN>(int pageIndex, int pageCount, Expression<Func<T, TN>> orderByProc, Expression<Func<T, bool>> whereExp) where T : class
{
try
{
using (TDbContext ef = new TDbContext())
{
if (whereExp == null)
{
return ef.Set<T>().OrderBy(orderByProc).Skip((pageIndex - 1) * pageCount).Take(pageCount).ToList();
}
return ef.Set<T>().OrderBy(orderByProc).Skip((pageIndex - 1) * pageCount).Take(pageCount).Where(whereExp).ToList();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return null;
}
}
}
}
原文: