none
Linq is saving database CalculatedColumn RRS feed

  • Question

  • Hello,

    I have a SQL table with a calculated column AggregateCost. In the dbml, L2S correctly marks the column as AutoGeneratedValue=True and UpdateCheck=Never. The data from this table is displayed in a WPF grid and my requirement is that the AggregateCost value should update in the UI as the user edits the data. I've used PropertyChanged handlers on the data class to recalculate AggregateCost when the user changes data and I store the value back into the _AggregateCost field.

    When I SubmitChanges(), I get the following exception:

    System.InvalidOperationException was unhandled

    Value of member 'AggregateCost' of an object of type 'MyObject' changed.
    A member that is computed or generated by the database cannot be changed.
    Source=System.Data.Linq

    Presumably, LINQ sees that I've changed the value for AggregateCost and since it is a computed column in the database, it rejects the update. My question is - why does LINQ check the value of a computed column during save? It shouldn't be saving it in the first place and this column is set to UpdateCheck=Never. The same thing happens if I set this column to ReadOnly in the dbml - LINQ still checks if the readonly AggregateCost value has changed.

    Is there any way I can force LINQ to completely ignore this calcluated column during an update?

    As a side note, I know there are a couple of alternative workarounds, such as creating a separate calculated field in a partial class and use that to update in my UI, or to remove the calculated column from the dbml and create it in a partial class. I would like to avoid doing any of these, if possible. I also know I can implement my own stored procedure for the Update operation, but I was hoping to use LINQ for database updates and concurrency handling.

    Environment is VS2010, .NET 4.0

    Thanks for any help.

    Monday, July 12, 2010 1:10 PM

Answers

  • The UpdateCheck property is for telling LINQ to SQL whether to use this field in the WHERE clause for UPDATE statements in order to implement concurrency. It does not tell LINQ to SQL to ignore it completely.

    Writing a stored proc to perform the update won't help - this check is done before the database operation occurs.

    You would normally just move this field to your own class especially if you calculate the value locally and don't need to select on it.

    [)amien

    • Marked as answer by AngelKS Monday, July 12, 2010 5:03 PM
    Monday, July 12, 2010 3:01 PM
    Moderator

All replies

  • The UpdateCheck property is for telling LINQ to SQL whether to use this field in the WHERE clause for UPDATE statements in order to implement concurrency. It does not tell LINQ to SQL to ignore it completely.

    Writing a stored proc to perform the update won't help - this check is done before the database operation occurs.

    You would normally just move this field to your own class especially if you calculate the value locally and don't need to select on it.

    [)amien

    • Marked as answer by AngelKS Monday, July 12, 2010 5:03 PM
    Monday, July 12, 2010 3:01 PM
    Moderator
  • Damien, thanks for your clarification on the stored proc approach. The reason I do not want to calculate this field in my own class is because I actually have more than ten calculated fields in that table, with thousands of records. I don't want to have to recalculate all that while the user is trying to scroll, especially since the calculated values are already in the database.

    I will probably end up doing a hybrid approach where I get the values from the database with LINQ, but use a different set of fields on my class to display/update the values in the UI. Still, it is strange that LINQ checks for changes on a calculated database field.

    Thanks.

    Monday, July 12, 2010 5:03 PM
  • Hi guys, have you found the solution for this

    Value of member 'MyComputedColumn' of an object of type 'MyObject' changed.

    A member that is computed or generated by the database cannot be changed.

    problem?

    I have the same one and no idea how to solve it. Already tried almost every possible combination of parameters but still without results.

    Linq constantly tries to update my computed columns.

    M.

    Wednesday, October 20, 2010 9:38 AM