EF4 - Sproc updates data, but data doesn't become updated in EF

Question

I'm using EF4 for a webapp.

99% of the time I'm using LINQ to EF for my dataaccess, however one of the calls needs to be a sproc. It looks like this:

object[] params = { new SqlParameter("SomeId", someId),
                    new SqlParameter("OtherId", otherId)};
db.Database.ExecuteSqlCommand("dbo.spMySproc @SomeId, @OtherId", parms);
db.SaveChanges();

The sproc gets fired, the data gets updated. However, when EF's data doesn't get updated. I have learned that this is expected behavior. I have also learned that there is a .Refresh method on the ObjectContext, however my database entities object inherits from DbContext which doesn't have this.

Is there a way to refresh my EF4 database after I've run this sproc?

score 1 · Answer 1 · answered Mar 21 '12 at 03:27

Documentation for DbContext says the following:

"DbContext wraps ObjectContext and exposes the most commonly used features of ObjectContext by using simplified and more intuitive APIs. You can access the underlying ObjectContext whenever you need to use features that are not supported by DbContext. For more information, see What’s Not Supported."

Which leads me to believe you could do something like:

DbContext.ObjectContext.Refresh(RefreshMode, Obj)

@MystereMan, Hence the "something like". :) – Casey Wilkins Mar 21 '12 at 14:08 — Casey Wilkins, Mar 21 '12 at 14:08

score 1 · Accepted Answer · answered Mar 21 '12 at 04:48

Casey is more or less correct, but you can't access it the way he indicates. You have cast the DbContext to an IObjectContextAdapter, then you can access the ObjectContext from that.

var oc = myContext as IObjectContextAdapter;
if (oc != null)    
     oc.ObjectContext.Refresh()

However, this is probably not the right way to go about things. I suspect that your problem is that you're using a single DbContext in your app. DbContext is designed to do a single operation, then be destroyed. You should create a new DbContext for each operation, destorying it when finished..

using(var db = new MyDbContext()) {
    object[] params = { new SqlParameter("SomeId", someId),  
                new SqlParameter("OtherId", otherId)};  
    db.Database.ExecuteSqlCommand("dbo.spMySproc @SomeId, @OtherId", parms);  
    //db.SaveChanges();  - This is irrelevant
}

Thanks for answering my question, and then resolving the REAL issue behind my question — Nathan Koop, Mar 21 '12 at 15:10

EF4 - Sproc updates data, but data doesn't become updated in EF

2 Answers2