When should I use IEnumerable and when IQueryable?

Question

I have tried to look for a difference quite a few times and there are multiple answers.

One of the common known difference is that IEnumerable filters data from within memory while IQueryable does it on the server side.

What does this mean exactly?

Isn't filtering occurring on in-memory data still a server side thing?

I have tried to look for their use in multiple places but I haven't been able to find it in simple words.

Thank you.

Answer 1

IEnumerable<T> represents something which produces a sequence of results. However, it doesn't expose any information about how the sequence is produced.

IQueryable<T> exposes the information about how the sequence is to be produced, at the Expression property, in the form of an expression tree. This information can then be easily mapped to a different set of instructions.

If you call Enumerable.Where on an IEnumerable<T>, you're passing in a compiled method compatible with Func<T, bool>. In theory, we could parse the IL of the compiled method to figure out what the method does, and use that to map to another set of instructions; but that's very complex. Inevitably, the only way to work with this is to load all the objects into memory from the server/provider/datasource, and apply the compiled method on each object.

If you call Queryable.Where on an IQueryable<T>, you're passing in an object which by definition represents different code operations -- Expression<Func<T, bool>>. For example:

IQueryable<Person> qry = /* ... */;
qry = qry.Where(x => x.LastName.StartsWith("A"));

the compiler converts x => x.LastName.StartsWith("A") to an object representing its various parts:

Lambda expression, returning a `bool`
    with an `x` parameter of type `Person`
    Call the `StartsWith` method, passing in a constant string `"A"`, on
        Result of the `LastName` property, of
            the `x` element

More, calling Queryable.Where itself also modifies the underlying expression tree:

Call to Queryable.Where, passing in
    The object at `qry`, and
    The previous lambda expression (see above)

When the query is enumerated (either with a foreach, or a call to ToList, or something similar), the information can easily be mapped from this object into another form, such as SQL:

SELECT *
FROM Persons
WHERE LastName LIKE N'A%'

or a web request:

http://example.com/Person?lastname[0]=a

---

The final expression tree after calling Queryable.Where will look something like this object graph, if it were possible to construct expression trees using constructors and object and collection initializers:

var x = new ParameterExpression {
    Type = typeof(Person),
    Name = "x"
};

new MethodCallExpression {
    Type = typeof(IQueryable<Person>),
    Arguments = new ReadOnlyCollection<Expression> {
        new ConstantExpression {
            Type = typeof(EnumerableQuery<Person>)
        },
        new UnaryExpression {
            NodeType = ExpressionType.Quote,
            Type = typeof(Expression<Func<Person, bool>>),
            Operand = new Expression<Func<Person, bool>> {
                NodeType = ExpressionType.Lambda,
                Type = typeof(Func<Person, bool>),
                Parameters = new ReadOnlyCollection<ParameterExpression> {
                    x
                },
                Body = new MethodCallExpression {
                    Type = typeof(bool),
                    Object = new MemberExpression {
                        Type = typeof(string),
                        Expression = x,
                        Member = typeof(Person).GetProperty("LastName")
                    },
                    Arguments = new ReadOnlyCollection<Expression> {
                        new ConstantExpression {
                            Type = typeof(string),
                            Value = "A"
                        }
                    },
                    Method = typeof(string).GetMethod("StartsWith", new[] { typeof(string) })
                },
                ReturnType = typeof(bool)
            }
        }
    },
    Method = typeof(Queryable).GetMethod("Where", new[] { typeof(IQueryable<Person>), typeof(Expression<Func<Person, bool>>) })
}

(NB. This was written using the ExpressionTreeToString library. Disclaimer: I am the author.)

Answer 2

One of the common known difference is that IEnumerable filters data from within memory while IQueryable does it on the server side

You are right, everything is happening in the server. But in the sentence above, server side means SQL Server, not the server running the .NET application. So the main idea is that an IQueryable can be translated to a SQL script that will be executed in SQL Server, which often brings a significant performance gain compared to bringing the data from the SQL Server to the application memory and then performing the query in-memory.

Answer 3

Zev Spitz has already provided a very nice account of the difference, but I felt like supplementing with an example of what happens in an extreme case, to highlight the difference.

Let's say your data is on one server, and the application runs on another. These are connected via the internet.
Let's assume you have a table in the data which has 1 million rows, and you want the one with a given ID, thatId.
Ignoring how exactly you connect, let the variable Table be your representation of that data in your code.

Now, we can get the row we want by asking for Table.Where(x => x.Id == thatId).

If Table is an IQueryable, then this will be translated into a request to the Table for that one row, which will be returned from the server storing the data in the memory of the server running the application.

On the other hand, if we treat this as an IEnumerable, for example by doing this as Table.AsEnumerable().Where(x => x.Id == thatId) then this will be turned into a request for all the rows of the table, which will then be transferred to the memory on the server running the application. Only after transferring all of those million rows across the internet will the application sort through the data and pick out the row you really wanted.

Answer 4

Not 100% sure, but i think that it means that with an IQueryable, in an example in which we are working with EF in the BE and a SQLike as DB, any Linq procedure written in it will be converted in SQL and sent to the DB, that will elaborate such sql code and then return the results.

While an IEnumelable miss this feature and if you, for example, convert and entire entity into a IEnumerable you will elaborate any filter always inside the BE.

I hope i'm right and i've been clear, have a nice session