Dapper - Multi Mapping with One Return Value

Question

Below is the code that I use to return the paged list of objects:

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

Although this works, it means that I have to define my criteria twice, once for the query count and once for the query for the result set. Instead of resorting to string concatenation, I can only execute one request:

  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"; 

However, I cannot match this with Dapper. I cannot use the same method as above because there are no multiple results. I tried using multiple mapping, but this is expecting IEnumerable to return.

How do I display the following?

  public class PostList { public IEnumerable<PostModel> Posts; public int TotalCount { get; set; } } 

thanks

Ben

Answer 1

Well ... you would not ...

You will need to modify your PostModel to enable the TotalCount property ... which is really ugly. Or do dynamic and reconfigure it to Select , which is also ugly.

You see that you return the counter (*) N times with count(*) over() ... this is a hack, using this hack is not necessarily faster. I measured it slower than running a double query in some of my scripts, in particular, you can shorten some indexes in select count(*) since you do not select all columns. In addition, the hack disables certain search engine optimizations, for example, you cannot add select top N to the query.

My recommendation for search queries would be to index correctly, this is the key. Measure perf and see if this hack really helps (when the correct indexing is in place).

I keep track of issues around string concatenation, but you can always define common helper methods for this.