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.

score 6 · Accepted Answer · edited May 23 '17 at 11:53

6

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.

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.

edited May 23 '17 at 11:53

Community

1
1

answered Jan 26 '17 at 14:59

Tim Schmelter

450,073
74
686
939

1

`This will still only load ten records into memory`. If I didn't know better and I read that, I would assume that you are saying it would issue a `Select Top 10 ...` – sgmoore Jan 26 '17 at 15:53
@sgmoore: no, the `AsEnumerable` casts the type to `IEnumerable` which forces Linq-To-Objects. So everything until there will be translated to a sql query and the result will be streamed into memory. So in ADO.NET it was like a query(including WHERE and ORDER BY) witha `SqlDataReader` and a `while(reader.Read(){}`-loop with a counter which will break this loop if ten records were processed. – Tim Schmelter Jan 26 '17 at 15:57
`AsEnumerable` just casts the `IQueryable` to `IEnumerable`, but this forces Linq-To-Objects because the [`Enumerable`](https://msdn.microsoft.com/en-us/library/system.linq.enumerable_methods(v=vs.110).aspx)- instead of [`Queryable`](https://msdn.microsoft.com/en-us/library/system.linq.queryable_methods(v=vs.110).aspx) methods will be used. – Tim Schmelter Jan 26 '17 at 16:03
1

Point I was trying to make was that this asks Sql Server to give you lots of records and once Sql has prepared them and starts to send them to you, you stop it after you have the first ten. Sometimes it is unavoidable, but I think AsEnumerable should always come with a warning. – sgmoore Jan 26 '17 at 16:14
@sgmoore: sure, that's why my pseudo query uses `Where` and `OrderBy` before it uses `AsEnumerable`. It's just a way to enable all code which can't be used in Linq-To-Entities/Linq-To-Sql and to avoid calling `ToList`(which many do instead). That will indeed load all into memory before it starts processing. So `db.LargeTable.AsEnumerable().Take(1)` is much better than `db.LargeTable.ToList().Take(1)`. The latter can cause `OutOfMemoryExceptions`. (of course the `AsEnumerable` isn't needed either in this case) – Tim Schmelter Jan 26 '17 at 16:15

score 3 · Answer 2 · answered Jan 26 '17 at 14:44

Yes. The whole table will be loaded into memory. Your call to ToList() will immediately execute the query which is the whole table in this case.

ToList(), ToArray(),... functions will return lists.

FirstOrDefault will return up to one item

SingleOrDefault will return up to one item and throw an exception if the result is more than 1 item.

See here for a full explanation of ef, linq and deferred execution

https://blogs.msdn.microsoft.com/charlie/2007/12/10/linq-and-deferred-execution/

score 1 · Answer 3 · answered Jan 26 '17 at 14:46

Well before calling ToList() there are no services loaded, but the first time you call ToList() EF will Load the whole services, so to avoid loading all the services , query the services first to fetch just what you need, call Skip, Take, Where or what ever on the services, EF will generate a query which load the exact services you need to fetch

vasil oreshenski · Answer 4 · 2017-01-26T15:44:11.130

About your first question, yes, this will load all records from the database to the client. When working with tables with a lot of records, as you have mentioned you can use .Skip(() => ...) and Take(() => ....)

About the memory and the other linq epxresions like .Where, SingleOrDefault, FirstOrDefault and so on ... there is the overhead of the translation from query expression to SQL query, but EF already cache those and reuses and parsed queries when it can. So it is not that bad.

About the query expression and more information about them you can check: http://www.tutorialsteacher.com/linq/linq-tutorials

About EF more here: http://www.entityframeworktutorial.net/

About EF query performance:

EF will cache queries and reuse them as i've mentioned already.

Also creates store procedures and use them instead of RAW sql queries. This can be easily observed with SQL Server Profiler

About db context performance One of the benefits of the DbContext is the ChangeTracker which will track any Entity changes during its lifetime (until disposed) and this is ok if you're planing of making some modifications/updates of specific entities, but when your making 'readonly' queries you can optimize the context a bit.

The DbContext has .Configuration property with the following settings.

AutoDetectChangesEnabled enable/disable the change tracking

LazyLoadingEnabled enable/disable lazy loading of navigation properties. More info about lazy loading: http://www.entityframeworktutorial.net/EntityFramework4.3/lazy-loading-with-dbcontext.aspx

ProxyCreationEnabled enables/disable the creation of runtime wrappers for the entities. This is related with the navigational properties in you entities. Shortly when you have relation One To Many (lets say person with many friends) when accessing the .Firends property with enabled LazyLoading EF will translate this to JOIN Query and will fetch and materialize all friends for the current entity, and this is possible because of Proxy class which overrides the .Friends Property behaviour.

So when you do only readonly queries you can disable those settings. Keep in mind that disabling the LazyLoading will result in not materialized navigation properties. To fix this you have to join/include those in the original query.

This can be achinved using the some of the approaches described here: https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx

About query materialization You've discovered two of the possible ways. Using .ToList() or pagination using Skip and Take

You can also foreach the query which will create SQL CURSOR.

More about query materialization: https://msdn.microsoft.com/en-us/library/bb738633(v=vs.110).aspx

This are some overall hints. Further you can check the links in the post, i think they are good starting point.

Summary: From my experience ORM's are perfect for new projects because they are easy for modification and can be approached from more team members (is closer to C# then SQL). But in the end some portion of the queries are rewritten in SQL mainly because it is still faster to run sp from the server and EF sometimes creates complex queries which is best to be avoided.

score 0 · Answer 5 · answered Jan 26 '17 at 14:43

Yes, if you call _ordersContext.Services.ToList() it will load the whole table and, yes, you can use the LINQ methods to query the database and retrieve only the records you need.

Take a look at this link on MSDN: https://msdn.microsoft.com/en-us/library/bb399367(v=vs.110).aspx

Entity Framework, linq functions and memory usage

5 Answers5

Linked