public void Init()
{
var communityId = "";
var buildId = "";
var unitId = "";
var keyword = "";
GetInstance().CodeFirst.InitTables(typeof(MainTable), typeof(SubTable), typeof(Brand), typeof(VendorAndBrand));
GetInstance().Queryable <MainTable>().Where(u =>
(u.CommunityID == communityId || SqlFunc.IsNullOrEmpty(communityId)) &&
(SqlFunc.Contains(u.BuildID, buildId) || SqlFunc.IsNullOrEmpty(buildId)) &&
(SqlFunc.Contains(u.UnitID, unitId) || SqlFunc.IsNullOrEmpty(unitId)) &&
(SqlFunc.Contains(u.RoomNumber, keyword) || SqlFunc.Contains(u.RoomerName, keyword) ||
SqlFunc.Contains(u.HousePlace, keyword) || SqlFunc.Contains(u.UnitName, keyword) ||
SqlFunc.Contains(u.BuildName, keyword) || SqlFunc.IsNullOrEmpty(keyword)))
.GroupBy(ru => new { ru.RoomNumber, ru.RoomID })
.Select(ru => new
{
RoomNumber = SqlFunc.AggregateMax(ru.RoomNumber),
CountRoomer = SqlFunc.AggregateCount(ru.RoomerName),
RoomID = SqlFunc.AggregateMax(ru.RoomID),
Owner = SqlFunc.Subqueryable <SubTable>().Where(r => r.RoomID == ru.RoomID && SqlFunc.Equals(r.RoomUserType, "业主") && SqlFunc.Equals(r.RoomUserType, "业主")).Select(s => s.RoomerName)
}).OrderBy((r) => r.RoomNumber, type: OrderByType.Desc).ToPageListAsync(1, 2).Wait();
GetInstance().Updateable <Student>().UpdateColumns(it =>
new Student()
{
Name = "a".ToString(),
CreateTime = DateTime.Now.AddDays(-1)
}
).Where(it => it.Id == 1).ExecuteCommand();
var list = GetInstance().Queryable <Student, School>((st, sc) => new object[] {
JoinType.Left, st.SchoolId == sc.Id && st.CreateTime == DateTime.Now.AddDays(-1)
})
.Where(st => st.Name == "jack").ToList();
GetInstance().Updateable <BugStudent>().Where(it => true).UpdateColumns(it => new BugStudent()
{
Float = 11
}).ExecuteCommand();
var reslut = GetInstance().Queryable <BugStudent>().ToList();
var list2 = GetInstance().Queryable <Brand, VendorAndBrand>((b, vb) => new object[] {
JoinType.Left, b.Id == vb.BrandId
})
.Where((b) => b.BrandType == 1).Select((b) => b).ToList();
var list3 = GetInstance().Queryable <Brand, VendorAndBrand>((b, vb) =>
b.Id == vb.BrandId)
.Where((b) => b.BrandType == 1).Select((b) => b).ToList();
var query = GetInstance().Queryable <Student>().Select(o => o);
var result = query.ToList();
}
public static Result <NanHuArticle> QueryPageList(ReqNanHuArticle req)
{
var dbContext = new DbContext();
Expression <Func <NanHuArticle, bool> > fun = r => true;
if (req.Title.IsNotNullOrEmpty())
{
fun = (r) => SqlFunc.Contains(r.Title, req.Title);
//fun.AndAlso(r => SqlFunc.Contains(r.Title, req.Title));
}
if (req.CategoryId > 0)
{
var prefix = fun.Compile();
fun = (r) => prefix(r) && r.CategoryId == req.CategoryId;
// fun.AndAlso(r => r.CategoryId == req.CategoryId);
}
var result = dbContext.NanHuArticleDb.GetPages(req.ConvertData(), fun, req.PageInfo);
if (result.TotalCount > 0)
{
result.Results.ForEach(r =>
{
r.CategoryName = ModuleSubBLL.GetById(r.CategoryId)?.Name;
});
}
return(result);
}
private static void SqlFuncTest()
{
Console.WriteLine("");
Console.WriteLine("#### SqlFunc Start ####");
var db = GetInstance();
var index = db.Queryable <Order>().Select(it => SqlFunc.Contains("a", "cccacc")).First();
Console.WriteLine("#### SqlFunc End ####");
}
public static Result <CouponGood> QueryPageList(ReqCouponGood req)
{
var dbContext = new DbContext();
Expression <Func <CouponGood, bool> > fun = null;
if (req.GoodName.IsNotNullOrEmpty())
{
fun = (r) => SqlFunc.Contains(r.GoodName, req.GoodName);
}
var result = dbContext.CouponGoodDb.GetPages(req.ConvertData(), fun, req.PageInfo);
return(result);
}
public static Result <Etyma> QueryPageList(ReqEtyma req)
{
var dbContext = new DbContext();
Expression <Func <Etyma, bool> > fun = null;
if (req.Word.IsNotNullOrEmpty())
{
fun = (r) => SqlFunc.Contains(r.Word, req.Word);
}
var result = dbContext.EtymaDb.GetPages(req.ConvertData(), fun, req.PageInfo);
return(result);
}
public static Result <Article> QueryPageList(ReqArticle req)
{
var dbContext = new DbContext();
Expression <Func <Article, bool> > fun = null;
if (req.Title.IsNotNullOrEmpty())
{
fun = (r) => SqlFunc.Contains(r.Title, req.Title);
}
var result = dbContext.ArticleDb.GetPages(req.ConvertData(), fun, req.PageInfo);
return(result);
}
private void Contains()
{
Expression <Func <Student, bool> > exp = it => SqlFunc.Contains(it.Name, "a");
MySqlExpressionContext expContext = new MySqlExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, " (`Name` like concat('%',@MethodConst0,'%')) ", new List <SugarParameter>()
{
new SugarParameter("@MethodConst0", "a")
}, "Contains error");
}
private void Contains2(string name = "a")
{
Expression <Func <Student, bool> > exp = it => SqlFunc.Contains(it.Name, name);
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, " ([Name] like '%'+@MethodConst0+'%') ", new List <SugarParameter>()
{
new SugarParameter("@MethodConst0", "a")
}, "Contains2 error");
}
private void Contains()
{
Expression <Func <Student, bool> > exp = it => SqlFunc.Contains(it.Name, "a");
OracleExpressionContext expContext = new OracleExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, " (\"NAME\" like '%'||:MethodConst0||'%') ", new List <SugarParameter>()
{
new SugarParameter(":MethodConst0", "a")
}, "Contains error");
}
private void Contains3(string name = "a")
{
Expression <Func <Student, bool> > exp = it => !SqlFunc.Contains(it.Name, name) && it.Id == 1;
OracleExpressionContext expContext = new OracleExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "(NOT (\"NAME\" like '%'||:MethodConst0||'%') AND( \"ID\" = :Id1 ))", new List <SugarParameter>()
{
new SugarParameter(":MethodConst0", "a"),
new SugarParameter(":Id1", 1)
}, "Contains3 error");
}
public List <DH_AccountInfoHistory> SearchAccountInfo(string key)
{
List <DH_AccountInfoHistory> result = new List <DH_AccountInfoHistory>();
var query = BusDb.Queryable <DH_AccountInfoHistory>()
.WhereIF(!string.IsNullOrEmpty(key), m => SqlFunc.Contains(m.Name, key) || SqlFunc.Contains(m.BankOfDeposit, key) || SqlFunc.Contains(m.Account, key));
if (string.IsNullOrEmpty(key))
{
result = query.Take(10).ToList();
}
else
{
result = query.ToList();
}
return(result);
}
public List <OA_Client> SearchClientInfo(string nameOrCode)
{
List <OA_Client> result = new List <OA_Client>();
var query = BusDb.Queryable <OA_Client>()
.WhereIF(!string.IsNullOrEmpty(nameOrCode), m => SqlFunc.Contains(m.ClientCode, nameOrCode) || SqlFunc.Contains(m.ClientName, nameOrCode));
if (string.IsNullOrEmpty(nameOrCode))
{
result = query.Take(10).ToList();
}
else
{
result = query.ToList();
}
return(result);
}
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 static void SqlFuncTest()
{
Console.WriteLine("");
Console.WriteLine("#### SqlFunc Start ####");
var db = GetInstance();
var index = db.Queryable <Order>().Select(it => SqlFunc.Contains("a", "cccacc")).First();
var list = db.Queryable <Order>().Select(it => new ViewOrder()
{
Id = SqlFunc.AggregateSum(SqlFunc.IF(it.Id > 0).Return(1).End(0))
}).ToList();
var list2 = db.Queryable <Order>().Select(it => new
{
date = SqlFunc.ToDateShort(it.CreateTime),
datetime = SqlFunc.ToDate(it.CreateTime)
}).ToList();
Console.WriteLine("#### SqlFunc End ####");
}
/// <summary>
/// 获取角色列表
/// </summary>
/// <param name="roleName">角色名称</param>
/// <param name="pageModel">分页</param>
/// <param name="departmentId">部门Id</param>
/// <param name="rsState">状态</param>
/// <returns></returns>
public ListResult <WXQ.Enties.Role> GetRoleList(string roleName, PageModel pageModel, int departmentId = -1, int rsState = 1)
{
RoleManager roleManager = new RoleManager();
ListResult <WXQ.Enties.Role> result = new ListResult <Enties.Role>();
int totalRs = 0;
result.Result = roleManager.Db.Queryable <WXQ.Enties.DepartmentRole, WXQ.Enties.Role>((dr, r) => new object[] {
JoinType.Left, dr.RoleId == r.RoleId
})
.Where((dr, r) => r.RsState == rsState && dr.DepartmentId == departmentId)
.WhereIF(!string.IsNullOrEmpty(roleName), (dr, r) => SqlFunc.Contains(r.RoleName, roleName))
.Select((dr, r) => r).ToPageList(pageModel.PageIndex, pageModel.PageSize, ref totalRs);
result.PageSize = pageModel.PageSize;
result.PageIndex = pageModel.PageIndex;
result.Total = pageModel.PageCount;
return(result);
}
private static void SqlFuncTest()
{
Console.WriteLine("");
Console.WriteLine("#### SqlFunc Start ####");
var db = GetInstance();
var index = db.Queryable <Order>().Select(it => SqlFunc.Contains("a", "cccacc")).First();
var list2 = db.Queryable <Order>().Select(it => new
{
date = SqlFunc.ToDateShort(it.CreateTime),
datetime = SqlFunc.ToDate(it.CreateTime)
}).ToList();
var list3 = db.Queryable <Order>().Select(it => new Order
{
CreateTime = SqlFunc.ToDateShort(it.CreateTime)
}).ToList();
Console.WriteLine("#### SqlFunc End ####");
}
public static Result <Prefix> QueryPageList(ReqPrefix req)
{
var dbContext = new DbContext();
Expression <Func <Prefix, bool> > fun = null;
if (req.Word.IsNotNullOrEmpty())
{
fun = (r) => SqlFunc.Contains(r.Word, req.Word);
}
var result = dbContext.PrefixDb.GetPages(req.ConvertData(), fun, req.PageInfo);
if (result.Results != null)
{
result.Results.ForEach(r => {
if (r.Json.IsNotNullOrEmpty())
{
r.Extensions = JsonConvert.DeserializeObject <List <FixExtension> >(r.Json);
}
});
}
return(result);
}
/// <summary>
/// 字典列表
/// </summary>
/// <param name="methodName"></param>
/// <param name="opUserId"></param>
/// <param name="bgDt"></param>
/// <param name="endDt"></param>
/// <param name="Ip"></param>
/// <param name="pageModel"></param>
/// <returns></returns>
public ListResult <WXQ.Enties.Dict> GetDictList(string GroupName, PageModel pageModel)
{
DictManager DictManager = new DictManager();
ListResult <WXQ.Enties.Dict> result = new ListResult <Enties.Dict>();
System.Linq.Expressions.Expression <Func <Enties.Dict, bool> > express = Expressionable.Create <WXQ.Enties.Dict>()
.AndIF(!string.IsNullOrEmpty(GroupName), m => SqlFunc.Contains(m.GroupName, GroupName)).ToExpression();//拼接表达式
result.Result = string.IsNullOrEmpty(GroupName) ? DictManager.GetPageList(express, pageModel, d => d.GroupName, OrderByType.Asc) : DictManager.GetPageList(express, pageModel, d => d.OrderBy, OrderByType.Asc);
result.PageSize = pageModel.PageSize;
result.PageIndex = pageModel.PageIndex;
result.Total = pageModel.PageCount;
return(result);
}
/// <summary>
/// 查找菜单
/// </summary>
/// <param name="menuName">菜单名</param>
/// <param name="url">地址</param>
/// <param name="pageModel">分页</param>
/// <param name="rsState">记录状态</param>
/// <returns></returns>
public ListResult <WXQ.Enties.Menu> GetMenuList(string menuName, string url, int parentId, PageModel pageModel, int rsState = 1)
{
MenuManager MenuManager = new MenuManager();
ListResult <WXQ.Enties.Menu> result = new ListResult <Enties.Menu>();
System.Linq.Expressions.Expression <Func <Enties.Menu, bool> > express = Expressionable.Create <WXQ.Enties.Menu>()
.AndIF(!string.IsNullOrEmpty(menuName), m => SqlFunc.Contains(m.MenuName, menuName))
.AndIF(!string.IsNullOrEmpty(url), m => SqlFunc.Contains(m.Url, url))
.AndIF(parentId > -1, it => it.ParentId == parentId)
.AndIF(rsState > 1, it => it.RsState == rsState).ToExpression();//拼接表达式
result.Result = MenuManager.GetPageList(express, pageModel);
result.PageSize = pageModel.PageSize;
result.PageIndex = pageModel.PageIndex;
result.Total = pageModel.PageCount;
return(result);
}
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");
}
}
public ListResult <WXQ.Enties.Department> GetDepartmentList(string departmentName, int parentId, int pageIndex, int pageSize, int rsState = 1)
{
int totalRs = 0;
DepartmentManager DepartmentManager = new DepartmentManager();
ListResult <WXQ.Enties.Department> result = new ListResult <Enties.Department>
{
Result = DepartmentManager.Db.Queryable <Enties.Department>().WhereIF(!string.IsNullOrEmpty(departmentName), m => SqlFunc.Contains(m.DepartmentName, departmentName))
.WhereIF(parentId > -1, it => it.ParentId == parentId)
.WhereIF(rsState > 1, it => it.RsState == rsState)
.ToPageList(pageIndex, pageSize, ref totalRs),
PageSize = pageSize,
PageIndex = pageIndex,
Total = totalRs
};
return(result);
}
public ListResult <WXQ.Enties.Users> GetUserList(string userName, PageModel pageModel, int rsState = 1)
{
UsersManager UsersManager = new UsersManager();
ListResult <WXQ.Enties.Users> result = new ListResult <Enties.Users>();
System.Linq.Expressions.Expression <Func <Enties.Users, bool> > express = Expressionable.Create <WXQ.Enties.Users>()
.AndIF(!string.IsNullOrEmpty(userName), m => SqlFunc.Contains(m.NickName, userName) || SqlFunc.Contains(m.UserName, userName))
.AndIF(rsState > 1, it => it.RsState == rsState).ToExpression();//拼接表达式
result.Result = UsersManager.GetPageList(express, pageModel);
foreach (Enties.Users u in result.Result)
{
u.PassWord = "";
}
result.PageSize = pageModel.PageSize;
result.PageIndex = pageModel.PageIndex;
result.Total = pageModel.PageCount * pageModel.PageSize;
return(result);
}
public static Result <Items> QueryPageList(ReqItems req)
{
Result <Items> results = new Result <Items>();
if (req.IsFull)
{
var tempItems = TaoBaoKeHelper.QueryCoupon(req.KeyWord);
if (tempItems.Count > req.PageInfo.PageSize)
{
results.Results = tempItems.OrderByDescending(t => t.CommissionRate).ThenByDescending(t => t.Volume).Skip(req.PageInfo.PageSize * (req.PageInfo.PageIndex - 1)).Take(req.PageInfo.PageSize).ToList();
}
else
{
results.Results = tempItems;
}
results.TotalCount = tempItems.Count;
Task.Factory.StartNew(() =>
{
UpdateCache(tempItems);
});
return(results);
}
if (mDict.HasValue())
{
List <Items> temp = mDict.Where(m =>
{
bool tempResult = m.Status == 1;
if (tempResult && req.KeyWord.IsNotNullOrEmpty())
{
tempResult = m.Title.TryContains(req.KeyWord);
if (tempResult == false)
{
tempResult = m.Tags.TryContains(req.KeyWord);
}
if (tempResult == false)
{
tempResult = m.ProductUrl.TryContains(req.KeyWord);
}
if (tempResult == false)
{
tempResult = m.ProductWapUrl.TryContains(req.KeyWord);
}
}
if (tempResult && req.TypeId > 0)
{
tempResult = m.TypeId == req.TypeId;
}
if (tempResult && req.ZCId > 0)
{
tempResult = m.ZCId == req.ZCId;
}
if (tempResult && req.Tag.IsNotNullOrEmpty() && m.Tags != null)
{
tempResult = m.Tags.TryContains(req.Tag) || m.Title.TryContains(req.Tag);
}
return(tempResult);
}).ToList();
temp = temp.OrderByDescending(t => t.YouhuiPrice).ToList();
if (req.PageInfo.SortFields.IsNotNullOrEmpty() && req.PageInfo.SortFields == "volume")
{
if (req.PageInfo.Sort.EqualsCurrentCultureIgnoreCase("desc"))
{
temp = temp.OrderByDescending(t => t.Volume).ToList();
}
else
{
temp = temp.OrderBy(t => t.Volume).ToList();
}
}
results.Results = temp.Skip(req.PageInfo.PageSize * (req.PageInfo.PageIndex - 1)).Take(req.PageInfo.PageSize).ToList();
results.TotalCount = temp.Count;
if (results.TotalCount == 0)
{
var tempItems = TaoBaoKeHelper.QueryCoupon(req.KeyWord);
if (tempItems.Count > req.PageInfo.PageSize)
{
results.Results = tempItems.OrderByDescending(t => t.CommissionRate).ThenByDescending(t => t.Volume).Take(req.PageInfo.PageSize).ToList();
}
results.TotalCount = tempItems.Count;
Task.Factory.StartNew(() =>
{
UpdateCache(tempItems);
});
}
return(results);
}
var dbContext = new DbContext();
Expression <Func <Items, bool> > fun = null;
if (req.KeyWord.IsNotNullOrEmpty())
{
fun = (r) => SqlFunc.Contains(r.Title, req.KeyWord);
}
var result = dbContext.ItemsDb.GetPages(req.ConvertData(), fun, req.PageInfo);
return(result);
}
/// <summary>
/// 获取用户集合，包含角色id
/// </summary>
/// <param name="name">用户名</param>
/// <param name="roleId">角色id</param>
/// <param name="pageSize">页大小</param>
/// <param name="pageIndex">页索引</param>
/// <returns></returns>
public ListResult <object> GetUsersRefRole(string name, int roleId, int pageSize, int pageIndex)
{
ListResult <object> result = new ListResult <object>
{
Result = new List <object>()
};
int totalRs = 0;
MenuManager MenuManager = new MenuManager();
ISugarQueryable <Enties.Users> u1 = MenuManager.Db.Queryable <Enties.Users>().WhereIF(!string.IsNullOrEmpty(name), u => SqlFunc.Contains(u.NickName, name) || SqlFunc.Contains(u.UserName, name));
ISugarQueryable <Enties.UserRole> ud1 = MenuManager.Db.Queryable <Enties.UserRole>().Where(ud => ud.RoleId == roleId);
var lt = MenuManager.Db.Queryable(u1, ud1, JoinType.Left, (j1, j2) => j1.UsersId == j2.UserId).OrderBy((j1, j2) => j2.RoleId, OrderByType.Desc).Select((j1, j2) => new { j1.UsersId, j1.UserName, j1.NickName, j2.RoleId }).ToPageList(pageIndex, pageSize, ref totalRs);
if (totalRs > 0)
{
result.Result = new List <object>();
foreach (var u in lt)
{
result.Result.Add(u);
}
}
result.PageSize = pageSize;
result.PageIndex = pageIndex;
result.Total = totalRs;
return(result);
}
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="methodName"></param>
/// <param name="opUserId"></param>
/// <param name="bgDt"></param>
/// <param name="endDt"></param>
/// <param name="Ip"></param>
/// <param name="pageModel"></param>
/// <returns></returns>
public ListResult <WXQ.Enties.OpLog> GetOpLogList(string methodName, int opUserId, DateTime bgDt, DateTime endDt, string Ip, PageModel pageModel)
{
OpLogManager oplogManager = new OpLogManager();
ListResult <WXQ.Enties.OpLog> result = new ListResult <Enties.OpLog>();
System.Linq.Expressions.Expression <Func <Enties.OpLog, bool> > express = Expressionable.Create <WXQ.Enties.OpLog>()
.AndIF(!string.IsNullOrEmpty(methodName), m => SqlFunc.Contains(m.MethodName, methodName))
.AndIF(bgDt < DateTime.Now, m => m.CreateDateTime >= bgDt)
.AndIF(endDt >= bgDt, m => m.CreateDateTime <= endDt)
.AndIF(!string.IsNullOrEmpty(Ip), m => SqlFunc.Contains(m.Ip, Ip))
.AndIF(opUserId > -1, it => it.OpUser == opUserId).ToExpression();//拼接表达式
result.Result = oplogManager.GetPageList(express, pageModel);
result.PageSize = pageModel.PageSize;
result.PageIndex = pageModel.PageIndex;
result.Total = pageModel.PageCount;
return(result);
}
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` ", 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 `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` ORDER BY `ID` ASC LIMIT 0,3", null, t4.Key, null, "single t4 Error");
var t5 = db.Queryable <Student>().OrderBy(it => it.Id).Skip(3).ToSql();
base.Check(@"SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` LIMIT 3,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 `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` ORDER BY `ID` DESC LIMIT 10,10", 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 `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` WHERE ( `ID` = @Id0 ) AND (`Name` like concat('%',@MethodConst1,'%')) ORDER BY `ID` DESC LIMIT 10,10", 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");
}
}
/// <summary>
/// 度量日志
/// </summary>
/// <param name="methodName"></param>
/// <param name="CostTime"></param>
/// <param name="bgDt"></param>
/// <param name="endDt"></param>
/// <param name="pageModel"></param>
/// <returns></returns>
public ListResult <WXQ.Enties.Metrics> GetMetricsList(string methodName, int CostTime, DateTime bgDt, DateTime endDt, PageModel pageModel)
{
MetricsManager metricsManager = new MetricsManager();
ListResult <WXQ.Enties.Metrics> result = new ListResult <Enties.Metrics>();
System.Linq.Expressions.Expression <Func <Enties.Metrics, bool> > express = Expressionable.Create <WXQ.Enties.Metrics>()
.AndIF(!string.IsNullOrEmpty(methodName), m => SqlFunc.Contains(m.MethodName, methodName))
.AndIF(bgDt < DateTime.Now, m => m.AddDateTime >= bgDt)
.AndIF(endDt >= bgDt, m => m.AddDateTime <= endDt)
.AndIF(CostTime > 0, m => m.CostTime > CostTime)
.ToExpression();//拼接表达式
result.Result = metricsManager.GetPageList(express, pageModel);
result.PageSize = pageModel.PageSize;
result.PageIndex = pageModel.PageIndex;
result.Total = pageModel.PageCount;
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="methodName"></param>
/// <param name="opUserId"></param>
/// <param name="bgDt"></param>
/// <param name="endDt"></param>
/// <param name="Ip"></param>
/// <param name="pageModel"></param>
/// <returns></returns>
public bool Delete(string methodName, int opUserId, DateTime bgDt, DateTime endDt, string Ip)
{
OpLogManager oplogManager = new OpLogManager();
System.Linq.Expressions.Expression <Func <Enties.OpLog, bool> > express = Expressionable.Create <WXQ.Enties.OpLog>()
.AndIF(!string.IsNullOrEmpty(methodName), m => SqlFunc.Contains(m.MethodName, methodName))
.AndIF(bgDt < DateTime.Now, m => m.CreateDateTime >= bgDt)
.AndIF(endDt >= bgDt, m => m.CreateDateTime <= endDt)
.AndIF(!string.IsNullOrEmpty(Ip), m => SqlFunc.Contains(m.Ip, Ip))
.AndIF(opUserId > -1, it => it.OpUser == opUserId).ToExpression();//拼接表达式
return(oplogManager.Delete(express));
}
/// <summary>
/// 删除度量日志
/// </summary>
/// <param name="methodName"></param>
/// <param name="CostTime"></param>
/// <param name="bgDt"></param>
/// <param name="endDt"></param>
/// <param name="pageModel"></param>
/// <returns></returns>
public bool Delete(string methodName, int CostTime, DateTime bgDt, DateTime endDt)
{
MetricsManager metricsManager = new MetricsManager();
System.Linq.Expressions.Expression <Func <Enties.Metrics, bool> > express = Expressionable.Create <WXQ.Enties.Metrics>()
.AndIF(!string.IsNullOrEmpty(methodName), m => SqlFunc.Contains(m.MethodName, methodName))
.AndIF(bgDt < DateTime.Now, m => m.AddDateTime >= bgDt)
.AndIF(endDt >= bgDt, m => m.AddDateTime <= endDt)
.AndIF(CostTime > 0, m => m.CostTime > CostTime)
.ToExpression();//拼接表达式
return(metricsManager.Delete(express));
}