public static void Easy() { var db = GetInstance(); var getAll = db.Queryable <Student>().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>().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 isAny2 = db.Queryable <Student>().Any(it => it.Id == -1); 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(); }
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(); }
public long getLovedCount(string openId) { SqlSugarClient db = SqlSugarInstance.newInstance(); string[] tpList = db.Queryable <tbl_posts>().Where(o => o.postsMaker == openId).Select(o => o.postsID).ToList().ToArray(); long postsLoved = db.Queryable <tbl_userloved>().Where(o => SqlFunc.ContainsArray <string>(tpList, o.postsID)).Count(); return(postsLoved); }
/// <summary> /// 根据商户号获取数据 /// </summary> /// <param name="mctNum">商户号</param> /// <param name="storeIds">门店编号集合</param> /// <param name="cusId"></param> /// <returns></returns> public List <Cus_Customer> GetListByCusId(string mctNum, string storeIds, string[] cusId) { using (SqlSugarClient db = MySqlHelper.GetInstance()) { return(db.Queryable <Cus_Customer, Cus_StaffCustomer>((s, s1) => new object[] { JoinType.Left, s.Id == s1.CustomerId }) .Where((s, s1) => s.MctNum == mctNum && s.IsDelete == false && storeIds.Contains(s.StoreId)) .Where((s, s1) => SqlFunc.ContainsArray(cusId, s.Id)) .ToList()); } }
private void ContainsArray() { string[] array = new string[] { "1", "2" }; Expression <Func <Student, bool> > exp = it => SqlFunc.ContainsArray(array, it.Name); SqlServerExpressionContext expContext = new SqlServerExpressionContext(); expContext.Resolve(exp, ResolveExpressType.WhereSingle); var value = expContext.Result.GetString(); var pars = expContext.Parameters; base.Check(value, null, " ([Name] IN ('1','2')) ", null, "Contains2 error"); }
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(); }
/// <summary> /// 按区域拣货 /// </summary> /// <param name="S"></param> /// <param name="areaCode"></param> /// <returns></returns> public pmw_order RegionalPicking(String[] shopNameArray, string site, string areaCode) { return(Common.Config.StartSqlSugar <pmw_order>((db) => { return db.Queryable <pmw_order, pmw_billcode, pmw_wavehouse, pmw_member>((a, b, c, d) => new object[] { JoinType.Left, a.order_code == b.order_code, JoinType.Left, b.stock_area == c.wavehouse_place_name, JoinType.Left, a.member_id == d.id }) .Where((a, b, c, d) => SqlFunc.IsNullToInt(a.DoubleCheck) == 1 && SqlFunc.IsNullToInt(a.is_payed) == 1 && SqlFunc.IsNullToInt(a.is_outplace) == 0 && SqlFunc.IsNullToInt(a.Abnormal) == 0 && SqlFunc.IsNullToInt(a.is_task) == 0 && SqlFunc.HasValue(a.order_code)) .Where((a, b, c, d) => SqlFunc.IsNullToInt(b.is_outplace) == 0 && SqlFunc.IsNullToInt(b.is_inplace) == 1 && b.wavehouse == site) .Where((a, b, c, d) => c.wavehouse_bigarea_name == areaCode) .Where((a, b, c, d) => SqlFunc.ContainsArray(shopNameArray, d.astro)) .First(); })); }
public JsonResult LoadData(string account, string sn, int?countryId, int?status, GridPager pager) { List <MerchantProfile> profileList; List <MerchantProfileAccountViewModel> merchantPos; string countryName; if (string.IsNullOrEmpty(sn)) { if (!countryId.HasValue) { return(Json(new List <MerchantProfile>().ToGridJson(pager))); } profileList = new MerchantProfileBLL().GetMerchantKYCProfilePageListL1(account, countryId.Value, status, ref pager); if (profileList == null || profileList.Count <= 0) { return(Json(new List <MerchantProfile>().ToGridJson(pager))); } var merchantIds = profileList.Select(t => t.MerchantId).ToArray(); countryName = FoundationDB.CountryDb.GetById(countryId)?.Name; merchantPos = FiiiPayDB.DB.Queryable <MerchantAccounts, POSs>((u, v) => new object[] { JoinType.Left, u.POSId == v.Id }) .Where((u, v) => SqlFunc.ContainsArray <Guid>(merchantIds, u.Id)) .Select((u, v) => new MerchantProfileAccountViewModel { MerchantId = u.Id, Cellphone = u.Cellphone, MerchantName = u.MerchantName, SN = v.Sn, Username = u.Username }).ToList(); } else { var pos = FiiiPayDB.POSDb.GetSingle(t => t.Sn == sn); if (pos == null) { return(Json(new List <MerchantProfile>().ToGridJson(pager))); } var merchantAccount = FiiiPayDB.MerchantAccountDb.GetSingle(t => t.POSId == pos.Id); if (merchantAccount == null) { return(Json(new List <MerchantProfile>().ToGridJson(pager))); } var profile = new MerchantProfileBLL().GetMerchantProfile(merchantAccount.Id); if (profile == null) { return(Json(new List <MerchantProfile>().ToGridJson(pager))); } profileList = new List <MerchantProfile> { profile }; profileList = profileList.Where(t => (account == null ? true : t.Cellphone.Contains(account)) && (countryId.HasValue ? t.Country == countryId.Value : true) && (status.HasValue ? (byte)t.L1VerifyStatus == status.Value : true) ).ToList(); countryName = FoundationDB.CountryDb.GetById(merchantAccount.CountryId)?.Name; merchantPos = new List <MerchantProfileAccountViewModel> { new MerchantProfileAccountViewModel { MerchantId = merchantAccount.Id, MerchantName = merchantAccount.MerchantName, Cellphone = merchantAccount.Cellphone, Username = merchantAccount.Username, SN = pos.Sn } }; } var data = profileList.ToGridJson(pager, r => new { id = r.MerchantId, cell = new { MerchantId = r.MerchantId, Username = merchantPos.Where(t => t.MerchantId == r.MerchantId).Select(t => t.Username).FirstOrDefault(), Cellphone = r.Cellphone, SN = merchantPos.Where(t => t.MerchantId == r.MerchantId).Select(t => t.SN).FirstOrDefault(), MerchantName = merchantPos.Where(t => t.MerchantId == r.MerchantId).Select(t => t.MerchantName).FirstOrDefault(), FirstName = r.FirstName, LastName = r.LastName, CountryName = countryName, L1VerifyStatus = r.L1VerifyStatus.ToString(), L1SubmissionDate = r.L1SubmissionDate.HasValue ? r.L1SubmissionDate.Value.ToLocalTime().ToString("yyyy-MM-dd HH:mm") : "", Remark = r.L1Remark, Options = (int)r.L1VerifyStatus } }); return(Json(data)); }
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); }
/// <summary> /// 根据指定列 Not In (!Contain)查询 /// </summary> /// <typeparam name="S">指定列类型</typeparam> /// <param name="list">Not In的结果集</param> /// <param name="field">指定列</param> /// <returns></returns> public async Task <List <T> > GetByNotIn_NID <S>(List <S> list, object field, bool isOrderBy = false, Expression <Func <T, object> > orderBy = null, OrderByType orderByType = OrderByType.Asc) { return(await _dbBase.Queryable <T>().Where(t => !SqlFunc.ContainsArray(list, field)).OrderByIF(isOrderBy, orderBy, orderByType).ToListAsync()); }
/// <summary> /// 记录打单数据 /// </summary> /// <param name="orderInfo"></param> /// <param name="orderSentBillCode"></param> /// <param name="orderSentCompany"></param> /// <param name="recipients"></param> /// <param name="houserInfo"></param> /// <param name="tbInfo"></param> /// <param name="forwarderInfo"></param> /// <param name="billCoderList"></param> /// <param name="goodsName"></param> /// <param name="S"></param> /// <param name="printNo"></param> /// <returns></returns> public bool Print(decimal collectingMoney, pmw_order orderInfo, string orderSentBillCode, string orderSentCompany, string recipients, pmw_house houserInfo, TaoBaoInfo tbInfo, Forwarder forwarderInfo, string[] billCoderList, string goodsName, Model.M_Print.Request S, string printNo, double weightBillcode) { return(Common.Config.StartSqlSugar <bool>((db) => { return db.Ado.UseTran(() => { db.Insertable <pmw_Print>(new pmw_Print { address = orderInfo.address, orderID = orderInfo.id, operateMan = S.operateMan, operateSiteID = SqlFunc.ToInt32(S.operateSite), operateTime = DateTime.Now, WaybillNo = printNo, z_weight = SqlFunc.ToDecimal(weightBillcode) }).ExecuteCommand(); db.Insertable <pmw_track>(new pmw_track { billcode = printNo, scan_time = DateTime.Now, scan_type = "拣货完成", scan_memo = "拣货完毕,快件已拣货完毕,正在打包", scan_site = houserInfo.house_name, scan_emp = S.operateMan }).ExecuteCommand(); db.Updateable <pmw_billcode>(new { is_packed = 1, packed_time = DateTime.Now, packed_emp = S.operateMan, packed_billcode = printNo, packed_code = forwarderInfo.ForwarderCode, packed_kd_com = forwarderInfo.ForwarderName, printID = forwarderInfo.id, is_Big = S.is_Big }) .Where(a => SqlFunc.ContainsArray(billCoderList, a.kd_billcode) && a.order_code == orderInfo.order_code && (SqlFunc.IsNullToInt(a.is_lock) == 0 || SqlFunc.IsNullToInt(a.is_lock) == 2)) .ExecuteCommand(); db.Updateable <pmw_order>(new { is_sented = 1, sent_emp = S.operateMan, sent_time = DateTime.Now, sent_kd_billcode = orderSentBillCode, sent_kd_com = orderSentCompany }) .Where(a => a.id == orderInfo.id) .ExecuteCommand(); db.Insertable <CFHMPring>(new CFHMPring { CForHM_number = printNo, OrderID = orderInfo.id, freightPayable = collectingMoney, goods = goodsName.ToString(), recipients = recipients, consignee = recipients, recipientsAdd = orderInfo.address, recipientsPhone = orderInfo.mobile, recipientsIDCard = orderInfo.RecipientCode, TurnNumber = billCoderList.Length, Order_Notes = orderInfo.order_memo, consolidator = tbInfo.Name, deliveryCom = forwarderInfo.ForwarderName, deliveryCode = forwarderInfo.ForwarderCode, billNumType = "PDA", CFpacked_time = DateTime.Now, CFpacked_billcode = printNo, CFpacked_code = forwarderInfo.ForwarderCode, CFpacked_emp = S.operateMan, CFpacked_kd_com = forwarderInfo.ForwarderName, KD_billcodeList = string.Join(",", billCoderList), houseID = houserInfo.id, houseName = houserInfo.house_name, netWeight = SqlFunc.ToDecimal(weightBillcode) }).ExecuteCommand(); }).IsSuccess; })); }