private static void EasyExamples()
{
Console.WriteLine("");
Console.WriteLine("#### Examples Start ####");
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Order>().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 test1 = db.UnionAll(
db.Queryable <Order>().Select(it => new ViewOrder()
{
CustomName = SqlFunc.MergeString(it.Name, "/", it.Name)
}),
db.Queryable <Order>().Select(it => new ViewOrder()
{
CustomName = SqlFunc.MergeString(it.Name, "/", it.Name)
})
).ToList();
var test2 = db.Queryable <Order>().Select <ViewOrder>().ToList();
var test3 = db.Queryable <Order>().Select(it => new Order()
{
CreateTime = SqlFunc.GetDate()
}).ToList();
Console.WriteLine("#### Examples End ####");
}
/// <summary>
/// 获取进行中的列表
/// </summary>
/// <param name="isDel"></param>
/// <returns></returns>
public List <ShareTopicEntity> UnderwayList()
{
return(ActionDal.ActionDBAccess.Queryable <ShareTopicEntity>()
.Where(it => it.isDel == false && it.startDate <= SqlFunc.GetDate() && SqlFunc.GetDate() <= it.endDate)
.OrderBy(it => it.startDate, OrderByType.Desc)
.ToList());
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().Select <object>("*").ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Student>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Select(it => it.SchoolId).ToList();
var sum2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Sum((st, sc) => sc.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var count = db.Queryable <Student>().Count(it => it.Id > 0);
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var joinSql = db.Queryable("student", "s").OrderBy("id").Select("id,name").ToPageList(1, 2);
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var getSqlList = db.Queryable <Student>().AS("(select * from student) t").ToList();
var getUnionAllList = db.UnionAll(db.Queryable <Student>().Where(it => it.Id == 1), db.Queryable <Student>().Where(it => it.Id == 2)).ToList();
var getUnionAllList2 = db.UnionAll(db.Queryable <Student>(), db.Queryable <Student>()).ToList();
var test1 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Where(st => st.CreateTime > SqlFunc.GetDate()).Select((st, sc) => SqlFunc.ToInt64(sc.Id)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
var test3 = db.Queryable <DataTestInfo2>().Select(it => it.Bool1).ToSql();
var test4 = db.Queryable <DataTestInfo2>().Select(it => new { b = it.Bool1 }).ToSql();
DateTime?result = DateTime.Now;
var test5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToList();
}
public static void Init()
{
SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = Config.ConnectionString,
DbType = DbType.Oracle,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute,
});
//调式代码 用来打印SQL
Db.Aop.OnLogExecuting = (sql, pars) =>
{
Console.WriteLine(sql);
};
var id = Enumerable.Range(0, 100).ToList();
// id = null;
Db.Queryable <Order>().Where(it => id.Contains(it.Id)).ToList();
Db.Insertable(new Order()
{
Name = "A", Price = 1, CustomId = 1, CreateTime = DateTime.Now.AddDays(-2)
}).ExecuteCommand();
var list = Db.Queryable <Order>().Where(it => Convert.ToDecimal(1.01) == Convert.ToDecimal(1.01)).ToList();;
var list2 = Db.Queryable <Order>().Where(it => it.CreateTime > SqlFunc.DateAdd(SqlFunc.GetDate(), -3)).ToList();;
var x = Db.Ado.SqlQuery <Order>("select null as price from dual").ToList();
}
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);
}
