When I execute a query in Dapper and only want to retrieve a block of records, can I use .Skip().Take(), or do I need use select top n * in the SQL?
eg Given a table with 10,000 records and I only want the first 200 because my list page only shows 200 per page. Do I run this?
conn.Query<Widget>("select * from Widgets").Skip((page - 1) * size).Take(size);
conn.Query<Widget>("select top 200 * from Widgets");
.Query<T> method deferred or not?
You should use
SELECT TOP n....
Query<T> method has an optional parameter
bool buffered = true, which when true loops through the full resultset, reading each row into a
List<T>. You could make this parameter false, and the resulting
IEnumerable<T> would be "deferred" in the sense that the db query would not be executed until you use it, and the rows would be retrieved from the db side "one at a time" (calls
IDataReader.Read on each iteration).
So, yes, it can be "deferred". HOWEVER, you should still use
TOP n because otherwise you would still execute and prepare the resultset for 10000 records on the db side, although you may transport only the first n rows of those to the client.