/// <summary>
/// 获得历史数据
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="vin"></param>
/// <returns></returns>
public async static Task <DataTable> GetHistoryResult(DateTime startTime, DateTime endTime, string vin)
{
try
{
return(await _sqlSugar.Queryable <T_Result>().Where(t => SqlFunc.Between(t.testtime, startTime, endTime))
.WhereIF(!string.IsNullOrEmpty(vin), t => t.vin == vin)
.Select(t => new
{
序号 = t.id,
VIN码 = t.vin,
MTOC码 = t.mtoc,
驱动文件 = t.flashBin,
写入文件 = t.writeBin,
标定文件 = t.calBin,
软件版本 = t.softwareversion,
刷写时间 = t.testtime,
刷写状态 = SqlFunc.IIF(t.teststate == 2, "成功", "失败"),
是否打印 = SqlFunc.IIF(t.isprint == 1, "是", "否"),
追溯码 = t.tracyCode,
刷写端口 = t.num,
}).ToDataTableAsync());
}
catch (Exception ex)
{
string timestr = "yyyy-MM-dd HH:mm:ss";
Log.Error($"查询数据失败！\r\n {startTime.ToString(timestr)},{endTime.ToString(timestr)},{vin}", ex);
}
return(new DataTable());
}
/// <summary>
/// 主播工时 分页信息
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public List <HourModel> GetHourDetailsPage(PageParm parm, ref int totalCount, ref decimal sumAmount, ref decimal sumDuration)
{
var result = new List <HourModel>();
try
{
if (parm == null)
{
parm = new PageParm();
}
Dictionary <string, object> dic = new Dictionary <string, object>();
if (!string.IsNullOrEmpty(parm.where))
{
dic = JsonConvert.DeserializeObject <Dictionary <string, object> >(parm.where);
}
using (var db = GetSqlSugarDB(DbConnType.QPVideoAnchorDB))
{
var query = db.Queryable <SysAnchorLiveRecordEntity, SysAnchor, SysShopAnchorEntity>((it, st, ot) => new object[] {
JoinType.Left, it.aid == st.id,
JoinType.Left, st.id == ot.AnchorID
})
.Where(it => it.ontime >= Convert.ToDateTime(dic["startTime"]) && it.ontime < Convert.ToDateTime(dic["endTime"]))
.WhereIF(dic.ContainsKey("seqid") && !string.IsNullOrEmpty(dic["seqid"].ToString()), it => it.seqid == dic["seqid"].ToString())
.WhereIF(dic.ContainsKey("isLive") && Convert.ToInt32(dic["isLive"]) == 1, it => SqlFunc.IsNullOrEmpty(it.uptime))
.WhereIF(dic.ContainsKey("isLive") && Convert.ToInt32(dic["isLive"]) == 0, it => !SqlFunc.IsNullOrEmpty(it.uptime))
.WhereIF(dic.ContainsKey("Name") && !string.IsNullOrEmpty(dic["Name"].ToString()), (it, st) => st.anchorName.Contains(dic["Name"].ToString()) || st.nickName.Contains(dic["Name"].ToString()))
.WhereIF(dic.ContainsKey("ShopID") && Convert.ToInt32(dic["ShopID"]) != -1, (it, st, ot) => ot.ShopID == Convert.ToInt32(dic["ShopID"]))
; //缓存30秒
var sumReuslt = query.Clone().Select((it, st) => new { amount = SqlFunc.AggregateSum(it.amount), duration = SqlFunc.AggregateSum(it.livetime) }).WithCache(30).First();
sumAmount = sumReuslt.amount;
sumDuration = sumReuslt.duration;
return(query
.Select((it, st, ot) => new HourModel
{
seqid = it.seqid,
AnchorName = st.anchorName,
NickName = st.nickName,
begintime = it.ontime,
endtime = it.uptime,
duration = it.livetime,
islive = SqlFunc.IIF(SqlFunc.IsNullOrEmpty(it.uptime), 1, 0),
flvurl = it.flvurl,
status = it.status
})
.WithCache(30)
.OrderBy(" it.ontime desc")
.ToPageList(parm.page, parm.limit, ref totalCount));
}
}
catch (Exception ex)
{
new LogLogic().Write(Level.Error, "主播工时 分页信息", ex.Message, ex.StackTrace);
}
return(result);
}
/// <summary>
/// 超管查看主播信息
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="keyWord"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <SysAnchor> UserSelectAnchorList(PageParm parm, ref int totalCount)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
if (!string.IsNullOrEmpty(parm.where))
{
dic = JsonConvert.DeserializeObject <Dictionary <string, object> >(parm.where);
}
var result = new List <SysAnchor>();
try
{
//lmstatus 连麦状态 live 直播 offline 离线 normal正常 kickline踢线 disabled禁用
//statu 正常unlock 禁用 lock 审核中 audit
using (var db = GetInstance())
{
result = db.Queryable <SysAnchor, SysAnchorInfoEntity, SysShopAnchorEntity, SysShopEntity>((it, st, ot, dt) => new object[] {
JoinType.Left, it.id == st.aid,
JoinType.Left, it.id == ot.AnchorID,
JoinType.Left, ot.ShopID == dt.ID
})
.WhereIF(dic.ContainsKey("Name") && !string.IsNullOrEmpty(dic["Name"].ToString()), (it) => it.anchorName.Contains(dic["Name"].ToString()) || it.nickName.Contains(dic["Name"].ToString()))
.WhereIF(dic.ContainsKey("startTime") && !string.IsNullOrEmpty(dic["startTime"].ToString()) && dic.ContainsKey("endTime") && !string.IsNullOrEmpty(dic["endTime"].ToString()), (it) => it.createTime >= Convert.ToDateTime(dic["startTime"]) && it.createTime <= Convert.ToDateTime(dic["endTime"]))
.WhereIF(dic.ContainsKey("Status") && Convert.ToInt32(dic["Status"]) != -1, (it, st) => st.status == (AnchorStatusEnum)Convert.ToInt32(dic["Status"]))
.WhereIF(dic.ContainsKey("isColletCode") && !string.IsNullOrEmpty(dic["isColletCode"].ToString()), (it, st) => it.isColletCode == dic["isColletCode"].ToString())
.WhereIF(dic.ContainsKey("ShopID") && Convert.ToInt32(dic["ShopID"]) != -1, (it, st, ot) => ot.ShopID == Convert.ToInt32(dic["ShopID"]))
.Select((it, st, ot, dt) => new SysAnchor
{
id = it.id,
anchorName = it.anchorName,
nickName = it.nickName,
headUrl = SqlFunc.IIF(it.headUrl.Contains("http"), it.headUrl, Image_CDN + it.headUrl),
balance = st.agentGold,
follow = st.follow,
birthday = it.birthday,
status = st.status,
createTime = it.createTime,
isColletCode = it.isColletCode,
shopName = dt.Name,
sort = it.sort
})
.OrderBy(" st.agentGold desc")
.ToPageList(parm.page, parm.limit, ref totalCount);
}
}
catch (Exception ex)
{
new LogLogic().Write(Level.Error, "超管查看主播信息", ex.Message, ex.StackTrace);
}
return(result);
}
private void IIF4()
{
Expression <Func <DataTestInfo2, bool> > exp = it => SqlFunc.IIF(true, it.Bool1, it.Bool2) == true;
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "(( CASE WHEN ( 1 = 1 ) THEN [Bool1] ELSE [Bool2] END ) = @Const0 )", new List <SugarParameter>()
{
new SugarParameter("@Const0", true)
}, "IIF4 error");
}
private void IIF5()
{
Expression <Func <DataTestInfo, bool> > exp = it => SqlFunc.IIF(true, Convert.ToBoolean(it.Datetime1), SqlFunc.ToBool(it.Datetime1)) == false;
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "(( CASE WHEN ( 1 = 1 ) THEN CAST([Datetime1] AS BIT) ELSE CAST([Datetime1] AS BIT) END ) = @Const0 )", new List <SugarParameter>()
{
new SugarParameter("@Const0", false)
}, "IIF5 error");
}
private static void Demo3(SqlSugarClient db)
{
db.CodeFirst.InitTables <operateinfo>();
db.Deleteable <operateinfo>().ExecuteCommand();
db.Insertable(new operateinfo()
{
id = 1,
operate_type = 1,
operate_time = Convert.ToDateTime("2021-1-1")
}).ExecuteCommand();
db.Insertable(new operateinfo()
{
id = 1,
operate_type = 1,
operate_time = Convert.ToDateTime("2021-1-2")
}).ExecuteCommand();
db.Insertable(new operateinfo()
{
id = 1,
operate_type = 1,
operate_time = Convert.ToDateTime("2021-3-1")
}).ExecuteCommand();
db.Insertable(new operateinfo()
{
id = 1,
operate_type = 1,
operate_time = Convert.ToDateTime("2021-3-2")
}).ExecuteCommand();
db.Insertable(new operateinfo()
{
id = 1,
operate_type = 1,
operate_time = Convert.ToDateTime("2021-4-2")
}).ExecuteCommand();
var queryableLeft = db.Reportable(ReportableDateType.MonthsInLast1years).ToQueryable <DateTime>();
var queryableRight = db.Queryable <operateinfo>();
var list = db.Queryable(queryableLeft, queryableRight, JoinType.Left,
(x1, x2) => x2.operate_time.ToString("yyyy-MM") == x1.ColumnName.ToString("yyyy-MM"))
.GroupBy((x1, x2) => x1.ColumnName)
.Where(x1 => SqlFunc.Between(x1.ColumnName, "2021-01-01", DateTime.Now))
.Select((x1, x2) => new
{
count = SqlFunc.AggregateSum(SqlFunc.IIF(x2.id > 0, 1, 0)),
date = x1.ColumnName.ToString("yyyy-MM")
}).ToList();
}
/// <summary>
/// 保存到本地数据库
/// </summary>
/// <param name="car"></param>
public async static Task <int> SaveLocalResult(string element, bool state, string vin, string driver, string write, string cal)
{
try
{
var configs = await _sqlSugar.Queryable <T_VCUConfig, T_MTOC>((v, m) => new JoinQueryInfos(JoinType.Inner, v.mtoc == m.mtoc))
.Where((v, m) => m.vin == vin).Select((v, m) => new T_Result
{
vin = vin,
mtoc = v.mtoc,
flashBin = v.drivername,
writeBin = v.binname,
calBin = v.calname,
softwareversion = v.softwareversion,
testtime = SqlFunc.GetDate(),
teststate = SqlFunc.IIF(state == true, 2, 1),
isprint = SqlFunc.IIF(state == true, 1, 0),
tracyCode = element,
sign = v.sign
}).FirstAsync();
if (configs == null)
{
configs = new T_Result
{
flashBin = driver,
writeBin = write,
calBin = cal,
teststate = SqlFunc.IIF(state == true, 2, 1),
isprint = SqlFunc.IIF(state == true, 1, 0),
};
}
var tmpK = await _sqlSugar.Insertable(configs).ExecuteCommandAsync();
if (tmpK > 0)
{
Log.Info("保持到本地数据库成功!");
}
return(tmpK);
}
catch (Exception ex)
{
Log.Error($"修改记录失败！\r\n element={element},state={state},vin={vin},driver={driver},write={write},cal={cal}", ex);
}
return(0);
}
private void IIF2()
{
Expression <Func <Student, bool> > exp = it => SqlFunc.IIF(SqlFunc.Contains(it.Name, "a"), 1, 2) == 1;
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "(( CASE WHEN ([Name] like '%'+@MethodConst0+'%') THEN @MethodConst1 ELSE @MethodConst2 END ) = @Const3 )", new List <SugarParameter>()
{
new SugarParameter("@MethodConst0", "a"),
new SugarParameter("@MethodConst1", 1),
new SugarParameter("@MethodConst2", 2),
new SugarParameter("@Const3", 1)
}, "IIF2 error");
}
private void IIF()
{
Expression <Func <Student, bool> > exp = it => SqlFunc.IIF(it.Id == 1, 1, 2) == 1;
MySqlExpressionContext expContext = new MySqlExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "(( CASE WHEN ( `Id` = @Id0 ) THEN @MethodConst1 ELSE @MethodConst2 END ) = @Const3 )", new List <SugarParameter>()
{
new SugarParameter("@Id0", 1),
new SugarParameter("@MethodConst1", 1),
new SugarParameter("@MethodConst2", 2),
new SugarParameter("@Const3", 1)
}, "IIF error");
}
private void IIF6()
{
var dt = DateTime.Now.Date;
Expression <Func <DataTestInfo, bool> > exp = it => SqlFunc.IIF(dt == it.Datetime1, it.Datetime1, it.Datetime1) == dt;
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.MappingColumns = new MappingColumnList();
expContext.MappingColumns.Add("Datetime1", "Datetime2", "DataTestInfo");
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "(( CASE WHEN ( @Datetime10 = [Datetime2] ) THEN [Datetime2] ELSE [Datetime2] END ) = @Const1 )", new List <SugarParameter>()
{
new SugarParameter("@Datetime10", dt),
new SugarParameter("@Const1", dt)
}, "IIF6 error");
}
/// <summary>
/// 商户获取 拥有的主播分页
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="keyWord"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <SysAnchor> GetShopAnchorList(int pageIndex, int pageSize, string keyWord, ref int totalCount)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
if (!string.IsNullOrEmpty(keyWord))
{
dic = JsonConvert.DeserializeObject <Dictionary <string, object> >(keyWord);
}
var result = new List <SysAnchor>();
try
{
//lmstatus 连麦状态 live 直播 offline 离线 disabled禁用 normal正常 kickline踢线
//statu 正常unlock 禁用 lock 审核中 audit
using (var db = GetInstance())
{
result = db.Queryable <SysShopAnchorEntity, SysAnchor, SysAnchorInfoEntity>((st, it, at) => new object[] { JoinType.Left, st.AnchorID == it.id, JoinType.Left, it.id == at.aid })
.WhereIF(dic.ContainsKey("anchorUserName") && !string.IsNullOrEmpty(dic["anchorUserName"].ToString()), (st, it) => it.anchorName.Contains(dic["anchorUserName"].ToString()) || it.nickName.Contains(dic["anchorUserName"].ToString()))
.WhereIF(dic.ContainsKey("userID") && !string.IsNullOrEmpty(dic["userID"].ToString()), (st, it) => st.ShopID == Convert.ToInt32(dic["userID"]))
//.WhereIF(dic.ContainsKey("isCollet") && Convert.ToInt32(dic["isCollet"]) != -1, (st, it) => it.isCollet == Convert.ToInt32(dic["isCollet"]))
.WhereIF(dic.ContainsKey("isColletCode") && !string.IsNullOrEmpty(dic["isColletCode"].ToString()), (st, it) => it.isColletCode == dic["isColletCode"].ToString())
.Select((st, it, at) => new SysAnchor
{
id = it.id,
anchorName = it.anchorName,
nickName = it.nickName,
headUrl = SqlFunc.IIF(it.headUrl.Contains("http"), it.headUrl, Image_CDN + it.headUrl),
balance = at.agentGold,
follow = at.follow,
birthday = it.birthday,
status = at.status,
createTime = it.createTime,
isColletCode = it.isColletCode,
}).ToPageList(pageIndex, pageSize, ref totalCount);
}
}
catch (Exception ex)
{
new LogLogic().Write(Level.Error, "商户获取 拥有的主播分页", ex.Message, ex.StackTrace);
}
return(result);
}
public string GetRoleAuditFlow(int roleid)
{
MessageModel <List <RoleAuditModel> > ramodel = new MessageModel <List <RoleAuditModel> >();
try
{
//(join1, join2) => new object[] { JoinType.Left, join1.UserNo == join2.UserNo }
var modellist = _rabll.QueryMuch <TB_AuditFlow, TB_RoleAudit, RoleAuditModel>((r1, r2) => new object[] { JoinType.Left, r1.AuditFlowID == r2.R_AuditID && r2.R_RoleID == roleid && r2.IsAudit > 0 },
(r1, r2) => new RoleAuditModel()
{
FlowID = r1.AuditFlowID, FlowName = r1.F_Name, LAY_CHECKED = SqlFunc.IIF(r2.IsAudit == 1, true, false), Serial = r1.Sort
});
if (modellist.Count > 0)
{
ramodel.data = modellist;
ramodel.code = System.Net.HttpStatusCode.OK;
ramodel.msg = "获取成功";
ramodel.success = true;
}
else
{
ramodel.data = null;
ramodel.code = System.Net.HttpStatusCode.OK;
ramodel.msg = "暂无数据";
ramodel.success = true;
}
}
catch (Exception ex)
{
ramodel.data = null;
ramodel.code = System.Net.HttpStatusCode.InternalServerError;
ramodel.msg = ex.Message;
ramodel.success = false;
}
return(Newtonsoft.Json.JsonConvert.SerializeObject(ramodel));
}
public static void Easy()
{
var db = GetInstance();
var getAll = db.Queryable <Student>().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>().Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Sum(it => it.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
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();
}
public static void Queryable2()
{
var list4 = Db.Queryable <ABMapping>()
.Mapper(it => it.A, it => it.AId)
.Where(it => it.A.Name == "a")
.ToList();
var list5 = Db.Queryable <ABMapping>()
.Mapper(it => it.A, it => it.AId, it => it.A.Id)
.Where(it => it.A.Name == "a")
.ToList();
var list3 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Count() > 0)
.ToList();
var list6 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Any())
.ToList();
var list7 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Any(y => y.ItemId == 1))
.ToList();
var sql = Db.Queryable <Order>().AS("[order]").ToList();
var sql1 = Db.Queryable <Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, c.Id == o.CustomId
))
.AS("[aa]")
.AS <OrderItem>("[xx]")
.AS <Custom>("[yy]")
.Select <ViewOrder>().ToSql().Key;
if (!sql1.Contains("[aa]") || !sql1.Contains("[xx]") || !sql1.Contains("[yy]"))
{
throw new Exception("unit queryable2 ");
}
var sql2 = Db.Queryable <OrderItem>().AS("[zz]").ToSql().Key;
if (sql2 != "SELECT [ItemId],[OrderId],[Price],[CreateTime] FROM [zz] ")
{
throw new Exception("unit queryable2 ");
}
Db.Queryable <Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, c.Id == o.CustomId
))
.AS("[order]")
.AS <OrderItem>("[orderdetail]")
.AS <Custom>("[custom]")
.Select <ViewOrder>().ToList();
Db.Queryable <object>().AS("[order]").Select("*").ToList();
var qu1 = Db.Queryable <Order>().Select(it => new
{
id = it.Id
}).MergeTable().Select <Order>();
var qu2 = Db.Queryable <Order>().Select(it => new
{
id = it.Id,
name = it.Name
}).MergeTable().Select <Order>();
var list = Db.Queryable(qu1, qu2, JoinType.Left, (x, y) => x.Id == y.Id).Select((x, y) => new
{
id1 = x.Id,
name = y.Name
}).ToList();
var qu3 = Db.Queryable <Order>().Select(it => new
{
id = it.Id,
name = it.Name
}).MergeTable()
.Where(it => 2 > it.id).Select(it => new Order()
{
Id = SqlFunc.IIF(2 > it.id, 1, 2)
}).ToList();
var qu4 = Db.Queryable <Order>().OrderBy(it => it.Id + it.Id).ToList();
}
/// <summary>
/// 修改当前检测VCU的状态
/// </summary>
/// <param name="state"></param>
public async static Task <int> ChangeState(bool result, string vin)
{
string state = (result == true) ? "2" : "1";
try
{
int tmpK = await _sqlSugar.Updateable <T_MTOC>().SetColumns(t => t.state == SqlFunc.IIF(result == true, 2, 1)).Where(t => t.vin == vin).ExecuteCommandAsync();
if (tmpK > 0)
{
Log.Info(vin + "更新写入状态" + state + "成功!");
}
return(tmpK);
}
catch (Exception ex)
{
Log.Error($"修改记录失败！\r\n result={result},vin={vin}", ex);
}
return(0);
}
public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlSugar.SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT [Dc] AS [DcNull] , [Int] AS [Dc] FROM [UnitSelectTest]", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE [UnitSelectTest2] SET
[Dc]=@Dc,[IntNull]=@IntNull WHERE [Int]=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT [Id],[Name],[Price],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT [Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT [Name],[Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlSugar.SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
var ids = Enumerable.Range(1, 11).ToList();
var list8 = Db.Queryable <Order>().Where(it => SqlFunc.ContainsArrayUseSqlParameters(ids, it.Id)).ToList();
var result2 = Db.Queryable <Unit_SYS_USER>().Where(o => o.XH == UserLoginInfo.XH).Select(o => o.XH).ToSql();
var x = Db.Queryable <BoolTest1>().Select(it => new BoolTest2()
{
a = it.a
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest1] ", "Queryable");
x = Db.Queryable <BoolTest2>().Select(it => new BoolTest1()
{
a = it.a.Value
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest2] ", "Queryable");
var db = Db;
db.CodeFirst.InitTables <UserInfo, UserIpRuleInfo>();
db.Deleteable <UserInfo>().ExecuteCommand();
db.Deleteable <UserIpRuleInfo>().ExecuteCommand();
db.Insertable(new UserInfo()
{
Id = 1,
Password = "******",
UserName = "******"
}).ExecuteCommand();
db.Insertable(new UserIpRuleInfo()
{
Addtime = DateTime.Now,
UserName = "******",
Id = 11,
UserId = 1,
Description = "xx",
IpRange = "1",
RuleType = 1
}).ExecuteCommand();
var vmList = db.Queryable <UserInfo, UserIpRuleInfo>(
(m1, m2) => m1.Id == m2.UserId
).Where((m1, m2) => m1.Id > 0).Select((m1, m2) => new UserIpRuleInfo()
{
IpRange = m2.IpRange,
Addtime = m2.Addtime,
RuleType = m2.RuleType,
}).ToList();
if (string.IsNullOrEmpty(vmList.First().IpRange))
{
throw new Exception("Queryable");
}
Db.Insertable(new Order()
{
CreateTime = DateTime.Now, CustomId = 1, Name = "a", Price = 1
}).ExecuteCommand();
var sa = Db.SqlQueryable <Order>("SELECT * FroM [ORDER] where id in (@id) ");
sa.AddParameters(new List <SugarParameter>()
{
new SugarParameter("id", new int[] { 1 })
});
int i = 0;
var salist = sa.ToPageList(1, 2, ref i);
db.CodeFirst.InitTables <UnitBytes11>();
db.Insertable(new UnitBytes11()
{
bytes = null, name = "a"
}).ExecuteCommand();
db.Insertable(new UnitBytes11()
{
bytes = new byte[] { 1, 2 }, name = "a"
}).ExecuteCommand();
var bytes = db.Queryable <UnitBytes11>().Select(it => new
{
b = it.bytes,
name = "a"
}).ToList();
var bytes2 = db.Queryable <UnitBytes11>().Select(it => new
{
b = it
}).ToList();
db.CodeFirst.InitTables <BoolTest1>();
db.CodeFirst.InitTables <BoolTest2>();
db.Queryable <BoolTest1>().Where(it => !it.a).ToList();
var test01 = db.Queryable <SaleOrder>().GroupBy(it => new { it.CheckTime.Value.Date })
.Select(it => new { x = it.CheckTime.Value.Date }).ToList();
var q1 = db.Queryable <BoolTest1>();
var x1 = q1.Clone().AS("BoolTest11");
var x2 = q1.Clone().AS("BoolTest12");
var q2 = db.UnionAll(x1, x2).ToSql();
if (!q2.Key.Contains("BoolTest11") || !q2.Key.Contains("BoolTest12"))
{
throw new Exception("unit query error");
}
db.Queryable <Order>().Where(it => SqlFunc.Round(it.Id, 2) == SqlFunc.Abs(it.Id)).ToList();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-1-1"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-1-9"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-9-11"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-11-30"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
var d1 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 1 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d1.Last() != "2021-01-01", "unit error");
var d11 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 9 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d11.Last() != "2021-01-09", "unit error");
var d111 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 11 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d111.Last() != "2021-09-11", "unit error");
var d1111 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 30 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d1111.Last() != "2021-11-30", "unit error");
var d11111 = db.Queryable <Order>()
.Where(it => it.CreateTime.ToString("yyyy-MM-dd") == "2021-11-30")
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d11111.Last() != "2021-11-30", "unit error");
db.CodeFirst.InitTables <UnitEnumadfa>();
db.Insertable(new UnitEnumadfa()).ExecuteCommand();
db.Insertable(new UnitEnumadfa()
{
Type = DbType.Sqlite
}).ExecuteCommand();
var listEnum = db.Queryable <UnitEnumadfa>().ToList();
var d111111 = db.Queryable <Order>()
.Where(it => it.Id == SqlFunc.IF(true).Return(1).End(0))
.ToList();
var d1111111 = db.Queryable <Order>()
.Where(it => it.Id == SqlFunc.IF(it.Id > 0).Return(1).End(0))
.ToList();
var d11111111 = db.Queryable <Order>()
.Where(it => it.Id == (it.Id > 0? (it.Id == 1?11:1):2))
.ToList();
var d111111111 = db.Queryable <Order>()
.Where(it => it.Id == (it.Id > 0 ? (it.Id == 1 ? 11 : (it.Id == 2?2:1)) : 2))
.ToList();
bool?bq = true;
var d1111111111 = db.Queryable <BoolTest1>().Where(it => it.a.Equals(bq.Value)).ToArray();
var d11111111111 = db.Queryable <BoolTest1>().Where(it => SqlFunc.IIF(bq.Value, 1, 2) == 1).ToArray();
var d111111111111 = db.Queryable <BoolTest1>().Select(it => new { x = SqlFunc.IsNull(it.a, false) }).ToArray();
db.CodeFirst.InitTables <SqlSugarDemo.UserEntity, SqlSugarDemo.RoleEntity, SqlSugarDemo.UserRoleEntity>();
var data = new SqlSugarDemo.UserEntity()
{
CardNo = "",
CompanyWX = "",
Credential = "",
EmailAccount = "",
EndDate = DateTime.Now,
FailedLoginPwdCount = 1,
IsChangePassword = true,
IsReal = 1,
LastLoginDate = DateTime.Now,
ManageAccount = Guid.NewGuid(),
ManageOrg = Guid.NewGuid(),
NickName = "",
PhoneAccount = "",
RealName = "",
VerificationLoginPwdDate = DateTime.Now,
SafePhone = "",
Sex = 1,
StartDate = DateTime.Now,
StopLoginTime = DateTime.Now,
UserAccount = "",
UserId = Guid.NewGuid(),
UserType = 1
};
db.Insertable(data).ExecuteCommand();
//var role = new SqlSugarDemo.RoleEntity()
//{
// RoleId=Guid.NewGuid(),
// ManageAccount= Guid.NewGuid(),
// ManageOrg=Guid.NewGuid(),
// OrganizationId=Guid.NewGuid(),
// UnitPrice=1,
// Quantity=1,
// RoleName="",
// RoleType=1,
// SortNum=1
//};
//db.Insertable(role).ExecuteCommand();
//db.Insertable(new SqlSugarDemo.UserRoleEntity()
//{
// RoleId= role.RoleId,
// UserId=data.UserId
//}).ExecuteCommand();
var d1111111111111 = db.Queryable <SqlSugarDemo.UserEntity>()
.Mapper <SqlSugarDemo.UserEntity, SqlSugarDemo.RoleEntity, SqlSugarDemo.UserRoleEntity>(it => ManyToMany.Config(it.UserId, it.RoleId)).InSingle(data.UserId);
}
public static void Init()
{
var db = GetInstance();
var updateObj = new Student()
{
Id = 1, Name = "jack", SchoolId = 0, CreateTime = Convert.ToDateTime("2017-05-21 09:56:12.610")
};
var updateObjs = new List <Student>()
{
updateObj, new Student()
{
Id = 2, Name = "sun", SchoolId = 0
}
}.ToArray();
db.IgnoreColumns.Add("TestId", "Student");
//db.MappingColumns.Add("id","dbid", "Student");
//update reutrn Update Count
var t1 = db.Updateable(updateObj).ExecuteCommand();
//Only update Name
var t3 = db.Updateable(updateObj).UpdateColumns(it => new { it.Name }).ExecuteCommand();
var t3_1 = db.Updateable(updateObj).UpdateColumns(it => it == "Name").ExecuteCommand();
//Ignore Name and TestId
var t4 = db.Updateable(updateObj).IgnoreColumns(it => new { it.Name, it.TestId }).ExecuteCommand();
//Ignore Name and TestId
var t5 = db.Updateable(updateObj).IgnoreColumns(it => it == "Name" || it == "TestId").With(SqlWith.UpdLock).ExecuteCommand();
//Use Lock
var t6 = db.Updateable(updateObj).With(SqlWith.UpdLock).ExecuteCommand();
//update List<T>
var t7 = db.Updateable(updateObjs).ExecuteCommand();
//Re Set Value
var t8 = db.Updateable(updateObj)
.ReSetValue(it => it.Name == (it.Name + 1)).ExecuteCommand();
//Where By Expression
var t9 = db.Updateable(updateObj).Where(it => it.Id == 1).ExecuteCommand();
//Update By Expression Where By Expression
var t10 = db.Updateable <Student>()
.UpdateColumns(it => new Student()
{
Name = "a", CreateTime = DateTime.Now
})
.Where(it => it.Id == 11).ExecuteCommand();
//Rename
db.Updateable <School>().AS("Student").UpdateColumns(it => new School()
{
Name = "jack"
}).Where(it => it.Id == 1).ExecuteCommand();
//Update Student set Name='jack' Where Id=1
//Column is null no update
db.Updateable(updateObj).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
//sql
db.Updateable(updateObj).Where("id=@x", new { x = "1" }).ExecuteCommand();
db.Updateable(updateObj).Where("id", "=", 1).ExecuteCommand();
var t12 = db.Updateable <School>().AS("Student").UpdateColumns(it => new School()
{
Name = "jack"
}).Where(it => it.Id == 1).ExecuteCommandAsync();
t12.Wait();
//update one columns
var count = db.Updateable <Student>().UpdateColumns(it => it.SchoolId == it.SchoolId).Where(it => it.Id == it.Id + 1).ExecuteCommand();
var count1 = db.Updateable <Student>()
.UpdateColumnsIF(false, it => it.SchoolId == it.SchoolId) //ignore
.UpdateColumnsIF(true, it => it.SchoolId == 2). //ok
Where(it => it.Id == it.Id + 1).ExecuteCommand();
//update one columns
var count2 = db.Updateable <Student>().UpdateColumns(it => it.SchoolId == it.SchoolId + 1).Where(it => it.Id == it.Id + 1).ExecuteCommand();
var dt = new Dictionary <string, object>();
dt.Add("id", 1);
dt.Add("name", null);
dt.Add("createTime", DateTime.Now);
var t66 = db.Updateable(dt).AS("student").WhereColumns("id").With(SqlWith.UpdLock).ExecuteCommand();
var dt2 = new Dictionary <string, object>();
dt2.Add("id", 2);
dt2.Add("name", null);
dt2.Add("createTime", DateTime.Now);
var dtList = new List <Dictionary <string, object> >();
dtList.Add(dt);
dtList.Add(dt2);
var t666 = db.Updateable(dtList).AS("student").WhereColumns("id").With(SqlWith.UpdLock).ExecuteCommand();
var t20 = db.Updateable <Student>().UpdateColumns(p => new Student()
{
SchoolId = SqlFunc.IIF(p.Id == 1, 2, 3)
}).Where(p => p.Id == 10000).ExecuteCommand();
var t21 = db.Updateable <Student>().UpdateColumns(p => new Student()
{
SchoolId = SqlFunc.IF(p.Id == 1).Return(1).End(p.Id)
}).Where(p => p.Id == 10000).ExecuteCommand();
var t22 = db.Updateable <Student>().UpdateColumns(p => new Student()
{
SchoolId = SqlFunc.Subqueryable <Student>().Where(s => s.SchoolId == p.Id).Select(s => s.Id)
}).Where(p => p.Id == 10000).ExecuteCommand();
var t23 = db.Updateable <Student>(new Student()
{
})
.Where(p => p.SchoolId == SqlFunc.Subqueryable <Student>().Where(s => s.SchoolId == p.Id).Select(s => s.Id)).ExecuteCommand();
var t24 = db.Updateable(new Student()
{
}).WhereColumns(it => it.CreateTime).ExecuteCommand();
var t25 = db.Updateable(new Student()
{
}).UpdateColumns(it => new { it.Name, it.CreateTime }).WhereColumns(it => it.CreateTime).ExecuteCommand();
var t26 = db.Updateable(new List <Student>()
{
new Student()
{
}, new Student()
{
}
}).UpdateColumns(it => new { it.Name, it.CreateTime }).WhereColumns(it => it.CreateTime).ExecuteCommand();
db.Updateable <Student>().UpdateColumns(it => new Student {
SchoolId = GeneratePassword(2, 1), Name = SqlFunc.ToString(it.Name), CreateTime = DateTime.Now.AddDays(1)
}).Where(it => it.Id == 1).ExecuteCommand();
}
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);
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().ToList();
var getTop2 = db.Queryable <Student>().Take(2).ToList();//TOP2
var getLike = db.Queryable <Student>().Where(it => it.Name.Contains("a")).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 == 2).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Sum(it => it.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var asCount = db.Queryable <object>().AS("student").Count();
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 unionAll = db.UnionAll <Student>(db.Queryable <Student>(), db.Queryable <Student>());
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 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();
}
public static void Queryable2()
{
var list4 = Db.Queryable <ABMapping>()
.Mapper(it => it.A, it => it.AId)
.Where(it => it.A.Name == "a")
.ToList();
var list5 = Db.Queryable <ABMapping>()
.Mapper(it => it.A, it => it.AId, it => it.A.Id)
.Where(it => it.A.Name == "a")
.ToList();
var list3 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Count() > 0)
.ToList();
var list6 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Any())
.ToList();
var list7 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Any(y => y.ItemId == 1))
.ToList();
var sql = Db.Queryable <Order>().AS("[order]").ToList();
var sql1 = Db.Queryable <Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, c.Id == o.CustomId
))
.AS("[aa]")
.AS <OrderItem>("[xx]")
.AS <Custom>("[yy]")
.Select <ViewOrder>().ToSql().Key;
if (!sql1.Contains("[aa]") || !sql1.Contains("[xx]") || !sql1.Contains("[yy]"))
{
throw new Exception("unit queryable2 ");
}
var sql2 = Db.Queryable <OrderItem>().AS("[zz]").ToSql().Key;
if (sql2 != "SELECT [ItemId],[OrderId],[Price],[CreateTime] FROM [zz] ")
{
throw new Exception("unit queryable2 ");
}
Db.Queryable <Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, c.Id == o.CustomId
))
.AS("[order]")
.AS <OrderItem>("[orderdetail]")
.AS <Custom>("[custom]")
.Select <ViewOrder>().ToList();
Db.Queryable <object>().AS("[order]").Select("*").ToList();
var qu1 = Db.Queryable <Order>().Select(it => new
{
id = it.Id
}).MergeTable().Select <Order>();
var qu2 = Db.Queryable <Order>().Select(it => new
{
id = it.Id,
name = it.Name
}).MergeTable().Select <Order>();
var list = Db.Queryable(qu1, qu2, JoinType.Left, (x, y) => x.Id == y.Id).Select((x, y) => new
{
id1 = x.Id,
name = y.Name
}).ToList();
var qu3 = Db.Queryable <Order>().Select(it => new
{
id = it.Id,
name = it.Name
}).MergeTable()
.Where(it => 2 > it.id).Select(it => new Order()
{
Id = SqlFunc.IIF(2 > it.id, 1, 2)
}).ToList();
var qu4 = Db.Queryable <Order>().OrderBy(it => it.Id + it.Id).ToList();
var list11 = Db.Queryable <A>()
.ToList();
var list8 = Db.Queryable <A>()
.Mapper <A, B, ABMapping>(it => ManyToMany.Config(it.AId, it.BId))
.ToList();
Db.CodeFirst.InitTables <ABMap, TableA, TableB>();
Db.DbMaintenance.TruncateTable("TableA");
Db.DbMaintenance.TruncateTable("Tableb");
Db.DbMaintenance.TruncateTable("ABMap");
Db.Insertable(new TableA()
{
id = 1, Name = "A1"
}).ExecuteCommand();
Db.Insertable(new TableA()
{
id = 2, Name = "A1"
}).ExecuteCommand();
Db.Insertable(new TableB()
{
id = 1, Name = "B1"
}).ExecuteCommand();
Db.Insertable(new TableB()
{
id = 2, Name = "B2"
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 1, Bid = 1
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 1, Bid = 2
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 2, Bid = 1
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 2, Bid = 2
}).ExecuteCommand();
var list9 = Db.Queryable <TableA>()
.Mapper <TableA, TableB, ABMap>(it => ManyToMany.Config(it.Aid, it.Bid)).ToList();
}
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");
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
}
}
private static void Subqueryable()
{
var db = GetInstance();
var i = 0;
var sumflat2num = db.Queryable <Student, Student>((s1, s2) =>
new object[] { JoinType.Left, s1.Id == s2.Id })
.Select((s1, s2) => new Student
{
Id = SqlFunc.IsNull(SqlFunc.AggregateSum(SqlFunc.IIF(s1.Id == 1, s1.Id, s1.Id * -1)), 0)
})
.First();
var getAll11 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Max(s => s.Id) == i).ToList();
var getAll12 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Max(s => s.Id) == 1).ToList();
var getAll7 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Any()).ToList();
var getAll9 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Count() == 1).ToList();
var getAll10 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).OrderBy(s => s.Id).Select(s => s.Id) == 1).ToList();
var getAll14 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).OrderByDesc(s => s.Id).Select(s => s.Id) == 1).ToList();
var getAll8 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Where(s => s.Name == it.Name).NotAny()).ToList();
var getAll1 = db.Queryable <Student>().Where(it => it.Id == SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Select(s => s.Id)).ToList();
var getAll2 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Left, st.Id == sc.Id
})
.Where(st => st.Id == SqlFunc.Subqueryable <School>().Where(s => s.Id == st.Id).Select(s => s.Id))
.ToList();
var getAll3 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Left, st.Id == sc.Id
})
.Select(st =>
new
{
name = st.Name,
id = SqlFunc.Subqueryable <School>().Where(s => s.Id == st.Id).Select(s => s.Id)
})
.ToList();
var getAll4 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Select(s => s.Id)
}).ToList();
var getAll5 = db.Queryable <Student>().Select(it =>
new Student
{
Name = it.Name,
Id = SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Select(s => s.Id)
}).ToList();
var getAll6 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <Student>().Where(s => s.Id == it.Id).Sum(s => (int)s.SchoolId)
}).ToList();
var getAll66 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <Student>().Where(s => s.Id == it.Id).Sum(s => s.SchoolId.Value)
}).ToList();
var getAll666 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <Student>().Where(s => s.Id == it.Id).Min(s => s.Id)
}).ToList();
}
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();
}
public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlSugar.SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT [Dc] AS [DcNull] , [Int] AS [Dc] FROM [UnitSelectTest]", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE [UnitSelectTest2] SET
[Dc]=@Dc,[IntNull]=@IntNull WHERE [Int]=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT [Id],[Name],[Price],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT [Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT [Name],[Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlSugar.SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
var ids = Enumerable.Range(1, 11).ToList();
var list8 = Db.Queryable <Order>().Where(it => SqlFunc.ContainsArrayUseSqlParameters(ids, it.Id)).ToList();
var result2 = Db.Queryable <Unit_SYS_USER>().Where(o => o.XH == UserLoginInfo.XH).Select(o => o.XH).ToSql();
var x = Db.Queryable <BoolTest1>().Select(it => new BoolTest2()
{
a = it.a
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest1] ", "Queryable");
x = Db.Queryable <BoolTest2>().Select(it => new BoolTest1()
{
a = it.a.Value
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest2] ", "Queryable");
Db.CodeFirst.InitTables <BoolTest3>();
var blist3 = Db.Queryable <BoolTest3>().Select(it => new BoolTest3()
{
a = string.IsNullOrEmpty(it.Name)
}).ToList();
var blist4 = Db.Queryable <BoolTest3>().Select(it => new BoolTest3()
{
a = SqlFunc.IIF(it.a == true, true, false)
}).ToList();
var blist5 = Db.Queryable <BoolTest3>().Select(it => new BoolTest3()
{
a = SqlFunc.IF(it.a == true).Return(true).End(false)
}).ToList();
var blist6 = Db.Queryable <BoolTest3>().Select(it => new BoolTest3()
{
a = it.a == true?true:false
}).ToList();
var blist7 = Db.Queryable <BoolTest3>().Select(it => new BoolTest3()
{
a = SqlFunc.Subqueryable <Order>().Any()
}).ToList();
var db = Db;
db.CodeFirst.InitTables <UserInfo, UserIpRuleInfo>();
db.Deleteable <UserInfo>().ExecuteCommand();
db.Deleteable <UserIpRuleInfo>().ExecuteCommand();
db.Insertable(new UserInfo()
{
Id = 1,
Password = "******",
UserName = "******"
}).ExecuteCommand();
db.Insertable(new UserIpRuleInfo()
{
Addtime = DateTime.Now,
UserName = "******",
Id = 11,
UserId = 1,
Description = "xx",
IpRange = "1",
RuleType = 1
}).ExecuteCommand();
var vmList = db.Queryable <UserInfo, UserIpRuleInfo>(
(m1, m2) => m1.Id == m2.UserId
).Where((m1, m2) => m1.Id > 0).Select((m1, m2) => new UserIpRuleInfo()
{
IpRange = m2.IpRange,
Addtime = m2.Addtime,
RuleType = m2.RuleType,
}).ToList();
if (string.IsNullOrEmpty(vmList.First().IpRange))
{
throw new Exception("Queryable");
}
Db.Insertable(new Order()
{
CreateTime = DateTime.Now, CustomId = 1, Name = "a", Price = 1
}).ExecuteCommand();
var sa = Db.SqlQueryable <Order>("SELECT * FroM [ORDER] where id in (@id) ");
sa.AddParameters(new List <SugarParameter>()
{
new SugarParameter("id", new int[] { 1 })
});
int i = 0;
var salist = sa.ToPageList(1, 2, ref i);
db.CodeFirst.InitTables <UnitBytes11>();
db.Insertable(new UnitBytes11()
{
bytes = null, name = "a"
}).ExecuteCommand();
db.Insertable(new UnitBytes11()
{
bytes = new byte[] { 1, 2 }, name = "a"
}).ExecuteCommand();
var bytes = db.Queryable <UnitBytes11>().Select(it => new
{
b = it.bytes,
name = "a"
}).ToList();
}
public static void Queryable2()
{
var list4 = Db.Queryable <ABMapping>()
.Mapper(it => it.A, it => it.AId)
.Where(it => it.A.Name == "a")
.ToList();
var list5 = Db.Queryable <ABMapping>()
.Mapper(it => it.A, it => it.AId, it => it.A.Id)
.Where(it => it.A.Name == "a")
.ToList();
var list3 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Count() > 0)
.ToList();
var list6 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Any())
.ToList();
var list7 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Any(y => y.ItemId == 1))
.ToList();
var sql = Db.Queryable <Order>().AS("[order]").ToList();
var sql1 = Db.Queryable <Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, c.Id == o.CustomId
))
.AS("[aa]")
.AS <OrderItem>("[xx]")
.AS <Custom>("[yy]")
.Select <ViewOrder>().ToSql().Key;
if (!sql1.Contains("[aa]") || !sql1.Contains("[xx]") || !sql1.Contains("[yy]"))
{
throw new Exception("unit queryable2 ");
}
var sql2 = Db.Queryable <OrderItem>().AS("[zz]").ToSql().Key;
if (sql2 != "SELECT [ItemId],[OrderId],[Price],[CreateTime] FROM [zz] ")
{
throw new Exception("unit queryable2 ");
}
Db.Queryable <Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, c.Id == o.CustomId
))
.AS("[order]")
.AS <OrderItem>("[orderdetail]")
.AS <Custom>("[custom]")
.Select <ViewOrder>().ToList();
Db.Queryable <object>().AS("[order]").Select("*").ToList();
var qu1 = Db.Queryable <Order>().Select(it => new
{
id = it.Id
}).MergeTable().Select <Order>();
var qu2 = Db.Queryable <Order>().Select(it => new
{
id = it.Id,
name = it.Name
}).MergeTable().Select <Order>();
var list = Db.Queryable(qu1, qu2, JoinType.Left, (x, y) => x.Id == y.Id).Select((x, y) => new
{
id1 = x.Id,
name = y.Name
}).ToList();
var qu3 = Db.Queryable <Order>().Select(it => new
{
id = it.Id,
name = it.Name
}).MergeTable()
.Where(it => 2 > it.id).Select(it => new Order()
{
Id = SqlFunc.IIF(2 > it.id, 1, 2)
}).ToList();
var qu4 = Db.Queryable <Order>().OrderBy(it => it.Id + it.Id).ToList();
var list11 = Db.Queryable <A>()
.ToList();
var list8 = Db.Queryable <A>()
.Mapper <A, B, ABMapping>(it => ManyToMany.Config(it.AId, it.BId))
.ToList();
Db.CodeFirst.InitTables <ABMap, TableA, TableB>();
Db.DbMaintenance.TruncateTable("TableA");
Db.DbMaintenance.TruncateTable("Tableb");
Db.DbMaintenance.TruncateTable("ABMap");
Db.Insertable(new TableA()
{
id = 1, Name = "A1"
}).ExecuteCommand();
Db.Insertable(new TableA()
{
id = 2, Name = "A1"
}).ExecuteCommand();
Db.Insertable(new TableB()
{
id = 1, Name = "B1"
}).ExecuteCommand();
Db.Insertable(new TableB()
{
id = 2, Name = "B2"
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 1, Bid = 1
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 1, Bid = 2
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 2, Bid = 1
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 2, Bid = 2
}).ExecuteCommand();
var list9 = Db.Queryable <TableA>()
.Mapper <TableA, TableB, ABMap>(it => ManyToMany.Config(it.Aid, it.Bid)).ToList();
var _db = Db;
int[] communities = { 1, 2, 3, 4, 5 };
_db.QueryFilter.Add(new TableFilterItem <Dat_WorkBill>(it => communities.Contains(it.CommunityID ?? 0)));
Db.CodeFirst.InitTables <Dat_WorkBill, Base_Community>();
var rlt = _db.Queryable(_db.Queryable <Dat_WorkBill>(), _db.Queryable <Base_Community>(),
JoinType.Left, (bill, com) => bill.CommunityID == com.CommunityID)
.Where((bill, com) => com.IsEnable == 1)
.Select((bill, com) => new
{
ID = bill.WorkBillID,
Name = com.CommunityName
})
.ToList();
var sql12 = Db.Queryable <Order, OrderItem, Custom>((o, i, c) => true)
.AS("[aa]")
.AS <OrderItem>("[xx]")
.AS <Custom>("[yy]").ToSql();
var list12 = Db.Queryable <Order>()
.Select(it => new
{
name = it.Name,
customName = SqlFunc.MappingColumn(default(string), " (select top 1 id from [Order] ) ")
}).ToList();
var p1 = "1";
var p2 = "2";
var list13 = Db.Queryable <Order>()
.Select(it => new
{
name = it.Name,
customName = SqlFunc.MappingColumn(default(string), $" (select top 1 id from [Order] where id={p1} or id={p2} ) ")
}).ToList();
int id = 0;
Db.Queryable(Db.Queryable <Order>().Where(it => it.Id == 1)).Where(it => it.Id == 1).ToList();
_db.QueryFilter.Clear();
Db.CodeFirst.InitTables <UnitEnumTest>();
Db.Insertable(new UnitEnumTest()
{
type = null
}).ExecuteCommand();
Db.Insertable(new UnitEnumTest()
{
type = DbType.MySql
}).ExecuteCommand();
var xx = Db.Queryable <UnitEnumTest>().ToList();
var xxx = Db.Storageable(new UnitEnumTest()
{
Name = "a",
type = DbType.Sqlite
}).WhereColumns(it => it.type).ToStorage();
xxx.AsUpdateable.ExecuteCommand();
var getOrderBy2 = Db
.Queryable <Order>()
.Select(it => new Order {
Name = it.Name.Replace("0", "1")
}).MergeTable().Select <Order>().Where(it => it.Name.Equals("2"))
.ToList();
var list14 = Db.Queryable <Order, Order, Order>((o1, o2, o3) =>
new JoinQueryInfos(JoinType.Inner, o1.Id == o2.Id * 2, JoinType.Inner, o1.Id == o3.Id * 4)
)
.Select((o1, o2, o3) => new
{
id = o1.Id,
x = o1,
x2 = o2,
x3 = o3
}).ToList();
var list15 = Db.Queryable <Order, Order, Order>((o1, o2, o3) =>
new JoinQueryInfos(JoinType.Inner, o1.Id == o2.Id * 2, JoinType.Inner, o1.Id == o3.Id * 4)
)
.Select((o1, o2, o3) => new TestModel1
{
id = o1.Id.SelectAll(),
x = o1,
x2 = o2,
x3 = o3
}).ToList();
}
public static void Queryable2()
{
var list4 = Db.Queryable <ABMapping>()
.Mapper(it => it.A, it => it.AId)
.Where(it => it.A.Name == "a")
.ToList();
var list5 = Db.Queryable <ABMapping>()
.Mapper(it => it.A, it => it.AId, it => it.A.Id)
.Where(it => it.A.Name == "a")
.ToList();
var list3 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Count() > 0)
.ToList();
var list6 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Any())
.ToList();
var list7 = Db.Queryable <Order>()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it => it.Items.Any(y => y.ItemId == 1))
.ToList();
var sql = Db.Queryable <Order>().AS("[order]").ToList();
var sql1 = Db.Queryable <Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, c.Id == o.CustomId
))
.AS("[aa]")
.AS <OrderItem>("[xx]")
.AS <Custom>("[yy]")
.Select <ViewOrder>().ToSql().Key;
if (!sql1.Contains("[aa]") || !sql1.Contains("[xx]") || !sql1.Contains("[yy]"))
{
throw new Exception("unit queryable2 ");
}
var sql2 = Db.Queryable <OrderItem>().AS("[zz]").ToSql().Key;
if (sql2 != "SELECT [ItemId],[OrderId],[Price],[CreateTime] FROM [zz] ")
{
throw new Exception("unit queryable2 ");
}
Db.Queryable <Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, c.Id == o.CustomId
))
.AS("[order]")
.AS <OrderItem>("[orderdetail]")
.AS <Custom>("[custom]")
.Select <ViewOrder>().ToList();
Db.Queryable <object>().AS("[order]").Select("*").ToList();
var qu1 = Db.Queryable <Order>().Select(it => new
{
id = it.Id
}).MergeTable().Select <Order>();
var qu2 = Db.Queryable <Order>().Select(it => new
{
id = it.Id,
name = it.Name
}).MergeTable().Select <Order>();
var list = Db.Queryable(qu1, qu2, JoinType.Left, (x, y) => x.Id == y.Id).Select((x, y) => new
{
id1 = x.Id,
name = y.Name
}).ToList();
var qu3 = Db.Queryable <Order>().Select(it => new
{
id = it.Id,
name = it.Name
}).MergeTable()
.Where(it => 2 > it.id).Select(it => new Order()
{
Id = SqlFunc.IIF(2 > it.id, 1, 2)
}).ToList();
var qu4 = Db.Queryable <Order>().OrderBy(it => it.Id + it.Id).ToList();
var list11 = Db.Queryable <A>()
.ToList();
var list8 = Db.Queryable <A>()
.Mapper <A, B, ABMapping>(it => ManyToMany.Config(it.AId, it.BId))
.ToList();
Db.CodeFirst.InitTables <ABMap, TableA, TableB>();
Db.DbMaintenance.TruncateTable("TableA");
Db.DbMaintenance.TruncateTable("Tableb");
Db.DbMaintenance.TruncateTable("ABMap");
Db.Insertable(new TableA()
{
id = 1, Name = "A1"
}).ExecuteCommand();
Db.Insertable(new TableA()
{
id = 2, Name = "A1"
}).ExecuteCommand();
Db.Insertable(new TableB()
{
id = 1, Name = "B1"
}).ExecuteCommand();
Db.Insertable(new TableB()
{
id = 2, Name = "B2"
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 1, Bid = 1
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 1, Bid = 2
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 2, Bid = 1
}).ExecuteCommand();
Db.Insertable(new ABMap()
{
Aid = 2, Bid = 2
}).ExecuteCommand();
var list9 = Db.Queryable <TableA>()
.Mapper <TableA, TableB, ABMap>(it => ManyToMany.Config(it.Aid, it.Bid)).ToList();
var _db = Db;
int[] communities = { 1, 2, 3, 4, 5 };
_db.QueryFilter.Add(new TableFilterItem <Dat_WorkBill>(it => communities.Contains(it.CommunityID ?? 0)));
Db.CodeFirst.InitTables <Dat_WorkBill, Base_Community>();
var rlt = _db.Queryable(_db.Queryable <Dat_WorkBill>(), _db.Queryable <Base_Community>(),
JoinType.Left, (bill, com) => bill.CommunityID == com.CommunityID)
.Where((bill, com) => com.IsEnable == 1)
.Select((bill, com) => new
{
ID = bill.WorkBillID,
Name = com.CommunityName
})
.ToList();
var sql12 = Db.Queryable <Order, OrderItem, Custom>((o, i, c) => true)
.AS("[aa]")
.AS <OrderItem>("[xx]")
.AS <Custom>("[yy]").ToSql();
var list12 = Db.Queryable <Order>()
.Select(it => new
{
name = it.Name,
customName = SqlFunc.MappingColumn(default(string), " (select top 1 id from [Order] ) ")
}).ToList();
var p1 = "1";
var p2 = "2";
var list13 = Db.Queryable <Order>()
.Select(it => new
{
name = it.Name,
customName = SqlFunc.MappingColumn(default(string), $" (select top 1 id from [Order] where id={p1} or id={p2} ) ")
}).ToList();
int id = 0;
Db.Queryable(Db.Queryable <Order>().Where(it => it.Id == 1)).Where(it => it.Id == 1).ToList();
_db.QueryFilter.Clear();
Db.CodeFirst.InitTables <UnitEnumTest>();
Db.Insertable(new UnitEnumTest()
{
type = null
}).ExecuteCommand();
Db.Insertable(new UnitEnumTest()
{
type = DbType.MySql
}).ExecuteCommand();
var xx = Db.Queryable <UnitEnumTest>().ToList();
var xxx = Db.Storageable(new UnitEnumTest()
{
Name = "a",
type = DbType.Sqlite
}).WhereColumns(it => it.type).ToStorage();
xxx.AsUpdateable.ExecuteCommand();
var getOrderBy2 = Db
.Queryable <Order>()
.Select(it => new Order {
Name = it.Name.Replace("0", "1")
}).MergeTable().Select <Order>().Where(it => it.Name.Equals("2"))
.ToList();
var list14 = Db.Queryable <Order, Order, Order>((o1, o2, o3) =>
new JoinQueryInfos(JoinType.Inner, o1.Id == o2.Id * 2, JoinType.Inner, o1.Id == o3.Id * 4)
)
.Select((o1, o2, o3) => new
{
id = o1.Id,
x = o1,
x2 = o2,
x3 = o3
}).ToList();
var list15 = Db.Queryable <Order, Order, Order>((o1, o2, o3) =>
new JoinQueryInfos(JoinType.Inner, o1.Id == o2.Id * 2, JoinType.Inner, o1.Id == o3.Id * 4)
)
.Select((o1, o2, o3) => new TestModel1
{
id = o1.Id.SelectAll(),
x = o1,
x2 = o2,
x3 = o3
}).ToList();
var list16 = Db.Queryable <Order>().OrderBy(it => it.CreateTime.ToString("yyyy-MM-dd")).Select(it => new { x = it.CreateTime.ToString("yyyy-MM-dd") }).ToList();
Db.CodeFirst.InitTables <TB_ClientConfig, TB_AdminUser>();
Db.Insertable(new TB_ClientConfig()
{
AlipayAppID = "aa",
AlipayPaymentOpen = true,
AlipayPrivateKey = "a",
AlipayPublicKey = "",
AlipayWithdrawOpen = true,
CreateAdminUserID = 1,
CreateDateTime = 11,
Extension = "a",
ModifyAdminUserID = 1,
ModifyDateTime = 1,
Name = "a",
WechatPayMchID = "a",
OpenWechatAppID = "a",
OpenWechatAppSecret = "a",
WechatMiniOriginalID = "b",
WechatPayApiKey = "a",
WechatPayApiKeyV3 = "z"
}).ExecuteReturnSnowflakeId();
var list17 = Db.Queryable <TB_ClientConfig, TB_AdminUser, TB_AdminUser>((f, c, m) => new JoinQueryInfos(
JoinType.Left, f.CreateAdminUserID == c.ID,
JoinType.Left, f.ModifyAdminUserID == m.ID))
.OrderBy(f => f.CreateDateTime, OrderByType.Desc)
.Select((f, c, m) => new
{
f,
CreateAdminUserName = c.Name,
ModifyAdminUserName = m.Name
}).ToList();
var listxxxxxxxxxxx = Db.Queryable <Tree2, Tree2>((a, b) => new JoinQueryInfos(JoinType.Inner, a.ParentId == b.Id))
.Select((a, b) => new {
user = a,
parentUser = b
})
.ToList();
var sql111 = Db.SqlQueryable <Order>("select 1 id ").ToSql().Key;
var sql222 = Db.SqlQueryable <Order>("select 1 id ").Where(it => it.Id == 1).ToSql().Key;
Check.Exception("select 1 id " != sql111, "unit query error");
Check.Exception("SELECT t.* FROM (select 1 id ) t WHERE ( [Id] = @Id0 )" != sql222, "unit query error");
var query5 = Db.Queryable <Order>()
.LeftJoin <Custom>((o, cus) => o.CustomId == cus.Id)
.Where((o) => o.Id > 0)
.Select((o, cus) => new VUOrder {
Ixd = o.Id.SelectAll()
})
.ToList();
Check.Exception(query5.Any() && query5.First().Ixd == 0, "unit error");
}
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");
}
}