How Dapper.NET works internally with .Count() and

I am new to Dapper though I am aware about ORMs and DAL and have implemented DAL with NHibernate earlier.

Example Query: -

string sql = "SELECT * FROM MyTable";
public int GetCount()
{
    var result = Connection.Query<MyTablePoco>(sql).Count();
    return result;
}

Will Dapper convert this query (internally) to SELECT COUNT(*) FROM MyTable looking at .Count() at the end?

Similarly, will it convert to SELECT TOP 1 * FROM MyTable in case of SingleOrDefault()?

I came from NHibernate world where it generates query accordingly. I am not sure about Dapper though. As I am working with MS Access, I do not see a way to check the query generated.

No, dapper will not adjust your query. The immediate way to tell this is simply: does the method return IEnumerable... vs IQueryable...? If it is the first, then it can only use local in-memory mechanisms.

Specifically, by default, Query will actually return a fully populated List<>. LINQ's Count() method recognises that and just accesses the .Count property of the list. So all the data is fetched from the database.

If you want to ask the database for the count, ask the database for the count.

As for mechanisms to view what is actually sent to the database: we use mini-profiler for this. It works great.

Note: when you are querying exactly one row: QueryFirstOrDefault (and the other variants you would expect) exist and have optimizations internally (including hints to ADO.NET, although not all providers can act on those things) to do things as efficiently as possible, but it does not adjust your query. In some cases the provider itself (not dapper) can help, but ultimately: if you only want the first row, ask the database for the first row (TOP or similar).