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.

References