WCF and SQL Server: changes in DB are displayed only after app restarts

Question

I'm developing a client-server app using WCF and Linq2Sql. My server-side program exposes to the clent an interface that provides methods of reading from and writing to my SQL Server DB. But when the client writes some date into DB, perhabs waites some time, and then tries to read that data from DB, it seems like no data has been written to DB, but if I restart my server-side app or perform DB detaching and reataching or restarting of sqlserver-service, then my client-side program can get that data from server-side program. Does anyone have any idea what's wrong with my app (server?) and how to fix this?

UPDATE: I'm using Linq2Sql (calling CataContext.SubmitChanges()).

UPDATE 2: I've discovered, than if I add some new rows into my table, all is correct, but when I'm updating some pieces of row (some properties of objects) and then save changes, the changes become displayed only after reconnection to DB. It appears not to have flushed data immediatly after updating some properties and invocation of DataContext.SubmitChanges().

Answer 1

I don't have an answer, but some ideas for how to further track down the issue. How do you write to the DB? Do you use transactions, that maybe remain open? Can you query the updates in the database when they don't show up in your WCF response? Does your update maintain locks and somehow not release them? Did you eliminate caching as the cause?

Try remote-debugging to find out what happens on the server. A WCF trace might be helpful, too.

Answer 2

I'm running into the exact same issue, and have yet to find a solution. However, on a whim, I converted one of my object's get and save methods to read and write a local XML file instead of the database. Changes were immediately reflected in the WCF response. So it doesn't seem to be a WCF issue at all.

My app is simple enough that I may simply use XML files and the DataContractSerializer. Before I take that drastic step, I'd really like to know why this is happening.

I'm also using Linq to SQL. My next test may be to use the this app, so I may end up dumping the database entirely for the first release.

Here's a thought... Does this have anything to do with the WCF service perhaps not releasing its DataContext? I haven't tested it yet, but my DB editor UI is directly-connected to the database through Linq to SQL. When it saves some data, does the WCF's context get notified?