Explanation of dapper buffer/cache

I use dapper to return objects from my database as IEnumerable. As default dapper has buffer setting set to true.

How does this work?

If dapper cache the first query and then get the objects from memory.

What happens if someone edit/delete/add rows in the table. Must dapper recache all data again for this query?

The buffer is unrelated to cache. Dapper does not include any kind of data-cache (although it does have a cache related to how it processes commands, i.e. "this command string, with this type of parameter, and this type of entity - has these associated dynamically generated methods to configure the command and populate the objects").

What this switch really means is:

• false: will iterate items as they are recieved/consumed - basically, an iterator-block around an IDataReader
  • minus: you can only iterate it once (unless you are happy to re-run the query)
  • plus: you can iterate over immense queries (many millions of rows), without needing them all in-memory at once - since you're only ever really looking at the current row being yielded
  • plus: you don't need to wait for the end of the data to start iterating - as soon as it has at least one row, you're good to go
  • minus: the connection is in-use while you're iterating, which can lead to "there is already an open reader on the connection" (or whatever the exact wording is) errors if you try to invoke other commands on a per-row basis (this can be mitigated by MARS)
  • minus: because the consumer can do anything they want per-item (it could take minutes per row, if they are doing something complex), the command/reader might be open for longer
• true (the default): the data is fully consumed into a List<T> before it hands it back to you
  • plus: you can iterate it as many times as you like
  • minus: if the query is immense, loading them all into memory (in a list) could be expensive / impossible
  • minus: if the query is large, there may be noticeable latency while it gathers the last row
  • plus: once you get the data, the command is complete - so there is no conflict between that and subsequent operations
  • plus: as soon as you get the data, the command has already released any resources (locks etc), so you're having minimal impact on the server

Most queries only return a moderate amount of data (say, less than 100 records), so we're happy that the default (true) gives the most appropriate behavior for most scenarios. But we make the option available to you, to cater for different usage scenarios.