Out of Memory Exception in Entity Framework

Question

I need to add some data into my postgres DB, about 600,000 rows in different tables, which can grow to much more. I am using Entity frame work to fill the DB. Unfortunately, for large data my code does not work, first it show me this error "Connection must be open" and in the next try it says "Exception of type 'System.OutOfMemoryException' was thrown". It's fair to say the problem is memory consumption of entity framework, as this code works for small data beautifully. I tried to release the memory by calling GC.Collection but it hasn't worked.

 public void saveChanges()
        {
            dbef.SaveChanges();
            GC.Collect();
            GC.Collect();
            dbef = new CIFImporterEF();
        }

Any idea about fixing this problem. Thanks

Ladislav Mrnka · Accepted Answer · 2011-11-22 13:31:20Z

7

You are using EF incorrectly. EF is not API for your problem. You need to import great amount and this amount can even grow. Use SQL directly. Use specific features of Postrgress to import bulk data or use technology more suitable for such processing.

What can be problem in your code? Anything. First of all EF context is disposable so start with disposing it. Also your GC.Collect has no effect on your dbef because you are holding still the reference and context holds reference to all data. Even if you use what @Dmitry suggested you can still find that it doesn't work. If you have EntityObject based entities or proxied POCOs those classes internally hold reference to the context so it is enough to hold reference to single such entity and whole context, with all attached entities is still considered as live (not collected by GC) - this is hard to solve because you must detach the entity from context to break this prior to releasing the context but detaching breaks all relations among entities.

The correct solution in this scenario is not abusing EF with something it was not designed to solve = bulk inserts. Even if you solve memory problems performance will be terrible and import will take ages.

answered Nov 22, 2011 at 13:31

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hashem Over a year ago

Thanks, so what is the best solution? for bulk copy

Ladislav Mrnka Over a year ago

That is another question you should ask with correct tagging. It is specific to database provider and its API. For MS SQL server we have SqlBulkCopy and I don't have much experience with other databases. You will even get much better performance then EF if you simply create DbCommand with multiple database commands inside = batch DB commands.

Hashem Over a year ago

I am currently working to import a huge amount of data into a postgres DB. As I haven't found anything like bulkcopy for postgres I should try batch command. Thanks

pearcewg Over a year ago

In my product, we have a few different (and critical) areas of the app where we had to move away from EF. I generally like EF, but I often wonder if it would have been better to just dump it. Longer term, I would hope that Microsoft would come up with a way to use EF for bulk operations...so you have one mechanism for dealing with the data store.

Crab Bucket · Accepted Answer · 2011-11-22 14:11:50Z

1

600,000 rows is too many for one transaction. I would try to save it in few calls in small batches.

Though, manual collection is ugly, you can try: dbef = null; GC.Collect(); GC.WaitForFullGCComplete();

edited Nov 22, 2011 at 14:11

Crab Bucket

6,2679 gold badges43 silver badges73 bronze badges

answered Nov 22, 2011 at 12:11

Dmitry Golubets

5705 silver badges15 bronze badges

1 Comment

Hashem Over a year ago

I found the problem. I used static type for my Entinty Framework object which was not correct: public class EFHelper { private static myImporterEF dbef; I deleted static from that and it worked

Collectives™ on Stack Overflow

Out of Memory Exception in Entity Framework

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related