2

I am trying to dump a database table using the Entity Framework in C# using EF Version 6.1.1. I was expecting the foreach loop to load each row one at a time but it gradually loads every row and then runs out of memory.

foreach (Quote L in context.Quotes)
    sw.WriteLine(
        "INSERT INTO [dbo].[Quote] ([QuoteId], [BookId], [VerbTenseId], [Form], [Quotation]) VALUES (%1,%2,%3,N'%4',N'%5')", 
        L.QuoteId, L.BookId, L.VerbTenseId, L.Form, L.Quotation);

In the above code, context is a DatabaseContext that has already been initialized to my database and Quotes is valid table of Quote records. I am attempting to dump every record to a file for backup. Obviously (as suggested previously) I could abandon the Entity Framework and use a DataReader to iterate the database table, but surely the Framework does allow the iteration of tables that are too big to fit in memory.

Is there an elegant way (perhaps a flag in the context somewhere) that would permit this?

Normally, the result sets are small (a few thousand) but in this case it runs into the millions.

There seem options that worked with previous versions of the framework but, unless I am misunderstanding, those methods and flags no longer exist.

This is only test code and not a regular database backup. I am not looking for a better way express my code. I am looking for an elegant way to use EF. If EF is just missing such support that is my answer.

Please do not mess with the example, just answer if there is a way to use EF to enumerate a database table without loading every row into memory and keeping it there. I can code other examples, and offer other options to achieve a database backup. This is an example only.

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
Mythlandia
  • 576
  • 1
  • 5
  • 15
  • Side note: I hope none of your quotations have any apostrophes in them. – Blorgbeard Jul 16 '14 at 23:47
  • 4
    If you need to make a backup of your database, why not back up the database using real backup tools? – Daniel Mann Jul 16 '14 at 23:51
  • is this using string formatting, or an ef context? – Daniel A. White Jul 16 '14 at 23:51
  • 1
    EF takes the entire result set and hyrdrates object instances out of it. It is not a row-at-a-time cursor. Sounds like you're going to have to switch to raw ADO.NET and use a DataReader. – Craig W. Jul 17 '14 at 00:07
  • 1
    Possibly turning off change tracking could help. From what I can see, the context is holding on to each record assuming you may change one and then call Save Changes. If you don't track changes it may be willing to free up the cache as the memory usage climbs. – Vaccano Jul 17 '14 at 01:46
  • Daniel Mann: I am not trying to backup my database but incrementally preserve the database I have as I establish the EF model. Once the model goes live, then we have different issues, but at the moment my tables are only a few million (and need to be that size to establish decent test data). – Mythlandia Jul 17 '14 at 04:30
  • Blorgbeard: This is just test code. Of course, they are validated not to include apostrophes. – Mythlandia Jul 17 '14 at 04:39
  • Daniel A. White: This is an EF context. Hence the question. – Mythlandia Jul 17 '14 at 04:39
  • Craig W: You are probably correct. I can do this. I am just disappointed that EF does not have sufficient flexibility to do something that I would have thought most users would need in certain situations. – Mythlandia Jul 17 '14 at 04:41
  • Vaccano: I thought this was *exactly* the solution I was looking for, but unfortunately it makes not change, and I get the same out-of-memory exception as before. – Mythlandia Jul 17 '14 at 04:42
  • possible duplicate of [Entity Framework 6: is there a way to iterate through a table without holding each row in memory](http://stackoverflow.com/questions/24795406/entity-framework-6-is-there-a-way-to-iterate-through-a-table-without-holding-ea) – JotaBe Jul 17 '14 at 08:07
  • @Mythlandia OK. Btw, people will only be notified of comments if you put an `@` before their name, like I just did. – Blorgbeard Jul 20 '14 at 23:22

1 Answers1

3

I haven't tested it, but try foreach (Quote L in context.Quotes.AsNoTracking()) {...}. .AsNoTracking() should not put entities in cache so I assume they will be consumed by GC when they out of the scope.

Alternative way would be to implement batch processing (select top N entities, process, select next top N). In this case make sure that you dispose and create new context every iteration and use proper OrderBy() in the query.

Vadim K.
  • 1,081
  • 1
  • 14
  • 26