AsEnumerable vs ToList vs AsQueryable in EF Core: Where SQL Stops and C# Starts

In the article on IEnumerable vs IQueryable I argued that you should keep IQueryable in the type until you genuinely want rows in memory - and make that moment explicit. These three methods are that moment. Getting them right is the difference between a deliberate hand-off and an accidental table load, and one of them hides a trap that only ever shows up in your test suite.

What actually changes when you call one of these?

Nothing about your data. Everything about who does the work.

Up to the point you call one of them, you are composing an expression tree that EF Core will translate into SQL. After it, you are composing delegates that run in your process. The docs call this switching to client evaluation, and they are explicit that it is something you opt into: "you can explicitly opt into client evaluation by calling methods like AsEnumerable or ToList (AsAsyncEnumerable or ToListAsync for async)".

So the only real question is where you put the line - and how much data crosses it.

AsEnumerable or ToList - which one?

Both switch you to the client. They differ in when the query runs and what it costs you in memory, and the docs put it plainly: "By using AsEnumerable you would be streaming the results, but using ToList would cause buffering by creating a list, which also takes additional memory. Though if you're enumerating multiple times, then storing results in a list helps more since there's only one query to the database."

The performance guidance spells out what that buys you: "the memory requirements of a streaming query are fixed - they are the same whether the query returns 1 row or 1000; a buffering query, on the other hand, requires more memory the more rows are returned". Which leads to a rule Microsoft states outright, and which is worth pinning to your wall: "Avoid using ToList or ToArray if you intend to use another LINQ operator on the result - this will needlessly buffer all results into memory. Use AsEnumerable instead."

The second condition is easier to miss. "In certain situations, EF will itself buffer the resultset internally, regardless of how you evaluate your query" - specifically when a retrying execution strategy is configured, and when split query is in use. So if you turned on EnableRetryOnFailure for Azure SQL, your carefully streamed query is being buffered underneath anyway. Worse, the docs warn that this stacks: "if you use ToList on a query and a retrying execution strategy is in place, the resultset is loaded into memory twice: once internally by EF, and once by ToList."

When should you deliberately switch to the client?

The docs give two good reasons: "The amount of data is small so that evaluating on the client doesn't incur a huge performance penalty", and "The LINQ operator being used has no server-side translation."

The second one is the common case. Some C# method has no SQL equivalent, EF Core refuses to translate it, and you have to run it yourself. The rule is not whether you hand off, but how much you hand off - narrow the set on the server first, and only then cross the line.

// The database does the heavy lifting...
var flagged = db.Orders
    .Where(o => o.PlacedAt > cutoff)   // SQL
    .Where(o => o.Total > 1_000)       // SQL
    .AsNoTracking()                    // nothing to track - we only read
    .AsEnumerable()                    // <- the hand-off, explicit
    .Where(o => IsSuspicious(o))       // C#, no SQL translation exists
    .ToList();

Move the AsEnumerable() one line up, before the filters, and you have written the exact query the previous article was about. The method is not the problem. Its position is.

In async code, reach for AsAsyncEnumerable() rather than AsEnumerable(). It is what the docs point at for exactly this shape - "use AsAsyncEnumerable to execute the query on the database, and continue composing client-side LINQ operators over the resulting IAsyncEnumerable<T>" - and it streams, so you keep the property you came for.

Do you even need to switch?

Often not - and this is the part that is easy to get backwards. Client evaluation in the final projection is allowed. EF Core "supports partial client evaluation in the top-level projection (essentially, the last call to Select())".

So if your untranslatable C# only appears in the last Select, you need no hand-off at all. EF Core fetches the columns it needs and runs your method on the results.

// No AsEnumerable needed - this works as-is
var rows = await db.Orders
    .Where(o => o.Total > 1_000)                  // SQL
    .Select(o => new OrderRow(
        o.Id,
        FormatReference(o.Id, o.PlacedAt)))       // C#, in the projection
    .ToListAsync(ct);

Put that same FormatReference call inside a Where instead and EF Core throws, because "If EF Core detects an expression, in any place other than the top-level projection, which can't be translated to the server, then it throws a runtime exception." Which is a good thing - the exception is the framework refusing to quietly drag your table into memory.

What does AsQueryable actually do?

It depends entirely on what you call it on, and the two cases could not be more different. The API documentation states both in one sentence: "If the type of source implements IQueryable<T>, AsQueryable returns it directly. Otherwise, it returns an IQueryable<T> that executes queries by calling the equivalent query operator methods in Enumerable instead of those in Queryable."

On a real query, it gives you back the real query. If the object is already an IQueryable - an EF Core query that has merely been typed as IEnumerable - AsQueryable() returns it directly, provider and all. SQL translation is restored.

IEnumerable<Order> leaky = db.Orders;   // still an EF query underneath

leaky.Where(o => o.Total > 100)         // Enumerable.Where - client side
     .ToList();

leaky.AsQueryable()                     // hands back the original IQueryable
     .Where(o => o.Total > 100)         // Queryable.Where - translated to SQL
     .ToList();

Useful trivia, and a neat proof that the static type was the only thing standing between you and a translated query. Do not build on it, though. If a variable's type is lying to you, fix the type.

On a plain collection, it gives you nothing. Call AsQueryable() on a List<T> and, per that same sentence, you get back something that runs the Enumerable operators anyway - an EnumerableQuery<T>, a queryable-shaped wrapper with no database behind it. No SQL is generated, and none can be. It satisfies a method signature; it does not make anything faster.

The AsQueryable trap that only shows up in tests

That second case is where people get hurt, because faking a DbSet with list.AsQueryable() is such an obvious idea.

It works right up until the code under test does something async. EF Core's async operators are its own extension methods - "ToListAsync, SingleAsync, AsAsyncEnumerable, etc." - and they need a source that can actually be enumerated asynchronously. An EnumerableQuery<T> cannot: it implements IQueryable<T> but not IAsyncEnumerable<T>, and its provider is not an IAsyncQueryProvider. So the call throws an InvalidOperationException at runtime. Your production code is fine. Your test blows up on the first await - which you can confirm in about thirty seconds, and which is worth doing rather than taking my word for it.

That is the entire reason libraries like MockQueryable exist. But before you reach for one, notice that the EF Core testing docs point away from this road altogether: they say "Mocking DbSet for querying is complex and difficult, and suffers from the same disadvantages as the in-memory approach; we discourage this as well." They are just as blunt about the in-memory provider - "it is highly limited and we discourage its use" - and steer you toward SQLite in-memory mode, which "offers better compatibility with production relational databases, since SQLite is itself a full-fledged relational database", or toward testing against your real database system.

When should you use each?

The one rule worth remembering: none of these methods is dangerous. Their position is. Call them after the database has finished its work and they are exactly the right tool. Call them before, and you have just asked for the whole table.

Frequently asked questions

Official resources

• Client vs. server evaluation - EF Core, Microsoft Learn
• Testing applications that use EF Core - Microsoft Learn
• Choosing a testing strategy - EF Core, Microsoft Learn
• Tracking vs. no-tracking queries - EF Core, Microsoft Learn
• Queryable.AsQueryable method - .NET API, Microsoft Learn
• Asynchronous programming - EF Core, Microsoft Learn
• Efficient querying - EF Core, Microsoft Learn