当前位置： 首页 > news >正文

【.NET】集成SqlSugar实现仓储模式

news 2026/5/27 22:54:49

仓储模式

仓储模式是在业务逻辑处理和数据访问实现之间引入一个负责聚合根或实体操作的抽象隔离层，业务逻辑通过稳定接口访问数据，而不直接依赖具体ORM、表结构或SQL细节，降低业务逻辑与数据访问技术的耦合，便于统一处理事务、审计、软删、缓存等横切逻辑
使用仓储模式有以下好处

  • 为不同的聚合根或实体提供统一的CRUD操作方法，减少重复代码
  • 便于进行单元测试，利于以假实现或内存实现做单元测试
  • 解耦业务逻辑层与数据访问层，不依赖于具体的ORM实现，后续可方便替换其他的ORM实现
  • 方便进行数据库层面的AOP设计

仓储定义

接下来，本文以SqlSugar为具体的ORM实现，实现仓储模式
首先创建两个类库，名称为SugarSqlCore和SugarSqlCore.Abstraction，SugarSqlCore.Abstraction类库存放抽象类和接口，SugarSqlCore类库存放基于SqlSugar的数据库访问和操作实现类，下文以接口库指代SugarSqlCore.Abstraction类库，实现库指代SugarSqlCore，实现库项目引用接口库项目
在接口库中的程序包管理控制台执行以下命令安装SqlSugar、依赖注入、选项功能相关的Nuget包

Install-Package SqlSugarCore -v 5.1.4.175
Install-Package Microsoft.Extensions.DependencyInjection.Abstractions -v 8.0.2
Install-Package Microsoft.Extensions.Options -v 8.0.2

实体

接口库中新建基础实体接口类和对应的泛型基础实体接口类

public interface IEntity
{object? GetKeys();
}public interface IEntity<TKey> : IEntity
{TKey Id { get; }
}

实现库中新建基础实体类和对应的泛型基础实体类

public abstract class Entity : IEntity
{protected Entity(){}public abstract object? GetKeys();
}public abstract class Entity<TKey> : Entity, IEntity<TKey>
{public virtual TKey Id { get; protected set; } = default!;protected Entity(){}protected Entity(TKey id){Id = id;}public override object? GetKeys(){return Id;}
}

这里默认使用实体中Id映射为表的主键，表的主键一般使用Guid、Long，所以这里需要定义成泛型的Id

审计属性

接口库中新建创建审计接口类、更新审计接口类和删除审计接口类

public interface ICreationAuditedEntity
{Guid? CreatorId { get; set; }DateTime CreationTime { get; set; }
}public interface ICreationAuditedEntity<TKey>
{TKey? CreatorId { get; set; }DateTime CreationTime { get; set; }
}

public interface IUpdationAuditedEntity
{Guid? LastModifierId { get; set; }DateTime? LastModificationTime { get; set; }
}public interface IUpdationAuditedEntity<TKey>
{TKey? LastModifierId { get; set; }DateTime? LastModificationTime { get; set; }
}

public interface IDeletionAuditedEntity
{Guid? DeleterId { get; set; }DateTime? DeletionTime { get; set; }bool IsDeleted { get; set; }
}public interface IDeletionAuditedEntity<TKey>
{TKey? DeleterId { get; set; }DateTime? DeletionTime { get; set; }bool IsDeleted { get; set; }
}

实现库中新建创建审计类、更新审计类和删除审计类

public class CreationAuditedEntity : ICreationAuditedEntity
{public Guid? CreatorId { get; set; }public DateTime CreationTime { get; set; }
}public class CreationAuditedEntity<TKey> : ICreationAuditedEntity<TKey>
{public TKey? CreatorId { get; set; }public DateTime CreationTime { get; set; }
}

public class UpdationAuditedEntity : IUpdationAuditedEntity
{public Guid? LastModifierId { get; set; }public DateTime? LastModificationTime { get; set; }
}public class UpdationAuditedEntity<TKey> : IUpdationAuditedEntity<TKey>
{public TKey? LastModifierId { get; set; }public DateTime? LastModificationTime { get; set; }
}

public class DeletionAuditedEntity : IDeletionAuditedEntity
{public Guid? DeleterId { get; set; }public DateTime? DeletionTime { get; set; }public bool IsDeleted { get; set; }
}public class DeletionAuditedEntity<TKey> : IDeletionAuditedEntity<TKey>
{public TKey? DeleterId { get; set; }public DateTime? DeletionTime { get; set; }public bool IsDeleted { get; set; }
}

后续业务实体可以实现这些审计接口类，扩展对应的审计功能

仓储

接口库中新建仓储接口类ISugarSqlRepository，定义一些常用的数据库操作方法

