//当前类已经继承了 DbContext增、删、查、改的方法 //这里面写的代码不会给覆盖,如果要重新生成请删除 RPT_Group_SettingManager.cs #region 教学方法 /// <summary> /// 如果DbContext中的增删查改方法满足不了你,你可以看下具体用法 /// </summary> public void Study() { /*********查询*********/ var data1 = RPT_Group_SettingDb.GetById(1); //根据ID查询 var data2 = RPT_Group_SettingDb.GetList(); //查询所有 var data3 = RPT_Group_SettingDb.GetList(it => 1 == 1); //根据条件查询 //var data4 = RPT_Group_SettingDb.GetSingle(it => 1 == 1);//根据条件查询一条,如果超过一条会报错 var p = new PageModel() { PageIndex = 1, PageSize = 2 }; // 分页查询 var data5 = RPT_Group_SettingDb.GetPageList(it => 1 == 1, p); Console.Write(p.PageCount); //返回总数 var data6 = RPT_Group_SettingDb.GetPageList(it => 1 == 1, p, it => SqlFunc.GetRandom(), OrderByType.Asc); // 分页查询加排序 Console.Write(p.PageCount); //返回总数 List <IConditionalModel> conModels = new List <IConditionalModel>(); //组装条件查询作为条件实现 分页查询加排序 conModels.Add(new ConditionalModel() { FieldName = typeof(RPT_Group_Setting).GetProperties()[0].Name, ConditionalType = ConditionalType.Equal, FieldValue = "1" }); //id=1 var data7 = RPT_Group_SettingDb.GetPageList(conModels, p, it => SqlFunc.GetRandom(), OrderByType.Asc); RPT_Group_SettingDb.AsQueryable().Where(x => 1 == 1).ToList();//支持了转换成queryable,我们可以用queryable实现复杂功能 //我要用事务 var result = Db.Ado.UseTran(() => { //写事务代码 }); if (result.IsSuccess) { //事务成功 } //多表查询地址 http://www.codeisbug.com/Doc/8/1124 /*********插入*********/ var insertData = new RPT_Group_Setting() { }; //测试参数 var insertArray = new RPT_Group_Setting[] { insertData }; RPT_Group_SettingDb.Insert(insertData); //插入 RPT_Group_SettingDb.InsertRange(insertArray); //批量插入 var id = RPT_Group_SettingDb.InsertReturnIdentity(insertData); //插入返回自增列 RPT_Group_SettingDb.AsInsertable(insertData).ExecuteCommand(); //我们可以转成 Insertable实现复杂插入 /*********更新*********/ var updateData = new RPT_Group_Setting() { }; //测试参数 var updateArray = new RPT_Group_Setting[] { updateData }; //测试参数 RPT_Group_SettingDb.Update(updateData); //根据实体更新 RPT_Group_SettingDb.UpdateRange(updateArray); //批量更新 //RPT_Group_SettingDb.Update(it => new RPT_Group_Setting() { Name = "a", CreateTime = DateTime.Now }, it => it.id==1);// 只更新Name列和CreateTime列,其它列不更新,条件id=1 RPT_Group_SettingDb.AsUpdateable(updateData).ExecuteCommand(); /*********删除*********/ var deldata = new RPT_Group_Setting() { }; //测试参数 RPT_Group_SettingDb.Delete(deldata); //根据实体删除 RPT_Group_SettingDb.DeleteById(1); //根据主键删除 RPT_Group_SettingDb.DeleteById(new int[] { 1, 2 }); //根据主键数组删除 RPT_Group_SettingDb.Delete(it => 1 == 2); //根据条件删除 RPT_Group_SettingDb.AsDeleteable().Where(it => 1 == 2).ExecuteCommand(); //转成Deleteable实现复杂的操作 }
/// <summary> /// 获取单条数据 /// </summary> /// <param name="id"></param> /// <returns></returns> public sys_role GetRoleById(string id) { return(Sqldb.Queryable <sys_role>().Where(s => s.id == SqlFunc.ToInt64(id)).First()); }
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>().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 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 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); }
/// <summary> /// 查询库存剩余数量和销售数量 /// 可根据店铺查询,日期,品牌 /// </summary> /// <returns></returns> public Task <ApiResult <Page <StockSaleNum> > > GetStockNumByShopAsync(PageParm parm, AppSearchParm searchParm) { var res = new ApiResult <Page <StockSaleNum> >(); try { var query = Db.Queryable <ErpShopSku, ErpGoodsSku>((t1, t2) => new object[] { JoinType.Left, t1.SkuGuid == t2.Guid }) .Where((t1, t2) => t1.ShopGuid == parm.guid) .WhereIF(!string.IsNullOrEmpty(searchParm.brand), (t1, t2) => t2.BrankGuid == searchParm.brand) .OrderByIF(parm.orderType == 1, (t1, t2) => t1.Sale, OrderByType.Desc) .Select((t1, t2) => new StockSaleNum() { Guid = t2.Guid, Code = t2.Code, Brand = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.BrankGuid).Select(g => g.Name), Style = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.StyleGuid).Select(g => g.Name), Stock = t1.Sale, returnSum = SqlFunc.Subqueryable <ErpReturnGoods>().Where(g => g.GoodsGuid == t1.SkuGuid && g.ShopGuid == parm.guid).Sum(g => g.ReturnCount) }).ToPage(parm.page, parm.limit); //根据日期查询 var guidList = query.Items.Select(m => m.Guid).ToList(); if (parm.types == 0) { //所有 var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid); foreach (var item in query.Items) { item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts); } } if (parm.types == 1) { DateTime dayTime = Convert.ToDateTime(DateTime.Now.AddDays(1).ToShortDateString() + " 00:00:00"); //本日 var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid && SqlFunc.DateIsSame(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), dayTime)); foreach (var item in query.Items) { item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts); } } if (parm.types == 2) { //本月 DateTime now = DateTime.Now; DateTime d1 = new DateTime(now.Year, now.Month, 1); DateTime d2 = d1.AddMonths(1).AddDays(-1); var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid && SqlFunc.Between(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), d1, d2)); foreach (var item in query.Items) { item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts); } } if (parm.types == 3) { //自定义时间 var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid && SqlFunc.Between(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime))); foreach (var item in query.Items) { item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts); } } res.data = query; } catch (Exception ex) { res.message = ApiEnum.Error.GetEnumText() + ex.Message; res.statusCode = (int)ApiEnum.Error; } return(Task.Run(() => res)); }
///tab1 ///tab1 ///设置搜索数据 private List <CP_TABLE> schData() { UserProc.CheckTime = ""; var exp = Expressionable.Create <CP_TABLE>().And(it => it.status == "2"); if (checkMohu.Checked) { if (chkDanHao.chkSel) { exp.And((b) => b.ganghao.Contains(chkDanHao.txt.Text)); } if (chkKehu.chkSel) { exp.And((b) => b.kehu.Contains(chkKehu.cobodgv.Text)); } if (chkPinmin.chkSel) { exp.And((b) => b.pinming.Contains(chkPinmin.cobodgv.Text)); } if (chkPS.chkSel) { exp.And((b) => b.pishu.Contains(chkPS.txt.Text)); } if (chkCW.chkSel) { exp.And((b) => b.cangwei.Contains(chkCW.txt.Text)); } if (chkRKDH.chkSel) { exp.And((b) => b.rukudanhao.Contains(chkRKDH.txt.Text)); } if (chkDDH.chkSel) { exp.And((b) => b.dingdanhao.Contains(chkDDH.txt.Text)); } if (chkYWY.chkSel) { exp.And((b) => b.yewuyuan.Contains(chkYWY.cobodgv.Text)); } if (chkZL.chkSel) { exp.And((b) => b.zhongliang.Contains(chkZL.txt.Text)); } if (chkJCY.chkSel) { exp.And((b) => b.jincangyuan.Contains(chkJCY.cobodgv.Text)); } } else { if (chkDanHao.chkSel) { exp.And((b) => b.ganghao == (chkDanHao.txt.Text)); } if (chkKehu.chkSel) { exp.And((b) => b.kehu == (chkKehu.cobodgv.Text)); } if (chkPinmin.chkSel) { exp.And((b) => b.pinming == (chkPinmin.cobodgv.Text)); } if (chkPS.chkSel) { exp.And((b) => b.pishu == (chkPS.txt.Text)); } if (chkCW.chkSel) { exp.And((b) => b.cangwei == (chkCW.txt.Text)); } if (chkRKDH.chkSel) { exp.And((b) => b.rukudanhao == (chkRKDH.txt.Text)); } if (chkDDH.chkSel) { exp.And((b) => b.dingdanhao == (chkDDH.txt.Text)); } if (chkYWY.chkSel) { exp.And((b) => b.yewuyuan == (chkYWY.cobodgv.Text)); } if (chkZL.chkSel) { exp.And((b) => b.zhongliang == (chkZL.txt.Text)); } if (chkJCY.chkSel) { exp.And((b) => b.jincangyuan == (chkJCY.cobodgv.Text)); } } DateTime dateTime; string str1 = "1970-01-01 00:00:00", str2 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); if (this.chkDtime1.chkFsel) { dateTime = chkDtime1.dtimeF.Value; str1 = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); } if (this.chkDtime1.chkTsel) { dateTime = chkDtime1.dtimeT.Value; str2 = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); exp.And((b) => SqlFunc.Between(b.riqi, str1, str2)); UserProc.CheckTime = str1 + " - " + str2; } var getAll1 = db.Queryable <CP_TABLE>() .Where(exp.ToExpression()) .OrderBy((b) => b.riqi, OrderByType.Desc) .ToList(); return(getAll1); }
/// <summary> /// table1 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> /// private List <BaseIList> schData() { UserProc.CheckTime = ""; var exp = Expressionable.Create <BaseIList>().And(it => it.leibie == benleibie); if (checkMohu.Checked) { if (chkGH.chkSel) { exp.And((b) => b.item0.Contains(chkGH.txt.Text)); } if (chkKehu.chkSel) { exp.And((b) => b.kehu.Contains(chkKehu.cobodgv.Text)); } if (chkPinmin.chkSel) { exp.And((b) => b.pingmin.Contains(chkPinmin.cobodgv.Text)); } if (chkZL.chkSel) { exp.And((b) => b.zongliang.Contains(chkZL.txt.Text)); } if (chkPS.chkSel) { exp.And((b) => b.pishu.Contains(chkPS.txt.Text)); } if (chkSH.chkSel) { exp.And((b) => b.sehao.Contains(chkSH.cobodgv.Text)); } if (chkSM.chkSel) { exp.And((b) => b.seming.Contains(chkSM.cobodgv.Text)); } if (chkDDH.chkSel) { exp.And((b) => b.dingdanhao.Contains(chkDDH.txt.Text)); } if (chkMF.chkSel) { exp.And((b) => b.menfu.Contains(chkMF.txt.Text)); } if (chkKZ.chkSel) { exp.And((b) => b.kezong.Contains(chkKZ.txt.Text)); } } else { if (chkGH.chkSel) { exp.And((b) => b.item0 == (chkGH.txt.Text)); } if (chkKehu.chkSel) { exp.And((b) => b.kehu == (chkKehu.cobodgv.Text)); } if (chkPinmin.chkSel) { exp.And((b) => b.pingmin == (chkPinmin.cobodgv.Text)); } if (chkZL.chkSel) { exp.And((b) => b.zongliang == (chkZL.txt.Text)); } if (chkPS.chkSel) { exp.And((b) => b.pishu == (chkPS.txt.Text)); } if (chkSH.chkSel) { exp.And((b) => b.sehao == (chkSH.cobodgv.Text)); } if (chkSM.chkSel) { exp.And((b) => b.seming == (chkSM.cobodgv.Text)); } if (chkDDH.chkSel) { exp.And((b) => b.dingdanhao == (chkDDH.txt.Text)); } if (chkMF.chkSel) { exp.And((b) => b.menfu == (chkMF.txt.Text)); } if (chkKZ.chkSel) { exp.And((b) => b.kezong == (chkKZ.txt.Text)); } } DateTime dateTime; string str1 = "1970-01-01 00:00:00", str2 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); if (this.chkDtime1.chkFsel) { dateTime = chkDtime1.dtimeF.Value; str1 = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); } if (this.chkDtime1.chkTsel) { dateTime = chkDtime1.dtimeT.Value; str2 = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); exp.And((b) => SqlFunc.Between(b.riqi, str1, str2)); UserProc.CheckTime = str1 + " - " + str2; } var getAll1 = db.Queryable <BaseIList>() .Where(exp.ToExpression()) .OrderBy((b) => b.SN, OrderByType.Desc) .ToList(); return(getAll1); }
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)); }
/// <summary> /// 前台右侧内容 /// </summary> /// <returns></returns> public async Task <ApiResult <PageRightDto> > GetRgithData() { var res = new ApiResult <PageRightDto>() { statusCode = (int)ApiEnum.Error }; try { var m = new PageRightDto() { }; //问题总数 m.QuestionCount = await Db.Queryable <Bbs_Questions>().CountAsync(); //会员总数 m.UserCount = await Db.Queryable <Member>().CountAsync(); //常用标签 m.TagList = await Db.Queryable <Bbs_Tags>() .Where(s => !s.IsDel) .Select(s => new TagsCount() { TagName = s.TagName, EnTagName = s.EnTagName, TagCount = SqlFunc.Subqueryable <Bbs_Questions>().Where(g => g.Tags.Contains(s.TagName)).Count() }).OrderBy(g => g.TagCount, OrderByType.Desc) .Take(8).ToListAsync(); //热门话题 m.RedQuestionList = await Db.Queryable <Bbs_Questions, Member>((q, u) => new JoinQueryInfos( JoinType.Inner, q.UserGuid == u.Guid )).Where((q, u) => q.IsRed) .OrderBy((q, u) => q.AnswerSum, OrderByType.Desc) .Select((q, u) => new Bbs_Questions() { Guid = q.Guid, EnTitle = q.EnTitle, HeadPic = u.HeadPic, Title = q.Title }) .Take(6).ToListAsync(); //推荐专家 m.ExpertList = await Db.Queryable <Member>() .Where(u => !u.IsDel && u.Grade == "1fc19833-539a-40cf-a000-1f9037b8dae4") .Select(u => new MemberQuestion() { Guid = u.Guid, NickName = u.NickName, HeadPic = u.HeadPic, AnswerCount = SqlFunc.Subqueryable <Bbs_Answer>().Where(g => g.UserGuid == u.Guid).Count(), AdoptCount = SqlFunc.Subqueryable <Bbs_Answer>().Where(g => g.UserGuid == u.Guid && g.IsAdopt).Count() }) .OrderBy(g => g.AnswerCount, OrderByType.Desc) .Take(6).ToListAsync(); res.data = m; res.statusCode = (int)ApiEnum.Status; } catch (Exception ex) { res.message = ex.Message; } return(res); }
public static List <UserInComesHis> GetInComesHisBySome(string useraddress, DateTime?begin, DateTime?end, int pagesize, int pageindex, ref int totalCount) { if (begin == null) { begin = DateTime.MinValue; } if (end == null) { end = DateTime.MaxValue; } totalCount = 0; return(SugarBase.DBAutoClose.Queryable <UserInComesHis>().Where(x => (x.UserAddress == useraddress || string.IsNullOrWhiteSpace(useraddress)) && SqlFunc.Between(x.SetTime, begin, end)).OrderBy(x => x.SetTime, OrderByType.Desc).ToPageList(pageindex, pagesize, ref totalCount)); }
public static decimal GetInComesSum(string useraddress, DateTime?begin, DateTime?end) { if (begin == null) { begin = DateTime.MinValue; } if (end == null) { end = DateTime.MaxValue; } return(SugarBase.DBAutoClose.Queryable <UserInComesHis>().Where(x => (x.UserAddress == useraddress || string.IsNullOrWhiteSpace(useraddress)) && SqlFunc.Between(x.SetTime, begin, end)).Sum(x => x.GetCoins)); }
private static void Subquery() { Console.WriteLine(""); Console.WriteLine("#### Subquery Start ####"); var db = GetInstance(); var list = db.Queryable <Order>().Take(10).Select(it => new { customName = SqlFunc.Subqueryable <Custom>().Where("it.CustomId=id").Select(s => s.Name), customName2 = SqlFunc.Subqueryable <Custom>().Where("it.CustomId = id").Where(s => true).Select(s => s.Name) }).ToList(); var list1 = db.Queryable <Order>().Select(it => new { id = SqlFunc.Subqueryable <Custom>().Where(s => s.Id == 1).Sum(s => s.Id) * 1 }).ToList(); var list2 = db.Queryable <Order>().Where(it => SqlFunc.Subqueryable <OrderItem>() .LeftJoin <OrderItem>((i, z) => i.ItemId == z.ItemId) .InnerJoin <OrderItem>((i, z, y) => i.ItemId == z.ItemId) .InnerJoin <OrderItem>((i, z, y, h) => i.ItemId == z.ItemId) .InnerJoin <OrderItem>((i, z, y, h, n) => i.ItemId == z.ItemId) .Where((i, z) => i.ItemId == z.ItemId) .Any() ).ToList(); var list3 = db.Queryable <Order>().Select(it => new { customName = SqlFunc.Subqueryable <Custom>().Where(s => s.Id == it.CustomId).GroupBy(s => s.Name).Having(s => SqlFunc.AggregateCount(s.Id) > 0).Select(s => s.Name) }).ToList(); var exp = Expressionable.Create <Custom>().And(s => s.Id == 1).ToExpression(); var list4 = db.Queryable <Order>().Select(it => new { customName = SqlFunc.Subqueryable <Custom>().Where(exp).Where(exp).GroupBy(s => s.Name).Having(s => SqlFunc.AggregateCount(s.Id) > 0).Select(s => s.Name) }).ToList(); var list5 = db.Queryable <Order>().Where(it => SqlFunc.Subqueryable <OrderItem>() .LeftJoin <OrderItem>((i, y) => i.ItemId == y.ItemId) .InnerJoin <OrderItem>((i, z) => i.ItemId == z.ItemId) .Where(i => i.ItemId == 1) .Any() ).ToList(); Console.WriteLine("#### Subquery End ####"); }
private static void EasyExamples() { Console.WriteLine(""); Console.WriteLine("#### Examples Start ####"); var db = GetInstance(); var dbTime = db.GetDate(); var getAll = db.Queryable <Order>().ToList(); var getTake = db.Queryable <Order>().Take(2).ToList(); var getSkip = db.Queryable <Order>().Skip(2).ToList(); var getOrderBy = db.Queryable <Order>().OrderBy(it => it.Name, OrderByType.Desc).ToList(); var getOrderBy2 = db.Queryable <Order>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList(); var getOrderBy3 = db.Queryable <Order>().OrderBy(it => new { it.Name, it.Id }).ToList(); var getRandom = db.Queryable <Order>().OrderBy(it => SqlFunc.GetRandom()).First(); var getByPrimaryKey = db.Queryable <Order>().InSingle(2); var getSingleOrDefault = db.Queryable <Order>().Where(it => it.Id == 1).Single(); var getFirstOrDefault = db.Queryable <Order>().First(); var getByWhere = db.Queryable <Order>().Where(it => it.Id == 1 || it.Name == "a").ToList(); var getByWhere2 = db.Queryable <Order>().Where(it => it.Id == DateTime.Now.Year).ToList(); var getByFuns = db.Queryable <Order>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList(); // var getByFuns2 = db.Queryable<Order>().GroupBy(it => it.Name).Select(it => SqlFunc.AggregateDistinctCount(it.Price)).ToList(); var getDicionary = db.Queryable <Order>().ToDictionary(it => it.Id, it => it.Name); var getDicionaryList = db.Queryable <Order>().ToDictionaryList(); var getTest = db.Queryable <Order>().Where(it => string.IsNullOrWhiteSpace(it.Name)).ToList(); // var test01 = db.Queryable<Order>().PartitionBy(it => it.Id).ToList(); var q1 = db.Queryable <Order>().Take(1); var q2 = db.Queryable <Order>().Take(2); //var test02 = db.Union(q1, q2).ToList(); var test03 = db.Queryable <Order>().Take(1).ToList(); var dp = DateTime.Now; var test05 = db.Queryable <Order>().Where(it => it.CreateTime.Month == dp.Month).ToList(); var test06 = db.Queryable <Order>() .ToPivotTable(it => it.Id, it => it.Name, it => it.Sum(x => x.Price)); var test07 = db.Queryable <Order>() .ToPivotList(it => it.Id, it => it.Name, it => it.Sum(x => x.Price)); var test08 = db.Queryable <Order>() .ToPivotJson(it => it.Id, it => it.Name, it => it.Sum(x => x.Price)); //var test09 = db.Queryable<Order>().PartitionBy(it=>it.Id).ToPageListAsync(1,2,0); //test09.Wait(); int c = 0; var test10 = db.Queryable <Order>().OrderBy(it => it.Id).ToPageList(3, 5, ref c); var test11 = db.Queryable <Order>().GroupBy(it => new { it.CreateTime.Year }).Select(it => it.CreateTime.Year).ToList(); // var test12 = db.Queryable<Order>().GroupBy(it => it.CreateTime.Date ).Select(it => it.CreateTime.Date).ToList(); //var test13 = db.Queryable<Order>().GroupBy(it => new { it.CreateTime.Date ,it.CreateTime.Year,it.CreateTime.Minute }) // .Select(it => new { it.CreateTime.Date, it.CreateTime.Year, it.CreateTime.Minute }).ToList(); //var test14 = db.Queryable<Order>() // .GroupBy(it => it.CreateTime.Year ) // .GroupBy(it => it.CreateTime.Second) // .GroupBy(it => it.CreateTime.Date) // .Select(it => new { // it.CreateTime.Year, // it.CreateTime.Second, // it.CreateTime.Date // }).ToList(); var test15 = db.Queryable <Order, Order>((o, i) => new JoinQueryInfos( JoinType.Left, o.Name == SqlFunc.ToString(SqlFunc.MergeString(",", i.Name, ",")) )) .Select <ViewOrder>().ToList(); // var test16 = db.Queryable<Order>().Select(it => SqlFunc.SqlServer_DateDiff("day", DateTime.Now.AddDays(-1), DateTime.Now)).ToList(); //var test17 = // db.Queryable<Order>() // .Select<Order>() // .MergeTable() // .Select(it => new ViewOrder() // { // Name = SqlFunc.Subqueryable<Order>().Select(s => s.Name) // }).ToList(); ; //var test18 = db.UnionAll( // db.Queryable<Order>() , // db.Queryable<Order>() // ) // .Select(it=>new ViewOrder(){ // Name=SqlFunc.Subqueryable<Order>().Select(s=>s.Name) // }).ToList(); var test19 = db.Queryable <Order>().Select <ViewOrder>().ToList(); var test20 = db.Queryable <Order>().LeftJoin <Custom>((o, cs) => o.Id == cs.Id) .ToDictionary(it => it.Id, it => it.Name); //var test21 = db.Queryable<Order>().Where(it=>it.Id.ToString()==1.ToString()).Select(it => it.CreateTime.ToString("24")).First(); Console.WriteLine("#### Examples End ####"); }
/// <summary> /// table1 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> /// private List <BaseIList> schData() { UserProc.CheckTime = ""; var exp = Expressionable.Create <BaseIList>().And(it => it.leibie == "染料入库"); if (checkMohu.Checked) { if (chkRKDH.chkSel) { exp.And((b) => b.dingdanhao.Contains(chkRKDH.txt.Text)); } if (chkSL.chkSel) { exp.And((b) => b.seming.Contains(chkSL.txt.Text)); } if (chkDJ.chkSel) { exp.And((b) => b.item0.Contains(chkDJ.txt.Text)); } if (chkZL.chkSel) { exp.And((b) => b.pishu.Contains(chkZL.txt.Text)); } if (chkRHLB.chkSel) { exp.And((b) => b.item1.Contains(chkRHLB.cobodgv.Text)); } if (chkGYS.chkSel) { exp.And((b) => b.item2.Contains(chkGYS.txt.Text)); } if (chkRHMC.chkSel) { exp.And((b) => b.item3.Contains(chkRHMC.cobodgv.Text)); } } else { if (chkRKDH.chkSel) { exp.And((b) => b.dingdanhao == (chkRKDH.txt.Text)); } if (chkSL.chkSel) { exp.And((b) => b.seming == (chkSL.txt.Text)); } if (chkDJ.chkSel) { exp.And((b) => b.item0 == (chkDJ.txt.Text)); } if (chkZL.chkSel) { exp.And((b) => b.pishu == (chkZL.txt.Text)); } if (chkRHLB.chkSel) { exp.And((b) => b.item1 == (chkRHLB.cobodgv.Text)); } if (chkGYS.chkSel) { exp.And((b) => b.item2 == (chkGYS.txt.Text)); } if (chkRHMC.chkSel) { exp.And((b) => b.item3 == (chkRHMC.cobodgv.Text)); } } DateTime dateTime; string str1 = "1970-01-01 00:00:00", str2 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); if (this.chkDtime1.chkFsel) { dateTime = chkDtime1.dtimeF.Value; str1 = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); } if (this.chkDtime1.chkTsel) { dateTime = chkDtime1.dtimeT.Value; str2 = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); exp.And((b) => SqlFunc.Between(b.riqi, str1, str2)); UserProc.CheckTime = str1 + " - " + str2; } var getAll1 = db.Queryable <BaseIList>() .Where(exp.ToExpression()) .OrderBy((b) => b.riqi, OrderByType.Desc) .ToList(); return(getAll1); }
/// <summary> /// 返回本月未登记的宿舍数量 /// </summary> /// <param name="dorm_id">园区ID:默认全部</param> /// <returns></returns> public short CountNoRecord(int dorm_id = 0) { var rooms = Db.Queryable <T_Used>(). Where(u => u.Used_model_state && SqlFunc.Between(SqlFunc.Substring(u.Used_post_date, 0, 7), SqlFunc.Substring(DateTime.Now, 0, 7), SqlFunc.Substring(DateTime.Now, 0, 7))).Select(u => u.Used_room_id).ToList(); // 已登记的宿舍ID var sql = Db.Queryable <T_Room>().Where(r => r.Room_model_state && r.Number > 0 && r.Room_is_active && !rooms.Contains(r.Room_id)); if (dorm_id != 0) { sql = sql.Where(r => r.Room_id == dorm_id); } return((short)sql.OrderBy(r => r.Room_vid).Count()); }
public void AddAccountInfoHistory(string name, string bankOfDeposit, string account) { name = string.IsNullOrWhiteSpace(name) ? string.Empty : name; bankOfDeposit = string.IsNullOrWhiteSpace(bankOfDeposit) ? string.Empty : bankOfDeposit; account = string.IsNullOrWhiteSpace(account) ? string.Empty : account; if (string.IsNullOrWhiteSpace(name) || string.IsNullOrWhiteSpace(bankOfDeposit) || string.IsNullOrWhiteSpace(account)) { return; } var query = BusDb.Queryable <DH_AccountInfoHistory>() .Where(m => SqlFunc.Equals(m.Name, name) && SqlFunc.Equals(m.BankOfDeposit, bankOfDeposit) && SqlFunc.Equals(m.Account, account)).ToList(); if (query.Count > 0) { return; } DH_AccountInfoHistory info = new DH_AccountInfoHistory() { Id = Guid.NewGuid(), Name = name, BankOfDeposit = bankOfDeposit, Account = account }; BusDb.Insertable(info).ExecuteCommand(); }
/// <summary> /// 加盟商库存盘点报表 /// </summary> /// <param name="parm"></param> /// <param name="searchParm"></param> /// <returns></returns> public Task <ApiResult <Page <ShopInventory> > > GetShopInventoryAsync(PageParm parm, SearchSaleOrderGoods searchParm) { var res = new ApiResult <Page <ShopInventory> >(); try { //默认只查询当月的营业额 DateTime now = DateTime.Now; DateTime beginTime = new DateTime(now.Year, now.Month, 1), endTime = beginTime.AddMonths(1); if (!string.IsNullOrEmpty(parm.time)) { var timeRes = Utils.SplitString(parm.time, '-'); beginTime = Convert.ToDateTime(timeRes[0].Trim()); endTime = Convert.ToDateTime(timeRes[1].Trim()); } if (string.IsNullOrEmpty(parm.field) || string.IsNullOrEmpty(parm.order)) { parm.field = "stock"; parm.order = "desc"; } var query = Db.Queryable <ErpShopSku, ErpGoodsSku, ErpShops>((t1, t2, t3) => new object[] { JoinType.Left, t1.SkuGuid == t2.Guid, JoinType.Left, t1.ShopGuid == t3.Guid }) .WhereIF(!string.IsNullOrEmpty(parm.time), (t1, t2, t3) => SqlFunc.Between(t1.AddDate, beginTime, endTime)) .WhereIF(!string.IsNullOrEmpty(searchParm.shopGuid), (t1, t2, t3) => t1.ShopGuid == searchParm.shopGuid) .WhereIF(!string.IsNullOrEmpty(searchParm.brank), (t1, t2, t3) => t2.BrankGuid == searchParm.brank) .WhereIF(!string.IsNullOrEmpty(searchParm.year), (t1, t2, t3) => t2.YearGuid == searchParm.year) .WhereIF(!string.IsNullOrEmpty(searchParm.size), (t1, t2, t3) => t2.SizeGuid == searchParm.size) .WhereIF(!string.IsNullOrEmpty(searchParm.style), (t1, t2, t3) => t2.StyleGuid == searchParm.style) .WhereIF(!string.IsNullOrEmpty(searchParm.season), (t1, t2, t3) => t2.SeasonGuid == searchParm.season) .WhereIF(!string.IsNullOrEmpty(parm.key), (t1, t2, t3) => t2.Code.Contains(parm.key)) .OrderByIF(parm.orderType == 1, (t1, t2, t3) => t1.Sale, OrderByType.Desc) .OrderByIF(parm.orderType == 2, (t1, t2, t3) => t1.Stock, OrderByType.Desc) .Select((t1, t2, t3) => new ShopInventory() { Code = t2.Code, ShopName = t3.ShopName, //CollectSum= SqlFunc.Subqueryable<ErpInOutLog>().Where(g => g.Types == 2).Sum(g => g.GoodsSum), Brand = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.BrankGuid).Select(g => g.Name), Style = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.StyleGuid).Select(g => g.Name), SeasonName = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.SeasonGuid).Select(g => g.Name), Size = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.SizeGuid).Select(g => g.Name), Stock = t1.Stock, Sale = t1.Sale, ReturnSum = SqlFunc.Subqueryable <ErpReturnGoods>().Where(g => g.GoodsGuid == t1.SkuGuid && g.ShopGuid == parm.guid && g.Status == 1).Sum(g => g.ReturnCount) }) .OrderBy(parm.field + " " + parm.order) .ToPage(parm.page, parm.limit); res.data = query; } catch (Exception ex) { res.message = ApiEnum.Error.GetEnumText() + ex.Message; res.statusCode = (int)ApiEnum.Error; } return(Task.Run(() => res)); }
public List <DH_AccountInfoHistory> SearchAccountInfo(string key) { List <DH_AccountInfoHistory> result = new List <DH_AccountInfoHistory>(); var query = BusDb.Queryable <DH_AccountInfoHistory>() .WhereIF(!string.IsNullOrEmpty(key), m => SqlFunc.Contains(m.Name, key) || SqlFunc.Contains(m.BankOfDeposit, key) || SqlFunc.Contains(m.Account, key)); if (string.IsNullOrEmpty(key)) { result = query.Take(10).ToList(); } else { result = query.ToList(); } return(result); }
private List <T_PFmain> schData() { var exp = Expressionable.Create <T_PFmain>().And(it => it.leibie == XYD); List <T_PFmain> getAll = null; if (schMohu.Checked) { if (schKH.chkSel) { exp.And(it => it.kehu.Contains(schKH.cobodgv.Text)); } if (schSZ.chkSel) { exp.And(it => it.shazhong.Contains(schSZ.cobodgv.Text)); } if (schSH.chkSel) { exp.And(it => it.sehao.Contains(schSH.cobodgv.Text)); } if (schYS.chkSel) { exp.And(it => it.yanse.Contains(schYS.cobodgv.Text)); } if (schLD.chkSel) { exp.And(it => it.danhao.Contains(schLD.txt.Text)); } if (schDY.chkSel) { exp.And(it => it.dayang.Contains(schDY.txt.Text)); } if (schDDH.chkSel) { exp.And(it => it.dingdan.Contains(schDDH.txt.Text)); } } else { if (schKH.chkSel) { exp.And(it => it.kehu == (schKH.cobodgv.Text)); } if (schSZ.chkSel) { exp.And(it => it.shazhong == (schSZ.cobodgv.Text)); } if (schSH.chkSel) { exp.And(it => it.sehao == (schSH.cobodgv.Text)); } if (schYS.chkSel) { exp.And(it => it.yanse == (schYS.cobodgv.Text)); } if (schLD.chkSel) { exp.And(it => it.danhao == (schLD.txt.Text)); } if (schDY.chkSel) { exp.And(it => it.dayang == (schDY.txt.Text)); } if (schDDH.chkSel) { exp.And(it => it.dingdan == (schDDH.txt.Text)); } } DateTime dateTime; string str1 = "1970-01-01 00:00:00", str2 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); if (this.schRQsave.chkFsel) { dateTime = schRQsave.dtimeF.Value; str1 = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); } if (this.schRQsave.chkTsel) { dateTime = schRQsave.dtimeT.Value; str2 = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); exp.And(it => SqlFunc.Between(it.riqiSave, str1, str2)); } getAll = db.Queryable <T_PFmain>().Where(exp.ToExpression()).OrderBy(it => it.SN, OrderByType.Desc).ToList(); return(getAll); }
public List <OA_Client> SearchClientInfo(string nameOrCode) { List <OA_Client> result = new List <OA_Client>(); var query = BusDb.Queryable <OA_Client>() .WhereIF(!string.IsNullOrEmpty(nameOrCode), m => SqlFunc.Contains(m.ClientCode, nameOrCode) || SqlFunc.Contains(m.ClientName, nameOrCode)); if (string.IsNullOrEmpty(nameOrCode)) { result = query.Take(10).ToList(); } else { result = query.ToList(); } return(result); }
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 => 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 => 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); }
/// <summary> /// 分页 /// </summary> /// <param name="parm"></param> /// <returns></returns> public async Task <ApiResult <Page <ErpShopUser> > > GetPagesAsync(PageParm parm) { var res = new ApiResult <Page <ErpShopUser> >(); try { if (string.IsNullOrEmpty(parm.field) || string.IsNullOrEmpty(parm.order)) { parm.field = "points"; parm.order = "desc"; } var dt = new DateTime().Date; var query = Db.Queryable <ErpShopUser>() .WhereIF(parm.guid != "all", m => m.ShopGuid == parm.guid) .WhereIF(parm.types == 1, m => m.RegDate.Year == SqlFunc.ToInt32(dt.Year) && m.RegDate.Month == SqlFunc.ToInt32(dt.Month) && m.RegDate.Day == SqlFunc.ToInt32(dt.Day)) .WhereIF(!string.IsNullOrEmpty(parm.key), m => m.Mobile == parm.key || m.UserName == parm.key) .OrderBy(parm.field + " " + parm.order) .ToPageAsync(parm.page, parm.limit); res.success = true; res.message = "获取成功!"; res.data = await query; } catch (Exception ex) { res.message = ApiEnum.Error.GetEnumText() + ex.Message; res.statusCode = (int)ApiEnum.Error; } return(await Task.Run(() => res)); }
public static void Init() { var db = NewUnitTest.Db; db.CodeFirst.InitTables <Export>(); db.CodeFirst.InitTables <LoadCon>(); db.CodeFirst.InitTables <ExToCon>(); var LclId = "FCL"; var conno = "conno"; var withSameCarriCarNo = db.Queryable <Export>() .Where(export => SqlFunc.IsNull(export.IeId, "E") == "E") .Where(export => SqlFunc.Subqueryable <LoadCon>().Where(loadconn => SqlFunc.Subqueryable <ExToCon>().Where(extocon => extocon.ExId == export.Id && extocon.LcId == loadconn.Id).Any()) .WhereIF(LclId == "FCL", loadconn => SqlFunc.IsNull(loadconn.ConNo, "") == conno) .WhereIF(LclId != "FCL", loadconn => SqlFunc.IsNull(loadconn.ConNo, "") == conno && export.LclId == "FCL") .Any()) .ToList(); }
public CommonRtnEntity GetInfoByLessThanInviteCountAndUpdateDoing(string clientName, string projectName, int inviteCount, string status = "0") { lock (GetInfoByLessThanInviteCountAndUpdateDoingLockStr) { IMobileInfoServices advertisementServices = new MobileInfoServices(); List <MobileInfo> list = new List <MobileInfo>(); list = advertisementServices.Query(d => d.ProjectName == projectName && d.ClientName == clientName && d.Status == status && !SqlFunc.IsNullOrEmpty(d.InviteCode)).Take(5).ToList(); list = list.Where(x => x.InviteCount < inviteCount).ToList(); MobileInfo info = null; if (list != null && list.Count > 0) { info = list[(new Random()).Next(0, list.Count - 1)]; info.Status = "占用"; if (info.CreateTime < DateTime.Now.AddYears(-5)) { info.CreateTime = DateTime.Now; } info.UpdateTime = DateTime.Now; advertisementServices.Update(info); } string errorMsg = "暂未获取到!"; CommonRtnEntity rtnInfo = new CommonRtnEntity() { Success = info != null, Data = info, Message = info != null ? "获取成功!" : errorMsg }; return(rtnInfo); } }
public static void Funs() { var db = GetInstance(); var t1 = db.Queryable <Student>().Where(it => SqlFunc.ToLower(it.Name) == SqlFunc.ToLower("JACK")).ToList(); var t2 = db.Queryable <Student>().Where(it => SqlFunc.IsNull(it.Name, "nullvalue") == "nullvalue").ToList(); //SELECT [Id],[SchoolId],[Name],[CreateTime] FROM [Student] WHERE ((LOWER([Name])) = (LOWER(@MethodConst0)) ) /***More Functions***/ //SqlFunc.IsNullOrEmpty(object thisValue) //SqlFunc.ToLower(object thisValue) //SqlFunc.string ToUpper(object thisValue) //SqlFunc.string Trim(object thisValue) //SqlFunc.bool Contains(string thisValue, string parameterValue) //SqlFunc.ContainsArray(object[] thisValue, string parameterValue) //SqlFunc.StartsWith(object thisValue, string parameterValue) //SqlFunc.EndsWith(object thisValue, string parameterValue) //SqlFunc.Equals(object thisValue, object parameterValue) //SqlFunc.DateIsSame(DateTime date1, DateTime date2) //SqlFunc.DateIsSame(DateTime date1, DateTime date2, DateType dataType) //SqlFunc.DateAdd(DateTime date, int addValue, DateType millisecond) //SqlFunc.DateAdd(DateTime date, int addValue) //SqlFunc.DateValue(DateTime date, DateType dataType) //SqlFunc.Between(object value, object start, object end) //SqlFunc.ToInt32(object value) //SqlFunc.ToInt64(object value) //SqlFunc.ToDate(object value) //SqlFunc.ToString(object value) //SqlFunc.ToDecimal(object value) //SqlFunc.ToGuid(object value) //SqlFunc.ToDouble(object value) //SqlFunc.ToBool(object value) //SqlFunc.Substring(object value, int index, int length) //SqlFunc.Replace(object value, string oldChar, string newChar) //SqlFunc.Length(object value) { throw new NotImplementedException(); } //SqlFunc.AggregateSum(object thisValue) //SqlFunc.AggregateAvg<TResult>(TResult thisValue) //SqlFunc.AggregateMin(object thisValue) //SqlFunc.AggregateMax(object thisValue) //SqlFunc.AggregateCount(object thisValue) }
public void Q2() { using (var db = GetInstance()) { //db.Database.IsEnableLogEvent = true; db.Ado.LogEventStarting = (sql, pars) => { Console.WriteLine(sql + " " + pars); }; #region dr ot entity db.IgnoreColumns.Add("TestId", "Student"); var s1 = db.Queryable <Student>().Select(it => new ViewModelStudent2 { Name = it.Name, Student = it }).ToList(); var s2 = db.Queryable <Student>().Select(it => new { id = it.Id, w = new { x = it } }).ToList(); var s3 = db.Queryable <Student>().Select(it => new { newid = it.Id }).ToList(); var s4 = db.Queryable <Student>().Select(it => new { newid = it.Id, obj = it }).ToList(); var s5 = db.Queryable <Student>().Select(it => new ViewModelStudent2 { Student = it, Name = it.Name }).ToList(); #endregion #region sql and parameters validate var t1 = db.Queryable <Student, School>((st, sc) => new object[] { JoinType.Inner, st.Id == sc.Id }).GroupBy(st => st.Id).Having(st => SqlFunc.AggregateAvg(st.Id) == 1).Select(st => new { avgId = SqlFunc.AggregateAvg(st.Id) }).ToSql(); base.Check("SELECT AVG(`st`.`ID`) AS `avgId` FROM `STudent` st Inner JOIN `School` sc ON ( `st`.`ID` = `sc`.`Id` ) GROUP BY `st`.`ID` HAVING (AVG(`st`.`ID`) = @Const0 ) ", new List <SugarParameter>() { new SugarParameter("@Const0", 1) } , t1.Key, t1.Value, " select t1 Error"); var t2 = db.Queryable <School, School>((st, st2) => new object[] { JoinType.Left, st.Id == st2.Id }) .Where(st => st.Id > 0) .Select((st, st2) => new { stid = st.Id, scId = st2.Id, xx = st }).ToSql(); base.Check("SELECT `st`.`Id` AS `stid` , `st2`.`Id` AS `scId` , `st`.`Id` AS `School.Id` , `st`.`Name` AS `School.Name` FROM `School` st Left JOIN `School` st2 ON ( `st`.`Id` = `st2`.`Id` ) WHERE ( `st`.`Id` > @Id0 ) " , new List <SugarParameter>() { new SugarParameter("@Id0", 0) }, t2.Key, t2.Value, "select t2 Error"); var t3 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] { JoinType.Left, st.SchoolId == sc.Id, JoinType.Left, sc2.Id == sc.Id }).Where(st => st.Id > 0) .Select <School>((st) => new School() { Id = st.Id }).ToSql(); base.Check("SELECT `st`.`ID` AS `Id` FROM `STudent` st Left JOIN `School` sc ON ( `st`.`SchoolId` = `sc`.`Id` ) Left JOIN `School` sc2 ON ( `sc2`.`Id` = `sc`.`Id` ) WHERE ( `st`.`ID` > @Id0 ) ", new List <SugarParameter>() { new SugarParameter("@Id0", 0) }, t3.Key, t3.Value, "select t3 Error"); #endregion } }
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(); }
private async Task <TWMOtherWhSendMainQueryModel> GetMainData(int iMainId) { try { var mainModel = await _db.Instance.Queryable <TWMOtherWhSendMainDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel>( (t, t0, t1, t2) => new object[] { JoinType.Left, t.OperatorId == t0.ID, JoinType.Left, t.ReceiptId == t1.ID, JoinType.Left, t.AuditId == t2.ID }) .Select((t, t0, t1, t2) => new TWMOtherWhSendMainQueryModel { ID = t.ID, WhSendType = t.WhSendType, WhSendDate = t.WhSendDate, WhSendOrder = t.WhSendOrder, AuditStatus = t.AuditStatus, OperatorId = t.OperatorId, OperatorName = t0.AccountName, ReceiptId = t.ReceiptId, ReceiptName = t1.AccountName, AuditId = t.AuditId, AuditName = t2.AccountName, AuditTime = t.AuditTime, DeleteFlag = t.DeleteFlag, Number = t.Number, Amount = t.Amount }) .Where(t => t.ID == iMainId) .FirstAsync(); var detailModels = await _db.Instance.Queryable <TWMOtherWhSendDetailDbModel, TBMMaterialFileDbModel, TBMDictionaryTypeDbModel, TBMDictionaryDbModel, TBMDictionaryDbModel, TBMDictionaryDbModel>( (t, t0, t1, t2, t3, t4) => new object[] { JoinType.Left, t.MaterialId == t0.ID, JoinType.Left, t0.MaterialTypeId == t1.ID, JoinType.Left, t0.ColorId == t2.ID, JoinType.Left, t0.BaseUnitId == t3.ID, JoinType.Left, t0.WarehouseUnitId == t4.ID }) .Select((t, t0, t1, t2, t3, t4) => new TWMOtherWhSendDetailQueryModel { ID = t.ID, MainId = t.MainId, MaterialId = t.MaterialId, MaterialName = t0.MaterialName, MaterialCode = t0.MaterialCode, WarehouseId = t.WarehouseId, BatchNumber = t.BatchNumber, ActualNumber = t.ActualNumber, UnitPrice = t.UnitPrice, Amount = t.Amount, MaterialTypeId = t0.MaterialTypeId, MaterialTypeName = t1.TypeName, ColorId = t0.ColorId, ColorName = t2.DicValue, BaseUnitId = t0.BaseUnitId, BaseUnitName = t3.DicValue, WarehouseUnitId = t0.WarehouseUnitId, WarehouseUnitName = SqlFunc.IsNullOrEmpty(t4.ID) ? t3.DicValue : t4.DicValue, WarehouseRate = t0.WarehouseRate, Spec = t0.Spec, ValidityPeriod = t.ValidityPeriod, Remark = t.Remark }) .Where(t => t.MainId == iMainId) .ToListAsync(); mainModel.ChildList = detailModels; return(mainModel); } catch (Exception ex) { return(null); } }
/// <summary> /// 根据权限获取操作按钮 /// </summary> /// <param name="menuId"></param> /// <returns></returns> public List <sys_operate> GetOperateByRole(string menuId) { var list = new List <sys_operate>(); if (UserCookie.IsSuper) { list = Sqldb.Queryable <sys_operate, sys_menu_ref_operate>((f, m) => f.id == m.operate_id && m.menu_id == SqlFunc.ToInt64(menuId)) .Select((f, m) => f) .ToList(); } else { list = Sqldb.Queryable <sys_operate, sys_role_authorize>((f, r) => f.id == r.menu_id) .Where((f, r) => r.role_id == UserCookie.SysRoleId && r.menu_pid == SqlFunc.ToInt64(menuId)) .Select((f, m) => f) .ToList(); } return(list); }
/// <summary> /// 查询营业额 /// </summary> /// <returns></returns> public Task <ApiResult <Page <ShopTurnover> > > GetShopTurnover(PageParm parm) { var res = new ApiResult <Page <ShopTurnover> >(); try { //默认只查询当月的营业额 DateTime now = DateTime.Now; DateTime beginTime = new DateTime(now.Year, now.Month, 1), endTime = beginTime.AddMonths(1); if (!string.IsNullOrEmpty(parm.time)) { var timeRes = Utils.SplitString(parm.time, '-'); beginTime = Convert.ToDateTime(timeRes[0].Trim()); endTime = Convert.ToDateTime(timeRes[1].Trim()); } if (string.IsNullOrEmpty(parm.field) || string.IsNullOrEmpty(parm.order)) { parm.field = "Money"; parm.order = "desc"; } var query = Db.Queryable <ErpShops>() .WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.Guid == parm.guid) .Select(m => new ShopTurnover() { ShopName = m.ShopName, Principal = m.AdminName, Mobile = m.Mobile, OrderCount = SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Count(), Money = SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Sum(g => g.RealMoney), ReturnCount = SqlFunc.Subqueryable <ErpReturnOrder>().Where(g => g.Status == 1 && g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Count(), BackCount = SqlFunc.Subqueryable <ErpBackGoods>().Where(g => g.Status == 1 && g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Count(), BackMoney = SqlFunc.Subqueryable <ErpBackGoods>().Where(g => g.Status == 1 && g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Sum(g => g.BackMoney), }) .OrderBy(parm.field + " " + parm.order) .ToPage(parm.page, parm.limit); res.data = query; } catch (Exception ex) { res.message = ApiEnum.Error.GetEnumText() + ex.Message; res.statusCode = (int)ApiEnum.Error; } return(Task.Run(() => res)); }
/* ** Find an SqlFunc structure with the given name. Or create a new ** one if an existing one cannot be found. Return a pointer to the ** structure. */ static SqlFunc findSqlFunc( SqliteDb pDb, string zName ) { SqlFunc p, pNew; int i; pNew = new SqlFunc();//(SqlFunc)Tcl_Alloc( sizeof(*pNew) + strlen30(zName) + 1 ); //pNew.zName = (char*)&pNew[1]; //for(i=0; zName[i]; i++){ pNew.zName[i] = tolower(zName[i]); } //pNew.zName[i] = 0; pNew.zName = zName.ToLower(); for ( p = pDb.pFunc ; p != null ; p = p.pNext ) { if ( p.zName == pNew.zName ) { //Tcl_Free((char*)pNew); return p; } } pNew.interp = pDb.interp; pNew.pScript = null; pNew.pNext = pDb.pFunc; pDb.pFunc = pNew; return pNew; }
/// <summary> /// 按宿舍楼查找当月未登记宿舍并返回Json /// </summary> /// <param name="id">宿舍楼ID</param> /// <returns></returns> public string GetJsonAllNoRecordByBuilding(int id) { /// 先获取本月已登记的宿舍ID var rooms = Db.Queryable <T_Used>(). Where(u => u.Used_model_state && SqlFunc.Between(SqlFunc.Substring(u.Used_post_date, 0, 7), SqlFunc.Substring(DateTime.Now, 0, 7), SqlFunc.Substring(DateTime.Now, 0, 7))).Select(u => u.Used_room_id).ToList(); return(Db.Queryable <T_Room>().Where((r) => r.Room_model_state && r.Number > 0 && r.Room_building_id == id && r.Room_is_active == true && !rooms.Contains(r.Room_id)).OrderBy(r => r.Room_vid). ToJson()); }