public void DynamicLinqTest()
{
using (var freeSql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "Data Source=:memory:;")
.UseAutoSyncStructure(true)
.Build())
{
freeSql.Aop.CurdBefore += (s, e) =>
{
Trace.WriteLine(e.Sql);
};
freeSql.Insert(
Enumerable.Range(1, 100)
.Select(i => new Topic {
Title = $"new topic {i}", Clicks = 100
})
.ToList())
.ExecuteAffrows();
// 常规用法
var list1 = freeSql.Select <Topic>()
.Where(t => t.Title.StartsWith("new topic 1"))
.ToList();
// 借助 DynamicExpressionParser
var list2 = freeSql.Select <Topic>()
.WhereDynamicLinq("Title.StartsWith(\"new topic 1\")")
.ToList();
// 拓展 DynamicFilter
var dynmaicFilterInfo = new DynamicFilterInfo
{
Field = $"{nameof(DynamicLinqCustom.DynamicLinq)} {typeof(DynamicLinqCustom).FullName},{typeof(DynamicLinqCustom).Assembly.FullName}",
Operator = DynamicFilterOperator.Custom,
Value = "Title.StartsWith(\"new topic 1\")",
};
var list3 = freeSql.Select <Topic>()
.WhereDynamicFilter(dynmaicFilterInfo)
.ToList();
}
}
public void IncludeLevelTest()
{
using (var freeSql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "Data Source=:memory:;")
.UseAutoSyncStructure(true)
.Build())
{
freeSql.Aop.CurdBefore += (s, e) =>
{
Trace.WriteLine(e.Sql);
};
var company = new Company {
Id = Guid.NewGuid(), Code = "CO001"
};
var department = new Department {
Id = Guid.NewGuid(), Code = "D001", CompanyId = company.Id
};
var orgnization = new Orgnization {
Code = "C001", CompanyId = company.Id
};
freeSql.Insert(company).ExecuteAffrows();
freeSql.Insert(orgnization).ExecuteAffrows();
freeSql.Insert(department).ExecuteAffrows();
var materials = new[]
{
new Material {
Code = "TEST1", Units = new List <Unit> {
new Unit {
Code = "KG"
}
}
},
new Material {
Code = "TEST2", Units = new List <Unit> {
new Unit {
Code = "KG"
}
}
}
};
var repo1 = freeSql.GetGuidRepository <Material>();
repo1.DbContextOptions.EnableCascadeSave = true;
repo1.Insert(materials);
var order = new Order
{
Code = "X001",
OrgnizationId = orgnization.Id,
OrderItems = new List <OrderItem>
{
new OrderItem {
ItemCode = "01", MaterialId = materials[0].Id
},
new OrderItem {
ItemCode = "02", MaterialId = materials[1].Id
},
}
};
var repo2 = freeSql.GetGuidRepository <Order>();
repo2.DbContextOptions.EnableCascadeSave = true;
repo2.Insert(order);
// 可以完整加载数据
var list1 = freeSql.Select <Orgnization>().IncludeMany(t => t.Company.Departments).ToList();
// 只能查询到Orgnization
var list2 = freeSql.Select <Order>().IncludeMany(t => t.Orgnization.Company.Departments).ToList();
//freeSql.Select<Order>().IncludeMany(t => t.OrderItems, then => then.IncludeMany(t => t.Material.Units)).ToList().Dump();
// 使用扩展方法加载到指定层级
var list3 = freeSql.Select <Order>().IncludeLevel(3).ToList();
}
}
public void WhereByPropertyTest()
{
using (var freeSql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "Data Source=:memory:;")
.UseAutoSyncStructure(true)
.Build())
{
freeSql.Aop.CurdBefore += (s, e) =>
{
Trace.WriteLine(e.Sql);
};
var company = new Company {
Id = Guid.NewGuid(), Code = "CO001"
};
var department = new Department {
Id = Guid.NewGuid(), Code = "D001", CompanyId = company.Id
};
var orgnization = new Orgnization {
Code = "C001", CompanyId = company.Id
};
freeSql.Insert(company).ExecuteAffrows();
freeSql.Insert(orgnization).ExecuteAffrows();
freeSql.Insert(department).ExecuteAffrows();
var materials = new[]
{
new Material {
Code = "TEST1", Units = new List <Unit> {
new Unit {
Code = "KG"
}
}
},
new Material {
Code = "TEST2", Units = new List <Unit> {
new Unit {
Code = "KG"
}
}
}
};
var repo1 = freeSql.GetGuidRepository <Material>();
repo1.DbContextOptions.EnableCascadeSave = true;
repo1.Insert(materials);
var order = new Order
{
Code = "X001",
OrgnizationId = orgnization.Id,
OrderItems = new List <OrderItem>
{
new OrderItem {
ItemCode = "01", MaterialId = materials[0].Id
},
new OrderItem {
ItemCode = "02", MaterialId = materials[1].Id
},
}
};
var repo2 = freeSql.GetGuidRepository <Order>();
repo2.DbContextOptions.EnableCascadeSave = true;
repo2.Insert(order);
// 根据导航属性过滤数据
//var list1 = freeSql.Select<Order>().Where(t => t.OrderItems.Any(t1 => t1.Material.Units.Any(t2 => t2.Code == "KG"))).ToList();
var filterInfo1 = new DynamicFilterInfo
{
Field = "Code",
Operator = DynamicFilterOperator.Eq,
Value = "KG",
};
var list1 = freeSql.Select <Order>().Where(t => t.OrderItems.Any(t1 => t1.Material.Units.AsSelect().WhereDynamicFilter(filterInfo1).Any())).ToList();
// 导航属性如果是 OneToOne 或者 ManyToOne 默认支持
var filterInfo2 = new DynamicFilterInfo
{
Field = "Orgnization.Company.Code",
Operator = DynamicFilterOperator.Eq,
Value = "CO001",
};
//var list2 = freeSql.Select<Order>().Where(t => t.Orgnization.Company.Code == "CO001").ToList();
var list2 = freeSql.Select <Order>().WhereDynamicFilter(filterInfo2).ToList();
// 实现效果 OrderItems.Material.Units.Code == "KG"
var list3 = freeSql.Select <Order>().Where("OrderItems.Material.Units.Code", DynamicFilterOperator.Eq, "KG").ToList();
// 实现效果 OrderItems.Material.Code == "TEST1"
// Error SQL:
// SELECT a."Id", a."Code", a."OrgnizationId"
// FROM "Order" a
// WHERE (exists(SELECT 1
// FROM "OrderItem" a
// LEFT JOIN "Material" a__Material ON a__Material."Id" = a."MaterialId"
// WHERE (a__Material."Code" = 'TEST1') AND (a."OrderId" = a."Id")
// limit 0,1))
var list4 = freeSql.Select <Order>().Where("OrderItems.Material.Code", DynamicFilterOperator.Eq, "TEST1").ToList();
// 拓展 DynamicFilter
var dynmaicFilterInfo = new DynamicFilterInfo
{
Field = $"{nameof(DynamicLinqCustom.WhereNavigation)} {typeof(DynamicLinqCustom).FullName},{typeof(DynamicLinqCustom).Assembly.FullName}",
Operator = DynamicFilterOperator.Custom,
Value = JsonConvert.SerializeObject(new DynamicFilterInfo {
Field = "OrderItems.Material.Units.Code", Operator = DynamicFilterOperator.Eq, Value = "KG"
}),
};
var list5 = freeSql.Select <Order>()
.WhereDynamicFilter(dynmaicFilterInfo)
.ToList();
}
}