public interface ISugarSqlRepository<TEntity> where TEntity : class, new()
{ISqlSugarClient SugarSqlClient { get; }ISugarQueryable<TEntity> DbQueryable { get; }ISqlSugarClient GetDbContext();ISugarQueryable<TEntity> AsQueryable();InsertNavTaskInit<TEntity, TEntity> AsInsertNav(TEntity entity);InsertNavTaskInit<TEntity, TEntity> AsInsertNav(List<TEntity> entities);UpdateNavTaskInit<TEntity, TEntity> AsUpdateNav(TEntity entity);UpdateNavTaskInit<TEntity, TEntity> AsUpdateNav(List<TEntity> entities);DeleteNavTaskInit<TEntity, TEntity> AsDeleteNav(TEntity entity);DeleteNavTaskInit<TEntity, TEntity> AsDeleteNav(List<TEntity> entities);#region 查询Task<bool> AnyAsync();Task<bool> AnyAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default);Task<TEntity?> FindIncludeAsync<TChildEntity>(Expression<Func<TEntity, bool>> expression, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, CancellationToken cancellationToken = default) where TChildEntity : class, new();Task<TEntity?> FindAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default);Task<TEntity?> FindAsync(dynamic id, CancellationToken cancellationToken = default);Task<TEntity> GetIncludeAsync<TChildEntity>(Expression<Func<TEntity, bool>> expression, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, CancellationToken cancellationToken = default) where TChildEntity : class, new();Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default);Task<TEntity> GetAsync(dynamic id, CancellationToken cancellationToken = default);Task<List<TEntity>> GetListIncludeAsync<TChildEntity>(Expression<Func<TEntity, List<TChildEntity>>> includeExpression, CancellationToken cancellationToken = default) where TChildEntity : class, new();Task<List<TEntity>> GetListAsync(CancellationToken cancellationToken = default);Task<List<TEntity>> GetListIncludeAsync<TChildEntity>(Expression<Func<TEntity, bool>> expression, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, CancellationToken cancellationToken = default) where TChildEntity : class, new();Task<List<TEntity>> GetListAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default);Task<int> GetCountAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default);Task<long> GetCountAsync(CancellationToken cancellationToken = default);Task<bool> IsAnyAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default);#endregion#region 新增Task<bool> InsertAsync(TEntity entity, CancellationToken cancellationToken = default);Task<bool> InsertIncludeAsync<TChildEntity>(TEntity entity, Expression<Func<TEntity, List<TChildEntity>>> expression, InsertNavOptions? insertNavOptions = null) where TChildEntity : class, IEntity, new();Task<int> InsertRangeAsync(List<TEntity> entities, CancellationToken cancellationToken = default);Task<int> InsertRangeIncludeAsync<TChildEntity>(List<TEntity> entities, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, InsertNavOptions? insertNavOptions = null) where TChildEntity : class, IEntity, new();Task<TEntity> InsertReturnAsync(TEntity entity, CancellationToken cancellationToken = default);Task<TEntity> InsertIncludeReturnAsync<TChildEntity>(TEntity entity, Expression<Func<TEntity, List<TChildEntity>>> expression, InsertNavOptions? insertNavOptions = null) where TChildEntity : class, IEntity, new();Task<long> InsertReturnIdAsync(TEntity entity, CancellationToken cancellationToken = default);#endregion#region 更新Task<bool> UpdateAsync(TEntity entity, CancellationToken cancellationToken = default);Task<bool> UpdateIncludeAsync<TChildEntity>(TEntity entity, Expression<Func<TEntity, List<TChildEntity>>> expression, UpdateNavOptions? updateNavOptions = null) where TChildEntity : class, IEntity, new();Task<bool> UpdateRangeIncludeAsync<TChildEntity>(List<TEntity> entities, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, UpdateNavOptions? updateNavOptions = null) where TChildEntity : class, IEntity, new();Task<TEntity> UpdateReturnAsync(TEntity entity, CancellationToken cancellationToken = default);Task<TEntity> UpdateIncludeReturnAsync<TChildEntity>(TEntity entity, Expression<Func<TEntity, List<TChildEntity>>> expression, UpdateNavOptions? updateNavOptions = null) where TChildEntity : class, IEntity, new();Task<bool> UpdateRangeAsync(List<TEntity> entities, CancellationToken cancellationToken = default);#endregion#region 删除Task<bool> DeleteAsync(Expression<Func<TEntity, bool>> expression, bool isSoftDelete = true, CancellationToken cancellationToken = default);#endregion
}public interface ISugarSqlRepository<TEntity, TKey> : ISugarSqlRepository<TEntity> where TEntity : class, new()
{#region 查询Task<TEntity?> FindByIdAsync(TKey id, CancellationToken cancellationToken = default);Task<TEntity> GetByIdAsync(TKey id, CancellationToken cancellationToken = default);#endregion#region 新增#endregion#region 更新#endregion#region 删除Task<bool> DeleteAsync(TEntity entity, bool isSoftDelete = true, CancellationToken cancellationToken = default);Task<bool> DeleteIncludeAsync<TChildEntity>(TEntity entity, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, DeleteNavOptions? deleteNavOptions = null) where TChildEntity : class, IEntity, new();Task<bool> DeleteAsync(List<TEntity> entities, bool isSoftDelete = true, CancellationToken cancellationToken = default);Task<bool> DeleteIncludeAsync<TChildEntity>(List<TEntity> entities, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, DeleteNavOptions? deleteNavOptions = null) where TChildEntity : class, IEntity, new();Task<bool> DeleteByIdAsync(TKey id, bool isSoftDelete = true, CancellationToken cancellationToken = default);Task<bool> DeleteByIdAsync(List<TKey> ids, bool isSoftDelete = true, CancellationToken cancellationToken = default);#endregion
}

接口库中新建数据库上下文接口类ISugarSqlDbContext，定义ISqlSugarClient类型数据库操作对象

public interface ISugarSqlDbContext
{public ISqlSugarClient SugarSqlClient { get; }
}

接口库中新建数据库上下文提供器接口类ISugarSqlDbContextProvider，提供获取具体的数据库上下文类功能

public interface ISugarSqlDbContextProvider<TDbContext> where TDbContext : ISugarSqlDbContext
{public TDbContext GetDbContext();
}

接口库中新建数据库连接构建器接口类ISugarSqlDbConnectionBuilder，提供赋值具体的数据库连接信息功能

public interface ISugarSqlDbConnectionBuilder
{ConnectionConfig Build(Action<ConnectionConfig>? action = null);
}

接下来在实现库中一一实现这些接口，在这之前先定义数据库连接选项类DbConnectionOptions和当前用户上下文类CurrentUserAmbient

public class DbConnectionOptions
{public const string DbConnectionOption = "DbConnection";/// <summary>/// 数据库连接字符串/// </summary>public string ConnectionString { get; set; } = string.Empty;/// <summary>/// 数据库类型/// </summary>public string Type { get; set; } = string.Empty;
}

public class CurrentUserAmbient : CurrentUserAmbient<Guid>
{}public class CurrentUserAmbient<TKey>
{private static readonly AsyncLocal<TKey?> UserIdHolder = new();public static TKey? UserId => Get();public static TKey? Get(){return UserIdHolder.Value;}public static void Set(TKey? userId){UserIdHolder.Value = userId;}public static void Clear(){UserIdHolder.Value = default;}
}

