public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlSugar.SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT [Dc] AS [DcNull] , [Int] AS [Dc] FROM [UnitSelectTest]", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE [UnitSelectTest2] SET
[Dc]=@Dc,[IntNull]=@IntNull WHERE [Int]=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT [Id],[Name],[Price],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT [Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT [Name],[Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlSugar.SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
var ids = Enumerable.Range(1, 11).ToList();
var list8 = Db.Queryable <Order>().Where(it => SqlFunc.ContainsArrayUseSqlParameters(ids, it.Id)).ToList();
var result2 = Db.Queryable <Unit_SYS_USER>().Where(o => o.XH == UserLoginInfo.XH).Select(o => o.XH).ToSql();
var x = Db.Queryable <BoolTest1>().Select(it => new BoolTest2()
{
a = it.a
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest1] ", "Queryable");
x = Db.Queryable <BoolTest2>().Select(it => new BoolTest1()
{
a = it.a.Value
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest2] ", "Queryable");
var db = Db;
db.CodeFirst.InitTables <UserInfo, UserIpRuleInfo>();
db.Deleteable <UserInfo>().ExecuteCommand();
db.Deleteable <UserIpRuleInfo>().ExecuteCommand();
db.Insertable(new UserInfo()
{
Id = 1,
Password = "******",
UserName = "******"
}).ExecuteCommand();
db.Insertable(new UserIpRuleInfo()
{
Addtime = DateTime.Now,
UserName = "******",
Id = 11,
UserId = 1,
Description = "xx",
IpRange = "1",
RuleType = 1
}).ExecuteCommand();
var vmList = db.Queryable <UserInfo, UserIpRuleInfo>(
(m1, m2) => m1.Id == m2.UserId
).Where((m1, m2) => m1.Id > 0).Select((m1, m2) => new UserIpRuleInfo()
{
IpRange = m2.IpRange,
Addtime = m2.Addtime,
RuleType = m2.RuleType,
}).ToList();
if (string.IsNullOrEmpty(vmList.First().IpRange))
{
throw new Exception("Queryable");
}
Db.Insertable(new Order()
{
CreateTime = DateTime.Now, CustomId = 1, Name = "a", Price = 1
}).ExecuteCommand();
var sa = Db.SqlQueryable <Order>("SELECT * FroM [ORDER] where id in (@id) ");
sa.AddParameters(new List <SugarParameter>()
{
new SugarParameter("id", new int[] { 1 })
});
int i = 0;
var salist = sa.ToPageList(1, 2, ref i);
db.CodeFirst.InitTables <UnitBytes11>();
db.Insertable(new UnitBytes11()
{
bytes = null, name = "a"
}).ExecuteCommand();
db.Insertable(new UnitBytes11()
{
bytes = new byte[] { 1, 2 }, name = "a"
}).ExecuteCommand();
var bytes = db.Queryable <UnitBytes11>().Select(it => new
{
b = it.bytes,
name = "a"
}).ToList();
var bytes2 = db.Queryable <UnitBytes11>().Select(it => new
{
b = it
}).ToList();
db.CodeFirst.InitTables <BoolTest1>();
db.CodeFirst.InitTables <BoolTest2>();
db.Queryable <BoolTest1>().Where(it => !it.a).ToList();
var test01 = db.Queryable <SaleOrder>().GroupBy(it => new { it.CheckTime.Value.Date })
.Select(it => new { x = it.CheckTime.Value.Date }).ToList();
var q1 = db.Queryable <BoolTest1>();
var x1 = q1.Clone().AS("BoolTest11");
var x2 = q1.Clone().AS("BoolTest12");
var q2 = db.UnionAll(x1, x2).ToSql();
if (!q2.Key.Contains("BoolTest11") || !q2.Key.Contains("BoolTest12"))
{
throw new Exception("unit query error");
}
db.Queryable <Order>().Where(it => SqlFunc.Round(it.Id, 2) == SqlFunc.Abs(it.Id)).ToList();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-1-1"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-1-9"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-9-11"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-11-30"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
var d1 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 1 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d1.Last() != "2021-01-01", "unit error");
var d11 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 9 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d11.Last() != "2021-01-09", "unit error");
var d111 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 11 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d111.Last() != "2021-09-11", "unit error");
var d1111 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 30 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d1111.Last() != "2021-11-30", "unit error");
var d11111 = db.Queryable <Order>()
.Where(it => it.CreateTime.ToString("yyyy-MM-dd") == "2021-11-30")
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d11111.Last() != "2021-11-30", "unit error");
db.CodeFirst.InitTables <UnitEnumadfa>();
db.Insertable(new UnitEnumadfa()).ExecuteCommand();
db.Insertable(new UnitEnumadfa()
{
Type = DbType.Sqlite
}).ExecuteCommand();
var listEnum = db.Queryable <UnitEnumadfa>().ToList();
var d111111 = db.Queryable <Order>()
.Where(it => it.Id == SqlFunc.IF(true).Return(1).End(0))
.ToList();
var d1111111 = db.Queryable <Order>()
.Where(it => it.Id == SqlFunc.IF(it.Id > 0).Return(1).End(0))
.ToList();
var d11111111 = db.Queryable <Order>()
.Where(it => it.Id == (it.Id > 0? (it.Id == 1?11:1):2))
.ToList();
var d111111111 = db.Queryable <Order>()
.Where(it => it.Id == (it.Id > 0 ? (it.Id == 1 ? 11 : (it.Id == 2?2:1)) : 2))
.ToList();
bool?bq = true;
var d1111111111 = db.Queryable <BoolTest1>().Where(it => it.a.Equals(bq.Value)).ToArray();
var d11111111111 = db.Queryable <BoolTest1>().Where(it => SqlFunc.IIF(bq.Value, 1, 2) == 1).ToArray();
var d111111111111 = db.Queryable <BoolTest1>().Select(it => new { x = SqlFunc.IsNull(it.a, false) }).ToArray();
db.CodeFirst.InitTables <SqlSugarDemo.UserEntity, SqlSugarDemo.RoleEntity, SqlSugarDemo.UserRoleEntity>();
var data = new SqlSugarDemo.UserEntity()
{
CardNo = "",
CompanyWX = "",
Credential = "",
EmailAccount = "",
EndDate = DateTime.Now,
FailedLoginPwdCount = 1,
IsChangePassword = true,
IsReal = 1,
LastLoginDate = DateTime.Now,
ManageAccount = Guid.NewGuid(),
ManageOrg = Guid.NewGuid(),
NickName = "",
PhoneAccount = "",
RealName = "",
VerificationLoginPwdDate = DateTime.Now,
SafePhone = "",
Sex = 1,
StartDate = DateTime.Now,
StopLoginTime = DateTime.Now,
UserAccount = "",
UserId = Guid.NewGuid(),
UserType = 1
};
db.Insertable(data).ExecuteCommand();
//var role = new SqlSugarDemo.RoleEntity()
//{
// RoleId=Guid.NewGuid(),
// ManageAccount= Guid.NewGuid(),
// ManageOrg=Guid.NewGuid(),
// OrganizationId=Guid.NewGuid(),
// UnitPrice=1,
// Quantity=1,
// RoleName="",
// RoleType=1,
// SortNum=1
//};
//db.Insertable(role).ExecuteCommand();
//db.Insertable(new SqlSugarDemo.UserRoleEntity()
//{
// RoleId= role.RoleId,
// UserId=data.UserId
//}).ExecuteCommand();
var d1111111111111 = db.Queryable <SqlSugarDemo.UserEntity>()
.Mapper <SqlSugarDemo.UserEntity, SqlSugarDemo.RoleEntity, SqlSugarDemo.UserRoleEntity>(it => ManyToMany.Config(it.UserId, it.RoleId)).InSingle(data.UserId);
}
public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlSugar.SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT [Dc] AS [DcNull] , [Int] AS [Dc] FROM [UnitSelectTest]", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE [UnitSelectTest2] SET
[Dc]=@Dc,[IntNull]=@IntNull WHERE [Int]=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT [Id],[Name],[Price],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT [Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT [Name],[Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlSugar.SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
var ids = Enumerable.Range(1, 11).ToList();
var list8 = Db.Queryable <Order>().Where(it => SqlFunc.ContainsArrayUseSqlParameters(ids, it.Id)).ToList();
var result2 = Db.Queryable <Unit_SYS_USER>().Where(o => o.XH == UserLoginInfo.XH).Select(o => o.XH).ToSql();
var x = Db.Queryable <BoolTest1>().Select(it => new BoolTest2()
{
a = it.a
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest1] ", "Queryable");
x = Db.Queryable <BoolTest2>().Select(it => new BoolTest1()
{
a = it.a.Value
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest2] ", "Queryable");
var db = Db;
db.CodeFirst.InitTables <UserInfo, UserIpRuleInfo>();
db.Deleteable <UserInfo>().ExecuteCommand();
db.Deleteable <UserIpRuleInfo>().ExecuteCommand();
db.Insertable(new UserInfo()
{
Id = 1,
Password = "******",
UserName = "******"
}).ExecuteCommand();
db.Insertable(new UserIpRuleInfo()
{
Addtime = DateTime.Now,
UserName = "******",
Id = 11,
UserId = 1,
Description = "xx",
IpRange = "1",
RuleType = 1
}).ExecuteCommand();
var vmList = db.Queryable <UserInfo, UserIpRuleInfo>(
(m1, m2) => m1.Id == m2.UserId
).Where((m1, m2) => m1.Id > 0).Select((m1, m2) => new UserIpRuleInfo()
{
IpRange = m2.IpRange,
Addtime = m2.Addtime,
RuleType = m2.RuleType,
}).ToList();
if (string.IsNullOrEmpty(vmList.First().IpRange))
{
throw new Exception("Queryable");
}
Db.Insertable(new Order()
{
CreateTime = DateTime.Now, CustomId = 1, Name = "a", Price = 1
}).ExecuteCommand();
var sa = Db.SqlQueryable <Order>("SELECT * FroM [ORDER] where id in (@id) ");
sa.AddParameters(new List <SugarParameter>()
{
new SugarParameter("id", new int[] { 1 })
});
int i = 0;
var salist = sa.ToPageList(1, 2, ref i);
db.CodeFirst.InitTables <UnitBytes11>();
db.Insertable(new UnitBytes11()
{
bytes = null, name = "a"
}).ExecuteCommand();
db.Insertable(new UnitBytes11()
{
bytes = new byte[] { 1, 2 }, name = "a"
}).ExecuteCommand();
var bytes = db.Queryable <UnitBytes11>().Select(it => new
{
b = it.bytes,
name = "a"
}).ToList();
db.CodeFirst.InitTables <BoolTest1>();
db.CodeFirst.InitTables <BoolTest2>();
db.Queryable <BoolTest1>().Where(it => !it.a).ToList();
var test01 = db.Queryable <SaleOrder>().GroupBy(it => new { it.CheckTime.Value.Date })
.Select(it => new { x = it.CheckTime.Value.Date }).ToList();
var q1 = db.Queryable <BoolTest1>();
var x1 = q1.Clone().AS("BoolTest11");
var x2 = q1.Clone().AS("BoolTest12");
var q2 = db.UnionAll(x1, x2).ToSql();
if (!q2.Key.Contains("BoolTest11") || !q2.Key.Contains("BoolTest12"))
{
throw new Exception("unit query error");
}
db.Queryable <Order>().Where(it => SqlFunc.Round(it.Id, 2) == SqlFunc.Abs(it.Id)).ToList();
}