以下是我用来返回对象分页列表的代码:
虽然这样可以工作,但这意味着我需要两次定义我的条件,一次用于计数查询,一次用于结果集查询。而不是采用字符串拼接的方式,我可以执行一个查询:
string query2 = @"
select count(*) as TotalCount from blogposts p where p.Deleted = 0 and p.PublishDate <= @date
select * from (
select p.*,
row_number() over(order by publishdate desc) as rownum
from blogposts as p
where p.Deleted = 0 and p.PublishDate <= @date
) seq
where seq.rownum between @x and @y";
using (var cn = new SqlConnection(connectionString))
{
cn.Open();
using (var multi = cn.QueryMultiple(query2, new { x= lower, y = upper, date = DateTime.UtcNow }))
{
var totalCount = multi.Read<int>().Single();
var posts = multi.Read<PostModel>().ToList();
return new PagedList<PostModel>(posts, page, pageSize, x => totalCount);
}
}
虽然这样可以工作,但这意味着我需要两次定义我的条件,一次用于计数查询,一次用于结果集查询。而不是采用字符串拼接的方式,我可以执行一个查询:
string query = @"
select * from (select p.*,
row_number() over(order by publishdate desc) as rownum,
count(*) over() as TotalCount
from blogposts as p) seq
where seq.rownum between @x and @y";
然而,我似乎无法使用Dapper进行映射。我不能像上面那样使用相同的方法,因为没有多个结果。我尝试过使用多重映射,但这需要返回IEnumerable。
我该如何映射到以下内容?
public class PostList
{
public IEnumerable<PostModel> Posts;
public int TotalCount { get; set; }
}
感谢
本