Refresh repository after run stored procedure

Question

I have following action in my WebAPI project that runs stored procedure to update (and some others operations) People table:

public HttpResponseMessage SetAsMain(int id)
{
    People people = repository.GetById(id);

    if (people == null)
    {
        return ErrorMsg(HttpStatusCode.NotFound, string.Format("No people with ID = {1}", id));
    }

    if (people.IsMain)
    {
        return ReturnMsg(HttpStatusCode.OK, string.Format("{0} is already set as main", people.FullName)); 
    }

    try
    {
        var parameters = new Dictionary<string, string>();
        parameters.Add("PeopleID", id.ToString());                

        repository.ExecProcedure("usp_PeopleSetMain", parameters);
        return Request.CreateResponse(HttpStatusCode.OK, repository.GetById(id)); 
    }
    catch (Exception ex)
    {
        return ErrorMsg(HttpStatusCode.InternalServerError, ex.Message);
    }
}

I have a problem with retrieve data after update by stored proc. In this place:

return Request.CreateResponse(HttpStatusCode.OK, repository.GetById(id));

I get the same data as it was before the update. But when I call this method again then there will be an actual data.

How can I refresh data in my repository?

Could you tell me why repository.GetById(id) never fetches a new data after ExecProcedure. Let me know if I have to add more information

UPDATE
Method GetById in my repository:

public T GetById(object id)
{
   return context.Set<T>().Find(id);
}

Answer 1

It happens because Entity Framework context already contains your object. Method Find(id) always checks if object exists in the context and if it is there, Entity Framework will not query the database. Stored procedures do not change context objects because they are executed directly against the database, that's why you don't see an updated values in your entity.

You can workaround this with 1 of the following things:

1. Stop entity tracking in the context
2. Use AsNoTracking() to query entity without following tracking.
3. Query object using First(...), FirstOrDefault(...), Single(...) etc. after executing stored procedure.

I would recommend you to go with option 1 or 2. Option 1:

context.Entry(people).State = EntityState.Detached;

If you run earlier version of Entity Framework, you should detach object:

context.People.Detach(people);

---

(Thanks ieaglle) You could also query your entities turning off tracking right away (Option 2):

context.People.AsNoTracking().FirstOrDefault(p => p.Id == id);

Answer 2

DbSet.Find behaves the next way:

If an entity with the given primary key values exists in the context, then it is returned immediately without making a request to the store. Otherwise, a request is made to the store for an entity with the given primary key values and this entity, if found, is attached to the context and returned. If no entity is found in the context or the store, then null is returned.

So Find won't query DB for your new updated entity it will just take cached old one.

To workaround this feature you have couple of options:

• Explicitly force EF to query DB for new entity by using .FirstOrDefault, .SingleOrDefault or any other method that triggers query execution;
• Do not attach entities to the context in the first place by using DbExtensions.AsNoTracking.