예제 #1
0
        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 ####");
        }
예제 #2
0
    //当前类已经继承了 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实现复杂的操作
    }
예제 #3
0
        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();
        }
예제 #4
0
        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 ####");
        }
예제 #5
0
        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);
        }
예제 #6
0
 /// <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 })));
 }
예제 #7
0
        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);
        }
예제 #8
0
        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 ####");
        }
예제 #9
0
        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 ####");
        }