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?

Mcknight

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.

Nook

When buffer set to false you have to worry about the connection. If you close it before you "ToList" (or the recommended AtList), it will fail with an error. – Animalism