mysql ef 随机排序_EFCore+Mysql倉儲層建設(分頁、多字段排序、部分字段更新)

前沿

園子里已有挺多博文介紹了EFCore+Mysql/MSSql如何進行使用，但實際開發不會把EF層放在Web層混合起來，需要多個項目配合結構清晰的進行分層工作，本文根據個人實踐經驗總結將各個項目進行分層，僅供想自己搭建,包含數據倉儲以及分頁多字段排序。

目錄結構

1.實體層(EF)搭建

1.1添加Nuget包

1.2添加實體

1.3構造DbContext

1.4數據遷移生成數據庫

2.倉儲層搭建

2.1添加Nuget包

2.2添加必要的支持IEnumerable和IQueryable 的OrderBy字符串支持類LinqExtensions

2.3構造RepositoryBase

2.4添加Table文件夾，添加SysUserRepository

2.5添加工作單元UnitOfWork

3.WebApi項目測試

3.1注入EF

3.2測試

4.Github項目地址

正文

1.實體層(EF)搭建

新建.NetCore類庫項目Entity,本人使用的是2.0的SDK

1.1添加Nuget包

PM> Install-Package Microsoft.AspNetCore.All -version 2.0.9

PM> Install-Package Pomelo.EntityFrameworkCore.MySql -version 2.0.1

1.2添加實體

父類EntityBase

usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel.DataAnnotations;usingSystem.Text;namespaceEntity.Table

{public classEntityBase

{///

///創建時間///

[Display(Name = "創建時間")]public DateTime? CreateTime { get; set; } =DateTime.Now;///

///創建人///

[Display(Name = "創建人")]

[StringLength(32)]public string CreateUser { get; set; }///

///狀態0-刪除，1-正常，2-禁用，3-待審核///

[Display(Name = "狀態0-邏輯刪除，1-正常，2-禁用,...")]public virtual int? Status { get; set; } = 2;///

///排序///

[Display(Name = "排序")]public int? Sort { get; set; } = 0;///

///備注///

[Display(Name = "備注")]

[StringLength(200)]public string Remark { get; set; } = "";

}

}

實體類SysUser

usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel.DataAnnotations;usingSystem.ComponentModel.DataAnnotations.Schema;usingSystem.Text;namespaceEntity.Table

{

[Table("Sys_User")]public classSysUser : EntityBase

{

[Key]

[StringLength(32)]public string SysUserId { get; set; }

[Display(Name= "用戶名")]

[Required]

[StringLength(32)]public string UserName { get; set; }

[Display(Name= "密碼")]

[StringLength(255)]public string Password { get; set; }

[Display(Name= "真實姓名")]

[StringLength(32)]public string TrueName { get; set; }

[Display(Name= "昵稱")]

[StringLength(32)]public string NikeName { get; set; }

[Display(Name= "手機號")]

[StringLength(20)]public string Mobile { get; set; }

[Display(Name= "郵箱")]

[EmailAddress]

[StringLength(100)]public string Email { get; set; }

[Display(Name= "QQOpenid")]

[StringLength(200)]public string QQ { get; set; }

[Display(Name= "微信openid")]

[StringLength(200)]public string WX { get; set; }

[Display(Name= "頭像")]

[StringLength(255)]public string Avatar { get; set; }

[Display(Name= "性別")]

[StringLength(1)]public string Sex { get; set; }

[Display(Name= "用戶類型")]

[StringLength(1)]public string UserType { get; set; }//0-前台用戶，1-管理用戶

}

}

1.3構造DbContext

usingEntity.Table;usingMicrosoft.EntityFrameworkCore;usingMicrosoft.Extensions.Configuration;usingSystem;namespaceEntity

{public classAeDbContext : DbContext

{#region 構造方法

public AeDbContext(DbContextOptions options) : base(options) { }public AeDbContext() { } //非注入構造方式

#endregion

#region 表對象

public virtual DbSet SysUsers { get; set; }#endregion

protected override voidOnConfiguring(DbContextOptionsBuilder optionsBuilder)

{base.OnConfiguring(optionsBuilder);if (!optionsBuilder.IsConfigured)

{//重點:數據遷移或者直接New AeDbContext時候用到的鏈接字符串獲取方式

var builder = newConfigurationBuilder()

.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);var configuration =builder.Build();string connectionString = configuration.GetConnectionString("SQLConnection");

optionsBuilder.UseMySql(connectionString);

}

}

}

}

