Entity framework: How can I use more then one context and stay synchronized?

Question

Hi,

I'm new with EntityFramework.

My application has a pool of context object instances (Each context has 1 connection to the DB).

The problem is that when I update an object (and calling SaveChanges), the data is updated in the DB and in the updating context but when I select from other instance, it gets the old data of the selected object.

Example:

Let's imagine a table called tbl.

The table has 2 columns: id and data.

There is 1 row: id = 1, data = 2.

EFContext context1 = new EFContext();      
EFContext context2 = new EFContext();      
var obj1 = context1.tbl.Where(a => a.id == 1);      
var obj2 = context2.tbl.Where(a => a.id == 1);      
obj2.data = 10;      
context2.SaveChanges(); 
var obj3 = context1.tbl.Where(a => a.id == 1); 

After executing these lines, obj3.data contains 2, instead of 10.

How can I solve this problem?

I don't want to create a context instance every time I want to access the DB.

Refresh is not good enough I'll have to do it before every query (My application is multithreaded), and it takes a lot of time.

If I had a way to tell entity framework to perform a query every time I'm trying to select, it would be great.

Thanks!

Answer 1

You're not meant to be holding a reference to object contexts over a long period of time.

http://blogs.microsoft.co.il/blogs/gilf/archive/2010/02/07/entity-framework-context-lifetime-best-practices.aspx

http://blogs.msdn.com/b/alexj/archive/2009/05/07/tip-18-how-to-decide-on-a-lifetime-for-your-objectcontext.aspx

This second one also points out:

Thread Safety: If you are trying to re-use an ObjectContext you should be aware that is not thread safe, i.e. similar to the standard .NET collection classes. If you access it from many threads (e.g. web requests) you will need to insure that you synchronize access manually.

So, basically, you're going to have to lock over the whole context. Instead you can simply use an instance for each thread (created as per the best practises described) and let the database handle the concurrency issues. Even better, use transactions.

• Many other blogs on this: http://www.google.co.uk/search?q=entity+framework+context+lifetime&sourceid=ie7&rls=com.microsoft:en-GB:IE-SearchBox&ie=&oe=&redir_esc=&ei=TZRFTLeiJpCTjAeOu4i2Bw

Although it is tempting to try and keep a context open for a long period of time to save on creation/disposal etc - in practise you should be opening and closing a context as and when you need it.

This is made easier with EF due to the fact that you can take an object from a context and reattach it to another later - something that is difficult, if not impossible to do with Linq to SQL.

So basically, don't do it :)

Answer 2

You need to rethink your design. Contexts are very lightweight and it is usually standard practice to create one per transaction or unit of work. A good rule of thumb I go by when dealing with database interactions is open connections as late as possible and close them as soon as possible, in your case you are leaving mutliple database connections open unnecessarily.

You should consider applying a better design pattern to your problem, have a look at the Repository and Unit of Work pattern

Answer 3

Note that it is possible to hold DB connections open across multiple OC lifetimes, even if your provider won't pool them. A couple things you can adjust after profiling:

1. Explicitly opening the Context.Connection when you instantiate the OC. Prevents the context from requesting a new connection for each query.

2. Provide an existing EntityConnection to the constructor of the OC. Then it will use the existing connection across multiple OC lifetimes.

3. Precompile EF queries; they're cached in the OC. Precompiling them allows the cache to span multiple OC instances.

Again, the OC constructor is not expensive. You must first profile and find the slow part. Fix that, not the OC; it isn't broken.