public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().Select <object>("*").ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Student>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Select(it => it.SchoolId).ToList();
var sum2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Sum((st, sc) => sc.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var count = db.Queryable <Student>().Count(it => it.Id > 0);
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var joinSql = db.Queryable("student", "s").OrderBy("id").Select("id,name").ToPageList(1, 2);
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var getSqlList = db.Queryable <Student>().AS("(select * from student) t").ToList();
var getUnionAllList = db.UnionAll(db.Queryable <Student>().Where(it => it.Id == 1), db.Queryable <Student>().Where(it => it.Id == 2)).ToList();
var getUnionAllList2 = db.UnionAll(db.Queryable <Student>(), db.Queryable <Student>()).ToList();
var test1 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Where(st => st.CreateTime > SqlFunc.GetDate()).Select((st, sc) => SqlFunc.ToInt64(sc.Id)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
var test3 = db.Queryable <DataTestInfo2>().Select(it => it.Bool1).ToSql();
var test4 = db.Queryable <DataTestInfo2>().Select(it => new { b = it.Bool1 }).ToSql();
DateTime?result = DateTime.Now;
var test5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToList();
}
private static void SqlFuncTest()
{
Console.WriteLine("");
Console.WriteLine("#### SqlFunc Start ####");
var db = GetInstance();
var index = db.Queryable <Order>().Select(it => SqlFunc.CharIndex("a", "cccacc")).First();
var list = db.Queryable <Order>().Select(it => new ViewOrder()
{
Id = SqlFunc.AggregateSum(SqlFunc.IF(it.Id > 0).Return(1).End(0))
}).ToList();
Console.WriteLine("#### SqlFunc End ####");
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().ToList();
var getTop2 = db.Queryable <Student>().Take(2).ToList();//TOP2
var getLike = db.Queryable <Student>().Where(it => it.Name.Contains("a")).ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 2).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Sum(it => it.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var asCount = db.Queryable <object>().AS("student").Count();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var unionAll = db.UnionAll <Student>(db.Queryable <Student>(), db.Queryable <Student>());
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).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 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 string PrintList(string stockInId)
{
var list1 = _client.Queryable <Wms_stockin, Wms_supplier, Sys_dict, Sys_user, Sys_user>
((s, p, d, c, u) => new object[] {
JoinType.Left, s.SupplierId == p.SupplierId,
JoinType.Left, s.StockInType == d.DictId,
JoinType.Left, s.CreateBy == c.UserId,
JoinType.Left, s.ModifiedBy == u.UserId,
})
.Where((s, p, d, c, u) => s.IsDel == 1 && d.IsDel == 1 && c.IsDel == 1)
.Select((s, p, d, c, u) => new
{
StockInId = s.StockInId.ToString(),
StockInType = d.DictName,
StockInTypeId = s.StockInType.ToString(),
s.StockInStatus,
s.StockInNo,
s.OrderNo,
s.SupplierId,
p.SupplierNo,
p.SupplierName,
s.IsDel,
s.Remark,
CName = c.UserNickname,
s.CreateDate,
UName = u.UserNickname,
s.ModifiedDate
}).MergeTable().Where(s => s.StockInId == stockInId).ToList();
bool flag1 = true;
bool flag2 = true;
var list2 = _client.Queryable <Wms_stockindetail, Wms_material, Wms_stockin, Wms_storagerack, Sys_user, Sys_user, Sys_user>
((s, m, p, g, c, u, a) => new object[] {
JoinType.Left, s.MaterialId == m.MaterialId,
JoinType.Left, s.StockInId == p.StockInId,
JoinType.Left, s.StoragerackId == g.StorageRackId,
JoinType.Left, s.CreateBy == c.UserId,
JoinType.Left, s.ModifiedBy == u.UserId,
JoinType.Left, s.AuditinId == a.UserId,
})
.Where((s, m, p, g, c, u, a) => s.IsDel == 1 && p.IsDel == 1 && g.IsDel == 1 && c.IsDel == 1)
.Select((s, m, p, g, c, u, a) => new
{
StockInId = s.StockInId.ToString(),
StockInDetailId = s.StockInDetailId.ToString(),
m.MaterialNo,
m.MaterialName,
g.StorageRackNo,
g.StorageRackName,
Status = SqlFunc.IF(s.Status == 1).Return(StockInStatus.initial.GetDescription())
.ElseIF(s.Status == 2).Return(StockInStatus.egis.GetDescription())
.ElseIF(s.Status == 3).Return(StockInStatus.auditfailed.GetDescription())
.End(StockInStatus.underReview.GetDescription()),
s.PlanInQty,
s.ActInQty,
s.IsDel,
s.Remark,
s.AuditinTime,
AName = a.UserNickname,
CName = c.UserNickname,
s.CreateDate,
UName = u.UserNickname,
s.ModifiedDate
}).MergeTable().Where(c => c.StockInId == stockInId).OrderBy(c => c.CreateDate, OrderByType.Desc).ToList();
if (!list1.Any())
{
flag1 = false;
}
if (!list2.Any())
{
flag2 = false;
}
var html = FileUtil.ReadFileFromPath(Path.Combine(_env.WebRootPath, "upload", "StockIn.html"));
return((flag1, list1, flag2, list2, html).JilToJson());
}
//public string PageList(PubParams.StockOutBootstrapParams bootstrap)
//{
// int totalNumber = 0;
// if (bootstrap.offset != 0)
// {
// bootstrap.offset = bootstrap.offset / bootstrap.limit + 1;
// }
// var query = _client.Queryable<Wms_stockout, Wms_Customer, Sys_dict, Sys_user, Sys_user>
// ((s, p, d, c, u) => new object[] {
// JoinType.Left,s.CustomerId==p.CustomerId,
// JoinType.Left,s.StockOutType==d.DictId,
// JoinType.Left,s.CreateBy==c.UserId,
// JoinType.Left,s.ModifiedBy==u.UserId,
// })
// .Where((s, p, d, c, u) => s.WarehouseId == bootstrap.storeId && s.IsDel == 1 && d.IsDel == 1 && c.IsDel == 1)
// .Select((s, p, d, c, u) => new
// {
// StockOutId = s.StockOutId.ToString(),
// StockOutType = d.DictName,
// StockOutTypeId = s.StockOutType.ToString(),
// s.StockOutStatus,
// s.StockOutNo,
// s.OrderNo,
// s.CustomerId,
// p.CustomerNo,
// p.CustomerName,
// s.IsDel,
// s.Remark,
// CName = c.UserNickname,
// s.CreateDate,
// UName = u.UserNickname,
// s.ModifiedDate
// }).MergeTable();
// if (!bootstrap.search.IsEmpty())
// {
// query.Where((s) => s.StockOutNo.Contains(bootstrap.search) || s.OrderNo.Contains(bootstrap.search));
// }
// if (!bootstrap.datemin.IsEmpty() && !bootstrap.datemax.IsEmpty())
// {
// query.Where(s => s.CreateDate > bootstrap.datemin.ToDateTimeB() && s.CreateDate <= bootstrap.datemax.ToDateTimeE());
// }
// if (!bootstrap.StockOutType.IsEmpty())
// {
// query.Where((s) => s.StockOutTypeId.Contains(bootstrap.StockOutType));
// }
// if (!bootstrap.StockOutStatus.IsEmpty())
// {
// query.Where((s) => s.StockOutStatus == bootstrap.StockOutStatus.ToByte());
// }
// if (bootstrap.order.Equals("desc", StringComparison.OrdinalIgnoreCase))
// {
// query.OrderBy($"MergeTable.{bootstrap.sort} desc");
// }
// else
// {
// query.OrderBy($"MergeTable.{bootstrap.sort} asc");
// }
// var list = query.ToPageList(bootstrap.offset, bootstrap.limit, ref totalNumber);
// return Bootstrap.GridData(list, totalNumber).JilToJson();
//}
public string PrintList(string stockInId)
{
var list1 = _client.Queryable <Wms_stockout, Wms_Customer, Sys_dict, Sys_user, Sys_user>
((s, p, d, c, u) => new object[] {
JoinType.Left, s.CustomerId == p.CustomerId,
JoinType.Left, s.StockOutType == d.DictId,
JoinType.Left, s.CreateBy == c.UserId,
JoinType.Left, s.ModifiedBy == u.UserId,
})
.Where((s, p, d, c, u) => s.IsDel == 1 && d.IsDel == 1)
.Select((s, p, d, c, u) => new
{
StockOutId = s.StockOutId.ToString(),
StockOutType = d.DictName,
StockOutTypeId = s.StockOutType.ToString(),
s.StockOutStatus,
s.StockOutNo,
s.OrderNo,
s.CustomerId,
p.CustomerName,
p.CustomerNo,
s.IsDel,
s.Remark,
CName = c.UserNickname,
s.CreateDate,
UName = u.UserNickname,
s.ModifiedDate
}).MergeTable().Where(s => s.StockOutId == stockInId).ToList();
bool flag1 = true;
bool flag2 = true;
var list2 = _client.Queryable <Wms_stockoutdetail, Wms_material, Wms_stockout, Sys_user, Sys_user>
((s, m, p, c, u) => new object[] {
JoinType.Left, s.MaterialId == m.MaterialId,
JoinType.Left, s.StockOutId == p.StockOutId,
JoinType.Left, s.CreateBy == c.UserId,
JoinType.Left, s.ModifiedBy == u.UserId,
})
.Where((s, m, p, c, u) => s.IsDel == 1 && m.IsDel == 1 && p.IsDel == 1 && c.IsDel == 1)
.Select((s, m, p, c, u) => new
{
StockOutId = s.StockOutId.ToString(),
StockOutDetailId = s.StockOutDetailId.ToString(),
m.MaterialNo,
m.MaterialName,
Status = SqlFunc.IF(s.Status == 1).Return(StockOutStatus.initial.GetDescription())
.ElseIF(s.Status == 2).Return(StockOutStatus.task_confirm.GetDescription())
.ElseIF(s.Status == 3).Return(StockOutStatus.task_canceled.GetDescription())
.ElseIF(s.Status == 3).Return(StockOutStatus.task_working.GetDescription())
.End(StockOutStatus.task_finish.GetDescription()),
s.PlanOutQty,
s.ActOutQty,
s.IsDel,
s.Remark,
CName = c.UserNickname,
s.CreateDate,
UName = u.UserNickname,
s.ModifiedDate
}).MergeTable().Where(c => c.StockOutId == stockInId).OrderBy(c => c.CreateDate, OrderByType.Desc).ToList();
if (!list1.Any())
{
flag1 = false;
}
if (!list2.Any())
{
flag2 = false;
}
var html = FileUtil.ReadFileFromPath(Path.Combine(_env.WebRootPath, "upload", "StockOut.html"));
return((flag1, list1, flag2, list2, html).JilToJson());
}
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");
Db.CodeFirst.InitTables <BoolTest3>();
var blist3 = Db.Queryable <BoolTest3>().Select(it => new BoolTest3()
{
a = string.IsNullOrEmpty(it.Name)
}).ToList();
var blist4 = Db.Queryable <BoolTest3>().Select(it => new BoolTest3()
{
a = SqlFunc.IIF(it.a == true, true, false)
}).ToList();
var blist5 = Db.Queryable <BoolTest3>().Select(it => new BoolTest3()
{
a = SqlFunc.IF(it.a == true).Return(true).End(false)
}).ToList();
var blist6 = Db.Queryable <BoolTest3>().Select(it => new BoolTest3()
{
a = it.a == true?true:false
}).ToList();
var blist7 = Db.Queryable <BoolTest3>().Select(it => new BoolTest3()
{
a = SqlFunc.Subqueryable <Order>().Any()
}).ToList();
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();
}
//public string PageList(PubParams.StockInBootstrapParams bootstrap)
//{
// int totalNumber = 0;
// if (bootstrap.offset != 0)
// {
// bootstrap.offset = bootstrap.offset / bootstrap.limit + 1;
// }
// var query = _client.Queryable<Wms_stockin, Wms_supplier, Sys_dict, Sys_user, Sys_user>
// ((s, p, d, c, u) => new object[] {
// JoinType.Left,s.SupplierId==p.SupplierId,
// JoinType.Left,s.StockInType==d.DictId,
// JoinType.Left,s.CreateBy==c.UserId,
// JoinType.Left,s.ModifiedBy==u.UserId,
// })
// .Where((s, p, d, c, u) => s.WarehouseId == bootstrap.storeId && s.IsDel == 1 && d.IsDel == 1 && c.IsDel == 1)
// .Select((s, p, d, c, u) => new
// {
// StockInId = s.StockInId.ToString(),
// StockInType = d.DictName,
// StockInTypeId = s.StockInType.ToString(),
// s.StockInStatus,
// s.StockInNo,
// s.OrderNo,
// s.SupplierId,
// p.SupplierNo,
// p.SupplierName,
// s.IsDel,
// s.Remark,
// CName = c.UserNickname,
// s.CreateDate,
// UName = u.UserNickname,
// s.ModifiedDate
// }).MergeTable();
// if (!bootstrap.search.IsEmpty())
// {
// query.Where((s) => s.StockInNo.Contains(bootstrap.search) || s.OrderNo.Contains(bootstrap.search));
// }
// if (!bootstrap.datemin.IsEmpty() && !bootstrap.datemax.IsEmpty())
// {
// query.Where(s => s.CreateDate > bootstrap.datemin.ToDateTimeB() && s.CreateDate <= bootstrap.datemax.ToDateTimeE());
// }
// if (!bootstrap.StockInType.IsEmpty())
// {
// query.Where((s) => s.StockInTypeId.Contains(bootstrap.StockInType));
// }
// if (!bootstrap.StockInStatus.IsEmpty())
// {
// query.Where((s) => s.StockInStatus == bootstrap.StockInStatus.ToByte());
// }
// if (bootstrap.order.Equals("desc", StringComparison.OrdinalIgnoreCase))
// {
// query.OrderBy($"MergeTable.{bootstrap.sort} desc");
// }
// else
// {
// query.OrderBy($"MergeTable.{bootstrap.sort} asc");
// }
// var list = query.ToPageList(bootstrap.offset, bootstrap.limit, ref totalNumber);
// return Bootstrap.GridData(list, totalNumber).JilToJson();
//}
public string PrintList(string stockInId, long?detailId)
{
long id = long.Parse(stockInId);
var list1 = _client.Queryable <Wms_stockin, Wms_supplier, Sys_dict, Sys_user, Sys_user>
((s, p, d, c, u) => new object[] {
JoinType.Left, s.SupplierId == p.SupplierId,
JoinType.Left, s.StockInType == d.DictId,
JoinType.Left, s.CreateBy == c.UserId,
JoinType.Left, s.ModifiedBy == u.UserId,
})
.Where((s, p, d, c, u) => s.IsDel == 1 && d.IsDel == 1 && c.IsDel == 1)
.Select((s, p, d, c, u) => new
{
StockInId = s.StockInId.ToString(),
StockInType = d.DictName,
StockInTypeId = s.StockInType.ToString(),
s.StockInStatus,
s.StockInNo,
s.OrderNo,
s.SupplierId,
p.SupplierNo,
p.SupplierName,
s.IsDel,
s.Remark,
CName = c.UserNickname,
s.CreateDate,
UName = u.UserNickname,
s.ModifiedDate
}).MergeTable().Where(s => s.StockInId == stockInId).ToList();
bool flag1 = true;
bool flag2 = true;
var query = _client.Queryable <Wms_stockindetail, Wms_stockindetail_box, Wms_inventorybox, Wms_material, Wms_stockin>
((s, sb, ib, m, p) => new object[] {
JoinType.Left, s.StockInDetailId == sb.StockinDetailId,
JoinType.Left, sb.InventoryBoxId == ib.InventoryBoxId,
JoinType.Left, s.MaterialId == m.MaterialId && m.IsDel == 1,
JoinType.Left, s.StockInId == p.StockInId && p.IsDel == 1
})
.Where((s, sb, ib, m, p) => s.StockInId == id && (detailId == null || s.StockInDetailId == detailId))
.OrderBy((s, sb, ib, m, p) => s.CreateDate, OrderByType.Desc)
.Select((s, sb, ib, m, p) => new
{
StockInId = s.StockInId.ToString(),
StockInDetailId = s.StockInDetailId.ToString(),
s.SubWarehousingId,
TaskId = sb.InventoryBoxTaskId.ToString(),
DetailBoxId = sb.DetailBoxId.ToString(),
ib.InventoryBoxNo,
m.MaterialNo,
m.MaterialName,
Status = SqlFunc.IF(s.Status == 1).Return(StockInStatus.initial.GetDescription())
.ElseIF(s.Status == 2).Return(StockInStatus.task_confirm.GetDescription())
.ElseIF(s.Status == 3).Return(StockInStatus.task_canceled.GetDescription())
.ElseIF(s.Status == 4).Return(StockInStatus.task_working.GetDescription())
.End(StockInStatus.task_finish.GetDescription()),
s.PlanInQty,
s.ActInQty,
sb.Qty,
m.UnitName,
s.IsDel,
s.Remark,
CName = s.CreateUser,
s.CreateDate,
UName = s.ModifiedUser,
s.ModifiedDate
});
var sql = query.ToSql();
var list2 = query.ToList();
if (!list1.Any())
{
flag1 = false;
}
if (!list2.Any())
{
flag2 = false;
}
var html = FileUtil.ReadFileFromPath(Path.Combine(_env.WebRootPath, "upload", "StockIn.html"));
return((flag1, list1, flag2, list2, html).JilToJson());
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().Select <object>("*").ToList();
var getAll2 = db.Queryable <Student>().Select(it => it.Name.Substring(0, 4)).ToList();
var getAll22 = db.Queryable <Student>().ToDataTable();
var getAll222 = db.Queryable <Student>().ToJson();
var getAll22222 = db.Queryable <Student>().ToArray();
var getAll2222 = db.Queryable <Student>().OrderBy(it => it.Name.Length).ToJson();
var getAll3 = db.Queryable <Student>().OrderBy(it => new { it.Id, it.Name }).GroupBy(it => new { it.Id, it.Name }).Select <object>("id").ToList();
var getRandomList = db.Queryable <Student>().OrderBy(it => SqlFunc.GetRandom()).ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Student>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Select(it => it.SchoolId).ToList();
var sum2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Sum((st, sc) => sc.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var count = db.Queryable <Student>().Count(it => it.Id > 0);
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var joinSql = db.Queryable("student", "s").OrderBy("id").Select("id,name").ToPageList(1, 2);
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var getSqlList = db.Queryable <Student>().AS("(select * from student) t").ToList();
var getUnionAllList = db.UnionAll(db.Queryable <Student>().Where(it => it.Id == 1), db.Queryable <Student>().Where(it => it.Id == 2)).ToList();
var getUnionAllList2 = db.UnionAll(db.Queryable <Student>(), db.Queryable <Student>()).ToList();
var getUnionAllList3 = db.UnionAll(db.Queryable <Student>()
.Select(it => new Student {
Id = SqlFunc.ToInt32(1), Name = SqlFunc.ToString("2"), SchoolId = Convert.ToInt32(3)
})
, db.Queryable <Student>()
.Select(it => new Student {
Id = SqlFunc.ToInt32(11), Name = SqlFunc.ToString("22"), SchoolId = Convert.ToInt32(33)
}))
.Select(it => new Student()
{
Id = SqlFunc.ToInt32(111), Name = SqlFunc.ToString("222")
}).ToList();
var test1 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Where(st => st.CreateTime > SqlFunc.GetDate()).Select((st, sc) => SqlFunc.ToInt64(sc.Id)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
var test3 = db.Queryable <DataTestInfo2>().Select(it => it.Bool1).ToSql();
var test4 = db.Queryable <DataTestInfo2>().Select(it => new { b = it.Bool1 }).ToSql();
DateTime?result = DateTime.Now;
var test5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToList();
var test6 = db.Queryable <DataTestInfo2>().Where(it => SqlFunc.HasValue(it.Bool2) == true && SqlFunc.HasValue(it.Bool2) == true).ToList();
var test7 = db.Queryable <DataTestInfo2>().Where(it => SqlFunc.HasValue(it.Bool1) && SqlFunc.HasValue(it.Bool1)).ToList();
var test8 = db.Queryable <Student>().Where(it => SqlFunc.HasValue(it.SchoolId) && SqlFunc.HasValue(it.SchoolId)).ToList();
bool?b = false;
var test9 = db.Queryable <DataTestInfo2>().Where(it => it.Bool1 == b).ToList();
var test10 = db.Queryable <Student>(db.Queryable <Student>().Select(it => new Student()
{
Name = it.Name.Substring(0, 1)
})).GroupBy(it => it.Name).ToList();;
var test11 = db.Queryable <Student>().Distinct().ToList();
var test12 = db.Queryable <Student>().Distinct().Select(it => new Student {
Name = it.Name
}).ToList();
var test13 = db.Queryable <Student>().Where(it => DateTime.Parse("2014-1-1") == DateTime.Now).Where(it => Boolean.Parse("true") == true).ToList();
var test14 = db.Queryable <DataTestInfo2>().Where(it => Convert.ToBoolean(it.Bool1)).ToList();
var test15 = db.Queryable <DataTestInfo2>().Where(it => it.Bool2.Value && it.Bool1).ToList();
var test16 = db.Queryable <DataTestInfo2>().Where(it => !it.Bool2.Value && !it.Bool1).ToList();
var test17 = db.Queryable <DataTestInfo2>().Where(it => it.Bool1 && it.Bool1).ToList();
var test18 = db.Queryable <Student>().Where(it => it.SchoolId.HasValue && it.SchoolId.HasValue).ToList();
var test19 = db.Queryable <Student>().Where(it => it.SchoolId.HasValue && it.SchoolId.HasValue && it.SchoolId.HasValue).ToList();
var test20 = db.Queryable <Student>().Where(it => it.SchoolId.HasValue && SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var test21 = db.Queryable <Student>().Where(it => !it.SchoolId.HasValue && it.Name == "").ToList();
var test22 = db.Queryable <Student>().Where(it => !it.SchoolId.HasValue && it.SchoolId.HasValue).ToList();
var test23 = db.Queryable <Student>().Where(it => !(it.Id == 1) && it.Name == "").ToList();
var test24 = db.Queryable <Student>().Where(it => string.IsNullOrEmpty("a")).Where(it => string.IsNullOrEmpty(it.Name)).ToList();
var test25 = db.Queryable <Student>().Where(it => SqlFunc.IIF(it.Id == 0, 1, 2) == 1).ToList();
var test26 = db.Queryable <Student>().Where(it => (it.Name == null?2:3) == 1)
.ToList();
var test27 = db.Queryable <Student>().Select(x => new {
name = x.Name == null?"1":"2"
}).ToList();
var test28 = db.Queryable <Student>().Select(x => new Student {
Name = x.Name == null ? "1" : "2"
}).ToList();
var test29 = db.Queryable <Student>().Where(it => it.Id % 1 == 0).ToList();
var test30 = db.Queryable <Student>().Select(x => new Student
{
Name = x.Name ?? "a"
}).ToList();
var test31 = db.Queryable <Student>().Where(it => (it.Name ?? "a") == "a").ToList();
var test32 = db.Queryable <Student>().Where(it => it.Name == null ? true : false).ToList();
var test33 = db.Queryable <Student>().Where(it => SqlFunc.IIF(it.Name == null, true, false)).ToList();
var test34 = db.Queryable <Student>().Where(it => SqlFunc.IIF(it.Name == null || 1 == 1, true, false)).ToList();
var test35 = db.Queryable <Student>().Where(it => it.Id == 1 && SqlFunc.IF(it.Id == 1).Return(true).End(false)).ToList();
var test36 = db.Queryable <Student>().Where(it => it.Id == 1 && it.SchoolId.HasValue).ToList();
var test37 = db.Queryable <Student>().Where(it => it.Id == 1 && SqlFunc.IIF(it.Id == 1, true, false)).ToList();
var test38 = db.Queryable <Student>().Where(it => it.Id == 1 && SqlFunc.IIF(it.Id == 1, true, false) == true).ToList();
var test39 = db.Queryable <Student>().Where(it => it.Id == 1 && (it.Id == 1?true:false)).ToList();
var test40 = db.Queryable <Student>().Where(it => it.Id == 1 && Convert.ToBoolean("true")).ToList();
var test41 = db.Queryable <Student>().Where(it => it.Id == ((it.Id == 1?2:3) == 2?1:2)).ToList();
var test42 = db.Queryable <Student>().Where(it => new int[] { 1, 2, 3 }.Contains(1)).ToList();
var test43 = db.Queryable <Student>().Where(it => new int[] { 1, 2, 3 }.Contains(it.Id)).ToList();
var test44 = db.Queryable <Student>().Select(it => new {
x = SqlFunc.Subqueryable <DataTestInfo>().Where(x => false).Sum(x => x.Decimal1)
}).ToList();
decimal?p = null;
var test45 = db.Queryable <DataTestInfo>().Select(it => new {
x = p
}).ToList();
var test46 = db.Queryable <Student>().Where(it => it.CreateTime > SqlFunc.ToDate(DateTime.Now.Date)).ToList();
var test47 = db.Queryable <Student>().Where(it => string.IsNullOrEmpty(it.Name) == true).ToList();
var test48 = db.Queryable <Student>().Where(it => it.CreateTime != null).Where(it => SqlFunc.ToDate(it.CreateTime).Date == DateTime.Now.Date).ToList();
var test49 = db.Queryable <Student>().Where(it => it.CreateTime != null).Where(it => SqlFunc.ToDate(it.CreateTime).Year == DateTime.Now.Year).ToList();
var test50 = db.Queryable <Student>().Where(it => it.CreateTime != null).Where(it => SqlFunc.ToDate(it.CreateTime).Year == SqlFunc.GetDate().Year).ToList();
var test51 = db.Queryable <Student>().Select(it => new { x = SqlFunc.ToDate(it.CreateTime).Year + "-" }).ToList();
var test52 = db.Queryable <Student>().Select(it => SqlFunc.IsNull(it.CreateTime, SqlFunc.GetDate())).ToList();
var test53 = db.Queryable <Student>().Select(it => SqlFunc.IsNull(it.CreateTime, SqlFunc.GetDate())).First();
var test54 = db.Queryable <Student>().Where(it => it.CreateTime == test52.First().Value).ToList();
var test55 = db.Queryable <Student>().Select(it => new {
isAny = SqlFunc.Subqueryable <School>().Any()?1:2
}).ToList();
var test56 = db.Queryable <Student>().Select(it => new {
isAny = SqlFunc.Subqueryable <Student>().Any(),
isAny2 = SqlFunc.Subqueryable <Student>().Where(s => false).Any()
}).ToList();
var totalPage = 0;
var total = 0;
db.Queryable <Student>().ToPageList(1, 2, ref total, ref totalPage);
}
public static void Init()
{
var db = GetInstance();
var updateObj = new Student()
{
Id = 1, Name = "jack", SchoolId = 0, CreateTime = Convert.ToDateTime("2017-05-21 09:56:12.610")
};
var updateObjs = new List <Student>()
{
updateObj, new Student()
{
Id = 2, Name = "sun", SchoolId = 0
}
}.ToArray();
db.IgnoreColumns.Add("TestId", "Student");
//db.MappingColumns.Add("id","dbid", "Student");
//update reutrn Update Count
var t1 = db.Updateable(updateObj).ExecuteCommand();
//Only update Name
var t3 = db.Updateable(updateObj).UpdateColumns(it => new { it.Name }).ExecuteCommand();
var t3_1 = db.Updateable(updateObj).UpdateColumns(it => it == "Name").ExecuteCommand();
//Ignore Name and TestId
var t4 = db.Updateable(updateObj).IgnoreColumns(it => new { it.Name, it.TestId }).ExecuteCommand();
//Ignore Name and TestId
var t5 = db.Updateable(updateObj).IgnoreColumns(it => it == "Name" || it == "TestId").With(SqlWith.UpdLock).ExecuteCommand();
//Use Lock
var t6 = db.Updateable(updateObj).With(SqlWith.UpdLock).ExecuteCommand();
//update List<T>
var t7 = db.Updateable(updateObjs).ExecuteCommand();
//Re Set Value
var t8 = db.Updateable(updateObj)
.ReSetValue(it => it.Name == (it.Name + 1)).ExecuteCommand();
//Where By Expression
var t9 = db.Updateable(updateObj).Where(it => it.Id == 1).ExecuteCommand();
//Update By Expression Where By Expression
var t10 = db.Updateable <Student>()
.UpdateColumns(it => new Student()
{
Name = "a", CreateTime = DateTime.Now
})
.Where(it => it.Id == 11).ExecuteCommand();
//Rename
db.Updateable <School>().AS("Student").UpdateColumns(it => new School()
{
Name = "jack"
}).Where(it => it.Id == 1).ExecuteCommand();
//Update Student set Name='jack' Where Id=1
//Column is null no update
db.Updateable(updateObj).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
//sql
db.Updateable(updateObj).Where("id=@x", new { x = "1" }).ExecuteCommand();
db.Updateable(updateObj).Where("id", "=", 1).ExecuteCommand();
var t12 = db.Updateable <School>().AS("Student").UpdateColumns(it => new School()
{
Name = "jack"
}).Where(it => it.Id == 1).ExecuteCommandAsync();
t12.Wait();
//update one columns
var count = db.Updateable <Student>().UpdateColumns(it => it.SchoolId == it.SchoolId).Where(it => it.Id == it.Id + 1).ExecuteCommand();
var count1 = db.Updateable <Student>()
.UpdateColumnsIF(false, it => it.SchoolId == it.SchoolId) //ignore
.UpdateColumnsIF(true, it => it.SchoolId == 2). //ok
Where(it => it.Id == it.Id + 1).ExecuteCommand();
//update one columns
var count2 = db.Updateable <Student>().UpdateColumns(it => it.SchoolId == it.SchoolId + 1).Where(it => it.Id == it.Id + 1).ExecuteCommand();
var dt = new Dictionary <string, object>();
dt.Add("id", 1);
dt.Add("name", null);
dt.Add("createTime", DateTime.Now);
var t66 = db.Updateable(dt).AS("student").WhereColumns("id").With(SqlWith.UpdLock).ExecuteCommand();
var dt2 = new Dictionary <string, object>();
dt2.Add("id", 2);
dt2.Add("name", null);
dt2.Add("createTime", DateTime.Now);
var dtList = new List <Dictionary <string, object> >();
dtList.Add(dt);
dtList.Add(dt2);
var t666 = db.Updateable(dtList).AS("student").WhereColumns("id").With(SqlWith.UpdLock).ExecuteCommand();
var t20 = db.Updateable <Student>().UpdateColumns(p => new Student()
{
SchoolId = SqlFunc.IIF(p.Id == 1, 2, 3)
}).Where(p => p.Id == 10000).ExecuteCommand();
var t21 = db.Updateable <Student>().UpdateColumns(p => new Student()
{
SchoolId = SqlFunc.IF(p.Id == 1).Return(1).End(p.Id)
}).Where(p => p.Id == 10000).ExecuteCommand();
var t22 = db.Updateable <Student>().UpdateColumns(p => new Student()
{
SchoolId = SqlFunc.Subqueryable <Student>().Where(s => s.SchoolId == p.Id).Select(s => s.Id)
}).Where(p => p.Id == 10000).ExecuteCommand();
var t23 = db.Updateable <Student>(new Student()
{
})
.Where(p => p.SchoolId == SqlFunc.Subqueryable <Student>().Where(s => s.SchoolId == p.Id).Select(s => s.Id)).ExecuteCommand();
var t24 = db.Updateable(new Student()
{
}).WhereColumns(it => it.CreateTime).ExecuteCommand();
var t25 = db.Updateable(new Student()
{
}).UpdateColumns(it => new { it.Name, it.CreateTime }).WhereColumns(it => it.CreateTime).ExecuteCommand();
var t26 = db.Updateable(new List <Student>()
{
new Student()
{
}, new Student()
{
}
}).UpdateColumns(it => new { it.Name, it.CreateTime }).WhereColumns(it => it.CreateTime).ExecuteCommand();
db.Updateable <Student>().UpdateColumns(it => new Student {
SchoolId = GeneratePassword(2, 1), Name = SqlFunc.ToString(it.Name), CreateTime = DateTime.Now.AddDays(1)
}).Where(it => it.Id == 1).ExecuteCommand();
}
public IActionResult PreviewJson(long id, long?boxId)
{
var list1 = _client.Queryable <Wms_stockout>()
.Where((s) => s.StockOutId == id && s.IsDel == DeleteFlag.Normal)
.Select((s) => new
{
StockOutId = s.StockOutId.ToString(),
StockOutType = s.StockOutTypeName,
StockOutTypeId = s.StockOutType.ToString(),
s.StockOutStatus,
s.StockOutNo,
s.OrderNo,
s.WorkNo,
s.WorkStationId,
s.WorkAreaName,
s.IsDel,
s.Remark,
CName = s.CreateUser,
s.CreateDate,
UName = s.ModifiedUser,
s.ModifiedDate
}).ToList();
bool flag1 = true;
bool flag2 = true;
if (boxId == null)
{
return(Content((flag1, list1, false, new object[1] {
new object()
}).JilToJson()));
}
var list2 = _client.Queryable <Wms_stockoutdetail_box, Wms_stockoutdetail>((sodb, sod) => new object[] {
JoinType.Left, sodb.StockOutDetailId == sod.StockOutDetailId,
})
.Where((sodb, sod) => sodb.DetailBoxId == boxId.Value && sod.IsDel == DeleteFlag.Normal)
.Select((sodb, sod) => new
{
BoxId = sodb.DetailBoxId.ToString(),
StockOutId = sod.StockOutId.ToString(),
StockOutDetailId = sod.StockOutDetailId.ToString(),
sod.SubWarehouseEntryId,
sod.UniqueIndex,
sodb.StockInUniqueIndex,
sod.MaterialNo,
sod.MaterialName,
Status = SqlFunc.IF(sod.Status == 1).Return(StockOutStatus.initial.GetDescription())
.ElseIF(sod.Status == 2).Return(StockOutStatus.task_confirm.GetDescription())
.ElseIF(sod.Status == 3).Return(StockOutStatus.task_canceled.GetDescription())
.ElseIF(sod.Status == 3).Return(StockOutStatus.task_working.GetDescription())
.End(StockOutStatus.task_finish.GetDescription()),
sod.PlanOutQty,
sod.ActOutQty,
sodb.Qty,
sod.IsDel,
sod.Remark,
CName = sod.CreateUser,
sod.CreateDate,
UName = sod.ModifiedUser,
sod.ModifiedDate
})
.ToList();
if (!list1.Any())
{
flag1 = false;
}
if (!list2.Any())
{
flag2 = false;
}
return(Content((flag1, list1, flag2, list2).JilToJson()));
}
public string PrintList(string InventorymoveId)
{
var list1 = _client.Queryable <Wms_inventorymove, Wms_storagerack, Wms_storagerack, Sys_user, Sys_user>
((s, p, d, c, u) => new object[] {
JoinType.Left, s.SourceStoragerackId == p.StorageRackId,
JoinType.Left, s.AimStoragerackId == p.StorageRackId,
JoinType.Left, s.CreateBy == c.UserId,
JoinType.Left, s.ModifiedBy == u.UserId
})
.Where((s, p, d, c, u) => s.IsDel == 1)
.Select((s, p, d, c, u) => new
{
InventorymoveId = s.InventorymoveId.ToString(),
s.Status,
s.InventorymoveNo,
SourceStoragerackId = s.SourceStoragerackId.ToString(),
SourceStoragerackNo = p.StorageRackNo.ToString(),
SourceStoragerackName = p.StorageRackName.ToString(),
AimStoragerackId = s.AimStoragerackId.ToString(),
AimStoragerackNo = d.StorageRackNo.ToString(),
AimStoragerackName = d.StorageRackName.ToString(),
s.IsDel,
s.Remark,
CName = c.UserNickname,
s.CreateDate,
UName = u.UserNickname,
s.ModifiedDate
}).MergeTable().Where(s => s.InventorymoveId == InventorymoveId).ToList();
bool flag1 = true;
bool flag2 = true;
var list2 = _client.Queryable <Wms_invmovedetail, Wms_material, Wms_inventorymove, Sys_user, Sys_user, Sys_user>
((s, m, p, c, u, a) => new object[] {
JoinType.Left, s.MaterialId == m.MaterialId,
JoinType.Left, s.InventorymoveId == p.InventorymoveId,
JoinType.Left, s.CreateBy == c.UserId,
JoinType.Left, s.ModifiedBy == u.UserId,
JoinType.Left, s.AuditinId == a.UserId,
})
.Where((s, m, p, c, u, a) => s.IsDel == 1)
.Select((s, m, p, c, u, a) => new
{
InventorymoveId = s.InventorymoveId.ToString(),
MoveDetailId = s.MoveDetailId.ToString(),
m.MaterialNo,
m.MaterialName,
Status = SqlFunc.IF(s.Status == 1).Return(StockInStatus.initial.GetDescription())
.ElseIF(s.Status == 2).Return(StockInStatus.task_confirm.GetDescription())
.ElseIF(s.Status == 3).Return(StockInStatus.task_canceled.GetDescription())
.ElseIF(s.Status == 3).Return(StockInStatus.task_working.GetDescription())
.End(StockInStatus.task_finish.GetDescription()),
s.PlanQty,
s.ActQty,
s.IsDel,
s.Remark,
s.AuditinTime,
AName = a.UserNickname,
CName = c.UserNickname,
s.CreateDate,
UName = u.UserNickname,
s.ModifiedDate
}).MergeTable().Where(c => c.InventorymoveId == InventorymoveId).OrderBy(c => c.CreateDate, OrderByType.Desc).ToList();
if (!list1.Any())
{
flag1 = false;
}
if (!list2.Any())
{
flag2 = false;
}
var html = FileUtil.ReadFileFromPath(Path.Combine(_env.WebRootPath, "upload", "InvMove.html"));
return((flag1, list1, flag2, list2, html).JilToJson());
}
