xDevil
xDevil

Reputation: 147

Entity Framework Ignore properties on inserting data with temporal tables

I have a class named Address that is a system versioned table. I am trying to insert into this table using entity framework context adding an object to the context and then doing a SaveChanges call. This worked as expected when the table was not system versioned. In my model that looks something like this minus some properties that are not related to this question:

        [DataMember]
        public System.DateTime SysStartTime
        {
            get { return _sysStartTime; }
            set
            {
                if (_sysStartTime != value)
                {
                    _sysStartTime = value;
                    OnPropertyChanged("SysStartTime");
                }
            }
        }
        private System.DateTime _sysStartTime;

        [DataMember]
        public System.DateTime SysEndTime
        {
            get { return _sysEndTime; }
            set
            {
                if (_sysEndTime != value)
                {
                    _sysEndTime = value;
                    OnPropertyChanged("SysEndTime");
                }
            }
        }
        private System.DateTime _sysEndTime;

        [DataMember]
        public int AddressId
        {
            get { return _addressId; }
            set
            {
                if (_addressId != value)
                {
                    if (ChangeTracker.ChangeTrackingEnabled && ChangeTracker.State != ObjectState.Added)
                    {
                        throw new InvalidOperationException("The property 'AddressId' is part of the object's key and cannot be changed. Changes to key properties can only be made when the object is not being tracked or is in the Added state.");
                    }
                    _addressId = value;
                    OnPropertyChanged("AddressId");
                }
            }
        }
        private int _addressId;

This historical table was created as per documentation with a generated always property hidden and default value. When I try to do a simple insert I get the Error:

Cannot insert value into generated always identity column

I understand what the error is saying but I don't know how to fix it. I have tried to remove the SysStartTime and SysEndTime properties from the model but that gives me validation issues. I have also tried not setting the properties on the model as DataMember and even IgnoreDataMember but the result was always the same. My question is how can I remove the SysStartTime and SysEndTime properties from the object with the address model before adding it to the context? Thank you.

UPDATE: I have used this code to create my temporal table:

ALTER TABLE [dbo].[Address] ADD
                             [SysStartTime] datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT DF_Telco_SysStartTime DEFAULT '1900-01-01 00:00:00',
                             [SysEndTime] datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT DF_Telco_SysEndTime DEFAULT '9999-12-31 23:59:59',
                             PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]) 

ALTER TABLE [dbo].[Address] 
                             SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Address_History]));

Upvotes: 1

Views: 708

Answers (1)

xDevil
xDevil

Reputation: 147

I have found a workaround for this issue by modifying the model.edmx file and adding to the SysStartTime and SysEndTime StoreGeneratedPattern="Computed" property.

Upvotes: 1

Related Questions