Is a full list returned first and then filtered when using linq to sql to filter data from a database or just the filtered list?

This is probably a very simple question that I am working through in an MVC project. Here's an example of what I am talking about.

I have an rdml file linked to a database with a table called Users that has 500,000 rows. But I only want to find the Users who were entered on 5/7/2010. So let's say I do this in my UserRepository:

from u in db.GetUsers() where u.CreatedDate = "5/7/2010" select u

(doing this from memory so don't kill me if my syntax is a little off, it's the concept I am looking for)

Does this statement first return all 500开发者_JAVA技巧,000 rows and then filter it or does it only bring back the filtered list?

It filters in the database since your building your expression atop of an ITable returning a IQueryable<T> data source.

Linq to SQL translates your query into SQL before sending it to the database, so only the filtered list is returned.

When the query is executed it will create SQL to return the filtered set only.

One thing to be aware of is that if you do nothing with the results of that query nothing will be queried at all.

The query will be deferred until you enumerate the result set.

These folks are right and one recommendation I would have is to monitor the queries that LinqToSql is creating. LinqToSql is a great tool but it's not perfect. I've noticed a number of little inefficiencies by monitoring the queries that it creates and tweaking it a bit where needed.

The DataContext has a "Log" property that you can work with to view the queries created. I created a simple HttpModule that outputs the DataContext's Log (formatted for sweetness) to my output window. That way I can see the SQL it used and adjust if need be. It's been worth its weight in gold.

Side note - I don't mean to be negative about the SQL that LinqToSql creates as it's very good and efficient almost every time. Another good side effect of monitoring the queries is you can show your friends that are die-hard ADO.NET - Stored Proc people how efficient LinqToSql really is.