Entity Framework 5 and SQL Queries

Question

I am facing serious performance issues... My query is supposed to filter Products with SQL directly in database. When I execute this code, it doesn't, and it returns all products and filters them in C#.

MyContext context = new MyContext();

Func<Product, bool> query = (p => p.UPC.StartsWith("817"));

var products = context.Products.Where(query).Take(10);

I've noticed that the products variable is of type TakeIterator. When I change the code slightly, I get the filtering OK, but it forces me to put the query logic directly in the same method, which is what I want to avoid.

MyContext context = new MyContext();

var products = context.Products.Where(p => p.UPC.StartsWith("817")).Take(10);

This second version is of an undisclosed type by the Visual Studio debugger, but it shows as the query I am trying to end with, which is good!

{SELECT TOP (10) 
[Extent1].[Id] AS [Id], 
[Extent1].[Brand] AS [Brand], 
[Extent1].[Description] AS [Description], 
[Extent1].[UPC] AS [UPC]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[UPC] LIKE N'817%'}

I need to figure out how to get a Func passed as an argument and execute the query in the same fashion as the first C# code excerpt, but with optimisations of the second.

Answer 1

Try this instead:

MyContext context = new MyContext();

Expression<Func<Product, bool>> query = (p => p.UPC.StartsWith("817"));

var products = context.Products.Where(query).Take(10);

And see this question for reference on why:

Why would you use Expression<Func<T>> rather than Func<T>?

The accepted answer there is so complete that I don't dare try to explain it better!