LINQ to SQL:DataContext.SubmitChanges not updating immediately

Question

I have a funny problem.

Doing DataContext.SubmitChanges() updates Count() in one way but not in the other, see my comment in the code below.
(DC is the DataContext)

  Company c = DC.Companies.SingleOrDefault(x => x.Name == companyName);
  DataCompliance compliances = c.DataCompliances.Where(x => x.ComplianceCriteria.FKElement == e.Id);

  if (compliances.Count() == 0) // Insert if not exists
  {
    DC.DataCompliances.InsertOnSubmit(new DataCompliance {
      FKCompany = c.Id,
      FKComplianceCriteria = criteria.Id
    });
    DC.SubmitChanges();

    compliances = c.DataCompliances.Where(x => x.ComplianceCriteria.FKElement == e.Id);

    // At this point DC.DataCompliances.Count() has increased,
    // but compliances.Count() is still 0
    // When I refresh the page however, it will be 1
  }

Why does that happen?

I need to update compliances immediately after inserting one. Does anyone have a solution?

tvanfosson · Accepted Answer · 2011-01-14 04:11:13Z

4

There's a difference between adding the object to the data context (and propagating it back to the database) and adding it to the associated entity collection of an object you've retrieved from the database. Once you've retrieved an object and its associated entities, making changes to the data in the database won't be reflected in the retrieved object because the database isn't required. The key bit is the SingleOrDefault() on the company -- that forces the query to be executed and the retrieved data assigned. Enumerating the associated entities will cause them to be loaded if they haven't been eagerly loaded already. Thus, even after updating the database, the previously retrieved object won't reflect the update. You can, however, add the inserted object to the company's data compliances collection, then do the update and it will be what you expect. Note that I think you still need to assign the associated entity which you're using in your check. Refreshing the page also solves the problem because the data is requeried from the database, updating the associated entity collections as well as the tables on the data context.

Company c = DC.Companies.SingleOrDefault(x => x.Name == companyName);
var compliances = c.DataCompliances.Where(x => x.ComplianceCriteria.FKElement == e.Id);

if (compliances.Count() == 0) // Insert one if not exist yet
{
    c.DataCompliances.Add( new DataCompliance
    {
        ComplianceCriteria = criteria
    });
    DC.SubmitChanges();

    compliances = c.DataCompliances.Where(x => x.ComplianceCriteria.FKElement == e.Id);
}

Original (left for context)

What if you try assigning the associated entity instead of just its id? I'm believe that assigning the id doesn't actually populate the associated entity that's being checked and SubmitChanges is only propagating the data back to the DB, not actually updating the table element with the data for the associated entity.

var compliances = c.DataCompliances.Where(x => x.ComplianceCriteria.FKElement == e.Id);

if (compliances.Count() == 0) // Insert one if not exist yet
{
    DC.DataCompliances.InsertOnSubmit(new DataCompliance {
        FKCompany = c.Id,
        ComplianceCriteria = criteria
    });
    DC.SubmitChanges();

    compliances = c.DataCompliances.Where(x => x.ComplianceCriteria.FKElement == e.Id);
}

edited Jan 14, 2011 at 4:11

answered Jan 14, 2011 at 3:52

tvanfosson

534k102 gold badges703 silver badges801 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Aximili Over a year ago

No that's not the problem. It is inserting the correct data into the database. But thanks!

tvanfosson Over a year ago

@aximili -- yes, it will but I think it's not actually retrieving the related entity from the DB. your where clause references this entity and thus the new element is omitted from the filtered collection.

tvanfosson Over a year ago

@aximili - just noticed the difference between c... and DC... after your update. The company isn't being updated when you update the data context. You need to add it to the company's data compliances as well as propagate it back to the database.

Aximili Over a year ago

Very interesting... it works!! So is it always better to assign the object rather than the ID? I thought assigning the ID directly would be more efficient... is it not?

tvanfosson Over a year ago

@aximili -- do you mean that it updated the collection on the Company as well as the data context by only changing the assignment to the entity? That seems odd. It is always better to assign the entity than the id. If you look at the generated code, assigning the entity updates the id, but not the other way around. In fact, I don't think you can update the id if there is already an associated entity.

The Scrum Meister · Accepted Answer · 2011-01-14 03:53:22Z

1

Change the c.DataCompliances.Where... to DC.DataCompliances.Where...

DC is the data context, what is the definition of c

answered Jan 14, 2011 at 3:53

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

2 Comments

Aximili Over a year ago

It's a Company, also LINQ object. Thanks, I'll try it.

The Scrum Meister Over a year ago

If c is a different object, you might need to refresh it. or add it to c

Collectives™ on Stack Overflow

LINQ to SQL:DataContext.SubmitChanges not updating immediately

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related