LINQPad - Refresh Entity after SubmitChanges to Avoid Cached Results
2018-04-19 10:15
Today I was using LINQPad to see how SQL record values were being affected by table triggers. The results were puzzling, so I did the same test using SQL Management Studio and got different results. Clearly, there was caching going on!
Here's roughly what my LINQPad C# Statements script looked like (table names changed to protect the innocent). I was going directly against the database, not using an Entity Framework DbContext, so LINQPad is using LinqToSql for the connection.
var reportId = "1234";
var header = ReportHeaders.Single(a => a.ReportID == reportId);
//Change Status from P to C. Trigger should reset to P because Completed is false.
header.Status ='C';
header.Completed = false;
SubmitChanges();
var report = ReportParents.Single(a => a.ReportID == reportId);
var d = new {HeaderStatus = header.Status, ReportStatus = report.Status,
HeaderCompleted = header.Completed, ReportCompleted = report.Completed};
d.Dump();
And my result:
But in the database, both statuses were (correctly) set back to P.
HEADER
STATUS Completed
------ ---------
P 0
REPORT
STATUS Completed
------ ---------
P 0
This was happening because Linq2Sql caches query results during a single Context session. Caching can be turned off by not tracking objects (ObjectTrackingEnabled = false;
), but that will prevent SubmitChanges from running.
The solution is to use Context.Refresh to requery the database for the entity.
var reportId = "1234";
var header = ReportHeaders.Single(a => a.ReportID == reportId);
//Change Status from P to C. Trigger should reset to P because Completed is false.
header.Status ='C';
header.Completed = false;
SubmitChanges();
//Force fresh result. Caching was preventing accurate trigger results.
this.Refresh(RefreshMode.OverwriteCurrentValues,header);
var report = ReportParents.Single(a => a.ReportID == reportId);
var d = new {HeaderStatus = header.Status, ReportStatus = report.Status,
HeaderCompleted = header.Completed, ReportCompleted = report.Completed};
d.Dump();
Now, running the script returns the correct results.