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

  • Question

  • I have a table with a column called LastUpdatedOn, which is used to record the time the record was last updated.  It is of type SmallDateTime and non-nullable with default value set to GetDate() so that an Insert will generate the correct time automatically.

    However, if I try to update this column via LINQ to SQL, I get the error "A member that is computed or generated by the database cannot be changed."

    The DBML Designer sets Auto Generated Value to true.

    Is there a way to have a column that has a database default value if none is provided and yet can be updated manually?

    Thanks.

    Saturday, December 17, 2011 4:47 PM

Answers

  • Thanks, my problem is similar but that workaround takes too much effort and has to be maintained.  I would rather set Auto Generated Value to false and then manually providing a default value when inserting new rows.

    My problem, not life threatening, can be summarized as thus:

    a. A SQL table has a column which have default values.

    b. If I set Auto Generated Value=true for the entity property mapped to that column, then I can can insert new rows without having to supply a value for that column.  However, I cannot update that column.  In other words, that column becomes sort of read only.

    Wednesday, December 28, 2011 2:33 PM
  • Thanks, my problem is similar but that workaround takes too much effort and has to be maintained.  I would rather set Auto Generated Value to false and then manually providing a default value when inserting new rows.

    My problem, not life threatening, can be summarized as thus:

    a. A SQL table has a column which have default values.

    b. If I set Auto Generated Value=true for the entity property mapped to that column, then I can can insert new rows without having to supply a value for that column.  However, I cannot update that column.  In other words, that column becomes sort of read only.

    Your requirement is little strange to me for more than one reason.

    You have field in sql server that is auto generated = true, that means creating it that way means you don't want to bother your self filling it (it will be filled automatically) and you want to stop that behavior and fill it by hand? if that is your requirement there is no need for making it auto generated in first place.

    Second, you cannot update that field from sql server itself, it is not LINQ limitation.

    You, chose the correct behavior to your field and work on that base.

    Wednesday, December 28, 2011 8:23 PM

All replies

  • Just created a dummy table with "your" structure, populated it with some data and then used VS2010 to create the Linq code.  In my case the date field was correctly defined 

    <Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_DateCreate", DbType:="Date NOT NULL")>

    Can you post the TSQL generated by Management Studio for the table?  

    Lloyd Sheen


    Lloyd Sheen
    Sunday, December 18, 2011 12:03 AM
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 26, 2011 3:04 AM
    Moderator
  • The table create script includes the following (the column is LastLoggedIn):

    ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_LastLoggedIn]  DEFAULT (getdate()) FOR [LastLoggedIn]
    GO
    
    

    In the ORM Designer, if I set Auto Generated Value to False, updating that column via LINQ to SQL works, but when I insert a new record into the table I must explicit supply a DateTime.

    If I set Auto Generated Value to True, inserting a new record in LINQ to SQL works without me having to supply a value for the column, but if I try to update that column via LINQ to SQL, it says:

    Value of member 'LastAccessed' of an object of type 'User' changed.
    A member that is computed or generated by the database cannot be changed.

    Thanks.

    Tuesday, December 27, 2011 9:39 AM
  • Hi K.Kong,

    I found a Kb here: http://www.codeproject.com/KB/linq/SettingDefaultValues.aspx, Is the same question as you? You can try to use the Workaround in his post.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, December 28, 2011 1:10 PM
    Moderator
  • Thanks, my problem is similar but that workaround takes too much effort and has to be maintained.  I would rather set Auto Generated Value to false and then manually providing a default value when inserting new rows.

    My problem, not life threatening, can be summarized as thus:

    a. A SQL table has a column which have default values.

    b. If I set Auto Generated Value=true for the entity property mapped to that column, then I can can insert new rows without having to supply a value for that column.  However, I cannot update that column.  In other words, that column becomes sort of read only.

    Wednesday, December 28, 2011 2:33 PM
  • Thanks, my problem is similar but that workaround takes too much effort and has to be maintained.  I would rather set Auto Generated Value to false and then manually providing a default value when inserting new rows.

    My problem, not life threatening, can be summarized as thus:

    a. A SQL table has a column which have default values.

    b. If I set Auto Generated Value=true for the entity property mapped to that column, then I can can insert new rows without having to supply a value for that column.  However, I cannot update that column.  In other words, that column becomes sort of read only.

    Your requirement is little strange to me for more than one reason.

    You have field in sql server that is auto generated = true, that means creating it that way means you don't want to bother your self filling it (it will be filled automatically) and you want to stop that behavior and fill it by hand? if that is your requirement there is no need for making it auto generated in first place.

    Second, you cannot update that field from sql server itself, it is not LINQ limitation.

    You, chose the correct behavior to your field and work on that base.

    Wednesday, December 28, 2011 8:23 PM
  • Hi K.Kong,

    I agree with @Samir.

    ==================

    you cannot update that field from sql server itself, it is not LINQ limitation.

    ==================

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 29, 2011 8:43 AM
    Moderator
  • You have field in sql server that is auto generated = true, that means creating it that way means you don't want to bother your self filling it (it will be filled automatically) and you want to stop that behavior and fill it by hand? if that is your requirement there is no need for making it auto generated in first place.

    Second, you cannot update that field from sql server itself, it is not LINQ limitation.

    You, chose the correct behavior to your field and work on that base.


    Perhaps I am wrong on some fundamental issue.  If that is the case please pardon me.

    I thought the Auto Generated Value is an ORM parameter and not a SQL Server feature. 

    As far as I know, SQL Server has a Default Value setting for a column that is automatically applied if a new record is INSERTed without a value supplied for that column.  And if I UPDATE a row, I can change the value in such a column that has a Default Value specified.  Such a column is not a read-only column.  It has always worked for me that way.

     

    Sunday, January 1, 2012 11:59 AM
  • .

    I thought the Auto Generated Value is an ORM parameter and not a SQL Server feature. 

    As far as I know, SQL Server has a Default Value setting for a column that is automatically applied if a new record is INSERTed without a value supplied for that column.  And if I UPDATE a row, I can change the value in such a column that has a Default Value specified.  Such a column is not a read-only column.  It has always worked for me that way.

     

    Hi

    It is my fault I did not picked up the correct term for what I want to explain, i used "auto generated" which can be explained in many ways.

    I guess I seen the word "Computed" somewhere in your previous post, and that is what I am talking about, the computed field which has formula in sql server cannot be updated, it is read only. While the field with default value is like any other normal field except when it is saved the default value is triggered if the field does not has a value.

    What I want to say is, computed field is read only in the sql server, while field with default value is normal and you can change it is value any time.


    Sunday, January 1, 2012 1:32 PM
  • Thanks, my problem is similar but that workaround takes too much effort and has to be maintained.  I would rather set Auto Generated Value to false and then manually providing a default value when inserting new rows.

    Yes, I can repro your question. Your workaround is good. I'd like to report your question, thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 10, 2012 6:33 AM
    Moderator