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 ####");
}
