private void HasValue()
{
Expression <Func <Student, bool> > exp = it => SqlFunc.HasValue(it.Name);
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "( [Name]<>'' AND [Name] IS NOT NULL )", new List <SugarParameter>()
{
}, "HasValue error");
}
public string GetNameId(string id)
{
id = id as string;
string message = "", code = '"' + "200" + '"', msg = '"' + "OK" + '"', total = "";
switch (id)
{
case "10001":
break;
case "10002":
infor.WriteInforLog("查询排行榜", "GetNameId()", "TB_Music");
var DB = db.Queryable <TB_Music>().OrderBy(it => it.M_Hot, OrderByType.Desc)
.OrderBy(it => it.M_Time, OrderByType.Desc)
.Distinct().Take(100);
infor.WriteInforLog($"执行的SQL语句为{DB.ToSql()}", "GetNameId()", "TB_Music");
message = DB.ToJson();
total = '"' + $"{DB.Count()}" + '"';
break;
case "10003":
break;
case "10004":
infor.WriteInforLog("查询歌手排行榜,并查询不为空的数据", "GetNameId()", "TB_Music");
var DB1 = db.Queryable <TB_SINGER>()
.Select(it => new {
// it.SingerId,
it.SingerClass
}).Where(it => SqlFunc.HasValue(it.SingerClass) && !(it.SingerClass.Contains("热门")))
.OrderBy(it => it.SingerClass, OrderByType.Asc).Distinct();
infor.WriteInforLog($"执行的SQL语句为{DB1.ToSql()}", "GetNameId()", "TB_Music");
message = DB1.ToJson();
total = '"' + $"{DB1.Count()}" + '"';
break;
}
return($"[{{" + '"' + "code" + '"' + $":{code}," + '"'
+ "msg" + '"' + $":{msg}," + '"'
+ "data" + '"' + $":{message}," + '"' + "Total" + '"' + $":{total}}}]");
}
/// <summary>
/// 获取我的任务列表
/// </summary>
/// <returns></returns>
public PageResponse <string> getTaskList(QueryProjectOrTaskRequest request)
{
var total = 0;
var query = Db.Queryable <Task, Project>((t, p) => new object[]
{
JoinType.Left, t.ProjectId == p.Id
}).Where((t, p) => t.TaskName.Contains(request.key));
if (SqlFunc.HasValue(request.startTime) && SqlFunc.HasValue(request.endTime))
{
query = query.Where((t, p) => SqlFunc.Between(t.CreateTime, request.startTime, request.endTime));
}
if (SqlFunc.HasValue(request.type))
{
query = query.Where((t, p) => t.Status == int.Parse(request.type));
}
// 如果是管理员 才查看所有任务 否则查询自己负责或者自己提交的
var roleId = Db.Queryable <UserRole>().Where(u => u.UserId == user.UserId).Select(u => u.RoleId).First();
if (roleId != 1)
{
query = query.Where((t, p) => t.ChargeUserId == user.UserId || t.SubmitterId == user.UserId || p.ChargeUserId == user.UserId);
}
var result = query.Select((t, p) => new
{
id = t.Id, projectId = t.ProjectId, taskName = t.TaskName, weight = t.Weight, progress = t.Progress,
status = t.Status, priority = t.Priority, startTime = t.StartTime, endTime = t.EndTime,
submitterName = t.SubmitterName, projectName = p.Name, chargeUserName = t.ChargeUserName
});
var json = result.ToPageList(request.page, request.limit, ref total).ToJson();
var pageResponse = new PageResponse <string> {
Total = total, Result = json
};
return(pageResponse);
}
public void Q2()
{
using (var db = GetInstance())
{
//db.Database.IsEnableLogEvent = true;
db.Ado.LogEventStarting = (sql, pars) =>
{
Console.WriteLine(sql + " " + pars);
};
#region dr ot entity
db.IgnoreColumns.Add("TestId", "Student");
var s1 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Name = it.Name, Student = it
}).ToList();
var s2 = db.Queryable <Student>().Select(it => new { id = it.Id, w = new { x = it } }).ToList();
var s3 = db.Queryable <Student>().Select(it => new { newid = it.Id }).ToList();
var s4 = db.Queryable <Student>().Select(it => new { newid = it.Id, obj = it }).ToList();
var s5 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Student = it, Name = it.Name
}).ToList();
#endregion
#region sql and parameters validate
var t1 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Inner, st.Id == sc.Id
}).GroupBy(st => st.Id).Having(st => SqlFunc.AggregateAvg(st.Id) == 1).Select(st => new { avgId = SqlFunc.AggregateAvg(st.Id) }).ToSql();
base.Check("SELECT AVG([st].[ID]) AS [avgId] FROM [STudent] st Inner JOIN [School] sc ON ( [st].[ID] = [sc].[Id] ) GROUP BY [st].[ID] HAVING (AVG([st].[ID]) = @Const0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Const0", 1)
}
,
t1.Key, t1.Value, " select t1 Error");
var t2 = db.Queryable <School, School>((st, st2) => new object[] {
JoinType.Left, st.Id == st2.Id
})
.Where(st => st.Id > 0)
.Select((st, st2) => new { stid = st.Id, scId = st2.Id, xx = st }).ToSql();
base.Check("SELECT [st].[Id] AS [stid] , [st2].[Id] AS [scId] , [st].[Id] AS [School.Id] , [st].[Name] AS [School.Name] FROM [School] st Left JOIN [School] st2 ON ( [st].[Id] = [st2].[Id] ) WHERE ( [st].[Id] > @Id0 ) "
, new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t2.Key, t2.Value, "select t2 Error");
var t3 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] {
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, sc2.Id == sc.Id
}).Where(st => st.Id > 0)
.Select <School>((st) => new School()
{
Id = st.Id
}).ToSql();
base.Check("SELECT [st].[ID] AS [Id] FROM [STudent] st Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] ) Left JOIN [School] sc2 ON ( [sc2].[Id] = [sc].[Id] ) WHERE ( [st].[ID] > @Id0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t3.Key, t3.Value, "select t3 Error");
db.Ado.IsEnableLogEvent = true;
db.Ado.LogEventStarting = (sql, pars) =>
{
base.Check(" SELECT COUNT(1) FROM (SELECT [st].[ID] FROM [STudent] st Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] ) Left JOIN [School] sc2 ON ( [sc2].[Id] = [sc].[Id] ) GROUP BY [st].[ID] ) CountTable ",
null, sql, null, "select t4 Error");
};
var t4 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] {
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, sc2.Id == sc.Id
}).GroupBy(st => st.Id).Select(st => st.Id).Count();
DateTime?result = DateTime.Now;
var t5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WHERE ( [CreateTime] > @Const0 )",
new List <SugarParameter>()
{
new SugarParameter("@Const0", result.Value.Date)
}, t5.Key, t5.Value, "select t5 Error");
db.Ado.IsEnableLogEvent = false;
var t6 = db.Queryable <DataTestInfo2>().Where(it => SqlFunc.HasValue(it.Bool2) == false).ToSql();
base.Check("SELECT [PK],[Bool1],[Bool2],[Text1] FROM [DataTestInfo2] WHERE (( CASE WHEN ( [Bool2]<>'' AND [Bool2] IS NOT NULL ) THEN 1 ELSE 0 END ) = @Const0 )",
new List <SugarParameter>()
{
new SugarParameter("@Const0", false)
}, t6.Key, t6.Value, "select t6 Error");
#endregion
}
}
public void Q2()
{
using (var db = GetInstance())
{
var t1 = db.Queryable <Student>().ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent]", null, t1.Key, null, "single t1 Error");
var t2 = db.Queryable <Student>().With(SqlWith.NoLock).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WITH(NOLOCK)", null, t2.Key, null, "single t2 Error");
var t3 = db.Queryable <Student>().OrderBy(it => it.Id).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] ORDER BY [ID] ASC", null, t3.Key, null, "single t3 Error");
var t4 = db.Queryable <Student>().OrderBy(it => it.Id).Take(3).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] ASC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 1 AND 3", null, t4.Key, null, "single t4 Error");
var t5 = db.Queryable <Student>().OrderBy(it => it.Id).Skip(3).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] ASC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 4 AND 9223372036854775807", null, t5.Key, null, "single t5 Error");
int pageIndex = 2;
int pageSize = 10;
var t6 = db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] DESC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 11 AND 20", null, t6.Key, null, "single t6 Error");
int studentCount = db.Ado.GetInt("select count(1) from Student");
var countIsSuccess = db.Queryable <Student>().Count() == studentCount;
if (!countIsSuccess)
{
throw new Exception(" single countIsSuccess Error");
}
var t7 = db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToPageList(pageIndex, pageSize, ref studentCount);
countIsSuccess = studentCount == db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize * pageIndex).Count();
if (!countIsSuccess)
{
throw new Exception("single t7 Error");
}
int studentMin = db.Ado.GetInt("select min(id) from Student");
var minIsSuccess = db.Queryable <Student>().Min(it => it.Id) == studentMin;
if (!minIsSuccess)
{
throw new Exception("single minIsSuccess Error");
}
int studentMax = db.Ado.GetInt("select max(id) from Student");
var maxIsSuccess = db.Queryable <Student>().Max(it => it.Id) == studentMax;
if (!maxIsSuccess)
{
throw new Exception("single maxIsSuccess Error");
}
int studentAvg = db.Ado.GetInt("select avg(id) from Student");
var avgIsSuccess = db.Queryable <Student>().Avg(it => it.Id) == studentAvg;
if (!maxIsSuccess)
{
throw new Exception(" single avgIsSuccess Error");
}
int studentSum = db.Ado.GetInt("select sum(id) from Student");
var sumIsSuccess = db.Queryable <Student>().Sum(it => it.Id) == studentSum;
if (!sumIsSuccess)
{
throw new Exception("single sumIsSuccess Error");
}
var t8 = db.Queryable <Student>()
.Where(it => it.Id == 1)
.WhereIF(true, it => SqlFunc.Contains(it.Name, "a"))
.OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).With(SqlWith.NoLock).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] DESC) AS RowIndex FROM [STudent] WITH(NOLOCK) WHERE ( [ID] = @Id0 ) AND ([Name] like '%'+@MethodConst1+'%') ) T WHERE RowIndex BETWEEN 11 AND 20", new List <SugarParameter>()
{
new SugarParameter("@Id0", 1), new SugarParameter("@MethodConst1", "a")
}, t8.Key, t8.Value, "single t8 Error");
var t9 = db.Queryable <Student>()
.In(1)
.Select(it => new { it.Id, it.Name, x = it.Id }).ToSql();
base.Check("SELECT [ID] AS [Id] , [Name] AS [Name] , [ID] AS [x] FROM [STudent] WHERE [Id] IN (@InPara0) ", new List <SugarParameter>()
{
new SugarParameter("@InPara0", 1)
}, t9.Key, t9.Value, "single t9 error");
var t10 = db.Queryable <Student>().Select(it => new StudentEnum()
{
Id = SqlFunc.GetSelfAndAutoFill(it.Id)
}).ToSql();
base.Check("SELECT * FROM [STudent] ", null, t10.Key, t10.Value, "single t10 error");
var t11 = db.Queryable <Student>().GroupBy("id").OrderBy("id").Select("id").ToSql();
base.Check("SELECT id FROM [STudent] GROUP BY id ORDER BY id ", null, t11.Key, t11.Value, "single t11 error");
var t12 = db.Queryable <Student>().Where(it => it.Id != null).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WHERE ( [ID] IS NOT NULL )", null, t12.Key, t12.Value, "single t12 error");
var id = 1;
var t13 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id && s.Id == id).Max(s => s.Id) == 1).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] it WHERE ((SELECT MAX([Id]) FROM [School] WHERE (( [Id] = [it].[ID] ) AND ( [Id] = @Id0 ))) = @Const1 )",
new List <SugarParameter>()
{
new SugarParameter("@Id0", 1),
new SugarParameter("@Const1", 1)
}, t13.Key, t13.Value, "single t13 error ");
var t14 = db.Queryable <Student>()
.Where(it => it.Name == "a" && SqlFunc.HasValue(it.Name)).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WHERE (( [Name] = @Name0 ) AND ( [Name]<>'' AND [Name] IS NOT NULL ))",
new List <SugarParameter>()
{
new SugarParameter("@Name0", "a")
}, t14.Key, t14.Value, "single t14 error ");
var t15 = db.Queryable <CapitalEntity>()
.Select(x => new
{
TGYArea = SqlFunc.AggregateSum(SqlFunc.IIF(x.FlatProp == "1", x.Areas, 0))
}).ToSql();
base.Check("SELECT SUM(( CASE WHEN ( [FlatProp] = @FlatProp0 ) THEN [Areas] ELSE @MethodConst1 END )) AS [TGYArea] FROM [RENT_CAPITAL] ", new List <SugarParameter>()
{
new SugarParameter("@FlatProp0", "1"),
new SugarParameter("@MethodConst1", 0)
}, t15.Key, t15.Value, "single t15 error");
}
}
/// <summary>
/// 按区域拣货
/// </summary>
/// <param name="S"></param>
/// <param name="areaCode"></param>
/// <returns></returns>
public pmw_order RegionalPicking(String[] shopNameArray, string site, string areaCode)
{
return(Common.Config.StartSqlSugar <pmw_order>((db) =>
{
return db.Queryable <pmw_order, pmw_billcode, pmw_wavehouse, pmw_member>((a, b, c, d) => new object[]
{
JoinType.Left, a.order_code == b.order_code,
JoinType.Left, b.stock_area == c.wavehouse_place_name,
JoinType.Left, a.member_id == d.id
})
.Where((a, b, c, d) => SqlFunc.IsNullToInt(a.DoubleCheck) == 1 && SqlFunc.IsNullToInt(a.is_payed) == 1 && SqlFunc.IsNullToInt(a.is_outplace) == 0 && SqlFunc.IsNullToInt(a.Abnormal) == 0 && SqlFunc.IsNullToInt(a.is_task) == 0 && SqlFunc.HasValue(a.order_code))
.Where((a, b, c, d) => SqlFunc.IsNullToInt(b.is_outplace) == 0 && SqlFunc.IsNullToInt(b.is_inplace) == 1 && b.wavehouse == site)
.Where((a, b, c, d) => c.wavehouse_bigarea_name == areaCode)
.Where((a, b, c, d) => SqlFunc.ContainsArray(shopNameArray, d.astro))
.First();
}));
}
public void Q2()
{
using (var db = GetInstance())
{
//db.Database.IsEnableLogEvent = true;
db.Aop.OnLogExecuting = (sql, pars) =>
{
Console.WriteLine(sql + " " + pars);
};
#region dr ot entity
db.IgnoreColumns.Add("TestId", "Student");
var s1 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Name = it.Name, Student = it
}).ToList();
var s2 = db.Queryable <Student>().Select(it => new { id = it.Id, w = new { x = it } }).ToList();
var s3 = db.Queryable <Student>().Select(it => new { newid = it.Id }).ToList();
var s4 = db.Queryable <Student>().Select(it => new { newid = it.Id, obj = it }).ToList();
var s5 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Student = it, Name = it.Name
}).ToList();
#endregion
#region sql and parameters validate
var t1 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Inner, st.Id == sc.Id
}).GroupBy(st => st.Id).Having(st => SqlFunc.AggregateAvg(st.Id) == 1).Select(st => new { avgId = SqlFunc.AggregateAvg(st.Id) }).ToSql();
base.Check("SELECT AVG([st].[ID]) AS [avgId] FROM [STudent] st Inner JOIN [School] sc ON ( [st].[ID] = [sc].[Id] ) GROUP BY [st].[ID] HAVING (AVG([st].[ID]) = @Const0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Const0", 1)
}
,
t1.Key, t1.Value, " select t1 Error");
var t2 = db.Queryable <School, School>((st, st2) => new object[] {
JoinType.Left, st.Id == st2.Id
})
.Where(st => st.Id > 0)
.Select((st, st2) => new { stid = st.Id, scId = st2.Id, xx = st }).ToSql();
base.Check("SELECT [st].[Id] AS [stid] , [st2].[Id] AS [scId] , [st].[Id] AS [School.Id] , [st].[Name] AS [School.Name] FROM [School] st Left JOIN [School] st2 ON ( [st].[Id] = [st2].[Id] ) WHERE ( [st].[Id] > @Id0 ) "
, new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t2.Key, t2.Value, "select t2 Error");
var t3 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] {
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, sc2.Id == sc.Id
}).Where(st => st.Id > 0)
.Select <School>((st) => new School()
{
Id = st.Id
}).ToSql();
base.Check("SELECT [st].[ID] AS [Id] FROM [STudent] st Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] ) Left JOIN [School] sc2 ON ( [sc2].[Id] = [sc].[Id] ) WHERE ( [st].[ID] > @Id0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t3.Key, t3.Value, "select t3 Error");
db.Aop.OnLogExecuting = (sql, pars) =>
{
base.Check(" SELECT COUNT(1) FROM (SELECT [st].[ID] FROM [STudent] st Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] ) Left JOIN [School] sc2 ON ( [sc2].[Id] = [sc].[Id] ) GROUP BY [st].[ID] ) CountTable ",
null, sql, null, "select t4 Error");
};
var t4 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] {
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, sc2.Id == sc.Id
}).GroupBy(st => st.Id).Select(st => st.Id).Count();
DateTime?result = DateTime.Now;
var t5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WHERE ( [CreateTime] > @Const0 )",
new List <SugarParameter>()
{
new SugarParameter("@Const0", result.Value.Date)
}, t5.Key, t5.Value, "select t5 Error");
db.Ado.IsEnableLogEvent = false;
var t6 = db.Queryable <DataTestInfo2>().Where(it => SqlFunc.HasValue(it.Bool2) == false).ToSql();
base.Check("SELECT [PK],[Bool1],[Bool2],[Text1] FROM [DataTestInfo2] WHERE (( CASE WHEN ( [Bool2]<>'' AND [Bool2] IS NOT NULL ) THEN 1 ELSE 0 END ) = @Const0 )",
new List <SugarParameter>()
{
new SugarParameter("@Const0", false)
}, t6.Key, t6.Value, "select t6 Error");
var t7 = db.Queryable <Student>().Select(it => new DataTestInfo2()
{
Bool1 = SqlFunc.IIF(SqlFunc.Subqueryable <Student>().Where(x => x.Id
== it.Id).Any(), true, false)
}).ToSql();
base.Check("SELECT ( CASE WHEN (EXISTS ( SELECT * FROM [STudent] WHERE ( [ID] = [it].[ID] ) )) THEN @MethodConst0 ELSE @MethodConst1 END ) AS [Bool1] FROM [STudent] it ",
new List <SugarParameter>()
{
new SugarParameter("@MethodConst0", true),
new SugarParameter("@MethodConst1", false)
}, t7.Key, t7.Value, "select t7 Error");
#endregion
try
{
var t8 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] {
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, sc2.Id == sc.Id
}).Where(st => st.Id > 0)
.Select <School>((st1) => new School()
{
Id = st1.Id
}).ToList();
}
catch (Exception ex)
{
if (!ex.Message.Contains("English Message : Join st needs to be the same as Select st1"))
{
throw new Exception("selec t8 error");
}
Console.WriteLine(ex.Message);
}
try
{
var t8 = db.Queryable <Student, School>((st, sc) => st.Id == sc.Id).Where(st => st.Id > 0)
.Where(x => x.Id == 1)
.Select <School>((st) => new School()
{
Id = st.Id
}).ToList();
}
catch (Exception ex)
{
if (!ex.Message.Contains("English Message : Join st needs to be the same as Where x"))
{
throw new Exception("selec t8 error");
}
Console.WriteLine(ex.Message);
}
try
{
var t8 = db.Queryable <Student, School>((st, sc) => st.Id == sc.Id)
.Sum(x => x.Id);
}
catch (Exception ex)
{
if (!ex.Message.Contains("English Message : Join st needs to be the same as Sum x"))
{
throw new Exception("selec t8 error");
}
Console.WriteLine(ex.Message);
}
}
}
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);
}
