CODEWITHSUNDEEP
.netlinq-to-sqldatacontext
David Fox
David Fox

Reputation: 10753

Not all records update on DataContext.SubmitChanges() (Linq to SQL)

In short, one specific record in one table updates while multiple, additional modifications in the same table do not.

theStat updates OK upon .SubmitChanges()

statToIncrease instances do not update upon SubmitChanges()

Upon watching .GetChangeSet(), only one update is being performed. Why aren't any instances of statToIncrease updated if modified? Do I have to open a new DataDataContext for each update?

The UserStat table has a primary key.

Maybe another question

Would it be wise to extend my UserStat class with a method like .Add(double amountToAdd)? Another connection would have to be opened, but it would allow me to continue using LINQ-only, right?

        using (DataDataContext db = new DataDataContext())
        {
            Random r = new Random();

            var theStat = db.UserStats.FirstOrDefault();

            // tried scoping `statToIncrease` outside of the for loop
            // didn't fix anything
            UserStat statToIncrease = null;

            for (int i = 0; i < vm.stats.Count(); i++)
            {
                statToIncrease = db.UserStats.Where(s => s.ID == i)
                    .FirstOrDefault();
                // here, .Property is type float
                statToIncrease.Property += r.NextDouble();
                // here, .Property is type int
                theStat.Property -= 1;
            }

            // create and insert another object
            // this object is inserted correctly

            // right here, .ChangeSet() only has one insert and one update
            // the update is only for `theStat` and not 
            // any modifications to `statToIncrease`
            db.SubmitChanges();
        }

Update

I found the ugly way of doing this:

db.ExecuteCommand("UPDATE [UserStat] SET [Value] = {0} WHERE [ID] = {1}, 
    statToIncrease.Value + amount, statToIncrease.ID");

Upvotes: 1

Views: 2340

Answers (3)

Yakimych
Yakimych

Reputation: 17752

I am not sure what exactly the problem is, but it does seem weird that you are reusing the same variable (statToIncrease) and fetching one record at a time. It seems more reasonable to fetch a list of objects, iterate through the list and update them, and then call SubmitChanges:

using (DataDataContext db = new DataDataContext())
{
    var r = new Random();

    UserStat theStat = db.UserStats.FirstOrDefault();

    List<UserStat> statsToIncrease = 
                               db.
                               UserStats.
                               Where(s => s.ID >= 0 && s.ID < vm.stats.Count()).
                               ToList();

    foreach (UserStat statToIncrease in statsToIncrease)
    {
        statToIncrease.Property += r.NextDouble();
        theStat.Property -= 1;
    }

    db.SubmitChanges();
}

Upvotes: 1

saus
saus

Reputation: 2174

Here's your problem:

.Where(s => s.ID == 1).FirstOrDefault();

this will obviously return the same object on each iteration. And I'm guessing that theStat has an ID of 1, which is why it is updated.

Upvotes: 0

Oleks
Oleks

Reputation: 32323

Try to attach you modified entity explicitly by using :

db.UserStats.Attach(statToIncrease, true);
...
db.SubmitChanges();

Do I have to open a new DataDataContext for each update?

No, you haven't.

Upvotes: 0

Related Questions