实现库中新建数据库连接构建器类SugarSqlDbConnectionBuilder，Build方法返回数据库连接配置，在构建SqlSugar的具体数据库操作对象时使用

public class SugarSqlDbConnectionBuilder : ISugarSqlDbConnectionBuilder
{public SugarSqlDbConnectionBuilder() {}public ConnectionConfig Build(Action<ConnectionConfig>? action = null){var connectionConfig = new ConnectionConfig(){};action?.Invoke(connectionConfig);return connectionConfig;}
}

实现库中新建数据库上下文提供器类SugarSqlDbContextProvider

public class SugarSqlDbContextProvider<TDbContext> : ISugarSqlDbContextProvider<TDbContext> where TDbContext : ISugarSqlDbContext
{private readonly IServiceProvider _serviceProvider;public SugarSqlDbContextProvider(IServiceProvider serviceProvider){_serviceProvider = serviceProvider;}public TDbContext GetDbContext(){var dbContext = (TDbContext)_serviceProvider.GetRequiredService<ISugarSqlDbContext>();return dbContext;}
}

实现库中新建数据库上下文类SugarSqlDbContext，在这里构建数据库操作对象SugarSqlClient，同时定义实体数据插入事件和更新事件，自动赋值审计属性，添加查询过滤器，不查询已经删除的数据条目，实现软删除功能
审计属性中的创建人Id和更新人Id，都从CurrentUserAmbient中的用户Id获取，而CurrentUserAmbient中的用户Id在进行认证成功后进行赋值

    public class SugarSqlDbContext : ISugarSqlDbContext{private readonly IServiceProvider _serviceProvider;public ISqlSugarClient SugarSqlClient { get; private set; }public SugarSqlDbContext(IServiceProvider serviceProvider){_serviceProvider = serviceProvider;var dbConnectionBuilder = _serviceProvider.GetRequiredService<ISugarSqlDbConnectionBuilder>();var dbConnectionOptions = _serviceProvider.GetRequiredService<IOptions<DbConnectionOptions>>().Value;DbType dbType;switch (dbConnectionOptions.Type){case "MySql":dbType = DbType.MySql;break;case "SqlServer":dbType = DbType.SqlServer;break;case "Sqlite":dbType = DbType.Sqlite;break;case "Oracle":dbType = DbType.Oracle;break;case "PostgreSQL":dbType = DbType.PostgreSQL;break;default:dbType = DbType.MySql;break;}SugarSqlClient = new SqlSugarClient(dbConnectionBuilder.Build(options =>{options.DbType = dbType;options.ConnectionString = dbConnectionOptions.ConnectionString;options.IsAutoCloseConnection = true;options.InitKeyType = InitKeyType.Attribute;options.AopEvents = new AopEvents{DataExecuting = (oldValue, entityInfo) =>{//插入事件if (entityInfo.OperationType == DataFilterType.InsertByObject){if (entityInfo.PropertyName.Equals(nameof(IEntity<Guid>.Id))){if (Guid.Empty.Equals(oldValue)){entityInfo.SetValue(Guid.NewGuid());}}//if (entityInfo.PropertyName.Equals(nameof(ICreationAuditedEntity.CreationTime)))//{//    if (oldValue is null || DateTime.MinValue.Equals(oldValue))//    {//        entityInfo.SetValue(DateTime.Now);//    }//}//if (entityInfo.PropertyName.Equals(nameof(ICreationAuditedEntity.CreatorId)))//{//    entityInfo.SetValue(CurrentUserAmbient.UserId);//}//行级别事件if (entityInfo.EntityColumnInfo.IsPrimarykey){var entity = entityInfo.EntityValue;if (entity is ICreationAuditedEntity creationAuditedEntity){creationAuditedEntity.CreationTime = DateTime.Now;creationAuditedEntity.CreatorId = CurrentUserAmbient.UserId;}}}//更新事件if (entityInfo.OperationType == DataFilterType.UpdateByObject){//行级别事件if (entityInfo.EntityColumnInfo.IsPrimarykey){var entity = entityInfo.EntityValue;if (entity is IUpdationAuditedEntity updationAuditedEntity){updationAuditedEntity.LastModificationTime = DateTime.Now;updationAuditedEntity.LastModifierId = CurrentUserAmbient.UserId;}}}},OnLogExecuting = (sql, param) =>{
#if DEBUGConsole.WriteLine("\r\n" + "【SQL语句】：" + GetWholeSql(param, sql));
#endif}};}), dbContext =>{dbContext.QueryFilter.AddTableFilter<IDeletionAuditedEntity>(entity => entity.IsDeleted == false);});}private static string GetWholeSql(SugarParameter[] paramArr, string sql){foreach (var param in paramArr){sql = sql.Replace(param.ParameterName, param.Value?.ToString());}return sql;}}

实现库中新建SugarSqlRepository，简单封装一些常用的数据库操作方法

public class SugarSqlRepository<TEntity> : ISugarSqlRepository<TEntity> where TEntity : class, IEntity, new()
{private const int BatchInsertUpdateCount = 1000;private readonly ISugarSqlDbContextProvider<ISugarSqlDbContext> _dbContextProvider;public ISqlSugarClient SugarSqlClient => GetDbContext();public ISugarQueryable<TEntity> DbQueryable => GetDbContext().Queryable<TEntity>();public SugarSqlRepository(ISugarSqlDbContextProvider<ISugarSqlDbContext> dbContextProvider){_dbContextProvider = dbContextProvider;}public ISqlSugarClient GetDbContext(){return _dbContextProvider.GetDbContext().SugarSqlClient;}public SimpleClient<TEntity> GetDbSimpleClient(){return GetDbContext().GetSimpleClient<TEntity>();}public ISugarQueryable<TEntity> AsQueryable(){return GetDbSimpleClient().AsQueryable();}public InsertNavTaskInit<TEntity, TEntity> AsInsertNav(TEntity entity){return GetDbContext().InsertNav(entity);}public InsertNavTaskInit<TEntity, TEntity> AsInsertNav(List<TEntity> entities){return GetDbContext().InsertNav(entities);}public UpdateNavTaskInit<TEntity, TEntity> AsUpdateNav(TEntity entity){return GetDbContext().UpdateNav(entity);}public UpdateNavTaskInit<TEntity, TEntity> AsUpdateNav(List<TEntity> entities){return GetDbContext().UpdateNav(entities);}public DeleteNavTaskInit<TEntity, TEntity> AsDeleteNav(TEntity entity){return GetDbContext().DeleteNav(entity);}public DeleteNavTaskInit<TEntity, TEntity> AsDeleteNav(List<TEntity> entities){return GetDbContext().DeleteNav(entities);}#region 查询public async Task<bool> AnyAsync(){var result = await DbQueryable.AnyAsync();return result;}public async Task<bool> AnyAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default){var result = await DbQueryable.AnyAsync(expression, cancellationToken);return result;}public async Task<TEntity?> FindIncludeAsync<TChildEntity>(Expression<Func<TEntity, bool>> expression, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, CancellationToken cancellationToken = default) where TChildEntity : class, new(){var entity = await DbQueryable.Includes(includeExpression).FirstAsync(expression, cancellationToken);return entity;}public async Task<TEntity?> FindAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default){var entity = await DbQueryable.FirstAsync(expression, cancellationToken);return entity;}public async Task<TEntity?> FindAsync(dynamic id, CancellationToken cancellationToken = default){var entity = await GetDbSimpleClient().GetByIdAsync(id, cancellationToken);return entity;}public async Task<TEntity> GetIncludeAsync<TChildEntity>(Expression<Func<TEntity, bool>> expression, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, CancellationToken cancellationToken = default) where TChildEntity : class, new(){var entity = await DbQueryable.Includes(includeExpression).FirstAsync(expression, cancellationToken);return entity;}public async Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default){var entity = await GetDbSimpleClient().GetFirstAsync(expression, cancellationToken);return entity;}public async Task<TEntity> GetAsync(dynamic id, CancellationToken cancellationToken = default){var entity = await GetDbSimpleClient().GetByIdAsync(id, cancellationToken);return entity;}public async Task<List<TEntity>> GetListIncludeAsync<TChildEntity>(Expression<Func<TEntity, List<TChildEntity>>> includeExpression, CancellationToken cancellationToken = default) where TChildEntity : class, new(){var entities = await DbQueryable.Includes(includeExpression).ToListAsync(cancellationToken);return entities;}public async Task<List<TEntity>> GetListAsync(CancellationToken cancellationToken = default){var entities = await GetDbSimpleClient().GetListAsync(cancellationToken);return entities;}public async Task<List<TEntity>> GetListIncludeAsync<TChildEntity>(Expression<Func<TEntity, bool>> expression, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, CancellationToken cancellationToken = default) where TChildEntity : class, new(){var entities = await DbQueryable.Includes(includeExpression).Where(expression).ToListAsync(cancellationToken);return entities;}public async Task<List<TEntity>> GetListAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default){var entities = await GetDbSimpleClient().GetListAsync(expression, cancellationToken);return entities;}public async Task<int> GetCountAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default){var count = await GetDbSimpleClient().CountAsync(expression, cancellationToken);return count;}public async Task<long> GetCountAsync(CancellationToken cancellationToken = default){var count = await DbQueryable.CountAsync(cancellationToken);return count;}public async Task<bool> IsAnyAsync(Expression<Func<TEntity, bool>> expression, CancellationToken cancellationToken = default){return await GetDbSimpleClient().IsAnyAsync(expression, cancellationToken);}#endregion 查询#region 新增public async Task<bool> InsertAsync(TEntity entity, CancellationToken cancellationToken = default){var result = await GetDbSimpleClient().InsertAsync(entity, cancellationToken);return result;}public async Task<bool> InsertIncludeAsync<TChildEntity>(TEntity entity, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, InsertNavOptions? insertNavOptions = null) where TChildEntity : class, IEntity, new(){var result = await AsInsertNav(entity).Include(includeExpression, insertNavOptions).ExecuteCommandAsync();return result;}public async Task<int> InsertRangeAsync(List<TEntity> entities, CancellationToken cancellationToken = default){var num = 0;if (entities.Count > BatchInsertUpdateCount){var index = 0;while (index < entities.Count && !cancellationToken.IsCancellationRequested){var takeCount = index + BatchInsertUpdateCount > entities.Count ? entities.Count - index : BatchInsertUpdateCount;var partitionEntities = entities.Skip(index).Take(takeCount).ToList();var partitionInsertResult = await GetDbSimpleClient().InsertRangeAsync(partitionEntities, cancellationToken);if (partitionInsertResult){num += partitionEntities.Count;}index += takeCount;}}else{await GetDbSimpleClient().InsertRangeAsync(entities, cancellationToken);num = entities.Count;}return num;}public async Task<int> InsertRangeIncludeAsync<TChildEntity>(List<TEntity> entities, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, InsertNavOptions? insertNavOptions = null) where TChildEntity : class, IEntity, new(){var num = 0;if (entities.Count > BatchInsertUpdateCount){var index = 0;while (index < entities.Count){var takeCount = index + BatchInsertUpdateCount > entities.Count ? entities.Count - index : BatchInsertUpdateCount;var partitionEntities = entities.Skip(index).Take(takeCount).ToList();var partitionInsertResult = await AsInsertNav(partitionEntities).Include(includeExpression, insertNavOptions).ExecuteCommandAsync();if (partitionInsertResult){num += partitionEntities.Count;}index += takeCount;}}else{await AsInsertNav(entities).Include(includeExpression, insertNavOptions).ExecuteCommandAsync();num = entities.Count;}return num;}public async Task<TEntity> InsertReturnAsync(TEntity entity, CancellationToken cancellationToken = default){var newEntity = await GetDbSimpleClient().InsertReturnEntityAsync(entity, cancellationToken);return newEntity;}public async Task<TEntity> InsertIncludeReturnAsync<TChildEntity>(TEntity entity, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, InsertNavOptions? insertNavOptions = null) where TChildEntity : class, IEntity, new(){var newEntity = await AsInsertNav(entity).Include(includeExpression, insertNavOptions).ExecuteReturnEntityAsync();return newEntity;}public async Task<long> InsertReturnIdAsync(TEntity entity, CancellationToken cancellationToken = default){var id = await GetDbSimpleClient().InsertReturnBigIdentityAsync(entity, cancellationToken);return id;}#endregion 新增#region 更新public async Task<bool> UpdateAsync(TEntity entity, CancellationToken cancellationToken = default){var result = await GetDbSimpleClient().UpdateAsync(entity, cancellationToken);return result;}public async Task<bool> UpdateIncludeAsync<TChildEntity>(TEntity entity, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, UpdateNavOptions? updateNavOptions = null) where TChildEntity : class, IEntity, new(){var result = await AsUpdateNav(entity).Include(includeExpression, updateNavOptions).ExecuteCommandAsync();return result;}public async Task<bool> UpdateRangeIncludeAsync<TChildEntity>(List<TEntity> entities, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, UpdateNavOptions? updateNavOptions = null) where TChildEntity : class, IEntity, new(){bool result;var num = 0;if (entities.Count > BatchInsertUpdateCount){var index = 0;while (index < entities.Count){var takeCount = index + BatchInsertUpdateCount > entities.Count ? entities.Count - index : BatchInsertUpdateCount;var partitionEntities = entities.Skip(index).Take(takeCount).ToList();var partitionInsertResult = await AsUpdateNav(partitionEntities).Include(includeExpression, updateNavOptions).ExecuteCommandAsync();if (partitionInsertResult){num += partitionEntities.Count;}index += takeCount;}result = num == entities.Count;}else{result = await AsUpdateNav(entities).Include(includeExpression, updateNavOptions).ExecuteCommandAsync();return result;}return result;}public async Task<TEntity> UpdateReturnAsync(TEntity entity, CancellationToken cancellationToken = default){await GetDbSimpleClient().UpdateAsync(entity, cancellationToken);return entity;}public async Task<TEntity> UpdateIncludeReturnAsync<TChildEntity>(TEntity entity, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, UpdateNavOptions? updateNavOptions = null) where TChildEntity : class, IEntity, new(){await AsUpdateNav(entity).Include(includeExpression, updateNavOptions).ExecuteCommandAsync();return entity;}public async Task<bool> UpdateRangeAsync(List<TEntity> entities, CancellationToken cancellationToken = default){bool result;var num = 0;if (entities.Count > BatchInsertUpdateCount){var index = 0;while (index < entities.Count){var takeCount = index + BatchInsertUpdateCount > entities.Count ? entities.Count - index : BatchInsertUpdateCount;var partitionEntities = entities.Skip(index).Take(takeCount).ToList();var partitionInsertResult = await GetDbSimpleClient().UpdateRangeAsync(partitionEntities, cancellationToken);if (partitionInsertResult){num += partitionEntities.Count;}index += takeCount;}result = num == entities.Count;}else{result = await GetDbSimpleClient().UpdateRangeAsync(entities, cancellationToken);return result;}return result;}#endregion 更新#region 删除public async Task<bool> DeleteAsync(Expression<Func<TEntity, bool>> expression, bool isSoftDelete = true, CancellationToken cancellationToken = default){if (isSoftDelete && typeof(IDeletionAuditedEntity).IsAssignableFrom(typeof(TEntity))){var result = await GetDbContext().Updateable<TEntity>().Where(expression).SetColumns("DeletionTime", DateTime.Now).SetColumns("DeleterId", CurrentUserAmbient.UserId).SetColumns("IsDeleted", true).ExecuteCommandAsync(cancellationToken);return result > 0;}else{var result = await GetDbSimpleClient().DeleteAsync(expression, cancellationToken);return result;}}#endregion
}public class SugarSqlRepository<TEntity, TKey> : SugarSqlRepository<TEntity>, ISugarSqlRepository<TEntity, TKey> where TEntity : class, IEntity<TKey>, new()
{public SugarSqlRepository(ISugarSqlDbContextProvider<ISugarSqlDbContext> dbContextProvider) : base(dbContextProvider){}#region 查询public async Task<TEntity?> FindByIdAsync(TKey id, CancellationToken cancellationToken = default){var entity = await FindAsync(id!, cancellationToken);return entity;}public async Task<TEntity> GetByIdAsync(TKey id, CancellationToken cancellationToken = default){var entity = await GetAsync(id!, cancellationToken);return entity;}#endregion 查询#region 删除public async Task<bool> DeleteAsync(TEntity entity, bool isSoftDelete = true, CancellationToken cancellationToken = default){if (isSoftDelete && typeof(IDeletionAuditedEntity).IsAssignableFrom(typeof(TEntity))){var idColumn = GetDbContext().EntityMaintenance.GetDbColumnName<TEntity>(nameof(IEntity<TKey>.Id));var result = await GetDbContext().Updateable<TEntity>().Where($"{idColumn} = @rowId", new { rowId = entity.Id }).SetColumns("DeletionTime", DateTime.Now).SetColumns("DeleterId", CurrentUserAmbient.UserId).SetColumns("IsDeleted", true).ExecuteCommandAsync(cancellationToken);return result == 1;}else{var result = await GetDbSimpleClient().DeleteAsync(entity, cancellationToken);return result;}}public async Task<bool> DeleteIncludeAsync<TChildEntity>(TEntity entity, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, DeleteNavOptions? deleteNavOptions = null) where TChildEntity : class, IEntity, new(){var result = await AsDeleteNav(entity).Include(includeExpression, deleteNavOptions).ExecuteCommandAsync();return result;}public async Task<bool> DeleteAsync(List<TEntity> entities, bool isSoftDelete = true, CancellationToken cancellationToken = default){if (isSoftDelete && typeof(IDeletionAuditedEntity).IsAssignableFrom(typeof(TEntity))){var result = await GetDbContext().Updateable<TEntity>().Where(x => entities.Select(entity => entity.Id).Contains(x.Id)).SetColumns("DeletionTime", DateTime.Now).SetColumns("DeleterId", CurrentUserAmbient.UserId).SetColumns("IsDeleted", true).ExecuteCommandAsync(cancellationToken);return result == entities.Count;}else{var result = await GetDbSimpleClient().DeleteAsync(entities, cancellationToken);return result;}}public async Task<bool> DeleteIncludeAsync<TChildEntity>(List<TEntity> entities, Expression<Func<TEntity, List<TChildEntity>>> includeExpression, DeleteNavOptions? deleteNavOptions = null) where TChildEntity : class, IEntity, new(){var result = await AsDeleteNav(entities).Include(includeExpression, deleteNavOptions).ExecuteCommandAsync();return result;}public async Task<bool> DeleteByIdAsync(TKey id, bool isSoftDelete = true, CancellationToken cancellationToken = default){if (isSoftDelete && typeof(IDeletionAuditedEntity).IsAssignableFrom(typeof(TEntity))){var idColumn = GetDbContext().EntityMaintenance.GetDbColumnName<TEntity>(nameof(IEntity<TKey>.Id));var result = await GetDbContext().Updateable<TEntity>().Where($"{idColumn} = @rowId", new { rowId = id }).SetColumns("DeletionTime", DateTime.Now).SetColumns("DeleterId", CurrentUserAmbient.UserId).SetColumns("IsDeleted", true).ExecuteCommandAsync(cancellationToken);return result == 1;}else{var result = await GetDbSimpleClient().DeleteByIdAsync(id, cancellationToken);return result;}}public async Task<bool> DeleteByIdAsync(List<TKey> ids, bool isSoftDelete = true, CancellationToken cancellationToken = default){if (isSoftDelete && typeof(IDeletionAuditedEntity).IsAssignableFrom(typeof(TEntity))){var result = await GetDbContext().Updateable<TEntity>().Where(x => ids.Contains(x.Id)).SetColumns("DeletionTime", DateTime.Now).SetColumns("DeleterId", CurrentUserAmbient.UserId).SetColumns("IsDeleted", true).ExecuteCommandAsync(cancellationToken);return result == ids.Count;}else{var result = await GetDbSimpleClient().DeleteAsync(x => ids.Contains(x.Id), cancellationToken);return result;}}#endregion 删除
}

其中的删除数据的方法全部根据软删除参数，来决定是硬删除还是赋值删除审计属性进行更新
新建名称为SugarSqlExtension的扩展类，用来进行相关实例的依赖注入，以及添加必要的服务

public static class SugarSqlExtension
{public static void AddSugarSql(this IServiceCollection services, Action<DbConnectionOptions> configureOptions){services.AddOptions();services.Configure(configureOptions);services.AddTransient<ISugarSqlDbConnectionBuilder, SugarSqlDbConnectionBuilder>();services.AddScoped<ISugarSqlDbContext, SugarSqlDbContext>();services.AddTransient(typeof(ISugarSqlDbContextProvider<>), typeof(SugarSqlDbContextProvider<>));services.AddTransient(typeof(ISugarSqlRepository<>), typeof(SugarSqlRepository<>));services.AddTransient(typeof(ISugarSqlRepository<,>), typeof(SugarSqlRepository<,>));}
}

仓储使用

在appsetting.json中配置数据库连接信息

{"DbConnection": {"Type": "MySql","ConnectionString": "server=localhost;database=sugarsql;user=youruser;password=yourpassword;port=yourport;CharSet=utf8mb4;Allow User Variables=True;SslMode=none;AllowLoadLocalInfile=true;"}
}

新建基础仓储接口类IBaseRepository和基础仓储类BaseRepository，提供复用的辅助功能，例如生成排序字段信息
IBaseRepository

public interface IBaseRepository<TEntity> : ISugarSqlRepository<TEntity> where TEntity : class, IEntity, new()
{string GenerateSortExpression(string sortField, string sortType, Type type);string GenerateSortExpression(string sortField, string sortType);
}public interface IBaseRepository<TEntity, TKey> : IBaseRepository<TEntity>, ISugarSqlRepository<TEntity, TKey> where TEntity : class, IEntity, new()
{}

BaseRepository

public abstract class BaseRepository<TEntity> : SugarSqlRepository<TEntity>, IBaseRepository<TEntity> where TEntity : class, IEntity, new()
{public BaseRepository(ISugarSqlDbContextProvider<ISugarSqlDbContext> dbContextProvider) : base(dbContextProvider){}public string GenerateSortExpression(string sortField, string sortType, Type type){string sortExpression = string.Empty;if (IsValidField(sortField, type)){sortExpression = sortField;if (sortType != "asc"){sortExpression += " desc";}else{sortExpression += " asc";}}return sortExpression;}public string GenerateSortExpression(string sortField, string sortType){string sortExpression = string.Empty;if (IsValidField(sortField)){sortExpression = sortField;if (sortType != "asc"){sortExpression += " desc";}else{sortExpression += " asc";}}return sortExpression;}private static bool IsValidField(string field, Type type){PropertyInfo[] properties = type.GetProperties();foreach (PropertyInfo property in properties){if (property.Name.Equals(field)){return true;}}return false;}private bool IsValidField(string field){try{string property = GetDbContext().EntityMaintenance.GetDbColumnName<TEntity>(field);}catch (Exception){return false;}return true;}
}public abstract class BaseRepository<TEntity, TKey> : SugarSqlRepository<TEntity, TKey>, IBaseRepository<TEntity, TKey> where TEntity : class, IEntity<TKey>, new()
{public BaseRepository(ISugarSqlDbContextProvider<ISugarSqlDbContext> dbContextProvider) : base(dbContextProvider){}public string GenerateSortExpression(string sortField, string sortType, Type type){string sortExpression = string.Empty;if (IsValidField(sortField, type)){sortExpression = sortField;if (sortType != "asc"){sortExpression += " desc";}else{sortExpression += " asc";}}return sortExpression;}public string GenerateSortExpression(string sortField, string sortType){string sortExpression = string.Empty;if (IsValidField(sortField)){sortExpression = sortField;if (sortType != "asc"){sortExpression += " desc";}else{sortExpression += " asc";}}return sortExpression;}private static bool IsValidField(string field, Type type){PropertyInfo[] properties = type.GetProperties();foreach (PropertyInfo property in properties){if (property.Name.Equals(field)){return true;}}return false;}private bool IsValidField(string field){try{string property = GetDbContext().EntityMaintenance.GetDbColumnName<TEntity>(field);}catch (Exception){return false;}return true;}
}

定义业务实体User和对应的User仓储类
User

/// <summary>
/// 用户
/// </summary>
[SugarTable("user")]
public class User : Entity<Guid>, ICreationAuditedEntity, IUpdationAuditedEntity, IDeletionAuditedEntity
{/// <summary>/// Id/// </summary>[SugarColumn(IsPrimaryKey = true)]public override Guid Id { get; protected set; }/// <summary>/// 姓名/// </summary>public string Name { get; set; } = null!;/// <summary>/// 年龄/// </summary>public int Age { get; set; }/// <summary>/// 邮箱/// </summary>public string? Email { get; set; }/// <summary>/// 电话/// </summary>public string? Phone { get; set; }/// <summary>/// 创建时间/// </summary>public DateTime CreationTime { get; set; }/// <summary>/// 创建人/// </summary>public Guid? CreatorId { get; set; }/// <summary>/// 更新时间/// </summary>public DateTime? LastModificationTime { get; set; }/// <summary>/// 更新人/// </summary>public Guid? LastModifierId { get; set; }/// <summary>/// 删除时间/// </summary>public DateTime? DeletionTime { get; set; }/// <summary>/// 删除人/// </summary>public Guid? DeleterId { get; set; }/// <summary>/// 是否删除，0：未删除，1：已删除/// </summary>public bool IsDeleted { get; set; }
}

IUserRepository

public interface IUserRepository : IBaseRepository<User, Guid>
{}

UserRepository

public class UserRepository : BaseRepository<User, Guid>, IUserRepository
{public UserRepository(ISugarSqlDbContextProvider<ISugarSqlDbContext> dbContextProvider) : base(dbContextProvider){}
}

新建控制器UserController类和对应User数据传输对象类，进行简单的数据操作功能测试
用户Dto类

public class UserDto
{/// <summary>/// Id/// </summary>public Guid Id { get; set; }/// <summary>/// 姓名/// </summary>public string Name { get; set; } = null!;/// <summary>/// 年龄/// </summary>public int Age { get; set; }/// <summary>/// 邮箱/// </summary>public string? Email { get; set; }/// <summary>/// 电话/// </summary>public string? Phone { get; set; }
}public class UserCreateDto
{/// <summary>/// 姓名/// </summary>public string Name { get; set; } = null!;/// <summary>/// 年龄/// </summary>public int Age { get; set; }/// <summary>/// 邮箱/// </summary>public string? Email { get; set; }/// <summary>/// 电话/// </summary>public string? Phone { get; set; }
}public class UserQueryDto
{/// <summary>/// 姓名/// </summary>public string? Name { get; set; }
}public class UserUpdateDto
{/// <summary>/// Id/// </summary>public Guid Id { get; set; }/// <summary>/// 年龄/// </summary>public int Age { get; set; }/// <summary>/// 邮箱/// </summary>public string? Email { get; set; }/// <summary>/// 电话/// </summary>public string? Phone { get; set; }
}

UserController

[ApiController]
[Route("api/[controller]")]
public class UserController : Controller
{private readonly IUserRepository _userRepository;public UserController(IUserRepository userRepository){_userRepository = userRepository;}[HttpGet]public async Task<List<UserDto>> GetListAsync([FromQuery] UserQueryDto input){// 分页信息和排序方式可以通过参数传递，这里只是示例，默认排序方式为按Name降序，默认分页大小为10，分页序号为1var sort = _userRepository.GenerateSortExpression("Name", "desc");var totalNumber = new RefAsync<int>();var totalPage = new RefAsync<int>();var userList = await _userRepository.AsQueryable().WhereIF(!string.IsNullOrWhiteSpace(input.Name), user => user.Name.Contains(input.Name!)).Select(user => new UserDto{Id = user.Id,Name = user.Name,Age = user.Age,Email = user.Email,Phone = user.Phone}).OrderByIF(!string.IsNullOrWhiteSpace(sort), sort).ToPageListAsync(1, 10, totalNumber, totalPage);return userList;}[HttpGet("{id}")]public async Task<UserDto> GetAsync(Guid id){var user = await _userRepository.FindAsync(id) ?? throw new Exception("User not found");return new UserDto{Id = user.Id,Name = user.Name,Age = user.Age,Email = user.Email,Phone = user.Phone};}[HttpPost]public async Task CreateAsync(UserCreateDto input){var existUser = await _userRepository.GetAsync(user => user.Name == input.Name);if (existUser != null){throw new Exception("User with the same name already exists");}var user = new User{Name = input.Name,Age = input.Age,Email = input.Email,Phone = input.Phone};await _userRepository.InsertAsync(user);}[HttpPut]public async Task UpdateAsync(UserUpdateDto input){var user = await _userRepository.FindAsync(input.Id) ?? throw new Exception("User not found");user.Age = input.Age;user.Email = input.Email;user.Phone = input.Phone;await _userRepository.UpdateAsync(user);}[HttpDelete("{id}")]public async Task DeleteAsync(Guid id){await _userRepository.DeleteByIdAsync(id);}[HttpDelete]public async Task BatchDeleteAsync([FromBody] List<Guid> ids){await _userRepository.DeleteByIdAsync(ids);}
}

新建认证中间件类AuthorizeMiddleware，模拟认证成功后，将获取到的UserId赋值到异步上下文CurrentUserAmbient中，待后续数据操作时，获取并赋值到对应的审计属性上

public class AuthorizeMiddleware
{private readonly RequestDelegate _next;public AuthorizeMiddleware(RequestDelegate next){_next = next;}public async Task InvokeAsync(HttpContext context){try{// 认证逻辑，从请求中提取用户Id并设置到CurrentUserAmbient中// todo CurrentUserAmbient.Set(Guid.NewGuid());await _next(context);}finally{CurrentUserAmbient.Clear();}}
}public static class AuthorizeMiddlewareExtension
{public static IApplicationBuilder UseAuthorize(this IApplicationBuilder builder){return builder.UseMiddleware<AuthorizeMiddleware>();}
}

在程序启动类中，使用该中间件app.UseAuthorize();
添加SqlSugar服务，配置数据库连接信息和手动进行仓储的依赖注入

var dbConnectionOptions = new DbConnectionOptions();
builder.Configuration.GetSection(DbConnectionOptions.DbConnectionOption).Bind(dbConnectionOptions);
builder.Services.AddSugarSql(options =>
{options.Type = dbConnectionOptions.Type;options.ConnectionString = dbConnectionOptions.ConnectionString;
});
builder.Services.AddTransient<IUserRepository, UserRepository>();

最后，不要忘记在数据库创建User表

DROP DATABASE IF EXISTS `sugarsql`;
CREATE DATABASE IF NOT EXISTS `sugarsql`;USE `sugarsql`;DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user` (`Id` CHAR(36) NOT NULL COMMENT 'Id' COLLATE 'utf8mb4_general_ci',`Name` VARCHAR(64) NOT NULL COMMENT '姓名' COLLATE 'utf8mb4_general_ci',`Age` INT(3) NOT NULL COMMENT '年龄',`Email` VARCHAR(64) NULL DEFAULT NULL COMMENT '邮箱' COLLATE 'utf8mb4_general_ci',`Phone` VARCHAR(64) NULL DEFAULT NULL COMMENT '电话' COLLATE 'utf8mb4_general_ci',`CreationTime` DATETIME NOT NULL COMMENT '创建时间',`CreatorId` CHAR(36) NULL DEFAULT NULL COMMENT '创建人' COLLATE 'utf8mb4_general_ci',`LastModificationTime` DATETIME NULL DEFAULT NULL COMMENT '更新时间',`LastModifierId` CHAR(36) NULL DEFAULT NULL COMMENT '更新人' COLLATE 'utf8mb4_general_ci',`IsDeleted` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '是否删除，0：未删除，1：已删除',`DeleterId` CHAR(36) NULL DEFAULT NULL COMMENT '删除人' COLLATE 'utf8mb4_general_ci',`DeletionTime` DATETIME NULL DEFAULT NULL COMMENT '删除时间',PRIMARY KEY (`Id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

仓储测试

结果如下图所示

  • 创建一个用户

  • 查询指定用户

  • 更新指定用户

  • 删除指定用户

http://www.jsqmd.com/news/899459/

相关文章：

  • GraphRAG【部署 01】Linux环境安装部署GraphRAG并使用Ollama本地大模型
  • 2026年iherb最新折扣码618大促优惠码 - 李先生sir
  • 小红书改版后发布按钮抓不到？两个思路绕开Shadow DOM限制
  • 2026年值得推荐的原装进口艺术漆榜单：意大利石灰基、矿物、灰泥艺术涂料与德系精工谁更强？ - 资讯纵览
  • 2026年5月知网AI率突然飙升？4款降AI软件深度推荐+实测 - 我要发一区
  • 从开题到定稿零返工：okbiye AI 毕业论文写作功能实测与流程拆解
  • 经济下行压力大，EB-Cable的license费用怎么砍？我这儿有几招狠的
  • Android开发转AI Agent：第2天——temperature调到1.5，LLM开始胡说八道
  • 4款降AI软件实测红黑榜：2026年5月哪个能真的去AI痕迹 - 我要发一区
  • 解耦异构算力与多协议接入：基于Docker与源码交付的开源企业级GB28181/RTSP边缘计算AI视频管理平台架构深度解析
  • 2026年跨境POD系统选购指南：风擎科技等主流方案深度对比 - 资讯纵览
  • IT之家：解构2026年GEO服务商五强——格局、壁垒与唯一性 - 罗兰艺境GEO
  • 从CMS内卷到ZGC封神！深度拆解GC分代模型与三大收集器优缺点+生产调优实战
  • 从泥泞中走来：一个普通人的十五年
  • 卫浴空间台面材料选型分析：高端亚克力人造石的性能优势与工程适配
  • 浩卡联盟推广手机卡真的靠谱吗？2026佣金置顶全网最高结算率98%以上 - 流量卡代理招商
  • 【实战指南】基于MATLAB GUI的指纹识别系统：从图像预处理到特征匹配全流程解析
  • 关于贪心算法的一些自我总结【力扣45.跳跃游戏II】【灵感来源：代码随想录】
  • 2026年全国对讲机优选厂家榜单：从“能用”到“耐用”，为何驰尔达成为3000+客户的首选？ - 资讯纵览
  • P15366 [IOI 2013] Cave
  • List＜T＞ 投影转换（Select）作用 + 详解 + 示例
  • 双重引擎：量子计算与AI如何将人类文明推向恒星时代
  • 2026毕业季降AI软件红黑榜：4款工具一次过知网维普AIGC - 我要发一区
  • 杰理AC696N蓝牙音频芯片开发TWS真无线立体声-开发指南（上）：使能与配对配置
  • 终极鼠标加速指南：Raw Accel 7大曲线类型深度解析与实战配置
  • Figma的组件系统是如何工作的？
  • Figma组件系统的优势有哪些？
  • 嵌入式 - 数据结构与算法：（1-14）排序算法 - 冒泡/选择/快速/希尔排序对比
  • 2026实测横评：无水印视频下载神器怎么选？免费好用的无水印视频下载工具有哪些？6大维度深度对比 - 科技热点发布
  • 战略落地难？别让“空中楼阁”拖垮你的团队