/// <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();
}