Entity Framework Core 数据库访问与仓储模式
创建实体模型与数据库
创建实体模型
public class Article { public int Id { get; set; } public string Submitter { get; set; } public string Title { get; set; } public string Category { get; set; } public string Content { get; set; } public int Traffic { get; set; } public int CommentNum { get; set; } public DateTime UpdateTime { get; set; } public DateTime CreateTime { get; set; } public string Remark { get; set; } public bool? IsDeleted { get; set; } }
设计仓储基类接口
public interface IBaseRepository<TEntity> where TEntity : class { Task<TEntity> InsertAsync(TEntity entity,bool autoSave=false,CancellationToken cancellationToken=default); Task InsertManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default); Task<TEntity> UpdateAsync(TEntity entity, bool autoSave = false, CancellationToken cancellationToken = default); Task UpdateManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default); Task DeleteAsync(TEntity entity, bool autoSave = false, CancellationToken cancellationToken = default); Task DeleteAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default); Task DeleteManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default); Task<TEntity> FindAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default); Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default); Task<List<TEntity>> GetListAsync(CancellationToken cancellationToken = default); Task<List<TEntity>> GetListAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default); Task<List<TEntity>> GetPageListAsync(int skipCount, int maxResultCount, string sorting, CancellationToken cancellationToken = default); Task<long> GetCountAsync(CancellationToken cancellationToken = default); Task<long> GetCountAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken); }
对仓储基接口进行实现
public class BaseRepository<TEntity> : IBaseRepository<TEntity> where TEntity : class, new() { private SwiftCodeBBSContext _context; public BaseRepository() { _context = new SwiftCodeBBSContext(); } protected SwiftCodeBBSContext DbContext() { return _context; } public async Task DeleteAsync(TEntity entity, bool autoSave = false, CancellationToken cancellationToken = default) { _context.Set<TEntity>().Remove(entity); if (autoSave) { await _context.SaveChangesAsync(cancellationToken); } } public async Task DeleteAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default) { var dbSet = _context.Set<TEntity>(); var entities = await dbSet.Where(predicate).ToListAsync(cancellationToken); await DeleteManyAsync(entities, autoSave, cancellationToken); if (autoSave) { await _context.SaveChangesAsync(cancellationToken); } } public async Task DeleteManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default) { _context.RemoveRange(entities); if (autoSave) { await _context.SaveChangesAsync(cancellationToken); } } public Task<TEntity> FindAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default) { return _context.Set<TEntity>().Where(predicate).SingleOrDefaultAsync(cancellationToken); } public async Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default) { var entity = await FindAsync(predicate, autoSave, cancellationToken); if (entity == null) { throw new Exception(nameof(TEntity) + ":数据不存在"); } return entity; } public Task<long> GetCountAsync(CancellationToken cancellationToken = default) { return _context.Set<TEntity>().LongCountAsync(cancellationToken); } public Task<long> GetCountAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken) { return _context.Set<TEntity>().Where(predicate).LongCountAsync(cancellationToken); } public Task<List<TEntity>> GetListAsync(CancellationToken cancellationToken = default) { return _context.Set<TEntity>().ToListAsync(cancellationToken); } public Task<List<TEntity>> GetListAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default) { return _context.Set<TEntity>().Where(predicate).ToListAsync(cancellationToken); } public Task<List<TEntity>> GetPageListAsync(int skipCount, int maxResultCount, string sorting, CancellationToken cancellationToken = default) { return _context.Set<TEntity>().Skip(skipCount).Take(maxResultCount).ToListAsync(cancellationToken); } public async Task<TEntity> InsertAsync(TEntity entity, bool autoSave = false, CancellationToken cancellationToken = default) { var savedEntity = (await _context.Set<TEntity>().AddAsync(entity, cancellationToken)).Entity; if (autoSave) { await _context.SaveChangesAsync(cancellationToken); } return savedEntity; } public async Task InsertManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default) { var entityArray = entities.ToArray(); await _context.Set<TEntity>().AddRangeAsync(entityArray, cancellationToken); if (autoSave) { await _context.SaveChangesAsync(cancellationToken); } } public async Task<TEntity> UpdateAsync(TEntity entity, bool autoSave = false, CancellationToken cancellationToken = default) { _context.Attach(entity); var updatedEntity = _context.Update(entity).Entity; if (autoSave) { await _context.SaveChangesAsync(cancellationToken); } return updatedEntity; } public async Task UpdateManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default) { _context.Set<TEntity>().UpdateRange(entities); if (autoSave) { await _context.SaveChangesAsync(cancellationToken); } } }
创建数据库
在API层通过Nuget引入Microsoft.EntityFrameworkCore.Design
在Repositories层通过Nuget引入Microsoft.EntityFrameworkCore,Microsoft.EntityFrameworkCore.SqlServer,Microsoft.EntityFrameworkCore.Tools
public class BBSContext : DbContext { public BBSContext() { } public BBSContext(DbContextOptions<BBSContext> options) : base(options) { } public DbSet<Article> Articles { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Article>().Property(p => p.Title).HasMaxLength(128); modelBuilder.Entity<Article>().Property(p => p.Submitter).HasMaxLength(64); modelBuilder.Entity<Article>().Property(p => p.Category).HasMaxLength(256); modelBuilder.Entity<Article>().Property(p => p.Content).HasMaxLength(128); modelBuilder.Entity<Article>().Property(p => p.Remark).HasMaxLength(1024); } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"Server=.;Database=CodeBBS; Uid=sa; password=sa;Trusted_Connection=True;Connection Timeout=600;MultipleActiveResultSets=True").LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information); } }
设计应用服务层基类与基接口
public interface IBaseService<TEntity> where TEntity : class { Task<TEntity> InsertAsync(TEntity entity, bool autoSave = false, CancellationToken cancellationToken = default); Task InsertManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default); Task<TEntity> UpdateAsync(TEntity entity, bool autoSave = false, CancellationToken cancellationToken = default); Task UpdateManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default); Task DeleteAsync(TEntity entity, bool autoSave = false, CancellationToken cancellationToken = default); Task DeleteAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default); Task DeleteManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default); Task<TEntity> FindAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default); Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default); Task<List<TEntity>> GetListAsync(CancellationToken cancellationToken = default); Task<List<TEntity>> GetListAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default); Task<List<TEntity>> GetPageListAsync(int skipCount, int maxResultCount, string sorting, CancellationToken cancellationToken = default); Task<long> GetCountAsync(CancellationToken cancellationToken = default); Task<long> GetCountAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken); }
public class BaseService<TEntity> : IBaseService<TEntity> where TEntity : class, new() { public IBaseRepository<TEntity> _baseRepository; public BaseService(IBaseRepository<TEntity> baseRepository) { _baseRepository = baseRepository; } public async Task DeleteAsync(TEntity entity, bool autoSave = false, CancellationToken cancellationToken = default) { await _baseRepository.DeleteAsync(entity, autoSave, cancellationToken); } public async Task DeleteAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default) { await _baseRepository.DeleteAsync(predicate, autoSave, cancellationToken); } public async Task DeleteManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default) { await _baseRepository.DeleteManyAsync(entities, autoSave, cancellationToken); } public Task<TEntity> FindAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default) { return _baseRepository.FindAsync(predicate, autoSave, cancellationToken); } public Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default) { return _baseRepository.GetAsync(predicate, autoSave, cancellationToken); } public Task<long> GetCountAsync(CancellationToken cancellationToken = default) { return _baseRepository.GetCountAsync(cancellationToken); } public Task<long> GetCountAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken) { return _baseRepository.GetCountAsync(predicate, cancellationToken); } public Task<List<TEntity>> GetListAsync(CancellationToken cancellationToken = default) { return _baseRepository.GetListAsync(cancellationToken); } public Task<List<TEntity>> GetListAsync(Expression<Func<TEntity, bool>> predicate, bool autoSave = false, CancellationToken cancellationToken = default) { return _baseRepository.GetListAsync(predicate, autoSave, cancellationToken); } public Task<List<TEntity>> GetPageListAsync(int skipCount, int maxResultCount, string sorting, CancellationToken cancellationToken = default) { return _baseRepository.GetPageListAsync(skipCount, maxResultCount, sorting, cancellationToken); } public Task<TEntity> InsertAsync(TEntity entity, bool autoSave = false, CancellationToken cancellationToken = default) { return _baseRepository.InsertAsync(entity, autoSave, cancellationToken); } public Task InsertManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default) { return _baseRepository.InsertManyAsync(entities, autoSave, cancellationToken); } public Task<TEntity> UpdateAsync(TEntity entity, bool autoSave = false, CancellationToken cancellationToken = default) { return _baseRepository.UpdateAsync(entity, autoSave, cancellationToken); } public async Task UpdateManyAsync(IEnumerable<TEntity> entities, bool autoSave = false, CancellationToken cancellationToken = default) { await _baseRepository.UpdateManyAsync(entities, autoSave, cancellationToken).ConfigureAwait(false); }