SqlSugar实战：除了增删改查，这些‘骚操作’能让你的.NET数据库开发效率翻倍

SqlSugar实战解锁高阶技巧让.NET数据库开发效率飙升在.NET生态中ORM框架的选择往往直接影响开发效率和系统性能。SqlSugar作为一款轻量级但功能强大的ORM工具其简洁的API设计和丰富的功能特性让它成为众多开发者的首选。然而很多开发者仅仅停留在基础的增删改查操作上未能充分挖掘SqlSugar的潜力。本文将带你深入探索SqlSugar的高级特性从代码生成到复杂查询从批量操作到性能优化全方位提升你的数据库开发效率。1. 从数据库到代码DbFirst的高效实践对于大多数项目来说数据库设计先行是常见的工作流程。SqlSugar的DbFirst功能能够将现有数据库结构快速转换为C#实体类大幅减少手动创建模型的时间成本。1.1 基础代码生成var db new SqlSugarClient(new ConnectionConfig() { ConnectionString YourConnectionString, DbType DbType.SqlServer, IsAutoCloseConnection true }); // 生成所有表的实体类 db.DbFirst .IsCreateAttribute() // 生成数据注解 .CreateClassFile(D:\\Models); // 输出目录执行这段代码后SqlSugar会自动扫描数据库中的所有表并在指定目录下为每个表生成对应的C#类文件。生成的文件会包含表名映射、主键标识等必要的特性标记。1.2 高级定制选项DbFirst提供了丰富的配置选项满足不同项目的需求表筛选可以指定只生成特定表或排除某些表命名空间设置自定义生成的实体类命名空间字段过滤忽略某些字段不生成命名规则转换将数据库命名风格转换为C#命名风格db.DbFirst .Where(table !table.Name.StartsWith(Temp_)) // 排除临时表 .SettingNamespaceTemplate(old YourCompany.Project.Models) // 自定义命名空间 .SettingClassTemplate(old { return old.Replace(public class, public partial class); // 生成分部类 }) .IsCreateAttribute() .CreateClassFile(D:\\Models);2. 复杂查询的艺术超越基础CRUD实际业务场景中简单的单表查询往往不能满足需求。SqlSugar提供了强大的查询构建能力能够优雅地处理各种复杂查询场景。2.1 多表关联查询SqlSugar支持多种联表查询方式每种方式各有优劣方式一Lambda表达式联表var list db.QueryableOrder() .LeftJoinCustomer((o, c) o.CustomerId c.Id) .LeftJoinOrderDetail((o, c, od) o.Id od.OrderId) .Select((o, c, od) new OrderViewModel { OrderId o.Id, CustomerName c.Name, ProductCount od.Count, TotalAmount od.Price * od.Count }) .ToList();方式二原生SQL联表var sql SELECT o.Id as OrderId, c.Name as CustomerName, od.Count as ProductCount, od.Price * od.Count as TotalAmount FROM Orders o LEFT JOIN Customers c ON o.CustomerId c.Id LEFT JOIN OrderDetails od ON o.Id od.OrderId; var list db.SqlQueryableOrderViewModel(sql).ToList();方式三动态条件联表var exp Expressionable.CreateOrder, Customer(); exp.And((o, c) o.CustomerId c.Id); if (!string.IsNullOrEmpty(keyword)) { exp.And((o, c) c.Name.Contains(keyword)); } var list db.QueryableOrder() .LeftJoinCustomer(exp.ToExpression()) .ToList();2.2 高级查询技巧分页查询优化// 传统分页 var page db.QueryableProduct() .Where(p p.Price 100) .ToPageList(pageNumber, pageSize, ref totalCount); // 高性能分页大数据量时更优 var page db.QueryableProduct() .Where(p p.Price 100) .ToOffsetPage(pageNumber, pageSize, totalCount);嵌套查询var subQuery db.QueryableOrderDetail() .GroupBy(od od.OrderId) .Select(od new { OrderId od.OrderId, TotalAmount SqlFunc.AggregateSum(od.Price * od.Count) }); var result db.QueryableOrder() .InnerJoin(subQuery, (o, s) o.Id s.OrderId) .Select((o, s) new OrderSummary { OrderId o.Id, OrderDate o.CreateTime, TotalAmount s.TotalAmount }) .ToList();3. 批量操作与性能优化当需要处理大量数据时传统的逐条操作方式性能低下。SqlSugar提供了多种批量操作方法可以显著提升数据处理效率。3.1 批量插入对比方法适用场景性能特点Insertable(list)小批量数据(1万)中等支持返回自增IDBulkCopy大批量数据非常高最快但不返回IDFastest.BulkInsert大批量数据高平衡速度和功能// 方式一普通批量插入 var success db.Insertable(productList).ExecuteCommand() 0; // 方式二高性能批量插入不返回ID var success db.FastestProduct().BulkCopy(productList) 0; // 方式三平衡型批量插入 var success db.FastestProduct().BulkInsert(productList) 0;3.2 批量更新策略// 小批量更新 var result db.Updateable(productList).ExecuteCommand(); // 大批量更新性能优化版 var result db.FastestProduct() .BulkUpdate(productList, new[] { nameof(Product.Price), nameof(Product.Stock) }); // 只更新指定列3.3 批量删除技巧// 按条件批量删除 var count db.DeleteableProduct() .Where(p p.IsDeleted true) .ExecuteCommand(); // 按主键列表批量删除 var ids productList.Select(p p.Id).ToList(); var count db.DeleteableProduct() .Where(p ids.Contains(p.Id)) .ExecuteCommand();4. 高级特性实战应用SqlSugar提供了许多开箱即用的高级功能合理使用这些特性可以简化代码并提升开发效率。4.1 过滤器全局数据过滤过滤器是SqlSugar中非常实用的功能它可以自动为所有查询添加过滤条件非常适合实现多租户、软删除等场景。// 配置全局过滤器 var db new SqlSugarClient(new ConnectionConfig() { // ...其他配置 }, db { // 软删除过滤器 db.QueryFilter.AddTableFilterProduct(p p.IsDeleted false); // 多租户过滤器 db.QueryFilter.AddTableFilterOrder(o o.TenantId currentTenantId); }); // 查询时会自动加上过滤条件 var activeProducts db.QueryableProduct().ToList(); // 自动加上 WHERE IsDeleted 04.2 AOP拦截与监控SqlSugar的AOP(面向切面编程)功能可以拦截各种操作非常适合实现SQL监控、审计日志等功能。var db new SqlSugarClient(new ConnectionConfig() { // ...其他配置 }, db { // 执行前拦截 db.Aop.OnLogExecuting (sql, pars) { Console.WriteLine(UtilMethods.GetNativeSql(sql, pars)); // 输出实际执行的SQL File.AppendAllText(sql.log, ${DateTime.Now}: {sql}{Environment.NewLine}); }; // 执行后拦截 db.Aop.OnLogExecuted (sql, pars) { Console.WriteLine($执行耗时: {db.Ado.SqlExecutionTime.TotalMilliseconds}ms); }; // 错误拦截 db.Aop.OnError exp { Console.WriteLine($数据库操作出错: {exp.Message}); }; });4.3 事务处理的优雅实现SqlSugar提供了多种事务处理方式从基础到高级满足不同场景需求。基础事务try { db.Ado.BeginTran(); // 业务操作1 db.Insertable(product1).ExecuteCommand(); // 业务操作2 db.Updateable(order).ExecuteCommand(); db.Ado.CommitTran(); } catch (Exception ex) { db.Ado.RollbackTran(); throw; }使用事务特性[Transactional] public void PlaceOrder(Order order, ListOrderDetail details) { db.Insertable(order).ExecuteCommand(); db.Insertable(details).ExecuteCommand(); // 如果任何操作失败整个方法会自动回滚 }跨方法事务using (var tran db.UseTran()) { // 业务操作1 ServiceA.DoSomething(db); // 业务操作2 ServiceB.DoSomethingElse(db); tran.CommitTran(); }5. 实战技巧与性能调优在实际项目中使用SqlSugar时掌握一些技巧和最佳实践可以避免很多坑。5.1 连接管理策略连接池配置var db new SqlSugarClient(new ConnectionConfig() { ConnectionString YourConnectionString, DbType DbType.SqlServer, IsAutoCloseConnection true, // 自动释放连接 ConnectionPoolType ConnectionPoolType.Pooled, // 使用连接池 PoolConfig new PoolConfig() { MaxConnections 100, // 最大连接数 MinConnections 5, // 最小连接数 ConnectionIdleTime 300 // 空闲连接存活时间(秒) } });连接复用技巧// 在Web应用中建议在请求开始时创建SqlSugarClient // 并在请求结束时释放整个请求周期复用同一个实例 // ASP.NET Core示例 services.AddScopedISqlSugarClient(provider { return new SqlSugarClient(new ConnectionConfig() { // 配置... }); });5.2 查询性能优化索引提示var list db.QueryableProduct() .With(SqlWith.NoLock) // NOLOCK提示 .With(SqlWith.Index(IX_Product_Category)) // 强制使用特定索引 .ToList();只查询必要字段// 不好的做法查询所有字段 var products db.QueryableProduct().ToList(); // 好的做法只查询需要的字段 var products db.QueryableProduct() .Select(p new { p.Id, p.Name, p.Price }) .ToList();延迟加载与立即加载// 延迟加载按需加载关联数据 var order db.QueryableOrder().First(); var customer db.QueryableCustomer().First(c c.Id order.CustomerId); // 立即加载一次性加载关联数据 var order db.QueryableOrder() .Includes(o o.Customer) // 预加载Customer .Includes(o o.Details) // 预加载OrderDetails .First();5.3 缓存策略SqlSugar内置了查询缓存功能可以显著减少数据库压力。// 基本缓存使用 var products db.QueryableProduct() .Where(p p.CategoryId 1) .WithCache() // 默认缓存5分钟 .ToList(); // 自定义缓存设置 var products db.QueryableProduct() .Where(p p.CategoryId 1) .WithCache(60 * 60) // 缓存1小时 .ToList(); // 按条件清除缓存 db.QueryableProduct().Where(p p.CategoryId 1).RemoveDataCache();6. 扩展与集成SqlSugar的灵活性使其能够轻松与其他技术栈集成满足各种复杂需求。6.1 与Dapper混合使用// 使用SqlSugar的Connection执行Dapper查询 var connection db.Ado.Connection; var products connection.QueryProduct(SELECT * FROM Products WHERE Price Price, new { Price 100 });6.2 动态实体与弱类型查询// 动态查询 var dynamicList db.Queryableobject() .AS(Products) // 指定表名 .Where(Price Price, new { Price 100 }) .Select(Id, Name, Price) .ToList(); // 动态实体 dynamic product new ExpandoObject(); product.Name New Product; product.Price 99.99m; db.Insertable(product).AS(Products).ExecuteCommand();6.3 多数据库支持SqlSugar支持多种数据库并且可以在运行时动态切换。// 多数据库配置 var db new SqlSugarClient(new ListConnectionConfig() { new ConnectionConfig() { ConfigId MainDb, ConnectionString MainDbConnectionString, DbType DbType.SqlServer }, new ConnectionConfig() { ConfigId LogDb, ConnectionString LogDbConnectionString, DbType DbType.MySql } }); // 使用不同数据库 var mainData db.GetConnection(MainDb).QueryableProduct().ToList(); var logData db.GetConnection(LogDb).QueryableLog().ToList();6.4 读写分离var db new SqlSugarClient(new ConnectionConfig() { ConnectionString writeConnectionString, DbType DbType.SqlServer, IsAutoCloseConnection true, SlaveConnectionConfigs new ListSlaveConnectionConfig() { new SlaveConnectionConfig() { ConnectionString readConnectionString1, HitRate 50 // 权重 }, new SlaveConnectionConfig() { ConnectionString readConnectionString2, HitRate 50 } } }); // 读操作会自动分配到从库 var products db.QueryableProduct().ToList(); // 写操作会自动使用主库 db.Insertable(newProduct).ExecuteCommand();7. 实际项目中的最佳实践经过多个项目的实践验证以下是一些特别值得推荐的使用方式。7.1 仓储模式实现public interface IRepositoryT where T : class, new() { T GetById(object id); ListT GetList(ExpressionFuncT, bool predicate null); bool Insert(T entity); bool Update(T entity); bool Delete(T entity); // 其他常用方法... } public class SugarRepositoryT : IRepositoryT where T : class, new() { private readonly ISqlSugarClient _db; public SugarRepository(ISqlSugarClient db) { _db db; } public T GetById(object id) { return _db.QueryableT().InSingle(id); } // 实现其他接口方法... } // 使用示例 var productRepository new SugarRepositoryProduct(db); var product productRepository.GetById(1);7.2 工作单元模式public interface IUnitOfWork : IDisposable { ISqlSugarClient Db { get; } void BeginTransaction(); void Commit(); void Rollback(); } public class SugarUnitOfWork : IUnitOfWork { private readonly ISqlSugarClient _db; public SugarUnitOfWork(ISqlSugarClient db) { _db db; } public ISqlSugarClient Db _db; public void BeginTransaction() { _db.Ado.BeginTran(); } public void Commit() { _db.Ado.CommitTran(); } public void Rollback() { _db.Ado.RollbackTran(); } public void Dispose() { _db?.Dispose(); } } // 使用示例 using (var uow new SugarUnitOfWork(db)) { try { uow.BeginTransaction(); var productRepo new SugarRepositoryProduct(uow.Db); var orderRepo new SugarRepositoryOrder(uow.Db); // 业务操作... uow.Commit(); } catch { uow.Rollback(); throw; } }7.3 动态查询构建器public class DynamicQueryBuilderT where T : class, new() { private ISqlSugarClient _db; private ISugarQueryableT _query; public DynamicQueryBuilder(ISqlSugarClient db) { _db db; _query _db.QueryableT(); } public DynamicQueryBuilderT WhereIf(bool condition, ExpressionFuncT, bool predicate) { if (condition) { _query _query.Where(predicate); } return this; } public DynamicQueryBuilderT OrderByIf(bool condition, ExpressionFuncT, object field, bool isDesc false) { if (condition) { _query isDesc ? _query.OrderBy(field, OrderByType.Desc) : _query.OrderBy(field); } return this; } public ListT ToList() { return _query.ToList(); } // 其他构建方法... } // 使用示例 var builder new DynamicQueryBuilderProduct(db) .WhereIf(!string.IsNullOrEmpty(category), p p.Category category) .WhereIf(minPrice.HasValue, p p.Price minPrice.Value) .OrderByIf(sortByPrice, p p.Price, descending: true); var products builder.ToList();8. 疑难问题解决方案在实际使用SqlSugar过程中可能会遇到一些棘手的问题。以下是常见问题的解决方案。8.1 导航属性循环引用// 实体类配置 public class Order { [SugarColumn(IsIgnore true)] // 忽略导航属性序列化 public Customer Customer { get; set; } } // 查询时使用Mapper映射 var orders db.QueryableOrder() .Mapper(o o.Customer, o o.CustomerId) // 指定外键关系 .ToList();8.2 大批量数据导出// 使用分块处理避免内存溢出 int pageSize 5000; int totalCount db.QueryableProduct().Count(); int pages (int)Math.Ceiling(totalCount / (double)pageSize); for (int i 1; i pages; i) { var chunk db.QueryableProduct() .ToPageList(i, pageSize); // 处理当前分块数据... }8.3 复杂统计报表// 使用SqlSugar的报表查询功能 var report db.QueryableOrder() .GroupBy(o new { o.CreateTime.Year, o.CreateTime.Month }) .Select(g new { Year g.CreateTime.Year, Month g.CreateTime.Month, TotalOrders SqlFunc.AggregateCount(g.Id), TotalAmount SqlFunc.AggregateSum(g.Amount) }) .MergeTable() // 合并中间结果 .OrderBy(Year, Month) .ToList();8.4 数据库迁移方案// 使用SqlSugar的CodeFirst功能进行数据库迁移 db.CodeFirst.InitTables(typeof(Product), typeof(Order)); // 创建或更新表结构 // 自定义迁移逻辑 if (!db.DbMaintenance.IsAnyTable(NewFeatureTable)) { db.DbMaintenance.CreateTable(NewFeatureTable, new ListDbColumnInfo() { new DbColumnInfo() { DbColumnName Id, IsPrimaryKey true, IsIdentity true }, new DbColumnInfo() { DbColumnName FeatureName, Length 100 } }); }