CODEWITHSUNDEEP
c#sqlperformancelinqlinq-to-sql
Igor Ševo
Igor Ševo

Reputation: 5515

LINQ to SQL under the hood

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.

Upvotes: 3

Views: 905

Answers (3)

Jens Kloster
Jens Kloster

Reputation: 11277

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")

Upvotes: 3

Cyril Gandon
Cyril Gandon

Reputation: 17058

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");
        }
    }
}

Upvotes: 7

Roy Dictus
Roy Dictus

Reputation: 33139

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...

Upvotes: 1

Related Questions