利用EFCore 封装Repository（可扩展不同数据的sql操作）
本篇是对EFCore 进行下封装并实现基本的增删改查的同步异步方法及针对不同数据库的批量插入、sql语句直接操作数据库；
一、 先定义基础仓储接口IRepository
public interface IRepository<TEntity,TKey> where TEntity : class { #region 查找数据 long Count(Expression<Func<TEntity, bool>> predicate = null); Task<long> CountAsync(Expression<Func<TEntity, bool>> predicate = null); TEntity Get(Expression<Func<TEntity, bool>> predicate, bool isNoTracking); Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> predicate, bool isNoTracking); Task<TEntity> GetAsync(TKey id); IQueryable<TEntity> Load(Expression<Func<TEntity, bool>> predicate , bool isNoTracking); Task<IQueryable<TEntity>> LoadAsync(Expression<Func<TEntity, bool>> predicate , bool isNoTracking); List<TEntity> GetList(Expression<Func<TEntity, bool>> predicate, string ordering, bool isNoTracking ); Task<List<TEntity>> GetListAsync(Expression<Func<TEntity, bool>> predicate, string ordering, bool isNoTracking ); #endregion #region 插入数据 bool Insert(TEntity entity, bool isSaveChange); Task<bool> InsertAsync(TEntity entity, bool isSaveChange); bool Insert(List<TEntity> entitys, bool isSaveChange = true); Task<bool> InsertAsync(List<TEntity> entitys, bool isSaveChange); #endregion #region 删除(删除之前需要查询) bool Delete(TEntity entity, bool isSaveChange); bool Delete(List<TEntity> entitys, bool isSaveChange); Task<bool> DeleteAsync(TEntity entity, bool isSaveChange); Task<bool> DeleteAsync(List<TEntity> entitys, bool isSaveChange = true); #endregion #region 修改数据 bool Update(TEntity entity, bool isSaveChange, List<string> updatePropertyList); Task<bool> UpdateAsync(TEntity entity, bool isSaveChange, List<string> updatePropertyList); bool Update(List<TEntity> entitys, bool isSaveChange); Task<bool> UpdateAsync(List<TEntity> entitys, bool isSaveChange ); #endregion #region 执行Sql语句 void BulkInsert<T>(List<T> entities); int ExecuteSql(string sql); Task<int> ExecuteSqlAsync(string sql); int ExecuteSql(string sql, List<DbParameter> spList); Task<int> ExecuteSqlAsync(string sql, List<DbParameter> spList); DataTable GetDataTableWithSql(string sql); DataTable GetDataTableWithSql(string sql, List<DbParameter> spList); #endregion
二、实现IRepository接口
public abstract class BaseRepository<TEntity,TKey> :IRepository<TEntity,TKey> where TEntity : class { private readonly DbSet<TEntity> _dbSet; public GeneralDbContext _dbContext { get; } = null; /// <summary> /// 连接字符串 /// </summary> protected string _connectionString { get; set; } /// <summary> /// 数据库类型 /// </summary> private DatabaseType _dbType { get; set; } public BaseRepository(GeneralDbContext context) { _dbContext = context; _dbSet = _dbContext.Set<TEntity>(); } public DatabaseFacade Database => _dbContext.Database; public IQueryable<TEntity> Entities => _dbSet.AsQueryable().AsNoTracking(); public int SaveChanges() { return _dbContext.SaveChanges(); } public async Task<int> SaveChangesAsync() { return await _dbContext.SaveChangesAsync(); } public bool Any(Expression<Func<TEntity, bool>> whereLambd) { return _dbSet.Where(whereLambd).Any(); } #region 插入数据 public bool Insert(TEntity entity, bool isSaveChange = true) { _dbSet.Add(entity); if (isSaveChange) { return SaveChanges() > 0; } return false; } public async Task<bool> InsertAsync(TEntity entity, bool isSaveChange = true) { _dbSet.Add(entity); if (isSaveChange) { return await SaveChangesAsync() > 0; } return false; } public bool Insert(List<TEntity> entitys, bool isSaveChange = true) { _dbSet.AddRange(entitys); if (isSaveChange) { return SaveChanges() > 0; } return false; } public async Task<bool> InsertAsync(List<TEntity> entitys, bool isSaveChange = true) { _dbSet.AddRange(entitys); if (isSaveChange) { return await SaveChangesAsync() > 0; } return false; } #endregion #region 删除 public bool Delete(TEntity entity, bool isSaveChange = true) { _dbSet.Attach(entity); _dbSet.Remove(entity); return isSaveChange ? SaveChanges() > 0 : false; } public bool Delete(List<TEntity> entitys, bool isSaveChange = true) { entitys.ForEach(entity => { _dbSet.Attach(entity); _dbSet.Remove(entity); }); return isSaveChange ? SaveChanges() > 0 : false; } public virtual async Task<bool> DeleteAsync(TEntity entity, bool isSaveChange = true) { _dbSet.Attach(entity); _dbSet.Remove(entity); return isSaveChange ? await SaveChangesAsync() > 0 : false; } public virtual async Task<bool> DeleteAsync(List<TEntity> entitys, bool isSaveChange = true) { entitys.ForEach(entity => { _dbSet.Attach(entity); _dbSet.Remove(entity); }); return isSaveChange ? await SaveChangesAsync() > 0 : false; } #endregion #region 更新数据 public bool Update(TEntity entity, bool isSaveChange = true, List<string> updatePropertyList = null) { if (entity == null) { return false; } _dbSet.Attach(entity); var entry = _dbContext.Entry(entity); if (updatePropertyList == null) { entry.State = EntityState.Modified;//全字段更新 } else { updatePropertyList.ForEach(c => { entry.Property(c).IsModified = true; //部分字段更新的写法 }); } if (isSaveChange) { return SaveChanges() > 0; } return false; } public bool Update(List<TEntity> entitys, bool isSaveChange = true) { if (entitys == null || entitys.Count == 0) { return false; } entitys.ForEach(c => { Update(c, false); }); if (isSaveChange) { return SaveChanges() > 0; } return false; } public async Task<bool> UpdateAsync(TEntity entity, bool isSaveChange = true, List<string> updatePropertyList = null) { if (entity == null) { return false; } _dbSet.Attach(entity); var entry = _dbContext.Entry<TEntity>(entity); if (updatePropertyList == null) { entry.State = EntityState.Modified;//全字段更新 } else { updatePropertyList.ForEach(c => { entry.Property(c).IsModified = true; //部分字段更新的写法 }); } if (isSaveChange) { return await SaveChangesAsync() > 0; } return false; } public async Task<bool> UpdateAsync(List<TEntity> entitys, bool isSaveChange = true) { if (entitys == null || entitys.Count == 0) { return false; } entitys.ForEach(c => { _dbSet.Attach(c); _dbContext.Entry<TEntity>(c).State = EntityState.Modified; }); if (isSaveChange) { return await SaveChangesAsync() > 0; } return false; } #endregion #region 查找 public long Count(Expression<Func<TEntity, bool>> predicate = null) { if (predicate == null) { predicate = c => true; } return _dbSet.LongCount(predicate); } public async Task<long> CountAsync(Expression<Func<TEntity, bool>> predicate = null) { if (predicate == null) { predicate = c => true; } return await _dbSet.LongCountAsync(predicate); } public TEntity Get(TKey id) { if (id == null) { return default(TEntity); } return _dbSet.Find(id); } public TEntity Get(Expression<Func<TEntity, bool>> predicate = null, bool isNoTracking = true) { var data = isNoTracking ? _dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate); return data.FirstOrDefault(); } public async Task<TEntity> GetAsync(TKey id) { if (id == null) { return default(TEntity); } return await _dbSet.FindAsync(id); } public async Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> predicate = null, bool isNoTracking = true) { var data = isNoTracking ? _dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate); return await data.FirstOrDefaultAsync(); } public async Task<List<TEntity>> GetListAsync(Expression<Func<TEntity, bool>> predicate = null, string ordering = "", bool isNoTracking = true) { var data = isNoTracking ? _dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate); if (!string.IsNullOrEmpty(ordering)) { data = data.OrderByBatch(ordering); } return await data.ToListAsync(); } public List<TEntity> GetList(Expression<Func<TEntity, bool>> predicate = null, string ordering = "", bool isNoTracking = true) { var data = isNoTracking ? _dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate); if (!string.IsNullOrEmpty(ordering)) { data = data.OrderByBatch(ordering); } return data.ToList(); } public async Task<IQueryable<TEntity>> LoadAsync(Expression<Func<TEntity, bool>> predicate = null, bool isNoTracking = true) { if (predicate == null) { predicate = c => true; } return await Task.Run(() => isNoTracking ? _dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate)); } public IQueryable<TEntity> Load(Expression<Func<TEntity, bool>> predicate = null, bool isNoTracking = true) { if (predicate == null) { predicate = c => true; } return isNoTracking ? _dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate); } #endregion #region SQL语句 public virtual void BulkInsert<T>(List<T> entities) { } public int ExecuteSql(string sql) { return _dbContext.Database.ExecuteSqlCommand(sql) ; } public Task<int> ExecuteSqlAsync(string sql) { return _dbContext.Database.ExecuteSqlCommandAsync(sql); } public int ExecuteSql(string sql, List<DbParameter> spList) { return _dbContext.Database.ExecuteSqlCommand(sql, spList.ToArray()); } public Task<int> ExecuteSqlAsync(string sql, List<DbParameter> spList) { return _dbContext.Database.ExecuteSqlCommandAsync(sql, spList.ToArray()); } public virtual DataTable GetDataTableWithSql(string sql) { throw new NotImplementedException(); } public virtual DataTable GetDataTableWithSql(string sql, List<DbParameter> spList) { throw new NotImplementedException(); } #endregion
｝
三、BaseRepository是个抽象类，有些比较复杂的sql语句通过EF来处理比较麻烦，还需要直接操作sql语句的方法，因不同的数据库sql语句不一样，针对不同的数据，继承BaseRepository这个基类，重写sql语句方法，下面简单实现SqlServerRepository仓储
public class SqlServerRepository<TEntity,TKey>: BaseRepository<TEntity,TKey>,IRepository<TEntity,TKey> where TEntity : class { protected ConfigOption _dbOpion; public SqlServerRepository(GeneralDbContext generalDbContext,IOptionsSnapshot<ConfigOption> options) :base(generalDbContext) { _dbOpion = options.Get("config"); _connectionString = _dbOpion.ReadWriteHosts; } #region 插入数据 /// <summary> /// 使用Bulk批量插入数据（适合大数据量，速度非常快） /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="entities">数据</param> public override void BulkInsert<T>(List<T> entities) { using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString =_connectionString ; if (conn.State != ConnectionState.Open) { conn.Open(); } string tableName = string.Empty; var tableAttribute = typeof(T).GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault(); if (tableAttribute != null) tableName = ((TableAttribute)tableAttribute).Name; else tableName = typeof(T).Name; SqlBulkCopy sqlBC = new SqlBulkCopy(conn) { BatchSize = 100000, BulkCopyTimeout = 0, DestinationTableName = tableName }; using (sqlBC) { sqlBC.WriteToServer(entities.ToDataTable()); } } } public override DataTable GetDataTableWithSql(string sql) { return GetDataTableWithSql(sql); } public override DataTable GetDataTableWithSql(string sql, List<DbParameter> spList=null) { DataTable dt = new DataTable(); ; using (SqlConnection conn = new SqlConnection(_connectionString)) { SqlDataAdapter da = new SqlDataAdapter(sql, conn); da.SelectCommand.CommandType = CommandType.Text; if (spList.ToArray() != null) { da.SelectCommand.Parameters.AddRange(spList.ToArray()); } da.Fill(dt); } return dt; } #endregion }
引用了http://www.cnblogs.com/coldairarrow/p/9626691.html该框架设计思想及部分代码
