private static void EasyExamples() { Console.WriteLine(""); Console.WriteLine("#### Examples Start ####"); var db = GetInstance(); var dbTime = db.GetDate(); var getAll = db.Queryable <Order>().Where(it => SqlFunc.EqualsNull(it.Name, null)).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(); Console.WriteLine("#### Examples End ####"); }
//当前类已经继承了 DbContext增、删、查、改的方法 //这里面写的代码不会给覆盖,如果要重新生成请删除 RPT_Product_SettingManager.cs #region 教学方法 /// <summary> /// 如果DbContext中的增删查改方法满足不了你,你可以看下具体用法 /// </summary> public void Study() { /*********查询*********/ var data1 = RPT_Product_SettingDb.GetById(1); //根据ID查询 var data2 = RPT_Product_SettingDb.GetList(); //查询所有 var data3 = RPT_Product_SettingDb.GetList(it => 1 == 1); //根据条件查询 //var data4 = RPT_Product_SettingDb.GetSingle(it => 1 == 1);//根据条件查询一条,如果超过一条会报错 var p = new PageModel() { PageIndex = 1, PageSize = 2 }; // 分页查询 var data5 = RPT_Product_SettingDb.GetPageList(it => 1 == 1, p); Console.Write(p.PageCount); //返回总数 var data6 = RPT_Product_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_Product_Setting).GetProperties()[0].Name, ConditionalType = ConditionalType.Equal, FieldValue = "1" }); //id=1 var data7 = RPT_Product_SettingDb.GetPageList(conModels, p, it => SqlFunc.GetRandom(), OrderByType.Asc); RPT_Product_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_Product_Setting() { }; //测试参数 var insertArray = new RPT_Product_Setting[] { insertData }; RPT_Product_SettingDb.Insert(insertData); //插入 RPT_Product_SettingDb.InsertRange(insertArray); //批量插入 var id = RPT_Product_SettingDb.InsertReturnIdentity(insertData); //插入返回自增列 RPT_Product_SettingDb.AsInsertable(insertData).ExecuteCommand(); //我们可以转成 Insertable实现复杂插入 /*********更新*********/ var updateData = new RPT_Product_Setting() { }; //测试参数 var updateArray = new RPT_Product_Setting[] { updateData }; //测试参数 RPT_Product_SettingDb.Update(updateData); //根据实体更新 RPT_Product_SettingDb.UpdateRange(updateArray); //批量更新 //RPT_Product_SettingDb.Update(it => new RPT_Product_Setting() { Name = "a", CreateTime = DateTime.Now }, it => it.id==1);// 只更新Name列和CreateTime列,其它列不更新,条件id=1 RPT_Product_SettingDb.AsUpdateable(updateData).ExecuteCommand(); /*********删除*********/ var deldata = new RPT_Product_Setting() { }; //测试参数 RPT_Product_SettingDb.Delete(deldata); //根据实体删除 RPT_Product_SettingDb.DeleteById(1); //根据主键删除 RPT_Product_SettingDb.DeleteById(new int[] { 1, 2 }); //根据主键数组删除 RPT_Product_SettingDb.Delete(it => 1 == 2); //根据条件删除 RPT_Product_SettingDb.AsDeleteable().Where(it => 1 == 2).ExecuteCommand(); //转成Deleteable实现复杂的操作 }
public static void Easy() { var db = GetInstance(); var getAll22 = db.Queryable <Student>().ToDataTable(); var getAll220 = db.Ado.GetDataSetAll("select 1"); var getAll22222 = db.Queryable <Student>().Where(it => it.Id == 1).ToSql(); var dbTime = db.GetDate(); var getAll = db.Queryable <Student>().Select <object>("*").ToList(); var getAll2 = db.Queryable <Student>().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(); }
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 ####"); }
public static void Easy() { var db = CreateDBInstance.GetInstance(); var dbTime = db.GetDate(); dynamic getALL = db.Queryable <Base_User>().ToList(); getALL = db.Queryable <Base_User>().Select <object>("*").ToList(); getALL = db.Queryable <Base_User>().Select <object>("UserName,Password").ToList(); //不要加引号 var getRandomList = db.Queryable <Base_User>().OrderBy(it => SqlFunc.GetRandom()).ToList(); var getAllOrder = db.Queryable <Base_User>().OrderBy(it => it.Id).OrderBy(it => it.UserId, OrderByType.Desc).ToList().Take(10); //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 <Base_User>().Where(it => it.Id == 17 || it.UserName == "admin").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() ; string strJson = JsonConvert.SerializeObject(getByWhere); Console.WriteLine(strJson); }
/// <summary> /// 随机文章10条 /// </summary> /// <returns></returns> public IActionResult Random() { return(Json(_articleInfoService.Queryable(c => c.Visible == true, o => SqlFunc.GetRandom(), false, 10).Select(s => new { ArticleId = s.ArticleId, Title = s.Title }))); }
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); }
private static void EasyExamples() { Console.WriteLine(""); Console.WriteLine("#### Examples Start ####"); var db = GetInstance(); var dbTime = db.GetDate(); var getAll = db.Queryable <Order>().Where(it => SqlFunc.EqualsNull(it.Name, null)).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>().ToPageList(1, 2, 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(); var test22 = db.Queryable <Order>().Where(it => it.Id.ToString() == 1.ToString()).Select(it => SqlFunc.AggregateDistinctCount(it.CreateTime)).First(); var test23 = db.Queryable <Order>().Where(it => true).Select(it => new { x1 = it.CreateTime.ToString("yyyy-MM"), it.CreateTime }).ToList(); var test24 = db.Queryable <Order>().Where(it => true).Select(it => new { x1 = it.CreateTime.ToString("yyyy-MM-dd _ HH _ mm _ ss "), it.CreateTime }).ToList(); var test25 = db.Queryable <Order>().Where(it => true).Select(it => new { x1 = it.CreateTime.Month, x2 = DateTime.Now.Month }).ToList(); var test26 = db.Queryable <Order>().Where(it => true).Select(it => new { x1 = it.CreateTime.Day, x2 = DateTime.Now.Day }).ToList(); var test27 = db.Queryable <Order>().Where(it => true).Select(it => new { x1 = it.CreateTime.Year, x2 = DateTime.Now.Year }).ToList(); var test28 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Day, Convert.ToDateTime("2021-1-1"), Convert.ToDateTime("2021-1-12"))).ToList(); var test29 = db.Queryable <Order>().Select(it => new { x = SqlFunc.LessThan(1, 2) }).ToList(); var test30 = db.Queryable <Order>().Select(it => new { x = SqlFunc.LessThanOrEqual(1, 2) }).ToList(); var test31 = db.Queryable <Order>().Select(it => new { x = SqlFunc.GreaterThan(1, 2) }).ToList(); var test32 = db.Queryable <Order>().Select(it => new { x = SqlFunc.GreaterThanOrEqual(1, 2) }).ToList(); List <Order> result = new List <Order>(); db.Queryable <Order>().ForEach(it => { result.Add(it); }, 10); result = new List <Order>(); int count = 0; db.Queryable <Order>().ForEachByPage(it => { result.Add(it); }, 2, 10, ref count, 5); var test33 = db.Queryable <Order>().ToList(); db.CurrentConnectionConfig.SqlMiddle = new SqlMiddle { IsSqlMiddle = true, ExecuteCommand = (s, p) => { return(s.Length); } }; var five = db.Ado.ExecuteCommand("11111"); db.CurrentConnectionConfig.SqlMiddle = null; Console.WriteLine("#### Examples End ####"); }
private static void EasyExamples() { Console.WriteLine(""); Console.WriteLine("#### Examples Start ####"); var db = GetInstance(); var dbTime = db.GetDate(); var getAll = db.Queryable <Order>().Where(it => SqlFunc.EqualsNull(it.Name, null)).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>().ToPageList(1, 2, 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(); Console.WriteLine("#### Examples End ####"); }