Updates not updating in Linq to SQL


  • kills Dumbledore

    The latest step in @jaloopa's Saga of Stupid Sage:

    I need to get an ID from a counter table, then update the table ASAP to reduce the race condition. I'm within a DataContext using block, and running the following code:

    var nextSequence = from c in context.Counters
                                   orderby c.CounterID descending
                                   select c;
    
    long nextID = nextSequence.First().NextValue;
    
    nextSequence.First().NextValue += 2;
    
    context.SubmitChanges();
    
    

    The SubmitChanges() call doesn't seem to be doing anything. The NextValue column in the table doesn't change as observed in SSMS. I know I'm in the expected database, because I'm adding rows later in the same using block, submitting changes and these are showing up fine.

    I've also tried moving the update to a separate context in its own using block, and there's still no change in the database table. WTF am I missing?

    The minimal reproduction:

    long nextID;
    using (MyDataContext context = new MyDataContext())
    {
        var nextSequence = from c in context.Counters
                           orderby c.CounterID descending
                           select c;
    
        nextID = nextSequence.First().NextValue;
    
        nextSequence.First().NextValue += 2;
        context.SubmitChanges();
    }
    

  • FoxDev

    I see no transaction commit… but then I don't see a transaction either.


  • kills Dumbledore

    context.SubmitChanges(); is the line that should commit the in memory changes I'm making to the database. According to MSDN,

    Immediately before any actual changes are transmitted, LINQ to SQL starts a transaction to encapsulate the series of individual commands.


  • FoxDev

    To be sure, I'd create my own transaction and explicitly commit it; context should have a method to create one.



  • @Jaloopa said:

    nextSequence.First().NextValue += 2;

    Been a while since I've done LINQ-to-SQL but I wonder if this line is not evaluating the way you expect. What if you do this instead?

    nextSequence.First().NextValue = nextID + 2;
    

    Does the += cause First() to be evaluated twice, and potentially returning two different items? Anything being returned by value or as a struct instead of an object or by reference?



  • @RaceProUK said:

    To be sure, I'd create my own transaction and explicitly commit it; context should have a method to create one.

    You don't have "to be sure", the documentation isn't a liar. Don't put irrelevant crap the framework does for you in your own code. "I'm not sure the plus operator works, better write your own integer addition function." Sheesh.


    Here's a possibility: LINQ-to-SQL requires a primary key to be set on any tables you expect to be able to modify with it, otherwise it can't tell the difference between an insert and an upsert. Do you have a primary key set?

    My memory's fuzzy but I remember the "there's no primary key" error gets swallowed or otherwise unreported in a lot of cases.

    EDIT: there should be a bool in the datacontext.table object that tells you if the table's in read-only mode or not-- if it's in read-only mode, context.SubmitChanges(); won't do jack to that table.



  • @blakeyrat said:

    Here's a possibility: LINQ-to-SQL requires a primary key to be set on any tables you expect to be able to modify with it, otherwise it can't tell the difference between an insert and an upsert. Do you have a primary key set?

    That's a good possibility too, I totally forgot it was like that. Without a primary key, it won't do updates, and it won't tell you anything is wrong either.


  • FoxDev

    @mott555 said:

    Does the += cause First() to be evaluated twice, and potentially returning two different items? Anything being returned by value or as a struct instead of an object or by reference?

    First() is eager-evaluated, so should only run once; if anything is evaluated twice, it's NextValue.
    @blakeyrat said:
    You don't have "to be sure", the documentation isn't a liar. Don't put irrelevant crap the framework does for you in your own code.

    Having an explicit transaction scope isn't exactly a bad thing, is it? 😛


  • kills Dumbledore

    @blakeyrat said:

    Here's a possibility: LINQ-to-SQL requires a primary key to be set on any tables you expect to be able to modify with it, otherwise it can't tell the difference between an insert and an upsert. Do you have a primary key set?

    That would be it. No primary key. And isReadOnly is indeed true for the table

    Guess I'm reduced to using a raw SqlConnection for this update then. Have I mentioned I hate this schema?



  • Can you add a primary key? Just an autoincremented integer to make LINQ-to-SQL happy?


  • FoxDev

    @Jaloopa said:

    Guess I'm reduced to using a raw SqlConnection for this update then

    Unless you can use this?



  • Well "no primary key" is the problem; don't blame LINQ for that. (Although LINQ should probably have a better way of warning you about it.)

    The fix is to add a primary key.

    EDIT: BTW you can still get your "raw SqlConnection" from the LINQ data context, so it's not like LINQ is entirely wasted here. Just 95% wasted.


  • FoxDev

    @blakeyrat said:

    The fix is to add a primary key.

    He might not be able to; I don't think you can add a PK via LINQ, and he may not have control over the schema.


  • kills Dumbledore

    @mott555 said:

    nextSequence.First().NextValue = nextID + 2;

    I had tried that as well at various points, thinking the evaluation might be getting funny.

    @mott555 said:

    Can you add a primary key?

    It's a table created by Sage, not our own software, so altering it would be frowned upon at best.

    @blakeyrat said:

    Well "no primary key" is the problem; don't blame LINQ for that

    I'm not. Haven't been using Linq that long, but I'm definitely a convert. I'm blaming Sage and their aversion to proper schema design.



  • Without a primary key, there's no way any ORM product can tell the difference between an insert and an upsert. (There's no way a human can, either, in isolation-- you'd need to know the context of previous queries before you know if a particular row is new or not.)



  • @blakeyrat said:

    EDIT: BTW you can still get your "raw SqlConnection" from the LINQ data context, so it's not like LINQ is entirely wasted here. Just 95% wasted.

    This is probably the easiest thing to do then.



  • @Jaloopa said:

    It's a table created by Sage, not our own software, so altering it would be frowned upon at best.

    Can you create a view on it and add a PK to the view?

    Not sure if LINQ would allow updates in that case, but might be worth a try.


  • FoxDev

    @mott555 said:

    This is probably the easiest thing to do then.

    DataContext.ExecuteCommand() called 😛
    @blakeyrat said:
    Without a primary key, there's no way any ORM product can tell the difference between an insert and an upsert.

    I know 😜
    But since he can't add a PK, he needs another solution ;)


  • Discourse touched me in a no-no place

    @blakeyrat said:

    Can you create a view on it and add a PK to the view?

    That sounds horrible. Fixing the schema would be a better approach. Or, given that it was produced by another tool that is an obvious 💩 pile, burning that tool in the flames of a thousand suns before tracking down the authors with a pack of hunting dogs.



  • Are we now playing LINQ-to-SQL Code Golf?

    I haven't touched LINQ-to-SQL in a few years now, and when I did it was always using relatively sane schemas I designed myself. I only had to go through the back door, so to speak, once or twice due to LINQ limitations.



  • @dkf said:

    That sounds horrible.

    I doubt it'd work. SQL Server would have the exact same problem as the ORMs when trying to associate a row in the view with a row in the original data.

    The only other thing I can think of is to put your logic in a SPROC and execute that from LINQ-to-SQL, but there's not really much point to that.


  • Discourse touched me in a no-no place

    @blakeyrat said:

    The only other thing I can think of is to put your logic in a SPROC and execute that from LINQ-to-SQL, but there's not really much point to that.

    I was thinking about some sort of abomination with a TEMP TABLE and a TRIGGER that writes things back to the real tables, but it seems to me to be less work to create a time machine and kill the grandparents of the developers to blame for the mess.

    (Some DBs give every table a counter-based PK column unless you specify the PK to be something else. That might not be of use here though.)


  • FoxDev

    @mott555 said:

    Are we now playing LINQ-to-SQL Code Golf?

    Nah, not really. Well, maybe a bit. Anyway, I'm just reporting what I found with a quick Googling ;)


Log in to reply