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();
}
}
public void ListContains()
{
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "data source=:memory:")
.UseGenerateCommandParameterWithLambda(true)
.Build())
{
fsql.Aop.ConfigEntityProperty += (s, e) =>
{
if (e.Property.PropertyType.IsEnum)
{
e.ModifyResult.MapType = typeof(int);
}
};
var listEnum = new List <UserType> {
UserType.Client
};
var sql = fsql.Select <User>().Where(a => listEnum.Contains(a.Type)).ToSql(a => a);
Assert.Equal(@"SELECT a.""Type"" as1
FROM ""User"" a
WHERE (((a.""Type"") in (1)))", sql);
}
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "data source=:memory:")
.UseGenerateCommandParameterWithLambda(true)
.Build())
{
fsql.CodeFirst.ConfigEntity <User>(a => { });
fsql.Aop.ConfigEntityProperty += (s, e) =>
{
if (e.Property.PropertyType.IsEnum)
{
e.ModifyResult.MapType = typeof(string);
}
};
var listEnum = new List <UserType> {
UserType.Client
};
var sql = fsql.Select <User>().Where(a => listEnum.Contains(a.Type)).ToSql(a => a);
Assert.Equal(@"SELECT a.""Type"" as1
FROM ""User"" a
WHERE (((a.""Type"") in ('Client')))", sql);
}
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=5;Allow User Variables=True")
.UseGenerateCommandParameterWithLambda(true)
.Build())
{
fsql.CodeFirst.Entity <User>(a => a.ToTable("issues1137_user"));
var listEnum = new List <UserType> {
UserType.Client
};
var sql = fsql.Select <User>().Where(a => listEnum.Contains(a.Type)).ToSql(a => a);
Assert.Equal(@"SELECT a.`Type` as1
FROM `issues1137_user` a
WHERE (((a.`Type`) in ('Client')))", sql);
}
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=5;Allow User Variables=True")
.UseGenerateCommandParameterWithLambda(true)
.Build())
{
fsql.CodeFirst.Entity <User>(a => a.ToTable("issues1137_user"));
fsql.Aop.ConfigEntityProperty += (s, e) =>
{
if (e.Property.PropertyType.IsEnum)
{
e.ModifyResult.MapType = typeof(int);
}
};
var listEnum = new List <UserType> {
UserType.Client
};
var sql = fsql.Select <User>().Where(a => listEnum.Contains(a.Type)).ToSql(a => a);
Assert.Equal(@"SELECT a.`Type` as1
FROM `issues1137_user` a
WHERE (((a.`Type`) in (1)))", sql);
}
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=5;Allow User Variables=True")
.UseGenerateCommandParameterWithLambda(true)
.Build())
{
fsql.CodeFirst.Entity <User>(a => a.ToTable("issues1137_user"));
fsql.Aop.ConfigEntityProperty += (s, e) =>
{
if (e.Property.PropertyType.IsEnum)
{
e.ModifyResult.MapType = typeof(string);
}
};
var listEnum = new List <UserType> {
UserType.Client
};
var sql = fsql.Select <User>().Where(a => listEnum.Contains(a.Type)).ToSql(a => a);
Assert.Equal(@"SELECT a.`Type` as1
FROM `issues1137_user` a
WHERE (((a.`Type`) in ('Client')))", sql);
}
}
public void ArrayUseGenerateCommandParameterWithLambda()
{
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "data source=:memory:")
.UseGenerateCommandParameterWithLambda(true)
.UseAutoSyncStructure(true)
.UseMonitorCommand(null, (cmd, log) => Trace.WriteLine(log))
.Build())
{
var arr = new[] { 1L, 2L, 3L }.Select(x => x);
var ids = arr.Select(x => x);
var sql001 = fsql.Select <TableAllType>().Where(x => ids.Contains(x.Id)).ToSql();
Assert.Equal(@"SELECT a.""Id"", a.""id2"", a.""Bool"", a.""SByte"", a.""Short"", a.""Int"", a.""Long"", a.""Byte"", a.""UShort"", a.""UInt"", a.""ULong"", a.""Double"", a.""Float"", a.""Decimal"", a.""TimeSpan"", a.""DateTime"", a.""DateTimeOffSet"", a.""Bytes"", a.""String"", a.""Guid"", a.""BoolNullable"", a.""SByteNullable"", a.""ShortNullable"", a.""IntNullable"", a.""testFielLongNullable"", a.""ByteNullable"", a.""UShortNullable"", a.""UIntNullable"", a.""ULongNullable"", a.""DoubleNullable"", a.""FloatNullable"", a.""DecimalNullable"", a.""TimeSpanNullable"", a.""DateTimeNullable"", a.""DateTimeOffSetNullable"", a.""GuidNullable"", a.""Enum1"", a.""Enum1Nullable"", a.""Enum2"", a.""Enum2Nullable""
FROM ""tb_alltype"" a
WHERE (((a.""Id"") in (1,2,3)))", sql001);
IEnumerable <int> testlinqlist = new List <int>(new[] { 1, 2, 3 });
var testlinq = fsql.Select <TableAllType>().Where(a => testlinqlist.Contains(a.Int)).ToList();
var testlinq2list = new string[] { };
var testlinq2 = g.sqlite.Delete <TableAllType>().Where(a => testlinq2list.Contains(a.String)).ToSql();
Assert.Equal("DELETE FROM \"tb_alltype\" WHERE (((\"String\") in (NULL)))", testlinq2);
//in not in
var sql111 = fsql.Select <TableAllType>().Where(a => new[] { 1, 2, 3 }.Contains(a.Int)).ToList();
var sql112 = fsql.Select <TableAllType>().Where(a => new[] { 1, 2, 3 }.Contains(a.Int) == false).ToList();
var sql113 = fsql.Select <TableAllType>().Where(a => !new[] { 1, 2, 3 }.Contains(a.Int)).ToList();
var inarray = new[] { 1, 2, 3 };
var sql1111 = fsql.Select <TableAllType>().Where(a => inarray.Contains(a.Int)).ToList();
var sql1122 = fsql.Select <TableAllType>().Where(a => inarray.Contains(a.Int) == false).ToList();
var sql1133 = fsql.Select <TableAllType>().Where(a => !inarray.Contains(a.Int)).ToList();
//in not in
var sql11111 = fsql.Select <TableAllType>().Where(a => new List <int>()
{
1, 2, 3
}.Contains(a.Int)).ToList();
var sql11222 = fsql.Select <TableAllType>().Where(a => new List <int>()
{
1, 2, 3
}.Contains(a.Int) == false).ToList();
var sql11333 = fsql.Select <TableAllType>().Where(a => !new List <int>()
{
1, 2, 3
}.Contains(a.Int)).ToList();
var sql11111a = fsql.Select <TableAllType>().Where(a => new List <int>(new[] { 1, 2, 3 }).Contains(a.Int)).ToList();
var sql11222b = fsql.Select <TableAllType>().Where(a => new List <int>(new[] { 1, 2, 3 }).Contains(a.Int) == false).ToList();
var sql11333c = fsql.Select <TableAllType>().Where(a => !new List <int>(new[] { 1, 2, 3 }).Contains(a.Int)).ToList();
var inarray2 = new List <int>()
{
1, 2, 3
};
var sql111111 = fsql.Select <TableAllType>().Where(a => inarray.Contains(a.Int)).ToList();
var sql112222 = fsql.Select <TableAllType>().Where(a => inarray.Contains(a.Int) == false).ToList();
var sql113333 = fsql.Select <TableAllType>().Where(a => !inarray.Contains(a.Int)).ToList();
var inarray2n = Enumerable.Range(1, 3333).ToArray();
var sql1111111 = fsql.Select <TableAllType>().Where(a => inarray2n.Contains(a.Int)).ToList();
var sql1122222 = fsql.Select <TableAllType>().Where(a => inarray2n.Contains(a.Int) == false).ToList();
var sql1133333 = fsql.Select <TableAllType>().Where(a => !inarray2n.Contains(a.Int)).ToList();
}
}
public void SubSelectUseGenerateCommandParameterWithLambda()
{
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "data source=:memory:")
.UseConnectionString(DataType.SqlServer, "Data Source=.;Integrated Security=True;Initial Catalog=issues684;Pooling=true;Max Pool Size=3;TrustServerCertificate=true")
.UseGenerateCommandParameterWithLambda(true)
.UseAutoSyncStructure(true)
.UseMonitorCommand(null, (cmd, log) => Trace.WriteLine(log))
.Build())
{
var guidval = Guid.NewGuid();
var strval = "nameval";
var timeval = DateTime.Now;
var decval1 = 1.1M;
var decval2 = 2.2M;
var subselect = fsql.Select <ssugcpwl01>();
var sql = subselect.ToSql(a => new
{
a.id, a.name, a.createTime,
sum1 = fsql.Select <TableAllType>().Where(b => b.Guid == guidval).Sum(b => b.Int),
sum2 = fsql.Select <TableAllType>().Where(b => b.String == strval).Sum(b => b.Long),
sum3 = fsql.Select <TableAllType>().Where(b => b.DateTime == timeval).Sum(b => b.Decimal),
sum4 = fsql.Select <TableAllType>().Where(b => b.Decimal == decval1).Sum(b => b.Decimal),
sum5 = fsql.Select <TableAllType>().Where(b => b.Decimal == decval2).Sum(b => b.Decimal),
});
var subselect0 = subselect as Select0Provider;
Assert.Equal(5, subselect0._params.Count);
Assert.Equal("@exp_0", subselect0._params[0].ParameterName);
Assert.Equal("@exp_1", subselect0._params[1].ParameterName);
Assert.Equal("@exp_2", subselect0._params[2].ParameterName);
Assert.Equal("@exp_3", subselect0._params[3].ParameterName);
Assert.Equal("@exp_4", subselect0._params[4].ParameterName);
Assert.Equal(@"SELECT a.[id] as1, a.[name] as2, a.[createTime] as3, isnull((SELECT sum(b.[Int])
FROM [tb_alltype] b
WHERE (b.[Guid] = @exp_0)), 0) as4, isnull((SELECT sum(b.[Long])
FROM [tb_alltype] b
WHERE (b.[String] = @exp_1)), 0) as5, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[DateTime] = @exp_2)), 0) as6, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[Decimal] = @exp_3)), 0) as7, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[Decimal] = @exp_4)), 0) as8
FROM [ssugcpwl01] a", sql);
var groupselect = fsql.Select <ssugcpwl01>().GroupBy(a => a.name);
sql = groupselect.ToSql(a => new
{
a.Key,
sum1 = fsql.Select <TableAllType>().Where(b => b.Guid == guidval).Sum(b => b.Int),
sum2 = fsql.Select <TableAllType>().Where(b => b.String == strval).Sum(b => b.Long),
sum3 = fsql.Select <TableAllType>().Where(b => b.DateTime == timeval).Sum(b => b.Decimal),
sum4 = fsql.Select <TableAllType>().Where(b => b.Decimal == decval1).Sum(b => b.Decimal),
sum5 = fsql.Select <TableAllType>().Where(b => b.Decimal == decval2).Sum(b => b.Decimal),
});
var groupselect0 = groupselect as SelectGroupingProvider;
Assert.Equal(5, groupselect0._select._params.Count);
Assert.Equal("@exp_0", groupselect0._select._params[0].ParameterName);
Assert.Equal("@exp_1", groupselect0._select._params[1].ParameterName);
Assert.Equal("@exp_2", groupselect0._select._params[2].ParameterName);
Assert.Equal("@exp_3", groupselect0._select._params[3].ParameterName);
Assert.Equal("@exp_4", groupselect0._select._params[4].ParameterName);
Assert.Equal(@"SELECT a.[name] as1, isnull((SELECT sum(b.[Int])
FROM [tb_alltype] b
WHERE (b.[Guid] = @exp_0)), 0) as2, isnull((SELECT sum(b.[Long])
FROM [tb_alltype] b
WHERE (b.[String] = @exp_1)), 0) as3, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[DateTime] = @exp_2)), 0) as4, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[Decimal] = @exp_3)), 0) as5, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[Decimal] = @exp_4)), 0) as6
FROM [ssugcpwl01] a
GROUP BY a.[name]", sql);
}
}