Does Dapper request a full object from the database prior to Linq operations?

Question

I having been curious for awhile about how Dapper (or perhaps other ORMs) handle object retrieval when combined with LINQ.

If I have a class like this:

public static IEnumerable<SitePage> GetAll()
{
    using (IDbConnection cn = new SqlConnection(g.Global.CONX))
    {
        cn.Open();
        return cn.GetAll<SitePage>();
    }
}

and I construct a query like this:

var result = SitePage.GetAll().Select(c=> new { c.id, c.PageUrl, c.ParentId });

I am curious if in the background, the entire record set gets pulled in including all the other columns (which may contain really big varchars), or does Dapper understand from this query only to pull in the columns I request from the sql db? I realize it's sort of newbish, but I want to better understand the Dapper/LINQ interaction.

A similar question was posted here: selecting-specific-columns-using-linq-what-gets-transferred, though I wasn't sure if was fully answered. The poster had 2 questions, and also wasn't using lambda expressions which I generally prefer.

The answer to this will set my mind afire (and quite possibly change the way I am coding, as I have been cautious and feel I am writing too much code via explicit sql).

Answer 1

Dapper doesn't transform your lambda expressions into SQL, so in your case the SQL query that Dapper generates returns full instances of SitePage.

A quick way to know if that's the case if have a look at the signature of Dapper's GetAll<T> method. As it returns IEnumerable<T>, it means that it returns a collection of T, so any operator you use after that - like Select in your case - will be applied to the full collection. In short, you're no longer in Dapper world after calling GetAll<T>.

If you used in the past fully-fledged ORMs - by that I mean with more capabilities, not necessarily better - like Entity Framework or NHibernate, you'll notice that some APIs return IQueryable<T>, which represents a query that has not yet been executed. So the operators you use on an IQueryable<T>, like Select and Where, actually modify the query. When you materialise the query by iterating it or calling ToList or ToArray on it, then the ORM transforms your query expression tree into SQL and sends that query to the database.