CODEWITHSUNDEEP
c#sql.netsql-serverdapper
ineztia
ineztia

Reputation: 825

dapper: Get result and count at same time using QueryMultiple

I wrote two segments of SQL command and want to process in one query like this:

SELECT COUNT(*) FROM books

SELECT * FROM books ORDER BY bookID OFFSET 1000 ROWS FETCH NEXT 10 ROWS ONLY

How can I use conn.QueryMultiple method to get count AND list of books at same time ?

btw: I don't want to create any extra entity classes

Upvotes: 7

Views: 9844

Answers (2)

user3398888
user3398888

Reputation: 26

For the better performance

var sql=@"SELECT *,count(*) over() as AllCount
FROM Books ORDER BY BookId OFFSET 1000 ROWS FETCH NEXT 10 ROWS ONLY"

var result = result = dbConnection.Query<Book, int, Tuple<Book, int>>(sql, Tuple.Create, splitOn:"AllCount").ToList();
var count = result.Select(c => c.Item2).FirstOrDefault();
List<Book> list = result.Select(c => c.Item1).ToList();

Upvotes: 1

Backs
Backs

Reputation: 24903

From github example:

var sql = @"SELECT COUNT(*) FROM books
            SELECT * FROM books ORDER BY bookID OFFSET 1000 ROWS FETCH NEXT 10 ROWS ONLY";

using(var multi = connection.QueryMultiple(sql))
{
    var count = multi.Read<int>().Single();
    var results = multi.Read<YourObject>().ToList();
}

Upvotes: 11

Related Questions