/// <summary> /// 查询所有的post,同时填充对应的category和tags /// </summary> /// <param name="pageNo">Page index</param> /// <param name="pageSize">Page size</param> /// <returns></returns> public IList<Post> Posts(int pageNo, int pageSize) { var subQuery = new SqlLam<Post>() .Where(p => p.Published) .OrderByDescending(p => p.PostedOn) .Select(p => p) .Join<Category>((p, c) => p.Category == c.CategoryId) .Select(t => t); var posts = CONN.SQLQuery<Post, Category, Post>(subQuery.QueryStringPage(pageSize, pageNo), (p, c) => { p.BlogCategory = c; p.Tags = GetTags(p); return p; }, subQuery.QueryParameters, splitOn: "CategoryId"); return posts.ToList(); //var subQuery = new SqlLam<Category>() // .Select(c => c).Join<Post>((c, p) => c.CategoryId == p.Category) // .Where(p => p.Published) // .OrderByDescending(p => p.PostedOn) // .Select(p => p); //var posts = CONN.SQLQuery<Category, Post, Post>(subQuery.QueryStringPage(pageSize, pageNo), // (c, p) => // { // p.BlogCategory = c; // p.Tags=GetTags(p); // return p; // }, subQuery.QueryParameters, splitOn: "Id"); //return posts.ToList(); }
public void TopTenResults() { const int reorderLevel = 0; const int pageSize = 10; var query = new SqlLam <Product>(p => p.ReorderLevel == reorderLevel) .OrderBy(p => p.ProductName); var results = Connection.Query <Product>(query.QueryStringPage(pageSize), query.QueryParameters).ToList(); Assert.AreEqual(pageSize, results.Count); Assert.IsTrue(results.All(p => p.ReorderLevel == reorderLevel)); }
public void PaginateOverResults() { const int reorderLevel = 0; const int pageSize = 5; const int numberOfPages = 5; const int lastPageSize = 4; var query = new SqlLam <Product>(p => p.ReorderLevel == reorderLevel) .OrderBy(p => p.ProductName); for (int page = 1; page < numberOfPages; ++page) { var results = Connection.Query <Product>(query.QueryStringPage(pageSize, page), query.QueryParameters).ToList(); Assert.AreEqual(pageSize, results.Count); Assert.IsTrue(results.All(p => p.ReorderLevel == reorderLevel)); } var lastResults = Connection.Query <Product>(query.QueryStringPage(pageSize, numberOfPages), query.QueryParameters).ToList(); Assert.AreEqual(lastPageSize, lastResults.Count); Assert.IsTrue(lastResults.All(p => p.ReorderLevel == reorderLevel)); }
public void TopTenResults() { const int reorderLevel = 0; const int pageSize = 10; var query = new SqlLam<Product>(p => p.ReorderLevel == reorderLevel) .OrderBy(p => p.ProductName); var results = Connection.Query<Product>(query.QueryStringPage(pageSize), query.QueryParameters).ToList(); Assert.AreEqual(pageSize, results.Count); Assert.IsTrue(results.All(p => p.ReorderLevel == reorderLevel)); }
public void PaginateOverResults() { const int reorderLevel = 0; const int pageSize = 5; const int numberOfPages = 5; const int lastPageSize = 4; var query = new SqlLam<Product>(p => p.ReorderLevel == reorderLevel) .OrderBy(p => p.ProductName); for(int page = 1; page < numberOfPages; ++page) { var results = Connection.Query<Product>(query.QueryStringPage(pageSize, page), query.QueryParameters).ToList(); Assert.AreEqual(pageSize, results.Count); Assert.IsTrue(results.All(p => p.ReorderLevel == reorderLevel)); } var lastResults = Connection.Query<Product>(query.QueryStringPage(pageSize, numberOfPages), query.QueryParameters).ToList(); Assert.AreEqual(lastPageSize, lastResults.Count); Assert.IsTrue(lastResults.All(p => p.ReorderLevel == reorderLevel)); }
/// <summary> /// 根据tag查询post,查询语句为针对多对多的 /// </summary> /// <param name="tagSlug">Tag's url slug</param> /// <param name="pageNo">Page index</param> /// <param name="pageSize">Page size</param> /// <returns></returns> public IList<Post> PostsForTag(string tagSlug, int pageNo, int pageSize) { var query = new SqlLam<Post>() .Select(o => o).Where(p => p.Published).OrderBy(x => x.PostedOn) .Join<PostTagMap>((o, d) => o.Id == d.Post_id) .Join<Tag>((d, p) => d.Tag_id == p.TagId).Where(t => t.TagUrlSlug == tagSlug).Select(t=>t); var posts = CONN.SQLQuery<Post, Tag, Post>(query.QueryStringPage(pageSize, pageNo), (p, t) => { p.BlogCategory = GetCategory(p); p.Tags.Add(t); return p; }, query.QueryParameters, splitOn: "TagId"); return posts.ToList(); }
/// <summary> /// Return posts based on pagination and sorting parameters. /// </summary> /// <param name="pageNo">Page index</param> /// <param name="pageSize">Page size</param> /// <param name="sortColumn">Sort column name</param> /// <param name="sortByAscending">True to sort by ascending</param> /// <returns></returns> public IList<Post> Posts(int pageNo, int pageSize, string sortColumn, bool sortByAscending) { SqlLamBase.SetAdapter(SqlAdapter.SqlServer2008); var query = new SqlLam<Post>(); switch (sortColumn) { case "Title": query = sortByAscending ? query.OrderBy(x => x.Title) : query.OrderByDescending(x => x.Title); break; case "Published": query = sortByAscending ? query.OrderBy(x => x.Published) : query.OrderByDescending(x => x.Published); break; case "Modified": query = sortByAscending ? query.OrderBy(x => x.Modified) : query.OrderByDescending(x => x.Modified); break; default: query = sortByAscending ? query.OrderBy(x => x.PostedOn) : query.OrderByDescending(x => x.PostedOn); break; } var posts = CONN.SQLQuery<Post>(query.QueryStringPage(pageSize, pageNo), query.QueryParameters); return posts.ToList(); }
/// <summary> /// 根据关键词查询,构建的语句为多级关联查询,首先子查询语句获取主键列表,然后进行in查询 /// </summary> /// <param name="search">search text</param> /// <param name="pageNo">Page index</param> /// <param name="pageSize">Page size</param> /// <returns></returns> public IList<Post> PostsForSearch(string search, int pageNo, int pageSize) { var subquery = new SqlLam<Category>().Or(t => t.CatUrlSlug.Contains(search)) .Join<Post>((c, p) => c.CategoryId==p.Category ) .Where(p => p.Published).Or(p => p.Title.Contains(search)).SelectDistinct(p=>p.Id) .Join<PostTagMap>((o, d) => o.Id == d.Post_id) .Join<Tag>((d, p) => d.Tag_id == p.TagId).Or(t => t.TagUrlSlug == search); var query = new SqlLam<Post>().WhereIsIn(p => p.Id, subquery).OrderBy(x => x.PostedOn);//TODO:效率存在问题 var posts = CONN.SQLQuery<Post>(query.QueryStringPage(pageSize, pageNo), query.QueryParameters).ToList(); posts.ForEach((x) => { x.BlogCategory = GetCategory(x); x.Tags = GetTags(x); }); return posts; }
/// <summary> /// 根据类目查询post,语句为多对一的查询 /// </summary> /// <param name="categorySlug">Category's url slug</param> /// <param name="pageNo">Page index</param> /// <param name="pageSize">Page size</param> /// <returns></returns> public IList<Post> PostsForCategory(string categorySlug, int pageNo, int pageSize) { var query = new SqlLam<Post>() .Select(o => o).Where(p => p.Published).OrderBy(x => x.PostedOn) .Join<Category>((o, d) => o.Category == d.CategoryId).Where(t => t.CatUrlSlug == categorySlug).Select(c=>c); var posts = CONN.SQLQuery<Post,Category,Post>(query.QueryStringPage(pageSize, pageNo), (p, c) => { p.BlogCategory = c; p.Tags = GetTags(p); return p; }, query.QueryParameters,splitOn:"CategoryId"); return posts.ToList(); }