在這有重寫OnConfiguring方法，如果沒有構造數據庫鏈接字符串的話則到appsettings.json中去取,注意將appsettings.json文件始終復制

appsettings.json

{"ConnectionStrings": {"SQLConnection": "server=127.0.0.1;database=eftest;userid=root;pwd=123456;port=3306;sslmode=none;"},"server.urls": "http://localhost:5001" //監聽端口配置，可多個

}

1.4數據遷移生成數據庫

打開PM選擇默認項目Entity

輸入

PM> Add-Migration init

若提示The configuration file 'appsettings.json' was not found and is not optional. The physical path is 'E:\VS項目\EFTest\Entity\bin\Debug\netcoreapp2.0\appsettings.json'.

則為沒檢測到appsettings.json，需要文件更改屬性為復制

提示

后進行更新數據庫，如下為更新成功。

PM> update-database

最后Entity項目內容如下

2.倉儲層搭建

新建.NetCore類庫項目Repository並引用項目Entity

2.1添加Nuget包

PM> Install-Package Microsoft.EntityFrameworkCore -version 2.0.3

PM> Install-Package LinqKit.Microsoft.EntityFrameworkCore -version 1.1.15

2.2添加必要的支持IEnumerable和IQueryable 的OrderBy字符串支持類LinqExtensions

usingSystem;usingSystem.Linq;usingSystem.Linq.Expressions;usingSystem.Reflection;usingSystem.Collections.Generic;namespaceRepository

{internal static classLinqExtensions {private static PropertyInfo GetPropertyInfo(Type objType, stringname) {var properties =objType.GetProperties();var matchedProperty = properties.FirstOrDefault(p => p.Name ==name);if (matchedProperty == null) {throw new ArgumentException("name");

}returnmatchedProperty;

}private staticLambdaExpression GetOrderExpression(Type objType, PropertyInfo pi) {var paramExpr =Expression.Parameter(objType);var propAccess =Expression.PropertyOrField(paramExpr, pi.Name);var expr =Expression.Lambda(propAccess, paramExpr);returnexpr;

}///

///多個OrderBy用逗號隔開,屬性前面帶-號表示反序排序，exp:"name,-createtime"///

///

///

///

///

public static IEnumerable OrderByBatch(this IEnumerable query, stringname) {var index = 0;var a = name.Split(',');foreach (var item ina) {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)genericMethod.Invoke(null, new object[] { query, expr.Compile() });

}returnquery;

}///

///多個OrderBy用逗號隔開,屬性前面帶-號表示反序排序，exp:"name,-createtime"///

///

///

///

///

public static IQueryable OrderByBatch(this IQueryable query, stringname)

{var index = 0;var a = name.Split(',');foreach (var item ina)

{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)genericMethod.Invoke(null, new object[] { query, expr });

}returnquery;

}///

///正序排序單個///

///

///

///

///

public static IQueryable OrderBy(this IQueryable query, stringname) {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)genericMethod.Invoke(null, new object[] { query, expr });

}///

///正序排序單個(非首個)///

///

///

///

///

public static IQueryable ThenBy(this IQueryable query, stringname)

{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)genericMethod.Invoke(null, new object[] { query, expr });

}///

///反序排序單個///

///

///

///

///

public static IQueryable OrderByDescending(this IQueryable query, stringname)

{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)genericMethod.Invoke(null, new object[] { query, expr });

}///

///反序排序單個(非首個)///

///

///

///

///

public static IQueryable ThenByDescending(this IQueryable query, stringname)

{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)genericMethod.Invoke(null, new object[] { query, expr });

}

}

}

以及分頁支持類PageData

usingSystem;usingSystem.Collections.Generic;usingSystem.Text;namespaceRepository

{public class PageData{public List Rows { get; set; }public long Totals { get; set; }

}

}

2.3構造RepositoryBase

usingEntity;usingMicrosoft.EntityFrameworkCore;usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Linq.Expressions;usingSystem.Text;usingSystem.Threading.Tasks;usingMicrosoft.EntityFrameworkCore.Infrastructure;usingSystem.IO;usingEntity.Table;namespaceRepository

