When selected into an object; does Linq To SQL still perform the logic in a SQL Query?

Question

Let's say I have the following simplified Linq query which will be run against the database using LinqToSQL:

public IEnumerable<MembershipDto> GetAllUserMemberships()
{
    return (from m in _workspace.GetDataSource<UserMembership>()
            join g in _workspace.GetDataSource<Group>()
                on m.GroupID equals g.GroupID
            join u in _workspace.GetDataSource<User>()
                on m.UserID equals u.UserID
            select 
                new MembershipDto
                {
                    GroupID = g.GroupID,
                    GroupName = g.Name,
                    UserID = u.UserID,
                    UserName = u.Name
                }
            ).Distinct()
             .OrderBy(x => x.GroupName)
             .ThenBy(x => x.UserName);
}

For performance sake I want as much of this query as possible to be run inside the generated SQL query; however I want the maintain the business logic in Linq so it can be easily unit tested.

The issue I have is that I am uncertain where the above query stops being executed in SQL and starts being operated on in memory.

My assumption is that as soon as the data is being selected into the new MembershipDto it is no longer being conducted in SQL; therefore the subsequent OrderBy() ThenBy() and Distinct() operations occur in memory.

However the OrderBy() produces a collection of type IOrderedQueryable rather than an IOrderedEnumerable; which indicates to me that the query may still be executed in its entirety in SQL.

Is my assumption correct, or is the whole query converted into SQL and if so why is this the case?

Answer 1

Just look at the generated SQL. I'm fairly confident that your italicized assumption is wrong. The LINQ to SQL provider will recognize that your OrderBy, ThenBy and Distinct operations are executed exclusively on SQL results so it completes those on the rows you select before deserializing them into objects. That part of the query would only executed by LINQ to Objects if you tried to operate on values that aren't available to the database at execution time.

If in your initializer you set some property to a value that wasn't available in the query results then used that in the OrderBy clause it would certainly do the ordering in memory because there would be no other way. In this case, your select is translated into a SQL SELECT, then OrderBy and Distinct are called as they normally would be. Converting your selection to an object is an operation that is completely independent from selecting columns in the database. To the SQL provider your query is no different than a simple select statement, it builds an expression tree and converts whatever it can into SQL at execution time.

Answer 2

I believe all of the code you posted will be converted into sql. The MembershipDto() construction will map to a select statement.

You will "exit" Linq to Sql and "enter" Linq to Objects when your function casts to IEnumerable and the enumerator is executed.

If you're developing in Linq to Sql its absolutely essential that you have the tools to test this for yourself.

I recommend LINQPad. You code in C# and it will show you the generated SQL.

Another solution is to log the generated SQL within your Data Context:

#if DEBUG
        /// <summary>
        /// Code which runs when the data context is created; called from the constructor
        /// </summary>
        /// <remarks>Adds console and/or ASP.NET trace logger in DEBUG builds only</remarks>
        partial void OnCreated()
        {
            this.DebugBuildLogging();
        }
#endif

I would also recommend reading a good book on LINQ. You will be rewarded many times over.