/*
* ------------------------------------------------------------------------------
*
* 创 建 者：F_Gang @2019
*
* 创建日期：2019-08-27 17:21:31
*
* 机器名称：DESKTOP-QUSP01L
*
* 版 本 号：4.0.30319.42000
*
* 功能描述：
*
* ------------------------------------------------------------------------------
*/ using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using XinLianXin.Utility; namespace MyProject.DAL
{
/// <summary>
/// EF DataAccessLayer 基类
/// </summary>
/// <typeparam name="T"></typeparam>
public class BaseicService<T> where T : class, new()
{
#region 基础
/// <summary>
/// 上下文
/// </summary>
protected readonly BaseDBContext dB; /// <summary>
/// 构造方法
/// </summary>
/// <param name="db"></param>
public BaseicService(BaseDBContext db)
{
dB = db;
} public void Dispose()
{
dB.Dispose();
}
#endregion #region Add
/// <summary>
/// 增加一条数据
/// </summary>
/// <param name="t"></param>
/// <returns>返回新增实体对象，考虑到使用新增主键编号</returns>
public T AddEntity(T t)
{
dB.Entry<T>(t).State = EntityState.Added;
if (dB.SaveChanges() > )
return t;
return null;
} /// <summary>
/// 批量增加多条数据到一张表（事务处理）
/// </summary>
/// <param name="ts">实体对象集合</param>
/// <returns></returns>
public bool AddEntity(IQueryable<T> ts)
{
dB.Set<T>().AddRange(ts);
return dB.SaveChanges() > ;
}
#endregion #region Modify
/// <summary>
/// 编辑一条数据，返回bool 类型
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public bool ModifyEntity(T t)
{
dB.Entry<T>(t).State = EntityState.Modified;
return dB.SaveChanges() > ;
} /// <summary>
/// 批量修改
/// </summary>
/// <param name="ts"></param>
/// <returns></returns>
public bool ModifyEntity(IQueryable<T> ts)
{
ts.ForEachAsync(o => { dB.Entry<T>(o).State = EntityState.Modified; });
return dB.SaveChanges() > ;
} /// <summary>
/// 修改一条数据,会修改指定列的值
/// </summary>
/// <param name="t">要修改的实体对象</param>
/// <param name="proNames">要修改的属性名称</param>
/// <returns></returns>
public bool ModifyEntity(T t, params string[] proNames)
{
DbEntityEntry<T> dbe = dB.Entry<T>(t); //先将所有属性状态标记为未修改
dbe.State = EntityState.Unchanged; //将要修改的属性状态标记为修改
proNames.ToList().ForEach(o => dbe.Property(o).IsModified = true); return dB.SaveChanges() > ;
} /// <summary>
/// 根据条件批量修改指定的列
/// </summary>
/// <param name="t"></param>
/// <param name="conditions">lambda</param>
/// <param name="proNames"></param>
/// <returns></returns>
public bool ModifyEntity(T t, Expression<Func<T, bool>> conditions, params string[] proNames)
{
var entitys = dB.Set<T>().Where(conditions).ToList();
PropertyInfo[] proinfos = t.GetType().GetProperties();
List<PropertyInfo> list = new List<PropertyInfo>();
proinfos.ToList().ForEach(o => { if (proNames.Contains(o.Name)) list.Add(o); });
entitys.ForEach(c => { list.ForEach(o => { object value = o.GetValue(t, null); o.SetValue(c, value, null); }); });
return dB.SaveChanges() > ;
}
#endregion #region Delete
/// <summary>
/// 删除一个实体对象
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public bool DeleteEntity(T t)
{
dB.Entry<T>(t).State = EntityState.Deleted;
return dB.SaveChanges() > ;
} /// <summary>
/// 根据条件删除单个实体对象
/// </summary>
/// <param name="conditions">lambda</param>
/// <returns></returns>
public bool DeleteSingleObj(Expression<Func<T, bool>> conditions)
{
var obj = dB.Set<T>().FirstOrDefault(conditions);
if (obj == null) throw new Exception("您要删除的内容已不存在");
dB.Entry<T>(obj).State = EntityState.Deleted;
return dB.SaveChanges() > ;
} /// <summary>
/// 根据条件批量删除
/// </summary>
/// <param name="conditions">lambda</param>
/// <returns></returns>
public bool BatchDeletion(Expression<Func<T, bool>> conditions)
{
var datas = dB.Set<T>().Where(conditions).ToList();
dB.Set<T>().RemoveRange(datas);
return dB.SaveChanges() > ;
}
#endregion #region Select
/// <summary>
/// 根据条件查询实体对象
/// </summary>
/// <param name="conditions">lambda</param>
/// <returns></returns>
public T GetEntityByQuery(Expression<Func<T, bool>> conditions)
{
return dB.Set<T>().FirstOrDefault(conditions);
} /// <summary>
/// 根据条件查询所有
/// </summary>
/// <param name="conditions">lambda</param>
/// <returns></returns>
public IQueryable<T> GetAll()
{
return dB.Set<T>().AsQueryable();
} /// <summary>
/// 根据条件查询所有
/// </summary>
/// <param name="conditions">lambda</param>
/// <returns></returns>
public IQueryable<T> GetListByQuery(Expression<Func<T, bool>> conditions)
{
return dB.Set<T>().Where(conditions).AsQueryable();
} /// <summary>
/// 根据条件查询列表
/// </summary>
/// <typeparam name="S">Sort fields</typeparam>
/// <param name="conditions">lambda</param>
/// <param name="orderByLambds"></param>
/// <param name="isAsc">是否倒叙</param>
/// <returns></returns>
public IQueryable<T> GetListByQuery<TKey>(Expression<Func<T, bool>> conditions, Expression<Func<T, TKey>> orderByLambds, bool isAsc)
{
var temp = dB.Set<T>().Where(conditions);
if (isAsc)
return temp.OrderBy(orderByLambds).AsQueryable();
else
return temp.OrderByDescending(orderByLambds).AsQueryable();
} #region 带分页查询
/// <summary>
/// 带分页查询
/// </summary>
/// <typeparam name="S"></typeparam>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="conditions">查询条件</param>
/// <param name="orderKey">排序字段</param>
/// <param name="isAsc">是否倒叙</param>
/// <param name="rows">返回总条数</param>
/// <param name="totalPage">返回总页数</param>
/// <returns></returns>
public IQueryable<T> GetPageListByQuery<TKey>(int pageIndex, int pageSize, Expression<Func<T, bool>> conditions, Expression<Func<T, TKey>> orderKey, bool isAsc, out int rows, out int totalPage)
{
var temp = dB.Set<T>().Where(conditions);
rows = temp.Count(); totalPage = GetTotalPage(rows, pageSize); if (isAsc)
temp = temp.OrderBy(orderKey);
else
temp = temp.OrderByDescending(orderKey); temp = temp.Skip(pageSize * (pageIndex - )).Take(pageSize).AsQueryable(); return temp;
}
#endregion #region 传统sql结合EF分页实现查询 fg 2019年8月28日
/// <summary>
/// 传统sql结合EF分页实现查询
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="sql"></param>
/// <param name="where">条件</param>
/// <param name="orderKey">排序字段</param>
/// <param name="isAsc">是否倒叙</param>
/// <param name="rows">返回总条数</param>
/// <param name="totalPage">总页数</param>
/// <returns></returns>
public IQueryable<T> GetPageListByQuery(int pageIndex, int pageSize, string sql, string where, string orderKey, bool isAsc, out int rows, out int totalPage)
{
if (string.IsNullOrEmpty(sql))
throw new Exception("sql 存在空内容，请核实后在尝试"); sql = sql + " WHERE 1 = 1 " + where;
if (!string.IsNullOrEmpty(orderKey))
sql += " ORDER BY " + orderKey;
if (!isAsc)
sql += " DESC "; var temp = dB.Database.SqlQuery<T>(sql).AsQueryable();
rows = temp.Count();
totalPage = GetTotalPage(rows, pageSize);
temp = temp.Skip(pageSize * (pageIndex - )).Take(pageSize);
return temp;
} #region 计算分页总页数
/// <summary>
/// 返回总页数
/// </summary>
/// <param name="rows">总统条数</param>
/// <param name="pageSize">页大小</param>
/// <returns></returns>
private int GetTotalPage(int rows, int pageSize)
{
if (rows % pageSize == )
return rows / pageSize;
return rows / pageSize + ;
}
#endregion
#endregion
#endregion #region 显式Tran 事务
/// <summary>
/// 显式执行事务
/// </summary>
/// <param name="dics"></param>
/// <returns></returns>
public int ExeTran(IDictionary<string, DbParameter[]> dics)
{
int result = ;
string outmsg = string.Empty;
DbConnection con = ((IObjectContextAdapter)dB).ObjectContext.Connection;
using (DbTransaction tran = con.BeginTransaction())
{
try
{
// 使用传统的执行事务的方法
foreach (var dic in dics)
{
if (dic.Value != null)
result += dB.Database.ExecuteSqlCommand(dic.Key, dic.Value);
else
result += dB.Database.ExecuteSqlCommand(dic.Key, dic.Value);
} tran.Commit();
return result; }
catch (Exception ex)
{
tran.Rollback();
LogHelper.Default.WriteError(string.Format("事务执行失败：") + ex.Message +
string.Format("\n执行内容:" + JsonHelper.ToJSON(dics, out outmsg)));
throw ex;
}
finally
{
con.Close();
}
}
}
#endregion
}
}
自定义排序用法：
string outmsg = string.Empty;
try
{
int rows = ;
int totalPath = ;
// 排序字段类型 查询条件 排序条件
return newsDAL.GetPageListByQuery<string>(pageInde, pageSize, o => o.c_user_id == , o => o.zhaiyao, false, out rows, out totalPath).ToList();
}
catch (Exception ex)
{
outmsg = ex.Message;
return null;
}