Error thrown while trying to update linq-to-sql object-CodePudding

I am using ASP.NET MVC 4 (aspx) using MSLinqToSQL as the connector to MYSQL database. I am trying to update the StatusID of an object based on certain criteria.

The criteria is simple, the current status should not be equal to specific statuses -> code below, all within a try - catch.

int ApplicationID = 20;
pleDBDataContext dp = new pleDBDataContext();

Recruit_Applicant_Application app = dp.Recruit_Applicant_Applications.FirstOrDefault(a => a.ApplicationID == ApplicationID);

var statuses = new List<string> { "New", "Rejected", "Archived", "Declined" };

if (statuses.Contains(app.Recruit_ApplicationStatuse.Status, StringComparer.OrdinalIgnoreCase))
{
   app.ApplicationStatusID = dp.Recruit_ApplicationStatuses.FirstOrDefault(s => s.Status == "Evaluating").ApplicationStatusID;
}

dp.SubmitChanges(); //throws error here

However I keep getting this error:

Operation is not valid due to the current state of the object

The stacktrace points me to this.SendPropertyChanging(); event in the designer.cs.

The weird/odd thing is, if I remove the [if..statement] in the above, it works fine...

ApplicationStatusID is a foreign key. The relationships are strong - I've even rebuilt these.

I've even tried referencing a second Recruit_Applicant_Application object and updating that object, as I though I am manipulating the current object too much - that didn't work.

I've even tried the below with no success...

int newstatusis = dp.Recruit_ApplicationStatuses.FirstOrDefault(s => s.Status == "Evaluating").ApplicationStatusID;

int currentstatusid= dp.Recruit_Applicant_Applications.FirstOrDefault(a => a.ApplicationID == ApplicationID).ApplicationStatusID;

string currentstatus =app.Recruit_ApplicationStatuse.Status;

var statuses = new List<string> { "New", "Rejected", "Archived", "Declined" };
    
if (statuses.Contains(currentstatus , StringComparer.OrdinalIgnoreCase)) //theres no reference to the 'app' object at all
{
   currentstatusid = newstatusid;
}

app.ApplicationStatusID = currentstatusid; 
dp.SubmitChanges(); //throws error here

I am expecting the if statement to validate the current status, and only if it meets that criteria then update the status of the current object...nothing complex.

CodePudding user response：

I am not an expert. It might be the case where, if the Application object has unsaved pending changes, the properties representing object relationships are not be considered reliable - particularly if a foreign key ID has been updated and EF has not yet performed the associated object lookup. This is only a guess. Someone more knowledgeable may be able to provide a better explanation.

As a workaround, perhaps you can check the ID instead of the object reference.

Something like:

var statuses = new List<string> { "New", "Rejected", "Archived", "Declined" };
var statusIds = dp.Recruit_ApplicationStatuses
    .Where(s => statuses.Contains(s.Status))
    .Select(s => s.StatusId)
    .ToList();

if (statusIds.Contains(app.ApplicationStatusID))
{
   app.ApplicationStatusID = dp.Recruit_ApplicationStatuses.FirstOrDefault(s => s.Status == "Evaluating").ApplicationStatusID;
}

Depending on how your code is structured, you may even be able to cache the status ID values in an in-memory dictionary. This would have database hits.

Even if you find another solution, it is worth considering the above approach, since ID (integer or GUID) comparisons are almost always be more efficient than string comparisons.