Does Dapper's IEnumerable<T> have deferred or immediate execution?

Question

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

Or this:

conn.Query<Widget>("select top 200 * from Widgets");

Is Dapper's .Query<T> method deferred or not?

Answer 1

You should use SELECT TOP n....

The 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.