I've participated in most interview processes as an interviewer, and have mostly used the above question as a starting point. The frustrating thing was that in most cases, I did not receive a correct answer to my question.
In this article, I’ll try to explain the differences between IEnumerable and IQueryable. I hope this will help you to understand or to refresh your understanding of the answer.
Let’s get started.
One of the new things added to In C# 3.0 was LINQ( Language Integrated Query). This works with different data containers using the same interfaces. You can think about it as a “single interface – multiple sources."
LINQ queries work not only with in-memory containers (arrays, collections, generics, etc), but also with outside-of-memory containers, like databases, JSON, XML, etc.
When you write LINQ queries against in-memory containers, you can find that they mostly return IEnumerable, but outside of memory containers mostly return IQueryable as an interface.
The generic interface IEnumerable<>(System.Collections.Generic) inherited from IEnumerable(System.Collections) and it has the GetEnumerator() method.
public interface IEnumerable < out T > : IEnumerable {
IEnumerator < T > GetEnumerator();
}
The IQueryable interface is also inherited from IEnumerable, but it has methods to implement dynamic querying to the container.
public interface IQueryable: IEnumerable {
Expression Expression {
get;
}
Type ElementType {
get;
}
IQueryProvider Provider {
get;
}
}
The differences between IEnumerable and IQueryable, in theory:
- If the container “lives” in memory (array, collection, etc) then IEnumerable will be used. Otherwise (if it is outside of memory), IQueryable will be used.
- IEnumerable can do forward-only reading in memory, but IQueryable can do the forward-backward reading.
- Querying as IEnumerable will lead to retrieving all the data to the memory. This means if you want to do some filtering, sorting, etc., it will be done in memory rather than in the source of the information. IQueryable doesn’t read all the information to the memory. First, it collects all the queries and executes them as a single query to the container. For additional queries (sorting, filtering, etc.) through the IQueryable interface, .NET will “generate” query to the source, and all the operations will be done directly in the source.
Let me briefly explain it using a real-world example:
Say you have 100 rows in a database. If you want to make these rows using IEnumerable, .NET will read all the rows to the memory. After that, writing, sorting and filtering will be applied directly to memory, because all information lives in memory. But in the case of IQueryable, applying WHERE, SELECT, ORDER BY, etc. will cause the “system” to generate an additional query to the source of the container (in this case to the database) and execute the query on the container side.
Differences between IEnumerable and IQueryable in practical examples:
Let's consolidate all the above information in practice by creating a console application. It doesn’t matter for now whether you use .NET core or classical. NET, because the topic is applicable for both. For ease, I’ll create a .NET framework console application.
Add the below Person class to the project:
namespace IEnumerableVsIQueryable {
public class Person {
public int Id {
get;
set;
}
public string Name {
get;
set;
}
public string Surname {
get;
set;
}
public string Email {
get;
set;
}
public bool IsEmailConfirmed {
get;
set;
}
public string Password {
get;
set;
}
public override string ToString() {
return $ "Name = {Name} ,Surname = {Surname}\n Email = {Email} ,IsEmailConfirmed = {
IsEmailConfirmed
}
";
}
}
}
Let's config EF code first and update the database.
public class AppDbContext: DbContext {
public AppDbContext(): base("appDb") {}
public DbSet People {
get;
set;
}
}
namespace IEnumerableVsIQueryable.Migrations {
using System.Data.Entity.Migrations;
using System.Linq;
internal sealed class Configuration: DbMigrationsConfiguration {
public Configuration() {
AutomaticMigrationsEnabled = false;
}
protected override void Seed(IEnumerableVsIQueryable.AppDbContext context) {
//add test datas
if (!context.People.Any()) {
Person[] people = new Person[] {
new Person {
Email = "[email protected]", IsEmailConfirmed = true,
Name = "Satoru", Password = "@#$R@DEC@#$", Surname = "Fujinuma"
},
new Person {
Email = "[email protected]", IsEmailConfirmed = true,
Name = "Airi", Password = "@#$R@DEC@#$", Surname = "Katagiri"
},
new Person {
Email = "[email protected]", IsEmailConfirmed = true,
Name = "Kayo", Password = "@#$RDwewe", Surname = "Hinazuki"
},
new Person {
Email = "[email protected]", IsEmailConfirmed = true,
Name = "Sachiko", Password = "23534t5324edSDF", Surname = "Fujinuma"
},
new Person {
Email = "[email protected]", IsEmailConfirmed = true,
Name = "Kenya", Password = "@#@#$@#4dd32", Surname = "Kobayashi"
}
};
context.People.AddOrUpdate(people);
}
}
}
}
<connectionStrings >
< add connectionString="Data Source=.;Initial Catalog=IEvsIQ;Integrated Security=SSPI;" name="appDb" providerName="System.Data.SqlClient" />
<connectionStrings >
I’ll write the first example using IEnumerable.
using System.Collections.Generic;
using System.Linq;
using static System.Console;
namespace IEnumerableVsIQueryable {
class Program {
static void Main(string[] args) {
List < Person > response;
using(AppDbContext appDbContext = new AppDbContext()) {
//log sql queries to console output
appDbContext.Database.Log = (str) => WriteLine(str);
//get data as IEnumerable
IEnumerable < Person > people = appDbContext.People;
//and then filter it
response = people.Where(x => x.Surname.StartsWith("Fuji")).ToList();
}
Show(response);
ReadLine();
}
private static void Show(IEnumerable people) {
foreach(Person person in people) {
WriteLine(person);
}
}
}
}
![]()
Let's write the same examples using IQueryable.
using(AppDbContext appDbContext = new AppDbContext()) {
//log sql queries to console output
appDbContext.Database.Log = (str) => WriteLine(str);
//get data as IQueryable
IQueryable people = appDbContext.People;
//and then filter it
response = people.Where(x => x.Surname.StartsWith("Fuji")).ToList();
}
And the result is:
![]()
As you can see from the above example, IQueryable “redirects” query to the data source(database).