public static List <MenuModel> GetMenuListRoles()
{
using (ARTICLE_DBEntities context = new ARTICLE_DBEntities())
{
//很多参数先写死吧
var res = context.Database.SqlQuery <MenuModel>(@"
WITH MENU
AS ( SELECT *
FROM DBO.T_MENUS
WHERE VERSION = 'V1'
AND DISABLED = 0
)
SELECT M.ID ,
M.PARENT_ID ,
M.ORDER_IDNEX ,
M.NAME ,
M.URL ,
M.VERSION ,
RO.ID ROLE_ID ,
RO.NAME ROLE_NAME
FROM DBO.T_ROLES_MENUS A
INNER JOIN MENU M ON A.MENU_ID = M.ID
INNER JOIN DBO.T_ROLES RO ON A.ROLE_ID = RO.ID
ORDER BY M.ORDER_IDNEX").ToList();
return(res);
}
}
public static List <MenuModel> GetMenuList()
{
using (ARTICLE_DBEntities context = new ARTICLE_DBEntities())
{
var res = context.Database.SqlQuery <MenuModel>("SELECT * FROM T_MENUS WHERE DISABLED = 0 AND VERSION='V1' ORDER BY ORDER_IDNEX").ToList();
return(res);
}
}
/// <summary>
/// EF方式操作返回所有数据
/// </summary>
/// <param name="cateid"></param>
/// <param name="title"></param>
/// <returns></returns>
public static List <v_get_articles> GetArticlesEF(int cateid, string title)
{
using (ARTICLE_DBEntities context = new ARTICLE_DBEntities())
{
//动态拼接多条件
var res = context.v_get_articles.Where(t => true);
if (cateid != -1)
{
res = res.Where(t => t.cate_id == cateid);
}
if (!string.IsNullOrEmpty(title))
{
res = res.Where(t => t.title.Contains(title));
}
//tolist是才会执行前面拼接的查询计划
return(res.ToList());
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="cateid"></param>
/// <param name="title"></param>
/// <param name="page"></param>
/// <returns></returns>
public static Pager <v_get_articles> GetArticlesByPager(int cateid, string title, PageParam page)
{
using (ARTICLE_DBEntities context = new ARTICLE_DBEntities())
{
Pager <v_get_articles> pg = new Pager <v_get_articles>();
var res = context.v_get_articles.Where(t => true);
if (cateid != -1)
{
res = res.Where(t => t.cate_id == cateid);
}
if (!string.IsNullOrEmpty(title))
{
// contains -> like
res = res.Where(t => t.title.Contains(title));
}
pg.Total = res.Count();
pg.Rows = res.OrderBy(t => t.update_time).Skip(page.Skip).Take(page.PageSize).ToList();
return(pg);
}
}
