The question about the basics of LINQ to SQL

Question

I just started learning LINQ to SQL, and so far I'm impressed with the easy of use and good performance.

I used to think that when doing LINQ queries like

from Customer in DB.Customers where Customer.Age > 30 select Customer

LINQ gets all customers from the database ("SELECT * FROM Customers"), moves them to the Customers array and then makes a search in that Array using .NET methods. This is very inefficient, what if there are hundreds of thousands of customers in the database? Making such big SELECT queries would kill the web application.

Now after experiencing how actually fast LINQ to SQL is, I start to suspect that when doing that query I just wrote, LINQ somehow converts it to a SQL Query string

SELECT * FROM Customers WHERE Age > 30

And only when necessary it will run the query.

So my question is: am I right? And when is the query actually run?

The reason why I'm asking is not only because I want to understand how it works in order to build good optimized applications, but because I came across the following problem.

I have 2 tables, one of them is Books, the other has information on how many books were sold on certain days. My goal is to select books that had at least 50 sales/day in past 10 days. It's done with this simple query:

from Book in DB.Books where (from Sale in DB.Sales where Sale.SalesAmount >= 50 && Sale.DateOfSale >= DateTime.Now.AddDays(-10) select Sale.BookID).Contains(Book.ID) select Book

The point is, I have to use the checking part in several queries and I decided to create an array with IDs of all popular books:

var popularBooksIDs = from Sale in DB.Sales where Sale.SalesAmount >= 50 && Sale.DateOfSale >= DateTime.Now.AddDays(-10) select Sale.BookID;

BUT when I try to do the query now:

from Book in DB.Books where popularBooksIDs.Contains(Book.ID) select Book

It doesn't work! That's why I think that we can't use thins kinds of shortcuts in LINQ to SQL queries, like we can't use them in real SQL. We have to create straightforward queries, am I right?

Answer 1

Hey,

Yes, query gets translated to a SQL string, and the underlying SQL can be different depending on what you are trying to do... so you have to be careful in that regard. Checkout a tool called linqpad, you can try your query in it and see the executing SQL.

Also, it runs when iterating through the collection or calling a method on it like ToList().

Answer 2

You are correct. LINQ to SQL does create the actual SQL to retrieve your results.

As for your shortcuts, there are ways to work around the limitations:

var popularBooksIds = DB.Sales
    .Where(s => s.SalesAmount >= 50 
        && s.DateOfSale >= DateTime.Now.AddDays(-10))
    .Select(s => s.Id)
    .ToList();

// Actually should work. 
// Forces the table into memory and then uses LINQ to Objects for the query
var popularBooksSelect = DB.Books
    .ToList()
    .Where(b => popularBooksIds.Contains(b.Id));

Answer 3

Entity framework or linq queries can be tricky sometimes. Sometimes you are surprised at the efficiency of the sql query generated and sometimes the query is so complicated and inefficient that you would smack your forehead.

Best idea is that if you have any suspicions about a query, run an sql profiler at the backend that would monitor all the queries coming in. That way you know exactly what is being passed on to the sql server and correct any inefficiencies if need be.

Answer 4

http://damieng.com/blog/2008/07/30/linq-to-sql-log-to-debug-window-file-memory-or-multiple-writers

This will help you to see what and when queries are being run. Also, Damiens blog is full of other linq to sql goodness.

You can generate an EXISTS clause by using the .Any method. I have had more success that way than trying to generate IN clauses, because it likes to retrieve all the data and pass it all back in as parameters to a query

In linq to sql, IQueryable expression fragments can be combined to create a single query, it will try to keep everything as an IQueryable for as long as it can, before you do something that cannot be expressed in SQL. When you call ToList you are directly asking it to resolve that query into an IEnumerable stored in memory.

In most cases you are better off not selecting the book ids in advance. Keep the fragment for popular books in a single place in the code and use it when necessary, to build on another query. An IQueryable is just an expression tree, which is resolved into SQL at some other point.

If you think your application will perform better by storing the popular books elsewhere (memcache or whatever), then you may consider pulling them out before hand, and checking against that later. This will mean each book id will be passed in as a sproc parameter and used in an IN clause.