SQL view data not updating properly

Question

I have two tables (Knicks and Knacks) and a view that joins the two tables (PaddyWhacks). The tables have the following fields:

Knicks.key, Knicks.data1

Knacks.fkey, Knacks.data2

So the view contains:

PaddyWhacks.key, PaddyWhacks.data1, PaddyWhacks.data2

I'm using LINQ to SQL to retrieve the full view as IQueryable and everything looks good. After this I grab a row from Knicks and update it:

Knick k = db.Knicks.Single(row => row.data1 == 5);
k.data1 = 6;
db.SubmitChanges();

I know the update is correct, because if I perform a Count() on Knicks where data1 == 5, the result has changed (and I can peek inside the database and see the change). However, if I again retrieve Paddywhacks using:

IQueryable<PaddyWhack> rows = from row in db.PaddyWhacks select row;

The corresponding data1 value is still 5.

The application is running on a hosted web server and if I wait long enough and come back to try again, I'll see the updated value in the IQueryable view. I'm guessing something's being cached and maybe LINQ's not going all the way back to the database to get the data again. Is there a LINQ or SQL Server feature I need be aware of to resolve the problem?

Answer 1

I don't use linqtosql, but you are correct that something is in cache. A view is just a "macro" query, the data is the data from the original tables, so it is always up to date. I would try to use a different function than IQueryable() or create a new connection and then run the query.

Answer 2

You could try to refresh the DataContext like this:

dc.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, rows);