This post compares IEnumerable against IQuerable using an experiment to illustrate the behaviour and performance differences. Spotting a func vs an expression func filter bug is easy to miss. The caller’s syntax stays the same but it could have a 10x performance impact on your application.
Download Source Code
Setup
SQL Server 2014 was used for hosting the database. The GeoAllCountries table content was sourced from GeoNames and contains just over 10 million rows. Entity Framework 6 was used for the LINQ to SQL integration.
Predicate Function
The code below will query the GeoAllCountries table and use a filter predicate function to filter the results starting with "Aus".
static void Main(string[] args)
{
var stopWatch = Stopwatch.StartNew();
var countryNames = GetCountryNames(name => name.StartsWith("Aus"));
foreach (var name in countryNames)
{
Console.WriteLine(name);
}
stopWatch.Stop();
Console.WriteLine("Running time: {0}", stopWatch.Elapsed.TotalSeconds);
Console.ReadLine();
}
public static IEnumerable<string> GetCountryNames(Func<string, bool> filterFunc)
{
using (var context = new TestDatabaseDataContext())
{
IQueryable<string> names = (from country in context.GeoAllCountries
select country.Name);
foreach (var name in names.Where(filterFunc))
{
yield return name;
}
}
}
Running time: 8.6558463
SQL Server Profiler captured the following query between the application and the database:
SELECT [t0].[Name] FROM [dbo].[GeoAllCountries] AS [t0]
Oops! Filtering was not performed at the database server. This is because the predicate function used in the where clause on line 23 turned IQueryable into IEnumerable.
Expression Predicate Function
The code below will query the GeoAllCountries table and use an expression filter predicate function to filter the results starting with "Aus".
static void Main(string[] args)
{
var stopWatch = Stopwatch.StartNew();
var countryNames = GetCountryNames(name => name.StartsWith("Aus"));
foreach (var name in countryNames)
{
Console.WriteLine(name);
}
stopWatch.Stop();
Console.WriteLine("Running time: {0}", stopWatch.Elapsed.TotalSeconds);
Console.ReadLine();
}
public static IEnumerable<string> GetCountryNames(
Expression<Func<string, bool>> filterFunc)
{
using (var context = new TestDatabaseDataContext())
{
IQueryable<string> names = (from country in context.GeoAllCountries
select country.Name);
foreach (var name in names.Where(filterFunc))
{
yield return name;
}
}
}
Running time: 0.8633603
SQL Server Profiler captured the following query between the application and the database:
exec sp_executesql N'SELECT [t0].[Name]
FROM [dbo].[GeoAllCountries] AS [t0]
WHERE [t0].[Name] LIKE @p0',N'@p0 nvarchar(4000)',@p0=N'Aus%'
Success! Filtering was performed at the database server. This is because the expression predicate function used in the where clause on line 24 kept the statement as IQueryable allowing the expression tree builder to add filters to the select query.
Note that the client code did not change. Adding the expression syntax around the func made a world of difference. It is pretty easy to add the predicate syntax but is just as easy to miss in a code review unless you have the fidelity to spot the issue and understand the implications.
Summary
IEnumerable executes the select query at the database and filters the data in-memory at the application layer.
IQueryable executes the select query and all of the filters at the database.
The database filtering reduced network traffic and application memory load resulting in a significant 10x performance gain.