{public class RepositoryBase whereT : EntityBase

{private readonly DbSet_dbSet;public AeDbContext DbContext { get; } = null;publicRepositoryBase(AeDbContext context)

{

DbContext=context;

_dbSet= DbContext.Set();

}public DatabaseFacade Database =>DbContext.Database;public IQueryable Entities =>_dbSet.AsQueryable().AsNoTracking();public intSaveChanges()

{returnDbContext.SaveChanges();

}public async TaskSaveChangesAsync()

{return awaitDbContext.SaveChangesAsync();

}public bool Any(Expression>whereLambd)

{return_dbSet.Where(whereLambd).Any();

}public voidDisposed()

{throw new Exception("不允許在這里釋放上下文，請在UnitOfWork中操作");//DbContext.Dispose();

}#region 插入數據

public bool Insert(T entity, bool isSaveChange = true)

{

_dbSet.Add(entity);if(isSaveChange)

{return SaveChanges() > 0;

}return false;

}public async Task InsertAsync(T entity, bool isSaveChange = true)

{

_dbSet.Add(entity);if(isSaveChange)

{return await SaveChangesAsync() > 0;

}return false;

}public bool Insert(List entitys, bool isSaveChange = true)

{

_dbSet.AddRange(entitys);if(isSaveChange)

{return SaveChanges() > 0;

}return false;

}public async Task InsertAsync(List entitys, bool isSaveChange = true)

{

_dbSet.AddRange(entitys);if(isSaveChange)

{return await SaveChangesAsync() > 0;

}return false;

}#endregion

#region 刪除

public bool Delete(T entity, bool isSaveChange = true)

{

_dbSet.Attach(entity);

_dbSet.Remove(entity);return isSaveChange ? SaveChanges() > 0 : false;

}public bool Delete(List entitys, bool isSaveChange = true)

{

entitys.ForEach(entity=>{

_dbSet.Attach(entity);

_dbSet.Remove(entity);

});return isSaveChange ? SaveChanges() > 0 : false;

}public virtual async Task DeleteAsync(T entity, bool isSaveChange = true)

{

_dbSet.Attach(entity);

_dbSet.Remove(entity);return isSaveChange ? await SaveChangesAsync() > 0 : false;

}public virtual async Task DeleteAsync(List entitys, bool isSaveChange = true)

{

entitys.ForEach(entity=>{

_dbSet.Attach(entity);

_dbSet.Remove(entity);

});return isSaveChange ? await SaveChangesAsync() > 0 : false;

}#endregion

#region 更新數據

public bool Update(T entity, bool isSaveChange = true, List 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 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 UpdateAsync(T entity, bool isSaveChange = true, List 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 await SaveChangesAsync() > 0;

}return false;

}public async Task UpdateAsync(List entitys, bool isSaveChange = true)

{if (entitys == null || entitys.Count == 0)

{return false;

}

entitys.ForEach(c=>{

_dbSet.Attach(c);

DbContext.Entry(c).State =EntityState.Modified;

});if(isSaveChange)

{return await SaveChangesAsync() > 0;

}return false;

}#endregion

#region 查找

public long Count(Expression> predicate = null)

{if (predicate == null)

{

predicate= c => true;

}return_dbSet.LongCount(predicate);

}public async Task CountAsync(Expression> predicate = null)

{if (predicate == null)

{

predicate= c => true;

}return await_dbSet.LongCountAsync(predicate);

}public T Get(objectid)

{if (id == null)

{return default(T);

}return_dbSet.Find(id);

}public T Get(Expression> predicate = null, bool isNoTracking = true)

{var data = isNoTracking ?_dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate);returndata.FirstOrDefault();

}public async Task GetAsync(objectid)

{if (id == null)

{return default(T);

}return await_dbSet.FindAsync(id);

}public async Task GetAsync(Expression> predicate = null, bool isNoTracking = true)

{var data = isNoTracking ?_dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate);return awaitdata.FirstOrDefaultAsync();

}public async Task> GetListAsync(Expression> 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 awaitdata.ToListAsync();

}public List GetList(Expression> 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);

}returndata.ToList();

}public async Task> LoadAsync(Expression> 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 Load(Expression> predicate = null, bool isNoTracking = true)

{if (predicate == null)

{

predicate= c => true;

}return isNoTracking ?_dbSet.Where(predicate).AsNoTracking() : _dbSet.Where(predicate);

}#region 分頁查找

///

///分頁查詢異步///

/// 查詢添加(可有，可無)

/// 排序條件(一定要有)

/// 當前頁碼

/// 每頁大小

/// 排序正反

///

public async Task> GetPageAsync(Expression> whereLambda, Expression> orderBy, int pageIndex, int pageSize, bool isOrder = true, bool isNoTracking = true)

{

IQueryable data = isOrder ?_dbSet.OrderBy(orderBy) :

_dbSet.OrderByDescending(orderBy);if (whereLambda != null)

{

data= isNoTracking ?data.Where(whereLambda).AsNoTracking() : data.Where(whereLambda);

}

PageData pageData = new PageData{

Totals= awaitdata.CountAsync(),

Rows= await data.Skip((pageIndex - 1) *pageSize).Take(pageSize).ToListAsync()

};returnpageData;

}///

///分頁查詢異步///

/// 查詢添加(可有，可無)

/// 排序條件(一定要有，多個用逗號隔開，倒序開頭用-號)

/// 當前頁碼

/// 每頁大小

///

public async Task> GetPageAsync(Expression> whereLambda, string ordering, int pageIndex, int pageSize, bool isNoTracking = true)

{//分頁 一定注意： Skip 之前一定要 OrderBy

if (string.IsNullOrEmpty(ordering))

{

ordering= nameof(T) + "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 pageData = new PageData{

Totals= awaitdata.CountAsync(),

Rows= await data.Skip((pageIndex - 1) *pageSize).Take(pageSize).ToListAsync()

};returnpageData;

}///

///分頁查詢///

/// 查詢添加(可有，可無)

/// 排序條件(一定要有，多個用逗號隔開，倒序開頭用-號)

/// 當前頁碼

/// 每頁大小

///

public PageData GetPage(Expression> whereLambda, string ordering, int pageIndex, int pageSize, bool isNoTracking = true)

{//分頁 一定注意： Skip 之前一定要 OrderBy

if (string.IsNullOrEmpty(ordering))

{

ordering= nameof(T) + "Id";//默認以Id排序

}var data =_dbSet.OrderByBatch(ordering);if (whereLambda != null)

{

data= isNoTracking ?data.Where(whereLambda).AsNoTracking() : data.Where(whereLambda);

}

PageData pageData = new PageData{

Totals=data.Count(),

Rows= data.Skip((pageIndex - 1) *pageSize).Take(pageSize).ToList()

};returnpageData;

}#endregion

#endregion}

}

