IEnumerable vs IQueryable – Part 2: Practical Questions

If you’re unsure of the difference between IEnumerable and IQueryable, please read Part 1 first.

In this article we’ll take a practical Q&A approach to test your understanding of these interfaces and how the work. Try figure each question out first before looking at the answers.

Setting the stage:

• We’ll be using Entity Framework (EF)
• with a SQL database
• that has 1 table “Person”.
• Assume we have a global DbContext instance called db

Here goes, good luck…

# Question 1

• On which line(s) does the Database get queried?
• How many times does the database get queried?

Console.WriteLine("Some code");
IQueryable<Person> people = db.People.Where(p => p.Name == "Niels");
IQueryable<Person> activePeople = people.Where(p => p.IsActive == true);
Console.WriteLine("Some more code");

foreach (var person in people) 
{ 
   Console.WriteLine($"{person.Id} : {person.Name} {person.Surname}");
} 

Answer:

• Line 6. Because of deferred execution, the database will only be hit once the IQueryable is used. This is in the foreach loop, when we need to access the first person in people collection.
• Only once at Line 6. It’s perfectly valid to build on previous IQueryable variables appending filters as you go along. Once executed eventually, only 1 query will be executed, taking into account all filter expressions added.

# Question 2

1. On which line does the Database get queried?

Console.WriteLine("Some code");
IQueryable<Person> people = db.People.Where(p => p.Name == "Niels");
IQueryable<Person> activePeople = people.Where(p => p.IsActive == true);
Console.WriteLine("Hello World");

Answer:

• Never. Because of deferred execution, the query will not execute until it’s used. Since it’s never used, it will never execute and only remain “intent”.

# Question 3

Person table has 100 records. 60 of these records are active and 40 inactive

1. On which line does the Database get queried?
2. Will the database query bring back 100 people or 60 people?
3. Is there an obvious way to improve the query?

var allPeople = db.People.ToList();
var activePeople = allPeople.Where(p => p.IsActive == true);

foreach (var person in activePeople)
{
   Console.WriteLine($"{person.Id} : {person.Name} {person.Surname}");
}

Answer:

• Line 1. ToList() forces the query to execute
• 100 records will be returned. Line 1 executed the query and loaded 100 results to memory. Line 2 then filtered the collection in memory and created a new collection in memory with 60 records.
• Yes there is, simply remove the ToList() in line 1. This means that allPeople will be IQueryable and finally only in the foreach (line 4) the database will be queried returning only 60 results into memory.

# Question 4

Person table has 100 records. 20 people’s names start with the letter “N”. Of these 20, 10 are active and 10 are inactive.

1. On which line does the Database get queried?
2. Will the database query bring back 100 people, 20 or 10 people?
3. Is there an obvious way to improve the query?

var allPeople = db.People.Where(p => p.Name.StartsWith("N"));
IEnumerable<Person> enumerablePeople = allPeople;
var activePeople = enumerablePeople.Where(p => p.IsActive == true);

foreach (var person in activePeople)
{
   Console.WriteLine($"{person.Id} : {person.Name} {person.Surname}");
}

Answer:

• Line 5. Even though we cast to IEnumerable (a memory collection), deferred execution will still only execute the query once needed (in the foreach).
• 20 records will be returned. Even though the query only executed at line 5. We indicated in line 2 that “from here on out, we will work with the collection in memory”. So once the foreach executes the query, Line 1’s query will execute against the database, returning 20 results into memory. Then Line 3 filters the 20 records in memory and creates a new collection in memory with 10 records.
• Yes there is, simply removing Line 2 would keep everything IQueryable until it’s needed in the foreach (line 4). Then the database would only return back the 10 results needed.

# Question 5

Person table has 100 records. 20 people’s names start with the letter “N”. Of these 20, 10 are active and 10 are inactive.

1. On which line does the Database get queried?
2. Will the database query bring back 100 people, 20 or 10 people?
3. Is there an obvious way to improve the query?

var allPeople = db.People.Where(p => p.Name.StartsWith("N"));
IEnumerable<Person> activePeople = allPeople.Where(p => p.IsActive == true);

foreach (var person in activePeople)
{
   Console.WriteLine($"{person.Id} : {person.Name} {person.Surname}");
}

Answer:

• Line 4. Even though we cast to IEnumerable (a memory collection), deferred execution will still only execute the query once needed (in the foreach).
• 10 records will be returned. Even though we marked the collection at line 2 as IEnumerable, the IsActive is still applied to the IQueryable before it’s cast to an IEnumerable
• No. The query works fine. It can be somewhat misleading with the IEnumerable at Line 2, so ideally we should replace this with var. But with no performance difference.

# Question 6

1. Will this query work?
2. If it works. On which line does the Database get queried?
3. If it works. Will the database return all columns in Person table or just a list of strings?

var activePeople = db.People
     .Where(p => p.IsActive == true)
     .Select(p => p.Name + " " + p.Surname);

foreach (var person in activePeople)
{
   Console.WriteLine(person);
}

Answer:

• Yes it works
• Line 5. Deferred execution will ensure that we only execute the query once needed (in the foreach).
• The database will query the database with a statment something like this: SELECT Name + ' ' + Surname FROM...and a list of strings will be loaded into memory (not all the Person columns)

# Question 7

Same as Question 6, except we’ve moved the Select part into a separate method.

1. Will this query work?
2. If it works. On which line does the Database get queried?
3. If it works. Will the database return all columns in Person table or just a list of strings?

var activePeople = db.People
     .Where(p => p.IsActive == true)
     .Select(p => BuildPersonName(p));

foreach (var person in activePeople)
{
   Console.WriteLine(person);
}
...

private string BuildPersonName(Person p)
{
   return p.Name + " " + p.Surname;
}

Answer:

• No it doesn’t work. A NotSupportedException will be thrown at runtime. Even though we’re doing exactly the same as Question 6 and only moved the Select part into a separate method, this query will fail.Why? Remember that IQueryable builds up an Expression Tree from our LINQ and then translates that into a SQL Query. Since our LINQ references a method in our .NET code, how would SQL map .NET functions with the SQL query? It would try to do something like this:
  SELECT BuildPersonName(p) FROM Person p... which can never work since `BuildPersonName` is a .NET function and not a SQL function.

# Question 8

Same as Question 7, except we added a .ToList() after the Where(...)

1. Will this query work?
2. If it works. On which line does the Database get queried?
3. If it works. Will the database return all columns in Person table or just a list of strings?
4. If it works. Is there an obvious way to improve the query?

var activePeople = db.People
     .Where(p => p.IsActive == true).ToList()
     .Select(p => BuildPersonName(p));

foreach (var person in activePeople)
{
   Console.WriteLine(person);
}
...

private string BuildPersonName(Person p)
{
   return p.Name + " " + p.Surname;
}

Answer:

• Yes it works. Unlike Question 7, this is perfectly valid. Since we return the database query results after the Where, the Select part will be handled in memory and therefore our LINQ can reference .NET functions.
• Line 2. The ToList() will execute the query with the IsActive filter and return the results to memory.
• The database will return all columns in the Person table, since the ToList() was called before the Select
• Yes there is. Returning all columns from Person is less optimum than just returning the appended name. Changing the code to look like Question 6 is the best for performance.

 

Conclusion

The above questions tried to cover all the different ways that IEnumerable and IQueryable could be used. I believe if you understand why each query behaved the way it did in the above questions, you can figure out any query behaviour.