仓储层搭建
添加查询支持Repository类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
namespace Model.Repository
{
internal static class LinqExtensions
{
private static PropertyInfo GetPropertyInfo(Type objType, string name)
{
var properties = objType.GetProperties();
var matchedProperty = properties.FirstOrDefault(p => p.Name == name);
if (matchedProperty == null)
{
throw new ArgumentException("name");
}
return matchedProperty;
}
private static LambdaExpression GetOrderExpression(Type objType, PropertyInfo pi)
{
var paramExpr = Expression.Parameter(objType);
var propAccess = Expression.PropertyOrField(paramExpr, pi.Name);
var expr = Expression.Lambda(propAccess, paramExpr);
return expr;
}
/// <summary>
/// 多个OrderBy用逗号隔开,属性前面带-号表示反序排序，exp:"name,-createtime"
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="name"></param>
/// <returns></returns>
public static IEnumerable<T> OrderByBatch<T>(this IEnumerable<T> query, string name)
{
var index = 0;
var a = name.Split(',');
foreach (var item in a)
{
var m = index++ > 0 ? "ThenBy" : "OrderBy";
if (item.StartsWith("-"))
{
m += "Descending";
name = item.Substring(1);
}
else
{
name = item;
}
name = name.Trim();
var propInfo = GetPropertyInfo(typeof(T), name);
var expr = GetOrderExpression(typeof(T), propInfo);
var method = typeof(Enumerable).GetMethods().FirstOrDefault(mt => mt.Name == m && mt.GetParameters().Length == 2);
var genericMethod = method.MakeGenericMethod(typeof(T), propInfo.PropertyType);
query = (IEnumerable<T>)genericMethod.Invoke(null, new object[] { query, expr.Compile() });
}
return query;
}
/// <summary>
/// 多个OrderBy用逗号隔开,属性前面带-号表示反序排序，exp:"name,-createtime"
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="name"></param>
/// <returns></returns>
public static IQueryable<T> OrderByBatch<T>(this IQueryable<T> query, string name)
{
var index = 0;
var a = name.Split(',');
foreach (var item in a)
{
var m = index++ > 0 ? "ThenBy" : "OrderBy";
if (item.StartsWith("-"))
{
m += "Descending";
name = item.Substring(1);
}
else
{
name = item;
}
name = name.Trim();
var propInfo = GetPropertyInfo(typeof(T), name);
var expr = GetOrderExpression(typeof(T), propInfo);
var method = typeof(Queryable).GetMethods().FirstOrDefault(mt => mt.Name == m && mt.GetParameters().Length == 2);
var genericMethod = method.MakeGenericMethod(typeof(T), propInfo.PropertyType);
query = (IQueryable<T>)genericMethod.Invoke(null, new object[] { query, expr });
}
return query;
}
/// <summary>
/// 正序排序单个
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="name"></param>
/// <returns></returns>
public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, string name)
{
var propInfo = GetPropertyInfo(typeof(T), name);
var expr = GetOrderExpression(typeof(T), propInfo);
var method = typeof(Queryable).GetMethods().FirstOrDefault(m => m.Name == "OrderBy" && m.GetParameters().Length == 2);
var genericMethod = method.MakeGenericMethod(typeof(T), propInfo.PropertyType);
return (IQueryable<T>)genericMethod.Invoke(null, new object[] { query, expr });
}
/// <summary>
/// 正序排序单个（非首个）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="name"></param>
/// <returns></returns>
public static IQueryable<T> ThenBy<T>(this IQueryable<T> query, string name)
{
var propInfo = GetPropertyInfo(typeof(T), name);
var expr = GetOrderExpression(typeof(T), propInfo);
var method = typeof(Queryable).GetMethods().FirstOrDefault(m => m.Name == "ThenBy" && m.GetParameters().Length == 2);
var genericMethod = method.MakeGenericMethod(typeof(T), propInfo.PropertyType);
return (IQueryable<T>)genericMethod.Invoke(null, new object[] { query, expr });
}
/// <summary>
/// 反序排序单个
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="name"></param>
/// <returns></returns>
public static IQueryable<T> OrderByDescending<T>(this IQueryable<T> query, string name)
{
var propInfo = GetPropertyInfo(typeof(T), name);
var expr = GetOrderExpression(typeof(T), propInfo);
var metMethods = typeof(Queryable).GetMethods();
var method = metMethods.FirstOrDefault(m => m.Name == "OrderByDescending" && m.GetParameters().Length == 2);
var genericMethod = method.MakeGenericMethod(typeof(T), propInfo.PropertyType);
return (IQueryable<T>)genericMethod.Invoke(null, new object[] { query, expr });
}
/// <summary>
/// 反序排序单个（非首个）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="name"></param>
/// <returns></returns>
public static IQueryable<T> ThenByDescending<T>(this IQueryable<T> query, string name)
{
var propInfo = GetPropertyInfo(typeof(T), name);
var expr = GetOrderExpression(typeof(T), propInfo);
var metMethods = typeof(Queryable).GetMethods();
var method = metMethods.FirstOrDefault(m => m.Name == "ThenByDescending" && m.GetParameters().Length == 2);
var genericMethod = method.MakeGenericMethod(typeof(T), propInfo.PropertyType);
return (IQueryable<T>)genericMethod.Invoke(null, new object[] { query, expr });
}
}
public class PageData<T>
{
public List<T> Rows { get; set; }
public long Totals { get; set; }
}
}
构造仓储RepositoryBase
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Model.Table;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
namespace Model.Repository
{
public class RepositoryBase<Entity> where Entity : EntityBase
{
private readonly DbSet<Entity> _dbSet;
public EFDbContext DbContext { get; } = null;
public RepositoryBase(EFDbContext _DbContext) {
DbContext = _DbContext;
_dbSet = DbContext.Set<Entity>();
}
public DatabaseFacade Database => DbContext.Database;
public int SaveChanges() {
return DbContext.SaveChanges();
}
public async Task<int> SaveChangesAsync() {
return await DbContext.SaveChangesAsync();
}
public bool Insert(Entity entity, bool isSaveChange = true)
{
_dbSet.Add(entity);
if (isSaveChange)
{
return SaveChanges() > 0;
}
return false;
}
public async Task<bool> InsertAsync(Entity entity, bool isSaveChange = true)
{
_dbSet.Add(entity);
if (isSaveChange)
{
return await SaveChangesAsync() > 0;
}
return false;
}
public bool Insert(List<Entity> entitys, bool isSaveChange = true)
{
_dbSet.AddRange(entitys);
if (isSaveChange)
{
return SaveChanges() > 0;
}
return false;
}
public async Task<bool> InsertAsync(List<Entity> entitys, bool isSaveChange = true)
{
_dbSet.AddRange(entitys);
if (isSaveChange)
{
return await SaveChangesAsync() > 0;
}
return false;
}
public bool Delete(Entity entity, bool isSaveChange = true)
{
_dbSet.Attach(entity);
_dbSet.Remove(entity);
return isSaveChange ? SaveChanges() > 0 : false;
}
public bool Delete(List<Entity> entitys, bool isSaveChange = true)
{
entitys.ForEach(entity =>
{
_dbSet.Attach(entity);
_dbSet.Remove(entity);
});
return isSaveChange ? SaveChanges() > 0 : false;
}
public virtual async Task<bool> DeleteAsync(Entity entity, bool isSaveChange = true)
{
_dbSet.Attach(entity);
_dbSet.Remove(entity);
return isSaveChange ? await SaveChangesAsync() > 0 : false;
}
public virtual async Task<bool> DeleteAsync(List<Entity> entitys, bool isSaveChange = true)
{
entitys.ForEach(entity =>
{
_dbSet.Attach(entity);
_dbSet.Remove(entity);
});
return isSaveChange ? await SaveChangesAsync() > 0 : false;
}
public bool Update(Entity entity, bool isSaveChange = true, List<string> updatePropertyList = null, bool modified = true)
{
if (entity == null)
{
return false;
}
_dbSet.Attach(entity);
var entry = DbContext.Entry(entity);
if (updatePropertyList == null)
{
entry.State = EntityState.Modified;//全字段更新
}
else
{
if (modified)
{
updatePropertyList.ForEach(c => {
entry.Property(c).IsModified = true; //部分字段更新的写法
});
}
else
{
entry.State = EntityState.Modified;//全字段更新
updatePropertyList.ForEach(c => {
entry.Property(c).IsModified = false; //部分字段不更新的写法
});
}
}
if (isSaveChange)
{
return SaveChanges() > 0;
}
return false;
}
public bool Update(List<Entity> 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(Entity entity, bool isSaveChange = true, List<string> updatePropertyList = null, bool modified = true)
{
if (entity == null)
{
return false;
}
_dbSet.Attach(entity);
var entry = DbContext.Entry<Entity>(entity);
if (updatePropertyList == null)
{
entry.State = EntityState.Modified;//全字段更新
}
else
{
if (modified)
{
updatePropertyList.ForEach(c => {
entry.Property(c).IsModified = true; //部分字段更新的写法
});
}
else
{
entry.State = EntityState.Modified;//全字段更新
updatePropertyList.ForEach(c => {
entry.Property(c).IsModified = false; //部分字段不更新的写法
});
}
}
if (isSaveChange)
{
return await SaveChangesAsync() > 0;
}
return false;
}
public async Task<bool> UpdateAsync(List<Entity> entitys, bool isSaveChange = true)
{
if (entitys == null || entitys.Count == 0)
{
return false;
}
entitys.ForEach(c => {
_dbSet.Attach(c);
DbContext.Entry<Entity>(c).State = EntityState.Modified;
});
if (isSaveChange)
{
return await SaveChangesAsync() > 0;
}
return false;
}
public long Count(Expression<Func<Entity, bool>> predicate = null)
{
if (predicate == null)
{
predicate = c => true;
}
return _dbSet.LongCount(predicate);
}
public async Task<long> CountAsync(Expression<Func<Entity, bool>> predicate = null)
{
if (predicate == null)
{
predicate = c => true;
}
return await _dbSet.LongCountAsync(predicate);
}
public Entity Get(object id)
{
if (id == null)
{
return default(Entity);
}
return _dbSet.Find(id);
}
public Entity Get(Expression<Func<Entity, bool>> predicate = null, bool isNoTracking = true)
{
var data = isNoTracking ? _dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate);
return data.FirstOrDefault();
}
public async Task<Entity> GetAsync(object id)
{
if (id == null)
{
return default(Entity);
}
return await _dbSet.FindAsync(id);
}
public async Task<Entity> GetAsync(Expression<Func<Entity, bool>> predicate = null, bool isNoTracking = true)
{
var data = isNoTracking ? _dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate);
return await data.FirstOrDefaultAsync();
}
public async Task<List<Entity>> GetListAsync(Expression<Func<Entity, 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<Entity> GetList(Expression<Func<Entity, 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<Entity>> LoadAsync(Expression<Func<Entity, 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<Entity> Load(Expression<Func<Entity, bool>> predicate = null, bool isNoTracking = true)
{
if (predicate == null)
{
predicate = c => true;
}
return isNoTracking ? _dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate);
}
/// <summary>
/// 分页查询异步
/// </summary>
/// <param name="whereLambda">查询添加（可有，可无）</param>
/// <param name="ordering">排序条件（一定要有）</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页大小</param>
/// <param name="isOrder">排序正反</param>
/// <returns></returns>
public async Task<PageData<Entity>> GetPageAsync<TKey>(Expression<Func<Entity, bool>> whereLambda, Expression<Func<Entity, TKey>> orderBy, int pageIndex, int pageSize, bool isOrder = true, bool isNoTracking = true)
{
IQueryable<Entity> data = isOrder ?
_dbSet.OrderBy(orderBy) :
_dbSet.OrderByDescending(orderBy);
if (whereLambda != null)
{
data = isNoTracking ? data.Where(whereLambda).AsNoTracking() : data.Where(whereLambda);
}
PageData<Entity> pageData = new PageData<Entity>
{
Totals = await data.CountAsync(),
Rows = await data.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync()
};
return pageData;
}
/// <summary>
/// 分页查询异步
/// </summary>
/// <param name="whereLambda">查询添加（可有，可无）</param>
/// <param name="ordering">排序条件（一定要有，多个用逗号隔开，倒序开头用-号）</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页大小</param>
/// <returns></returns>
public async Task<PageData<Entity>> GetPageAsync(Expression<Func<Entity, bool>> whereLambda, string ordering, int pageIndex, int pageSize, bool isNoTracking = true)
{
// 分页 一定注意： Skip 之前一定要 OrderBy
if (string.IsNullOrEmpty(ordering))
{
ordering = nameof(Entity) + "Id";//默认以Id排序
}
var data = _dbSet.OrderByBatch(ordering);
if (whereLambda != null)
{
data = isNoTracking ? data.Where(whereLambda).AsNoTracking() : data.Where(whereLambda);
}
//查看生成的sql，找到大数据下分页巨慢原因为order by 耗时
//var sql = data.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToSql();
//File.WriteAllText(@"D:\sql.txt",sql);
PageData<Entity> pageData = new PageData<Entity>
{
Totals = await data.CountAsync(),
Rows = await data.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync()
};
return pageData;
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="whereLambda">查询添加（可有，可无）</param>
/// <param name="ordering">排序条件（一定要有，多个用逗号隔开，倒序开头用-号）</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页大小</param>
/// <returns></returns>
public PageData<Entity> GetPage(Expression<Func<Entity, bool>> whereLambda, string ordering, int pageIndex, int pageSize, bool isNoTracking = true)
{
// 分页 一定注意： Skip 之前一定要 OrderBy
if (string.IsNullOrEmpty(ordering))
{
ordering = nameof(Entity) + "Id";//默认以Id排序
}
var data = _dbSet.OrderByBatch(ordering);
if (whereLambda != null)
{
data = isNoTracking ? data.Where(whereLambda).AsNoTracking() : data.Where(whereLambda);
}
PageData<Entity> pageData = new PageData<Entity>
{
Totals = data.Count(),
Rows = data.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList()
};
return pageData;
}
}
}
添加UserRepository
using System;
using System.Collections.Generic;
using System.Text;
namespace Model.Repository.Table
{
public class UserRepository : RepositoryBase<User>
{
public UserRepository(EFDbContext context):base(context){
}
}
}
在WebApi Startup.cs 文件中ConfigureServices注入EF,依赖注入用到了Autofac框架，需要添加Nuget包
public IServiceProvider ConfigureServices(IServiceCollection services)
{
services.AddDbContext<EFDbContext>(options => options.UseSqlServer(connection));
//注册Autofac组件 实例化容器
var builder = new ContainerBuilder();
//注册单个接口
builder.RegisterType<UserRepository>();
//批量注册程序集
//builder.RegisterAssemblyTypes(System.Reflection.Assembly.Load("IService"),//实现类所在的程序集名称
//System.Reflection.Assembly.Load("Service"))
// .Where(t => t.Name.EndsWith("Service"))//带筛选
// .AsImplementedInterfaces()//是以接口方式进行注入，注入这些类的所有的公共接口作为服务
// .InstancePerLifetimeScope();//在一个生命周期中，每一次的依赖组件或调用(Resolve())创建一个单一的共享的实例，且每一个不同的生命周期域，实例是不同的
builder.Populate(services);
this.ApplicationContainer = builder.Build();
//第三方IOC接管 core内置DI容器
return new AutofacServiceProvider(this.ApplicationContainer);
}
Controller中进行测试
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using GetMD5Encryption;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Model;
using Model.Dto;
using Model.Repository.Table;
using Newtonsoft.Json;
namespace BlogApi.Controllers
{
[Produces("application/json")]
[Route("api/user")]
[ApiController]
public class UserController : ControllerBase
{
private readonly UserRepository _service;
public UserController(UserRepository service) {
_service = service;
}
/// <summary>
/// 新增
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
[HttpPost]
[Route("Add")]
public async Task<IActionResult> Add(User user) {
user.ID = Guid.NewGuid().ToString("N");
JsonResult result = new JsonResult(await _service.InsertAsync(user));
return result;
}
}
}
执行返回Code=200表示成功
image.png
数据库添加成功
image.png
网友评论