2.4添加Table文件夾，添加SysUserRepository

usingEntity;usingEntity.Table;usingSystem;usingSystem.Collections.Generic;usingSystem.Text;namespaceRepository.Table

{public class SysUserRepository : RepositoryBase{public SysUserRepository(AeDbContext context) : base(context)

{

}

}

}

2.5添加工作單元UnitOfWork

usingEntity;usingMicrosoft.EntityFrameworkCore;usingMicrosoft.EntityFrameworkCore.Storage;usingRepository.Table;usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;namespaceRepository

{public classUnitOfWork : IDisposable

{public static UnitOfWork Instance = new UnitOfWork(newAeDbContext());public AeDbContext DbContext { get; set; } = null;publicUnitOfWork(AeDbContext dbContext)

{

DbContext=dbContext;

}#region 字段

private SysUserRepository _SysUserRepository = null;#endregion

#region 操作類屬性

public SysUserRepository SysUserRepository => _SysUserRepository ?? (_SysUserRepository = newSysUserRepository(DbContext));#endregion

#region 倉儲操作(提交事務保存SaveChanges(),回滾RollBackChanges(),釋放資源Dispose())

///

///保存///

public intSaveChanges()

{returnDbContext.SaveChanges();

}public async TaskSaveChangesAsync()

{return awaitDbContext.SaveChangesAsync();

}///

///回滾///

public voidRollBackChanges()

{var items =DbContext.ChangeTracker.Entries().ToList();

items.ForEach(o=> o.State =EntityState.Unchanged);

}///

///釋放資源///

private bool disposed = false;protected virtual void Dispose(booldisposing)

{if (!this.disposed)

{if(disposing)

{

DbContext.Dispose();//隨着工作單元的銷毀而銷毀

}

}this.disposed = true;

}public voidDispose()

{

Dispose(true);

GC.SuppressFinalize(this);

}publicIDbContextTransaction BeginTransaction()

{var scope =DbContext.Database.BeginTransaction();returnscope;

}#endregion}

}

這樣倉儲層就構造完成了，篇幅已經很長了，Service層就先不介紹了。

3.WebApi項目測試

新建.NetCore的項目的Web應用程序ApiTest,選擇webapi方式，並引用Entity和Repository項目

3.1注入EF

public voidConfigureServices(IServiceCollection services)

{

services.AddMvc();

services.AddDbContext(options => options.UseMySql(Configuration.GetConnectionString("SQLConnection")));

services.AddTransient(typeof(UnitOfWork));//注入工作單元

}

3.2測試

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;usingMicrosoft.AspNetCore.Mvc;usingRepository;namespaceApiTest.Controllers

{

[Route("api/[controller]")]public classValuesController : Controller

{

UnitOfWork _unitOfWork;publicValuesController(UnitOfWork unitOfWork)

{

_unitOfWork=unitOfWork;

}//GET api/values

[HttpGet]public IEnumerableGet()

{var adminModel = _unitOfWork.SysUserRepository.Get("admin");if(adminModel == null)

{

adminModel= newEntity.Table.SysUser()

{

SysUserId= "admin",

UserName= "admin",

Password= "123456",

UserType= "1",

CreateTime=DateTime.Now,

Status= 1,

Sort= 0};

_unitOfWork.SysUserRepository.Insert(adminModel);

}return new List{ adminModel.UserName , adminModel.Password };

}//GET api/values/5

[HttpGet("{id}")]public string Get(intid)

{return "value";

}//POST api/values

[HttpPost]public void Post([FromBody]stringvalue)

{

}//PUT api/values/5

[HttpPut("{id}")]public void Put(int id, [FromBody]stringvalue)

{

}//DELETE api/values/5

[HttpDelete("{id}")]public void Delete(intid)

{

}

}

}

測試結果：

倉儲分頁查詢 測試

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;usingEntity.Table;usingLinqKit;usingMicrosoft.AspNetCore.Http;usingMicrosoft.AspNetCore.Mvc;usingRepository;namespaceApiTest.Controllers

{

[Produces("application/json")]

[Route("api/[controller]")]public classQueryPageController : Controller

{

UnitOfWork _unitOfWork;publicQueryPageController(UnitOfWork unitOfWork)

{

_unitOfWork=unitOfWork;

}///

///分頁測試///

///

[HttpGet]public async Task Get(string userName,int? Status,stringNikeName)

{var userCount =_unitOfWork.SysUserRepository.Count();if(userCount < 100)

{await CreateUser(100);

}//獲取分頁數據方式1//獲取用戶名包含user的排序根據Sort正序，CreateTime倒序排序的第1頁的20條數據

var pageModel = await _unitOfWork.SysUserRepository.GetPageAsync(o => o.UserName.Contains("user") && o.Status == 1, "Sort,-CreateTime", 1, 20);//獲取分頁數據方式2//使用PredicateBuilder獲取分頁數據方式支持篩選

var predicate = PredicateBuilder.New(true);//查詢條件,推薦后台使用這種方式靈活篩選

#region 添加條件查詢

if (!string.IsNullOrEmpty(userName))

{

predicate= predicate.And(i =>i.UserName.Contains(userName));

}if (Status != null)

{

predicate= predicate.And(i =>i.Status.Equals(Status));

}if (!string.IsNullOrEmpty(NikeName))

{

predicate= predicate.And(i =>i.NikeName.Equals(NikeName));

}#endregion

var pageModel1 = await _unitOfWork.SysUserRepository.GetPageAsync(predicate, "Sort,-CreateTime", 1, 20);return Json(new{ pageModel, pageModel1 });

}///

///構造數據///

///

///

public async Task CreateUser(int count = 1)

{

List inserUsers = new List();for (int i = 0; i < count; i++)

{

inserUsers.Add(newSysUser

{

SysUserId= Guid.NewGuid().ToString("N"),

UserName= $"user{i}",

Password= "123456",

UserType= "0",

CreateTime=DateTime.Now,

Status= 1,

Sort= 0});

}return await_unitOfWork.SysUserRepository.InsertAsync(inserUsers);

}

}

}

結果：

4.Github項目地址

最后附上github源碼：https://github.com/atorzhang/EFTest