none
Entity Framework code first 4.1 : Calculated/Computed column is not inserted in database RRS feed

  • Question

  • I have below entity with a calculated/computed column:

    public EntityA
    {
        [Key(), Required]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
    
        .....
    
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public virtual string RefId {
            get
            {
                return this.Id.ToString().PadLeft(7, '0');
            }
    
            private set
            {
            }
        }
    } 

    RefId is a computed column that depends on the Id value.

    After performing commit changes to database with SaveChanges I can check that Id and RefId have been set correctly for the entity I am currently inserting on database, but If I open the database and check RefId column for this entity, I can observe that RefId column has not been set, if figures as NULL. Why? Any ideas?

    Friday, June 7, 2013 6:19 PM

Answers

  • Hi rrodri;

    No matter what you come up with you will need two trips to the database because RefId is dependent on the primary key and that is not known to EF until immediately after the insert which means that another SaveChanges is needed to update the RefId column.  

    One way to get around this issue would be to create a stored procedure on the server and have the server at insert add RefId into the column format it the way you wanted. That would mean each time you wish to insert a new EntitiesA record into the database you would need to use the stored procedure to do that so that the RefId column would be correctly inserted.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Marked as answer by rrodri Friday, June 7, 2013 8:56 PM
    Friday, June 7, 2013 8:49 PM

All replies

  • Hi rrodri;

    You state the following, "RefId is a computed column that depends on the Id value.", So in the database you have a formula that when the row is created / inserted the database calculates the RefId column and inserts that calculated value into the column?

    When a property in the model is marked with the attribute [DatabaseGenerated(DatabaseGeneratedOption.Computed)] Entity Framework will NOT update this column in the database it will return the value that is stored there.

    When you look at the value of RefId in code you are just looking at the value of the ID field re-formatted and not the value in the database because of your getter in RefId.

    To your statement, "but If I open the database and check RefId column for this entity, I can observe that RefId column has not been set, if figures as NULL. Why?", I believe it is because the database is not generating / calculating a value for RFID and therefore it is inserting null's in that column. 

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Friday, June 7, 2013 7:35 PM
  • Hi rrodri;

    You state the following, "RefId is a computed column that depends on the Id value.", So in the database you have a formula that when the row is created / inserted the database calculates the RefId column and inserts that calculated value into the column?

    When a property in the model is marked with the attribute [DatabaseGenerated(DatabaseGeneratedOption.Computed)] Entity Framework will NOT update this column in the database it will return the value that is stored there.

    When you look at the value of RefId in code you are just looking at the value of the ID field re-formatted and not the value in the database because of your getter in RefId.

    To your statement, "but If I open the database and check RefId column for this entity, I can observe that RefId column has not been set, if figures as NULL. Why?", I believe it is because the database is not generating / calculating a value for RFID and therefore it is inserting null's in that column. 

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.


    So as I understand I must put the formula in the database side, so I do not understand which is the advantage of mark it as computed from code by putting  [DatabaseGenerated(DatabaseGeneratedOption.Computed)] if EF is not able to set it.... it has no sense then. What is the goal of marking it as computed? when using computed columns then? so then why mark it from code? If I do it from database side it is not necessary to mark it from code using EF, so this data annotation it seems to not has sense...
    Friday, June 7, 2013 7:51 PM
  • Hi rrodri;

    To the questions in your last post. The reason for the attribute placed on RefId in Ef is so that EF will NOT attempt to over write the databases computed value stored in the column. So having the attribute on the property makes the property read only. Without this property any changes made to RefId in code will over write the values in the database which is not what you want if the value is being calculated in the database.

       


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Friday, June 7, 2013 8:07 PM
  • Hi rrodri;

    To the questions in your last post. The reason for the attribute placed on RefId in Ef is so that EF will NOT attempt to over write the databases computed value stored in the column. So having the attribute on the property makes the property read only. Without this property any changes made to RefId in code will over write the values in the database which is not what you want if the value is being calculated in the database.

       


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Ok, now I understand you. Many thanks for you explanation. My problem is that I need to have the RefId column in database and as computed from Id but I do not want to make it as computed from database side.

    I have thought on not mark RefId as calculated and set it from code once I insert the entityA by performing the below code and using a transactionscope:

    using (var trans = new TransactionScope()) { // Create new EntityA object // and set EntityA object dbcontext.SaveChanges(); int id = newEntity.Id; dbContext.EntitiesA.RefId = string.Format("{0:0000000}", id); dbcontext.SaveChanges(); trans.Complete(); }

    the problem with this approach is that I have to savechanges twice and I am not sure if it is efficient in performance. Is it the approprate way to do it?

    Thanks.

    Friday, June 7, 2013 8:20 PM
  • Hi rrodri;

    No matter what you come up with you will need two trips to the database because RefId is dependent on the primary key and that is not known to EF until immediately after the insert which means that another SaveChanges is needed to update the RefId column.  

    One way to get around this issue would be to create a stored procedure on the server and have the server at insert add RefId into the column format it the way you wanted. That would mean each time you wish to insert a new EntitiesA record into the database you would need to use the stored procedure to do that so that the RefId column would be correctly inserted.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Marked as answer by rrodri Friday, June 7, 2013 8:56 PM
    Friday, June 7, 2013 8:49 PM