An FAQ about EF/Astoria/LINQ to SQL: Is the data filtered on the server or on the client?

Wednesday, April 01 2009 - Data Access

I've been asked this question enough times that I thought I would blog it. While it may seem obvious to some, it is not to everyone who is just starting out with Entity Framework or LINQ to SQL or ADO.NET Data Services.

When you write a query against an Entity Data Model or a LINQ to SQL model that query is what goes to the server.

There are people who are concerned that the filters don't get applied until after the data has been returned from the server.

In other words if you write a LINQ to Entities query such as

DateTime olddate = DateTime.Now.AddYears(-1);
IEnumerable employee = from e in context.Employees 
                       where e.StartDate > olddate select e;

The store query that is executed on the server includes the Where StartDate>'4/1/2008' filter.

LINQ to SQL works the same way. ADO.NET Data Services works the same way.

If you are wondering why I separated the date calculation to another line of code, that is because LINQ to Entities can use the date parameter, but it cannot interpret the AddYears function. Therefore, I let .NET calculate the date for me in advance.

Tagged as: ,

#1 Michael K. Campbell on 4.01.2009 at 1:55 PM

And... if LINQ/LINQ to EF _WERE_ able to interpret the AddYears function (i.e. into T-SQL's DATEADD(yy,4,StartDate) for example)... then you'd end up with a query that would be less peformant as it would automatically result in an index scan instead of an index seek. So, this approach that LINQ/EF take is actually the best approach as it will yield SARGable queries that are better capable of taking advantage of properly defined indexes. (Now if we could just get MS to be as performance-minded when it came to doing UPDATEs on tables with primary keys defined (rather than trying to do UPDATEs with WHERE clauses that basically include the ENTIRE kitchen sink instead of just the PK).)