LINQ to SQL under the hood

Question

I know this was partly asked before, but none of the questions completely answer this.

What happens when one uses LINQ to SQL to retrieve data from the database?

I've read the following questions:

What is unclear to me is: at which point is the database accessed? When are the queries run?

If I run the following query, how will it translate to a SQL query?

    DatabaseDataContext db = new DatabaseDataContext();
    var users = from x in db.Users 
                where x.Rank > 10 
                orderby x.RegistrationDate descending
                select x)
                .Skip(pageIndex * recordCount)
                .Take(recordCount);

And then, later, if I try to access some property of some user, how will the query be constructed (this is partly answered here)?

var temp = users.ToList()[0].SomeProperty;

Basically, what I really want to know is how the LINQ to SQL works under the hood, how it goes from the C# language statement to the SQL and how it is optimized.

_"what I really want to know is how the LINQ to SQL works under the hood, how it goes from the C# language statement to the SQL and how it is optimized"_ - I think you really want to know how you can optimize specific queries for LINQ to SQL, not how the latter works internally. Am I wrong? — CodeCaster, Nov 05 '13 at 09:44
In the code in your question: it is the `ToList()` that causes a query to execute, by walking the enumerator of the query. Until then, it is purely *composition*. — Marc Gravell, Nov 05 '13 at 09:48
@CodeCaster: I actually know how to optimize the LINQ queries, I wanted to know more of the implementation. If you know the internals, you can better understand optimization opportunities in very specific scenarios. — Igor Ševo, Nov 05 '13 at 09:51
I don't think the source code implementing that is available, and as it is an implementation detail it is bound to change between versions. I think you'd better just analyze the generated SQL in those specific scenarios. Anyway your question seems to be purely read as _"When are the queries run?"_, according to the answers. If you want an answer to your actual question, try rewording it. — CodeCaster, Nov 05 '13 at 10:02
As a sidenote, if you have it on your system, SQL Server Profiler is a great tool for seeing what goes on under the hood, and when things happen when working with LINQ to SQL. — JMK, Nov 05 '13 at 10:04
@JMK meh; mini-profiler is even easier to get working, and can run 24x7 so you *always* know what is happening — Marc Gravell, Nov 05 '13 at 11:13
@MarcGravell I had never heard of it, thanks, will check it out! — JMK, Nov 05 '13 at 11:29

Cyril Gandon · Accepted Answer · 2013-11-05T10:01:15.113

The LINQ to SQL framework will take your Linq query, which is in fact an expression tree, and then will convert this expression tree into a pure SQL query. See How to: Use Expression Trees to Build Dynamic Queries

In fact, every expression tree can be translated into whatever langage or database you need. You will have different providers implementing IQueryable for different databases (Oracle, SQLite, etc.). Note that LINQ to SQL is the abbreviation of LINQ to SQL Server. On the other hand, Entity framework/LINQ to Entities can be extent more easily for other databases.

The main point here is the IQueryable interface, which contains an expression tree, and also the implementation of the provider. For an example on how to implement a provider, ie how to translate from an expression tree to a query, see LINQ: Building an IQueryable Provider

Here is a snippet that will give you a flavor of what happens under the hood:

if (select.OrderBy != null && select.OrderBy.Count > 0) 
{
    this.AppendNewLine(Indentation.Same);
    sb.Append("ORDER BY ");
    for (int i = 0, n = select.OrderBy.Count; i < n; i++) 
    {
        OrderExpression exp = select.OrderBy[i];
        if (i > 0) 
        {
            sb.Append(", ");
        }
        this.Visit(exp.Expression);
        if (exp.OrderType != OrderType.Ascending) 
        {
            sb.Append(" DESC");
        }
    }
}

In fact Linq to XML is not translated to any XML query. It does not use expressions, and it behaves as Linq to Objects — Sergey Berezovskiy, Nov 05 '13 at 09:47

score 3 · Answer 2 · edited Nov 05 '13 at 10:14

3

The queries are run as soon as you demand the result.

var qry = (from x in db.Users where x.Rank > 10 orderby x.RegistrationDate descending
            select x)

at this point the query has not run, becuase you haven't used the result.
Put it in a foreach or transfor it to a List and the query is forced to Materiliaze.

The rule of thumb is: Whenever GetEnumerator is called on an IQueryable - the query is forced to materiliaze (wich meens "to to the database and get the actual recourds")

edited Nov 05 '13 at 10:14

Jakub Konecki

45,581
7
87
126

answered Nov 05 '13 at 09:44

Jens Kloster

11,099
5
40
54

In that example, `qry` will be of a type implementing `IQueryable`; it mightn't have been materialized *yet*; calling `AsEnumerable()` or `ToList()` will materialize it. – Rowland Shaw Nov 05 '13 at 12:10

Roy Dictus · Answer 3 · 2013-11-05T09:49:30.010

All you want to know is answered in the article on MSDN about LINQ to SQL: http://msdn.microsoft.com/en-us/library/bb425822.aspx

By the way, if you're only going to use a part of your result, as in your code above, it's better to modify your query, like so:

var prop = (from x in db.Users 
                where x.Rank > 10 
                orderby x.RegistrationDate descending
                select x.SomeProperty)
                .Skip(pageIndex)
                .First()
                .Select(x => x);

Optimization you do in your query is often more important than how the system performs peephole optimization under the hood...

LINQ to SQL under the hood

3 Answers3