I have some computed columns in a SQL database that're giving EF6 a good bit of trouble. I'm using this technique.
For some reason, the PM command Update-Database
insists on trying to add a column to a table that already exists as a computed column.
I only discovered this by temporarily enabling Automatic Migrations. With Automatic Migrations disabled, I get the famously obscure "Unable to update database to match the current model because there are pending changes and automatic migration is disabled."
Here's the relevant excerpted SQL output from Update-Database -Verbose
:
Applying explicit migration: 201805012255477_001.
CREATE TABLE [dbo].[LogEntries] (
[Id] [int] NOT NULL IDENTITY,
[ReceiveTime] [datetime] NOT NULL DEFAULT '1753-01-01T00:00:00.000',
[SendTime] [datetime] NOT NULL DEFAULT '1753-01-01T00:00:00.000',
[LogTime] [datetime] NOT NULL DEFAULT '1753-01-01T00:00:00.000',
[Scale] [real] NOT NULL DEFAULT 0,
[Co] [real] NOT NULL DEFAULT 0,
[O2] [real] NOT NULL DEFAULT 0,
CONSTRAINT [PK_dbo.LogEntries] PRIMARY KEY ([Id])
)
...
ALTER TABLE [LogEntries] ADD [CorrectionFactor] AS (13.9 / (20.9 - [O2])) PERSISTED
...
Applying automatic migration: 201901160207184_AutomaticMigration.
ALTER TABLE [dbo].[LogEntries] ADD [CorrectionFactor] [real] NOT NULL DEFAULT 0
System.Data.SqlClient.SqlException (0x80131904): Column names in each table must be unique. Column name 'CorrectionFactor' in table 'dbo.LogEntries' is specified more than once.
(Note that I've created no migration named AutomaticMigration
. EF6 seems to be doing this on its own.)
It's worth mentioning that both the database and the table are created successfully. This problem doesn't occur until after my last migration has run.
Expected result
The Update-Database
command should complete without error
Actual result
The Update-Database
command attempts to create the duplicate column, resulting in a database error
Things I've tried
.Ignore()
API; however, that and the .HasDatabaseGeneratedOption()
API are mutually exclusiveThe .HasDatabaseGeneratedOption()
API appears to have no effect. What else can be done to instruct EF6 to not attempt to create that column?
Here's my code:
Context
Namespace Db
Public Class Context
Inherits DbContext
Public Sub New()
MyBase.New(Utils.DbConnectionString)
End Sub
Private Sub New(Connection As DbConnection)
MyBase.New(Connection, True)
Database.SetInitializer(New CreateDatabaseIfNotExists(Of Context))
Database.SetInitializer(New MigrateDatabaseToLatestVersion(Of Context, Migrations.Configuration))
Me.Database.Initialize(False)
End Sub
Public Shared Function Create() As Context
Return New Context(DbConnection)
End Function
Private Shared ReadOnly Property DbConnection As SqlConnection
Get
Return New SqlConnection(Utils.DbConnectionString)
End Get
End Property
Protected Overrides Sub OnModelCreating(Builder As DbModelBuilder)
Builder.Configurations.AddFromAssembly(Assembly.GetExecutingAssembly)
MyBase.OnModelCreating(Builder)
End Sub
Public Property LogEntries As DbSet(Of LogEntry)
End Class
End Namespace
Entity
Namespace Db
Public Class LogEntry
Public Property Id As Integer
Public Property ReceiveTime As Date
Public Property SendTime As Date
Public Property LogTime As Date
Public Property Scale As Single
Public Property Scc As Single
Public Property Co As Single
Public Property O2 As Single
#Region " Computed Columns "
Public Property CorrectionFactor As Single
Get
Return 13.9 / (20.9 - Me.O2)
End Get
Private Set(Value As Single) : End Set
End Property
Public Property MinutesOffline As Single
Get
Return IIf(Me.Scale < 1, 1, 0)
End Get
Private Set(Value As Single) : End Set
End Property
Public Property OffsetTime As Date
Get
Return Me.LogTime.AddHours(-7)
End Get
Private Set(Value As Date) : End Set
End Property
Public Property Co7Online As Single
Get
Return IIf(Me.Scale > 1, Me.Co7, 0)
End Get
Private Set(Value As Single) : End Set
End Property
Public Property Shift As Short
Get
Return IIf(Me.OffsetTime.Hour < 12, 1, 2)
End Get
Private Set(Value As Short) : End Set
End Property
Public Property Co7 As Single
Get
Return Me.Co * Me.CorrectionFactor
End Get
Private Set(Value As Single) : End Set
End Property
#End Region
End Class
End Namespace
Configuration
Namespace Configurations
Public Class LogEntry
Inherits EntityTypeConfiguration(Of Db.LogEntry)
Public Sub New()
Me.Property(Function(Entry) Entry.ReceiveTime).IsRequired()
Me.Property(Function(Entry) Entry.SendTime).IsRequired()
Me.Property(Function(Entry) Entry.LogTime).IsRequired()
Me.Property(Function(Entry) Entry.Scale).IsRequired()
Me.Property(Function(Entry) Entry.Scc).IsRequired()
Me.Property(Function(Entry) Entry.Co).IsRequired()
Me.Property(Function(Entry) Entry.O2).IsRequired()
Me.HasIndex(Function(Entry) Entry.ReceiveTime).HasName("IX_LogEntries_ReceiveTime")
Me.HasIndex(Function(Entry) Entry.SendTime).HasName("IX_LogEntries_SendTime")
Me.HasIndex(Function(Entry) Entry.LogTime).HasName("IX_LogEntries_LogTime")
Me.HasIndex(Function(Entry) Entry.Scale).HasName("IX_LogEntries_Scale")
Me.HasIndex(Function(Entry) Entry.Scc).HasName("IX_LogEntries_Scc")
Me.HasIndex(Function(Entry) Entry.Co).HasName("IX_LogEntries_Co")
Me.HasIndex(Function(Entry) Entry.O2).HasName("IX_LogEntries_O2")
Me.Property(Function(Entry) Entry.CorrectionFactor).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)
Me.Property(Function(Entry) Entry.MinutesOffline).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)
Me.Property(Function(Entry) Entry.OffsetTime).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)
Me.Property(Function(Entry) Entry.Co7Online).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)
Me.Property(Function(Entry) Entry.Shift).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)
Me.Property(Function(Entry) Entry.Co7).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)
End Sub
End Class
End Namespace
User contributions licensed under CC BY-SA 3.0