[Solved]-How to avoid memory overflow when querying large datasets with Entity Framework and LINQ-LINQ,C#

i would use lazy ienumerable and implement some kind of paging for you data internaly.

maybe create your own ienumerable interface, so user of your library is not tempted to call tolist on it himself.

but question is.. is it really good way to hide fact, that user of this data layer would work with such ammounts of data? first thing to do is to limit returned data to bare minimum. dont return whole entity, but only parts, that you really need. are you eager-fetching any related entities? did you thought about using lazy loading?

one way to be certain is to always set an upper threshold that you will return to avoid gigantic set by ending your query with .take(max_rows). this can be a workaround or a preventive move from a bad call that take down your service but best is to rethink the solution

just a quick note about this point:

but since other classes have no access to the entities in ef, i need to perform a "tolist()" operation on the query and by that fetching the full dataset into memory.

the problem you encounter here is in my opinion not related to ef at all. what would you do if you wouldn't use ef for data access but raw ado.net? "having no access to ef" translates then to "having no access to a database connection".

if you have services or methods which must work with large amounts of objects but cannot access the database - either via ef or another kind of database connection - you must load the data into memory before you pass them to those services/methods. you may then think about solutions to buffer the loaded data somehow on hard disk on client side, but that has nothing to do with the source of the data and how you retrieve them. if you don't buffer and load everything into memory you are limited by your available memory. there is no way to escape from this limitation, i think.

if you have a connection to ef, i think the solution provided in ladislav's answer is the right one as the settings and procedure he described reduce ef almost to the behaviour of a simple datareader.

as much as i like ef for quick/simple data access, i probably wouldn't use it for such a scenario. when dealing with data of that size i'd opt for stored procedures that return exactly what you need, and nothing extra. then use a lightweight datareader to populate your objects.

the datareader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. the datareader is a good choice when retrieving large amounts of data because the data is not cached in memory.

additionally, as far as memory management goes, of course make sure you wrap your code handling unmanaged resources in a using block for proper disposal/garbage collection.

you also may want to consider implementing paging.