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 task = CustomTest1.GetPurchaseDetailPageAsync(Db); task.Wait(); }
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(); }
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); Db.CodeFirst.InitTables <UnitTest0012>(); Db.Insertable(new UnitTest0012() { ID = "a" }).ExecuteCommand(); //Db.Insertable(new UnitTest0012() { ID = "b" }).ExecuteCommand(); //Db.CurrentConnectionConfig.MoreSettings=new ConnMoreSettings { DisableNvarchar = true }; var x = Db.Ado.GetDataTable("SELECT ID FROM UNITTEST0012 WHERE ID = :Const0 ", new SugarParameter("Const0", "a", System.Data.DbType.AnsiStringFixedLength)); if (x.Rows.Count == 0) { throw new Exception("unit query error"); } }
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(); }
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 list14 = Db.Queryable <Order, Order, Order>((o1, o2, o3) => new JoinQueryInfos(JoinType.Inner, o1.Id == o2.Id * 2, JoinType.Inner, o1.Id == o3.Id * 4) ) .Select((o1, o2, o3) => new { id = o1.Id, x = o1, x2 = o2, x3 = o3 }).ToList(); var list15 = Db.Queryable <Order, Order, Order>((o1, o2, o3) => new JoinQueryInfos(JoinType.Inner, o1.Id == o2.Id * 2, JoinType.Inner, o1.Id == o3.Id * 4) ) .Select((o1, o2, o3) => new TestModel1 { id = o1.Id.SelectAll(), x = o1, x2 = o2, x3 = o3 }).ToList(); }