先建立資料操作類接口IRepository，定義資料操作方法
/// <summary>
/// Represents an entity repository
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
public partial interface IRepository<TEntity> where TEntity : class
{
#region Methods
/// <summary>
/// 根據id查找實列
/// </summary>
/// <param name="id">Identifier</param>
/// <returns>Entity</returns>
TEntity GetById(object[] id);
/// <summary>
/// 插入單條記錄
/// </summary>
/// <param name="entity">Entity</param>
TEntity Insert(TEntity entity);
/// <summary>
/// 批量插入記錄
/// </summary>
/// <param name="entities">Entities</param>
void Insert(IEnumerable<TEntity> entities);
/// <summary>
/// 更新單條記錄
/// </summary>
/// <param name="entity">Entity</param>
bool Update(TEntity entity);
/// <summary>
/// 批量更新記錄
/// </summary>
/// <param name="entities">Entities</param>
bool Update(IEnumerable<TEntity> entities);
/// <summary>
/// 智能更新單條記錄
/// </summary>
/// <param name="entity">Entity</param>
bool SmartUpdate(TEntity entity);
/// <summary>
/// 删除單條記錄
/// </summary>
/// <param name="entity">Entity</param>
void Delete(TEntity entity);
/// <summary>
/// 批量删除記錄
/// </summary>
/// <param name="entities">Entities</param>
void Delete(IEnumerable<TEntity> entities);
#endregion
#region Properties
/// <summary>
/// Gets a table
/// </summary>
IQueryable<TEntity> Table { get; }
/// <summary>
/// Gets a table with "no tracking" enabled (EF feature) Use it only when you load record(s) only for read-only operations
/// </summary>
IQueryable<TEntity> TableNoTracking { get; }
#endregion
} 接下來實作資料新增、删除、修改的操作和查詢方法，
public partial class EfRepository<TEntity> : IRepository<TEntity> where TEntity : class
{
private readonly IDbContext _context;
private DbSet<TEntity> _entities;
public EfRepository(IDbContext context)
{
this._context = context;
}
public EfRepository()
{
this._context = new ExtObjectContext();
}
#region Properties
/// <summary>
/// 數量保留小數位數
/// </summary>
protected int QuantityDecimals { get; set; } = 4;
/// <summary>
/// 單價保留小數位數
/// </summary>
protected int PriceDecimals { get; set; } = 4;
/// <summary>
/// 金額保留小數位數
/// </summary>
protected int AmountDecimals { get; set; } = 2;
/// <summary>
/// 系統登陸使用者資訊
/// </summary>
protected LoginUserInfo LoginUserInfo { get; set; }
/// <summary>
/// Gets a table
/// </summary>
public virtual IQueryable<TEntity> Table => Entities;
/// <summary>
/// Gets a table with "no tracking" enabled (EF feature) Use it only when you load record(s) only for read-only operations
/// </summary>
public virtual IQueryable<TEntity> TableNoTracking => Entities.AsNoTracking();
/// <summary>
/// Gets an entity set
/// </summary>
protected virtual DbSet<TEntity> Entities
{
get
{
if (_entities == null)
{
_entities = _context.Set<TEntity>();
}
return _entities;
}
}
/// <summary>
/// 依賴加載表達式
/// </summary>
public virtual List<Expression<Func<TEntity, object>>> IncludeExpression { get; set; }
/// <summary>
/// 更新字段表達式
/// </summary>
public virtual List<Expression<Func<TEntity, object>>> PropertyExpressions { get; set; }
#endregion
#region Utilities
/// <summary>
/// Rollback of entity changes and return full error message
/// </summary>
/// <param name="exception">Exception</param>
/// <returns>Error message</returns>
protected string GetFullErrorTextAndRollbackEntityChanges(DbUpdateException exception)
{
//rollback entity changes
if (_context is DbContext dbContext)
{
var entries = dbContext.ChangeTracker.Entries()
.Where(e => e.State == EntityState.Added || e.State == EntityState.Modified).ToList();
entries.ForEach(entry => entry.State = EntityState.Unchanged);
}
_context.SaveChanges();
return exception.ToString();
}
#endregion
#region Methods
/// <summary>
/// 設定緩存
/// </summary>
protected void SetMemoryCache()
{
Dictionary<string, string> SystemOptionDict = MemoryCacheHelper.GetItem(CacheConst.SystemOptionDict) as Dictionary<string, string>;
if (SystemOptionDict != null)
{
this.PriceDecimals = SystemOptionDict.ContainsKey(CacheConst.Price_Decimals) ? SystemOptionDict[CacheConst.Price_Decimals].ToInt32(4) : 4;
this.QuantityDecimals = SystemOptionDict.ContainsKey(CacheConst.Qty_Decimals) ? SystemOptionDict[CacheConst.Qty_Decimals].ToInt32(4) : 4;
this.AmountDecimals = SystemOptionDict.ContainsKey(CacheConst.Amount_Decimals) ? SystemOptionDict[CacheConst.Amount_Decimals].ToInt32(2) : 2;
}
this.LoginUserInfo = MemoryCacheHelper.GetItem(CacheConst.LoginUserInfo) as LoginUserInfo;
}
/// <summary>
/// 複制Entity
/// </summary>
/// <param name="entity">要複制對象</param>
/// <param name="reversal">是非沖銷</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public virtual TEntity Copy(TEntity entity, bool reversal = false)
{
this._context.Detach(entity);
try
{
TEntity Clone = Activator.CreateInstance<TEntity>();
Clone = entity;
Type Ts = entity.GetType();
PropertyInfo info = Ts.GetProperty("ID");
if (info != null)
{
info.SetValue(entity, 0, null);
}
return this.Insert(Clone);
}
catch (DbUpdateException exception)
{
//ensure that the detailed error text is saved in the Log
throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
}
}
/// <summary>
/// 複制Entity
/// </summary>
/// <param name="id">要複制對象ID</param>
/// <param name="reversal">是非沖銷</param>
/// <returns></returns>
public virtual TEntity Copy(int id, bool reversal = false)
{
TEntity entity = this.GetById(id);
return this.Copy(entity, reversal);
}
public virtual void Detach(TEntity entity)
{
_context.Detach(entity);
}
/// <summary>
/// Insert entity
/// </summary>
/// <param name="entity">Entity</param>
public virtual TEntity Insert(TEntity entity)
{
if (entity == null)
throw new ArgumentNullException(nameof(entity));
try
{
this.Entities.Add(entity);
_context.SaveChanges();
}
catch (DbUpdateException exception)
{
//ensure that the detailed error text is saved in the Log
throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
}
return entity;
}
/// <summary>
/// Insert entities
/// </summary>
/// <param name="entities">Entities</param>
public virtual void Insert(IEnumerable<TEntity> entities)
{
if (entities == null)
throw new ArgumentNullException(nameof(entities));
try
{
Entities.AddRange(entities);
_context.SaveChanges();
}
catch (DbUpdateException exception)
{
//ensure that the detailed error text is saved in the Log
LogTextHelper.Error(exception.Message);
throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
}
}
/// <summary>
/// 更新實體
/// </summary>
/// <param name="entity">Entity</param>
public virtual bool Update(TEntity entity)
{
bool succeed = false;
if (entity == null)
throw new ArgumentNullException(nameof(entity));
try
{
Entities.Update(entity);
_context.SaveChanges();
succeed = true;
}
catch (DbUpdateException exception)
{
//ensure that the detailed error text is saved in the Log
throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
}
return succeed;
}
/// <summary>
/// 批量更新實體
/// </summary>
/// <param name="entities">Entities</param>
public virtual bool Update(IEnumerable<TEntity> entities)
{
bool succeed = false;
if (entities == null)
throw new ArgumentNullException(nameof(entities));
try
{
Entities.UpdateRange(entities);
_context.SaveChanges();
succeed = true;
}
catch (DbUpdateException exception)
{
//ensure that the detailed error text is saved in the Log
throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
}
return succeed;
}
public virtual bool SmartUpdate(TEntity entity)
{
if (this.PropertyExpressions.Count > 0)
return this.SmartUpdate(entity, this.PropertyExpressions);
else
return this.Update(entity);
}
/// <summary>
/// 部分字段更新
/// </summary>
/// <param name="entity">Entity</param>
/// <param name="PropertyExpressions">要更新字段表達式</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException"></exception>
public virtual bool SmartUpdate(TEntity entity, List<Expression<Func<TEntity, object>>> PropertyExpressions)
{
using (var dbContext = new ExtObjectContext())
{
bool succeed = false;
if (entity == null)
throw new ArgumentNullException(nameof(entity));
try
{
dbContext.Attach(entity);
foreach (var p in PropertyExpressions)
{
dbContext.Entry(entity).Property(p).IsModified = true;
}
dbContext.SaveChanges();
succeed = true;
}
catch (DbUpdateException exception)
{
throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
}
return succeed;
}
}
/// <summary>
/// 部分字段更新
/// </summary>
/// <param name="entity">要更新的實體</param>
/// <param name="PropertyExpression">要更新的字段表達式</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException">空值錯誤</exception>
/// <exception cref="Exception"></exception>
public virtual int SmartUpdate(TEntity entity, Expression<Func<TEntity, object>> PropertyExpression)
{
if (entity == null)
throw new ArgumentNullException(nameof(entity));
using (var dbContext = new ExtObjectContext())
{
try
{
if (entity == null)
throw new ArgumentNullException(nameof(entity));
dbContext.Attach(entity);
dbContext.Entry(entity).Property(PropertyExpression).IsModified = true;
return dbContext.SaveChanges();
}
catch (DbUpdateException exception)
{
//ensure that the detailed error text is saved in the Log
throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
}
}
}
/// <summary>
/// 删除記錄
/// </summary>
/// <param name="entity">Entity</param>
public virtual void Delete(TEntity entity)
{
if (entity == null)
throw new ArgumentNullException(nameof(entity));
try
{
Entities.Remove(entity);
_context.SaveChanges();
}
catch (DbUpdateException exception)
{
//ensure that the detailed error text is saved in the Log
throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
}
}
/// <summary>
/// 根據實體ID删除記錄
/// </summary>
/// <param name="ID">實體ID</param>
public virtual void Delete(int ID)
{
this.Delete(this.GetById(ID));
}
/// <summary>
/// 批量删除記錄
/// </summary>
/// <param name="entities">Entities</param>
public virtual void Delete(IEnumerable<TEntity> entities)
{
if (entities == null)
throw new ArgumentNullException(nameof(entities));
try
{
Entities.RemoveRange(entities);
_context.SaveChanges();
}
catch (DbUpdateException exception)
{
//ensure that the detailed error text is saved in the Log
throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
}
}
/// <summary>
/// 稽核
/// </summary>
/// <param name="entity">Entity</param>
public virtual void Audit(TEntity entity) { }
/// <summary>
/// 儲存記錄
/// </summary>
/// <param name="entity">Entity</param>
/// <returns></returns>
public virtual int Save(TEntity entity)
{
Type Ts = entity.GetType();
PropertyInfo? info = Ts.GetProperty("ID");
if (info != null)
{
int ID = info.GetValue(entity, null).ToInt32();
if (ID > 0)
this.Update(entity);
else
entity = this.Insert(entity);
return info.GetValue(entity, null).ToInt32();
}
else
return -1;
}
#endregion
} 查詢方法
public partial class EfRepository<TEntity> where TEntity : class
{
/// <summary>
/// 根據ID查找
/// </summary>
/// <param name="ids">Identifier</param>
/// <returns>Entity</returns>
public virtual TEntity GetById(object[] ids)
{
return Entities.Find(ids);
}
/// <summary>
/// 根據ID查找
/// </summary>
/// <param name="id">Identifier</param>
/// <returns>Entity</returns>
public virtual TEntity GetById(int id)
{
return Entities.Find(id);
}
/// <summary>
/// 查找所有記錄
/// </summary>
/// <param name="NoTrack"></param>
/// <returns></returns>
public virtual IQueryable<TEntity> GetAll(bool NoTrack = false)
{
IQueryable<TEntity> query = NoTrack ? this.TableNoTracking : this.Table;
if (this.IncludeExpression != null)
{
foreach (var p in IncludeExpression)
{
query = query.Include(p);
}
}
return query;
}
public virtual List<TEntity> GetList4Tree(bool NoTrack = false)
{
IQueryable<TEntity> query = NoTrack ? this.TableNoTracking:this.Table;
return query.ToList();
}
/// <summary>
/// 擷取記錄供下拉清單使用
/// </summary>
/// <param name="NoTrack"></param>
/// <returns></returns>
public virtual List<TEntity> GetEntityList4Cache()
{
string key = typeof(TEntity).Name + "_LookupList";
List<TEntity> list = MemoryCacheHelper.GetCacheItem<List<TEntity>>(key,
delegate () { return this.TableNoTracking.ToList(); },
new TimeSpan(0, 5, 0));//5分鐘過期
return list;
}
/// <summary>
/// 根據條件分頁查找并排序
/// </summary>
/// <param name="filter">過濾條件</param>
/// <param name="pagerInfo">分頁條件</param>
/// <param name="orderByExpression">排序條件</param>
/// <returns></returns>
public virtual List<TEntity> QueryWithPager(Expression<Func<TEntity, bool>> filter, PagerInfo pagerInfo, params OrderModelField[] orderByExpression)
{
List<TEntity> list = this.QueryWithPager(filter, pagerInfo.CurrenetPageIndex, pagerInfo.PageSize, out int rowCount, orderByExpression).ToList();
pagerInfo.RecordCount = rowCount;
return list;
}
/// <summary>
/// 根據條件分頁查找并排序
/// </summary>
/// <param name="filter">過濾條件</param>
/// <param name="pageIndex">目前頁索引</param>
/// <param name="pageSize">每頁記錄數</param>
/// <param name="total">合計記錄數</param>
/// <param name="OrderExpression">排序條件</param>
/// <returns></returns>
public virtual IEnumerable<TEntity> QueryWithPager(Expression<Func<TEntity, bool>> filter, int pageIndex, int pageSize, out int total, params OrderModelField[] OrderExpression)
{
//條件過濾
IQueryable<TEntity> query = this.Table.AsNoTracking();
if (this.IncludeExpression != null)
{
foreach (var p in this.IncludeExpression)
{
query = query.Include(p);
}
}
if (filter != null)
{
query = query.Where(filter);
}
if (OrderExpression != null && OrderExpression.Length > 0)
{
//建立表達式變量參數
var parameter = Expression.Parameter(typeof(TEntity), "o");
for (int i = 0; i < OrderExpression.Length; i++)
{
//根據屬性名擷取屬性
var property = typeof(TEntity).GetProperty(OrderExpression[i].PropertyName);
//建立一個通路屬性的表達式
var propertyAccess = Expression.MakeMemberAccess(parameter, property);
var orderByExp = Expression.Lambda(propertyAccess, parameter);
string OrderName = "";
if (i > 0)
OrderName = OrderExpression[i].IsDESC ? "ThenByDescending" : "ThenBy";
else
OrderName = OrderExpression[i].IsDESC ? "OrderByDescending" : "OrderBy";
MethodCallExpression resultExp = Expression.Call(typeof(Queryable), OrderName, new Type[] { typeof(TEntity), property.PropertyType }, query.Expression, Expression.Quote(orderByExp));
query = query.Provider.CreateQuery<TEntity>(resultExp);
}
}
total = query.Count();
return query.Skip((pageIndex - 1) * pageSize).Take(pageSize);
}
/// <summary>
/// 根據條件查找并排序
/// </summary>
/// <param name="filter">過濾條件表達式</param>
/// <returns>結果清單</returns>
public virtual IQueryable<TEntity> Query(Expression<Func<TEntity, bool>> filter, params OrderModelField[] OrderExpression)
{
IQueryable<TEntity> query = this.Table;
if (this.IncludeExpression != null)
{
foreach (var p in this.IncludeExpression)
{
query = query.Include(p);
}
}
if (filter != null)
query = query.Where(filter);
if (OrderExpression != null && OrderExpression.Length > 0)
{
//建立表達式變量參數
var parameter = Expression.Parameter(typeof(TEntity), "o");
for (int i = 0; i < OrderExpression.Length; i++)
{
//根據屬性名擷取屬性
var property = typeof(TEntity).GetProperty(OrderExpression[i].PropertyName);
//建立一個通路屬性的表達式
var propertyAccess = Expression.MakeMemberAccess(parameter, property);
var orderByExp = Expression.Lambda(propertyAccess, parameter);
string OrderName = "";
if (i > 0)
OrderName = OrderExpression[i].IsDESC ? "ThenByDescending" : "ThenBy";
else
OrderName = OrderExpression[i].IsDESC ? "OrderByDescending" : "OrderBy";
MethodCallExpression resultExp = Expression.Call(typeof(Queryable), OrderName, new Type[] { typeof(TEntity), property.PropertyType }, query.Expression, Expression.Quote(orderByExp));
query = query.Provider.CreateQuery<TEntity>(resultExp);
}
}
return query;
}
/// <summary>
/// Executes the given SQL against the database
/// </summary>
/// <param name="sql">The SQL to execute</param>
/// <param name="doNotEnsureTransaction">true - the transaction creation is not ensured; false - the transaction creation is ensured.</param>
/// <param name="timeout">The timeout to use for command. Note that the command timeout is distinct from the connection timeout, which is commonly set on the database connection string</param>
/// <param name="parameters">Parameters to use with the SQL</param>
/// <returns>The number of rows affected</returns>
public virtual int ExecuteSqlCommand(string sql, bool doNotEnsureTransaction = false, int? timeout = null, params object[] parameters)
{
return this._context.ExecuteSqlCommand(sql, doNotEnsureTransaction, timeout, parameters);
}
/// <summary>
/// Creates a LINQ query for the entity based on a raw SQL query
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="sql">The raw SQL query</param>
/// <param name="parameters">The values to be assigned to parameters</param>
/// <returns>An IQueryable representing the raw SQL query</returns>
public virtual IQueryable<TEntity> EntityFromSql(string sql, params object[] parameters)
{
return this._context.EntityFromSql<TEntity>(sql, parameters);
}
public virtual IQueryable<TEntity> EntityFromSql(string sql)
{
return this._context.EntityFromSql<TEntity>(sql);
}
} 分頁類PagerInfo
public delegate void PageInfoChanged(PagerInfo info);
public class PagerInfo
{
public event PageInfoChanged OnPageInfoChanged;
private int currenetPageIndex; //目前頁碼
private int pageSize;//每頁顯示的記錄
private int recordCount;//記錄總數
#region 屬性變量
/// <summary>
/// 擷取或設定目前頁碼
/// </summary>
public int CurrenetPageIndex
{
get { return currenetPageIndex; }
set
{
currenetPageIndex = value;
if (OnPageInfoChanged != null)
{
OnPageInfoChanged(this);
}
}
}
/// <summary>
/// 擷取或設定每頁顯示的記錄
/// </summary>
public int PageSize
{
get { return pageSize; }
set
{
pageSize = value;
if (OnPageInfoChanged != null)
{
OnPageInfoChanged(this);
}
}
}
/// <summary>
/// 擷取或設定記錄總數
/// </summary>
public int RecordCount
{
get { return recordCount; }
set
{
recordCount = value;
if (OnPageInfoChanged != null)
{
OnPageInfoChanged(this);
}
}
}
#endregion
} public struct OrderModelField
{
/// <summary>
/// 排序字段名稱
/// </summary>
public string PropertyName { get; set; }
/// <summary>
/// 是否倒序
/// </summary>
public bool IsDESC { get; set; }
} ![Core i5-11600K VS Core i5-13600K，性能差距有多大？值得更新嗎？[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)