Entity Framework, linq functions and memory usage

Question

I am new to EF, I used to work with datasets, table adapters and stored procedure. I just dicovered the simplicity of EF and I found that EF approach can help me a lot with my developments. I have few questions, i tried to search for their answers but in vain. Since I always work with clients that have huge tables, the fact that I make this call for example :

_ordersContext.Services.ToList()

does it mean that the whole Services table is loaded to the memory ? If the answer is yes (which by the way i think the answer is yes), can we avoid that memory cost by using linq functions? for example the Take() method ? (I mean if you want to have only 10 records, without loading the whole table in the memory). Same question about the other linq functions like where, first, firstordefault, count, etc ... I mean, do we have to load the whole table ? Is there a good documentation talking about how to use the EF in terms of best practices and memory usage.

Answer 1

Look at MSDN at every LINQ method. If you find the term deferred you know that this method does not execute the query and can be chained with others. Only those which are not using deferred execution will start processing the query and loading the result into memory.

Keep also in mind that you can force Linq-To-Objects without loading everything into memory with AsEnumerable(). This will translate your query into sql, perform the database query and stream the result into memory.

So you could do something like this:

var orderList = _ordersContext.Services
    .Where(x => somecondition)
    .OrderBy(x => x.Column)
    .AsEnumerable() // after this you can use any .NET method since it doesnt need to be translated to sql
    .Where(x => complex filter not supported by Linq-To-Entities)
    .Take(10)
    .ToList()

This will still only load ten records into memory and it uses the database engine to (pre-)filter or sorting but allows to use .NET methods which are not supported by Linq-To-Entities.

Related:

Linq - What is the quickest way to find out deferred execution or not?

Generally methods that return a sequence use deferred execution and methods that return a single object doesn't.

Exceptions are the methods that return collections like ToList, ToArray, ToLookup, ToDictionary which don't use deferred execution.