When does Dapper 'materialize'? Like Entity materializes at ToList()

Question

The title pretty much says it all. Since Dapper does not have much documentation. Thanks in advance.

Answer 1

Query() returns an IEnumerable. When you start to iterate over your IEnumerable, inside Dapper some method is called with Yield to fill a POCO with a line of results, so your results are materialized progressively as you iterate over them. You need to keep the DB connection open as you do this, because you're not guaranteed that all lines of the result set come back at once. Indeed for large result sets, you want to be able to loop through them without ever having the whole result set in memory in the app. At any point, you can call ToList() on your IEnumerable, and then you've materialized everything, and you can close your connection.

Under the hood, the whole stack is optimized for paging. ADO has a page-size property. If you iterate on the IEnumerable, you don't know and don't care how big these pages are, but you should understand that they're there, and that the performance cost of retrieving a page is directly related to the page number. The further you advance into your result set, the more each page costs to retrieve.

The biggest thing you can do for the performance of your app is to get good at SQL, and put all the logic for your projection in the SQL, returning a smaller result set, and avoiding the need to materialize then manipulate a big result set in C#. This is obviously a big generalization, but there. I said it.

Answer 2

As you can see, the results are materialized immediately after they query is executed.

Dapper - Version 1.50.5

public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
{
        CommandDefinition command = new CommandDefinition(sql, param, transaction, commandTimeout, commandType, buffered ? CommandFlags.Buffered : CommandFlags.None);
        IEnumerable<T> enumerable = cnn.QueryImpl<T>(command, typeof(T));
        if (!command.Buffered)
        {
            return enumerable;
        }
        return enumerable.ToList();
}