Freesql ORM框架核心功能详解与实战应用
基础配置与连接
首先需要引入必要的NuGet包：
// 核心包和SqlServer驱动包
FreeSql
FreeSql.Provider.SqlServer
初始化数据库连接：
var db = new FreeSqlBuilder()
.UseConnectionString(DataType.SqlServer, connectionString)
.UseMonitorCommand(cmd => Console.WriteLine(cmd.CommandText))
.Build();
启用脏读避免死锁（仅SqlServer）：
db.SetGlobalSelectWithLock(SqlServerLock.NoLock, null);
查询操作详解
日期范围查询：
var result = db.Select<Entity>()
.Where(a => a.CreateTime.Between(startTime, endTime))
.ToList();
单实体查询：
var item = await db.Select<Song>()
.Where(a => a.Id == id)
.ToOneAsync();
自定义SQL查询：
var customResult = db.Ado.Query<CustomDto>(
"SELECT * FROM Users WHERE Name LIKE @name",
new { name = "%keyword%" }
);
排序操作：
var sortedList = db.Select<Topic>()
.OrderBy(true, "CreatedDate DESC, Id ASC")
.ToList();
去重查询：
var distinctList = db.Select<Area>()
.Where(x => x.Type == 1)
.Distinct()
.ToList(x => new Area { CityCode = x.CityCode, CityName = x.CityName });
聚合查询：
// 单列聚合
var maxId = db.Select<City>()
.Where(x => x.Code == "123")
.Max(x => x.Id);
// 多列聚合
var aggregate = await db.Select<City>()
.ToAggregateAsync(x => new {
maxId = x.Max(x.Key.Id),
count = x.Count(x.Key.CityId),
sum = x.Sum(x.Key.ProId)
});
ToSql高级用法
字段别名设置：
var sql1 = db.Select<Topic>()
.ToSql(a => new { a.Id, a.Title }, FieldAliasOptions.AsProperty);
var sql2 = db.Select<Topic>()
.ToSql("a.Id AS Id, a.Title AS Title");
复杂查询技术
多表连接：
var joinResult = db.Select<Topic, Category, Type>()
.LeftJoin((t1, t2, t3) => t1.CategoryId == t2.Id)
.LeftJoin((t1, t2, t3) => t2.ParentId == t3.Id)
.Where((t1, t2, t3) => t3.Id > 0)
.ToList((t1, t2, t3) => new { t1, t2, t3 });
条件表达式：
StatusText = x.Enabled == 1 ? "启用" : "停用"
PlaceText = SqlExt.Case()
.When(x.Type == 1, "国家")
.When(x.Type == 2, "地区")
.Else("街道")
.End()
分组聚合：
var groupResult = db.Select<City>()
.GroupBy(x => new { x.CityName, x.Id })
.ToList(g => new {
CityName = g.Key.CityName,
Id = g.Key.Id,
Count = g.Count()
});
表达式拼接：
Expression<Func<Student, bool>> exp1 = x => x.Id == "1";
var exp2 = exp1.And(x => x.Key == "2");
var exp3 = exp1.Or(x => x.Type == "3");
子查询：
var subQuery = db.Select<Province>()
.Where(x => db.Select<City>()
.Where(b => b.CityType == 0)
.ToList(b => b.ProvinceId)
.Contains(x.Id))
.ToList();
窗口函数：
var windowResult = db.Select<City>()
.ToList(x => new {
all = x,
RowNum = SqlExt.RowNumber()
.Over()
.PartitionBy(x.ProvinceId)
.OrderBy(x.CityCode)
.ToValue()
});
数据操作
插入操作：
[Column(IsPrimary = true, IsIdentity = true)]
public int Id { get; set; }
db.Insert(entity).ExecuteAffrows();
db.Insert(list).IgnoreColumns(x => x.Id).ExecuteAffrows();
删除操作：
db.Delete<Topic>(entity).ExecuteAffrows();
db.Delete<Topic>().Where(s => s.Id == 1).ExecuteAffrows();
更新操作：
// 先查后更
db.Update<Topic>()
.SetSource(list)
.IgnoreColumns(a => new { a.Clicks, a.CreateTime })
.ExecuteAffrows();
// 直接更新
db.Update<Topic>()
.Set(a => a.Title, "新标题")
.Where(a => a.Id == 1)
.ExecuteAffrows();
保存操作：
db.InsertOrUpdate<Entity>()
.SetSource(list)
.ExecuteAffrows();
特性配置
// 表结构相关
[Table(Name = "Student")]
[Column(IsPrimary = true)]
[Column(IsIdentity = true)]
// 数据精度
[Column(Precision = 10, Scale = 2)]
[Column(StringLength = 128)]
// 时间戳自动维护
[Column(ServerTime = DateTimeKind.Local, CanUpdate = false)]
public DateTime CreateTime { get; set; }
[Column(ServerTime = DateTimeKind.Local, CanInsert = false)]
public DateTime UpdateTime { get; set; }
过滤器与事务
全局过滤器：
db.GlobalFilter.Apply<UserInfo>("active", x => x.IsActive == true);
var list = db.Select<UserInfo>()
.DisableGlobalFilter("active")
.ToList();
事务处理：
using (var uow = db.CreateUnitOfWork()) {
try {
uow.GetOrBeginTransaction();
var student = new Student { Name = "张三" };
uow.Orm.Insert(student).ExecuteAffrows();
uow.Commit();
} catch (Exception ex) {
uow.Rollback();
Console.WriteLine($"操作失败：{ex.Message}");
}
}
数据迁移
CodeFirst模式：
db.CodeFirst.SyncStructure<EntityType>();
DbFirst模式：
var tables = db.DbFirst.GetTablesByDatabase("databaseName");
var tableInfo = db.DbFirst.GetTableByName("tableName");
性能优化技巧
大批量数据查询分批处理：
static List<List<T>> BatchProcess<T>(List<T> source, int batchSize) {
var batches = new List<List<T>>();
for (int i = 0; i < source.Count; i += batchSize) {
batches.Add(source.GetRange(i, Math.Min(batchSize, source.Count - i)));
}
return batches;
}
