0

To resolve my error (shown below) I would like to know how to edit my Entity Framework T4 template so that it adds “HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)” to the GENERATED ALWAYS AS ROW START/END columns of my Temporal Table in the POCO Configuration section of the auto-generated class like this: 

Property(x => x.ValidFrom).HasColumnName(@"valid_from").HasColumnType("datetime2").IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
Property(x => x.ValidTo).HasColumnName(@"valid_to").HasColumnType("datetime2").IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);

I can manually add this (which resolves my error), but of course it's overwritten when I save the T4 template.

Error trying to insert a record: "Cannot insert an explicit value into a GENERATED ALWAYS column in table 'my_table’. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column."

I saw this post, but it didn’t help (maybe it only works when using edmx files, or I didn’t understand the solution): Entity Framework not working with temporal table

Example of my Temporal Table: 

CREATE TABLE [dbo].[my_table](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [user_id] [int] NOT NULL,
    [valid_from] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [valid_to] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_my_table] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([valid_from], [valid_to])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[my_table_history] )
)
GO
ALTER TABLE [dbo].[my_table] ADD  CONSTRAINT [DF_my_table_SysStart]  DEFAULT (sysutcdatetime()) FOR [valid_from]
GO
ALTER TABLE [dbo].[my_table] ADD  CONSTRAINT [DF_my_table_SysEnd]  DEFAULT (CONVERT([datetime2](7),'9999-12-31 23:59:59')) FOR [valid_to]
GO

Example code to insert a record:

var recordToCreate = new MyTable()
{
    UserId = info.UserId,
    ValidFrom = info.ValidFrom,
    ValidTo = info.ValidTo,
};
_context.MyTables.Add(recordToCreate);
_context.SaveChanges();
NLandis
  • 199
  • 3
  • 8
  • I found a work-around; it's not what I wanted to add to the T4 template, but it solves the error. In the T4 template I edited the line: Settings.ColumnFilterExclude = null; to Settings.ColumnFilterExclude = new Regex("^valid_from$|^valid_to$"); – NLandis May 21 '18 at 22:00
  • This solution only works because I don't have any other columns in any other tables within my context named "valid_from" and "valid_to". If I did, and wanted to include them in my context, I couldn't because this would exclude them as well. – NLandis May 21 '18 at 22:08
  • Does this help? http://www.ozkary.com/2015/01/add-data-annotations-to-entity.html https://msdn.microsoft.com/en-us/library/ee256141(v=vs.98).aspx it explains how to add annotations to your model without them being overwritten by using 'buddy' classes – Nick.Mc May 22 '18 at 00:11
  • Thanks, I'm not using .edmx files, which the solution in your link uses, but it may help me, if I could first figure out how to get my T4 template to create partial classes when it generates them from the database. Still, it would not be as succinct as the solution I'm looking for. – NLandis May 22 '18 at 15:26

1 Answers1

0

I figured it out. I had to edit the EF.Reverse.POCO.Core.ttinclude file, adding ‘IsGeneratedAlwaysType’ to a number of classes, methods and SQL queries. I basically added it everywhere I found ‘IsForeignKey’. I’ve pasted below the snippet of what I added to each section.

public class Column
{
    public bool IsGeneratedAlwaysType;

    private void SetupConfig()
    {
        else if (IsGeneratedAlwaysType)
        {
            if(Settings.UseDataAnnotations)
                DataAnnotations.Add("DatabaseGenerated(DatabaseGeneratedOption.Computed)");
            else
                databaseGeneratedOption = string.Format(".HasDatabaseGeneratedOption({0}DatabaseGeneratedOption.Computed)", schemaReference);
        }

    private static Column CreateColumn(IDataRecord rdr, Regex rxClean, Table table, Regex columnFilterExclude)
    { 
        var col = new Column
        {
            IsGeneratedAlwaysType = rdr["IsGeneratedAlwaysType"].ToString().Trim().ToLower() == "true",

private class SqlServerSchemaReader : SchemaReader
{
    private const string TableSQL = @"
    CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'GeneratedAlwaysType') > 0 THEN 1
         ELSE 0
       END AS BIT) AS IsGeneratedAlwaysType
    FROM    #Columns c

private const string SynonymTableSQLSetup = @"
        CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(sc.NAME) + '.' + QUOTENAME(o.NAME)), c.NAME, 'GeneratedAlwaysType') > 0 THEN 1
                  ELSE 0
             END AS BIT) AS IsGeneratedAlwaysType
        INTO    #SynonymDetails
        FROM    sys.synonyms sn

INSERT INTO #SynonymDetails (SchemaName, TableName, TableType, Ordinal, ColumnName, IsNullable, TypeName, [MaxLength], [Precision],
                            [Default], DateTimePrecision, Scale, IsIdentity, IsStoreGenerated, PrimaryKey, PrimaryKeyOrdinal, IsForeignKey, IsGeneratedAlwaysType)

CAST(CASE WHEN COLUMNPROPERTY(st.base_object_id, c.NAME, ''GeneratedAlwaysType'') > 0 THEN 1
                ELSE 0
            END AS BIT) AS IsGeneratedAlwaysType
FROM    #SynonymTargets st
    INNER JOIN sys.columns c

        private const string SynonymTableSQL = @"
SELECT SchemaName, TableName, TableType, Ordinal, ColumnName, IsNullable, TypeName, [MaxLength], [Precision],
    [Default], DateTimePrecision, Scale, IsIdentity, IsStoreGenerated, PrimaryKey, PrimaryKeyOrdinal, IsForeignKey, IsGeneratedAlwaysType FROM #SynonymDetails";

        private const string TableSQLCE = @"
0 as IsGeneratedAlwaysType
FROM    INFORMATION_SCHEMA.COLUMNS c
NLandis
  • 199
  • 3
  • 8