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.

2 Answers
2

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.

Thanks, so what is the best solution? for bulk copy
–
HashemNov 22 '11 at 16:06

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.
–
Ladislav MrnkaNov 22 '11 at 16:28

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
–
HashemNov 22 '11 at 17:51

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.
–
pearcewgApr 9 '12 at 19:32

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
–
HashemNov 22 '11 at 16:53