SubmitChanges call not updating data

Question

The last few hours I'm trying to find out why I'm not able to update the data in the db using the SubmitChanges method.

I'm able to retrieve data normally but when I'm calling the SubmitChanges method, the call is executing like for 5+ minutes, then its proceeding without any error but when I'm checking the db, nothing gets updated.

I researched a bit before and some other posts were saying to check if primary key has been declared but that has been declared in fact.

This is the code I'm using:

SitesDB sitesDB = new SitesDB(SqlConnection);
Site site = sitesDB.GetSite(ProgramArgs.SiteID);

var records = DB.records
    .Join(DB.Locations.Where(l => l.SiteID == ProgramArgs.SiteID),
       s => Convert.ToInt32(s.store_id),
       l => l.LocationID,
       (s, l) => s)
    .OrderBy(s => s.survey_date_utc);


foreach (var record in records)
{
    record.date_local = ConvertTimeFromUTC(date_utc, site.TimeZoneID);

    DB.SubmitChanges();
}

I did...but I'm still waiting for the SubmitChanges method to be executed, 4+ minutes at this moment @216 — Laziale, Jan 06 '15 at 19:21
Restart SQL Service, clean/rebuild project, restart Visual Studio, restart computer — The One, Jan 06 '15 at 20:01

Pleun · Accepted Answer · 2015-01-06T19:52:55.813

You should profile the database to see whay is happening in SQL.

Are you sure it is the submitchanges? Since you have the SubmitChanges() inside your foreach it will only update one record at a time. That should not be that much of a performance problem unless you have a very large table / lots of indexes etc. etc. You may want to move that outside the foreach. However, that will still do one update per record so it will not be that much faster.

Your problem might be before the submithanges. By default, Linq-2-sql is deferred execution. That means your select only is executed in the database as soon as you do your

foreach (var record in records)

So my idea is that this is your performance problem, not so much the submitchanges.

You would want to see this in the debugger, put a breakpoint on the first line inside the foreach record.data... and test if you actually get there.

SubmitChanges call not updating data

1 Answers1