2

Possible References

I have found a similar problem: Refreshing a SQL database connection which does not suggest a solution (more to check the code)

and LINQ to SQL does not update when data has changed in database which should be closer to my problem.

Setup

I allow the user to change the connectionstring at runtime (through SqlConnectionStringBuilder, EntityConnectionStringBuilder and "New EntityClient.EntityConnection(entityconn())") I use the EntityConnection with .Open and with "New Database.DatabaseContainer(entityConnection)"

I keep using the same container for reading db data. and for calling ".SaveChanges" and do not explicit use transactions so far. (I expected "SaveChanges" to automatically do a commit and throw exceptions if the cached data has changed before committing)

Problem Description

When I execute 2 programs and change data with one program, then the other does not notice the changes when it established the connection before the other program committed.

For example if I have 2 times the same program (A and B), the following happens: Start A, Start B, Change data in A, B is unable to read the changed data (because it started the connection before A finished the changes).

Question

How to refresh the local cached data, and how to deal with racing problems?

When I refresh connection for A and then change the data, but meanwhile B was refreshed and also starts to change data, then B may overwrite the changes from A without noticing. This happens whenever the database is changed after a refresh. The second question I linked above seems not to deal with that problem.

My guessed ideas how I may be able to solve the problem

Maybe the command "Container.Refresh(Objects.RefreshMode.StoreWins, entity) may help with that, but I am not sure what object to give as entity. the outdated one that I just read from the cached database? And tere is still the problem, that I am not sure when that conflict will occur.

Maybe it should be somethign with transaction or changing transactionscope, assuring that all changes are only made (or repeatedly tried to make) until there were no changed in the database since the transaction started? Is that possible or how to deal with data that was already changed by another program with active transaction?

Community
  • 1
  • 1
Amegon
  • 604
  • 6
  • 14

2 Answers2

1

What you see is identity map behavior and it is core feature of EF.

To solve problems with refreshing you can either use

Refresh

objectContext.Refresh(RefreshMode.StoreWins, entity);

entity is object previously retrieved from the database which you want that you want to refresh.

MergeOption

objectContext.YourEntitySet.MergeOption = MergeOption.OverwriteChanges;
var data = objectContext.YourEntitySet.
              /* Any query you want to execute to refresh your data */
              ToList();

MergeOption controls the way how data are read to identity map. Default settings is AppendOnly which will materialize only entities which were not previously loaded but entities already loaded will remain intact - that is the core feature because otherwise any query would be able to overwrite your unsaved state.

The second question is completely unrelated to your initial problem. If you are afraid that one application can change data during preparing modifications in other application you need to deal with concurrency.

Community
  • 1
  • 1
Ladislav Mrnka
  • 349,807
  • 56
  • 643
  • 654
  • Thanks for the quick answer. Now I know about what I have to read more – Amegon Dec 19 '11 at 18:51
  • As you linked, I read the article about concurrency, followed a link to http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2008/07/02/10564.aspx and applied in my EF model the Property Concurrency Mode = Fixed to my Timestamp variable (which is one variable in a complextype that is used in most tables). Immediately after that, I receive always the latest Timestamp, even with the old connection that previously had only old 'cached' data. I'm so happy that there are easy solutions for those problems. – Amegon Dec 19 '11 at 19:36
0

I'm not sure if this will help you in the context of EF, but take a look at SqlDependency:

"SqlDependency allows you to receive notifications when the original data in the database changes so that the cache can be refreshed."

Branko Dimitrijevic
  • 47,349
  • 10
  • 80
  • 152
  • Thanks, I think this is useful, too. I am working on a client application, but probably less than 3 people will use it at the same time, maximum 10, so an eventhandler for database changes should work and feels like a good way to immediately refresh the data when changes occured. I guess it makes use of the observer pattern and registers at the database, because that would explain why the structure is only recommended for maximum a few active clients. I will also read more about that stuff – Amegon Dec 19 '11 at 18:58