CODEWITHSUNDEEP
c#entity-frameworkdbcontext
Brendan Hill
Brendan Hill

Reputation: 3752

EF4 How to clear DbContext internal state?

How can I tell an EF4 DbContext to clear its internal cache/state?

I have a database updater program which executes a large number of inserts on a variety of tables via EF4 within a transaction. I find the inserts to a common Permissions table get slower and slower as the update proceeds.

There are the following constraints at play:

1) Everything must occur within a single (giant) transaction.

2) Can't introduce dependency on MSDTC - so I can't have a cross-connection transaction.

3) Can't seem to open a new DbContext for a SqlConnection which is already open - encounters "EntityConnection can only be constructed with a closed DbConnection" error. (note that I am already sharing a SqlConnection between multiple DbContext's, but only opening the connection after they are all initialized)

Given these constraints, I can't create a new DbContext for each chunk of the work, as it breaks the transaction.

I've satisfied these functional constraints, but the performance is poor. I suspect the DbContext is struggling to handle the volume of data being inserted into the DbSet.

How can I tell the DbContext to reset its internal cache (eg. the rows I inserted recently and don't care about any more)?

Upvotes: 0

Views: 2549

Answers (2)

carbo18
carbo18

Reputation: 468

In your application you can use a mix of Entity Framework for reading your data and doing small insert and updates and use ADO.NET DataAdapters for Bulk Inserts and Updates http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx

Alternatively you could use the ExecuteSQLCommand of EF5 http://msdn.microsoft.com/en-us/library/gg679456(v=vs.103).aspx to do your inserts in combination with stored procedures and passing a Table parameter to pass the bulk data. In EF4 it's ExecuteStoreCommand http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext.executestorecommand.aspx

Upvotes: 1

spender
spender

Reputation: 120508

IIRC, you get a decent speedup on insert if you:

myDbcontext.Configuration.AutoDetectChangesEnabled = false;
myDbcontext.Configuration.ValidateOnSaveEnabled = false;

Perhaps it might be better to take a read of this: http://patrickdesjardins.com/blog/entity-framework-4-3-with-poco-and-track-change

I'd probably abandon EF for a gigantic insert go with SqlBulkCopy. The relevant section is here: http://msdn.microsoft.com/en-us/library/tchktcdk.aspx#sectionSection2

Upvotes: 1

Related Questions