Bulk insert fails when database contains computed columns

[InteXX]

I'm getting errors when attempting to insert into a table that contains computed columns. In this case, Shift is a computed column.

Is there a way to 'ignore' these columns for the purpose of the insert?

Unhandled Exception: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Shift', table 'tempdb.dbo.#8e9dd922f00c4719b21059e9c4042ccf___________________________________________________________________________________000000000009'; column does not allow nulls. INSERT fails.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinuedOnSuccess(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkCopy(DbContext ctx, IList entities, Type t, Mappings mappings, SqlTransaction transaction, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, BulkInsertResponse response)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkInsertAll(DbContext ctx, IList`1 entities, SqlTransaction transaction, Boolean recursive, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, Dictionary`2 savedEntities, BulkInsertResponse response)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.BulkInsertAll[T](DbContext ctx, BulkInsertRequest`1 request)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.BulkInsertAll[T](DbContext ctx, IList`1 entities, SqlTransaction transaction, Boolean recursive)
   at DataMigrator.Main.Migrate() in D:\Dev\DataMigrator\Modules\Main.vb:line 47
   at DataMigrator.Main.Main() in D:\Dev\DataMigrator\Modules\Main.vb:line 5

[mtanneryd]

Interesting. I’ll have a look as soon as I can.

Måns Tånneryd Tånneryd IT AB Barrskogsvägen 19 186 53 Vallentuna +46-705140093 https://se.linkedin.com/in/manstanneryd

13 jan. 2019 kl. 14:25 skrev InteXX notifications@github.com:

I'm getting errors when attempting to insert into a table that contains computed columns. In this case, Shift is a computed column.

Is there a way to 'ignore' these columns for the purpose of the insert?

Unhandled Exception: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Shift', table 'tempdb.dbo.#8e9dd922f00c4719b21059e9c4042ccf___000000000009'; column does not allow nulls. INSERT fails. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler) at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinuedOnSuccess(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource1 source) at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource1 source) at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource1 source) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource1 source) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource1 source) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken) at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkCopy(DbContext ctx, IList entities, Type t, Mappings mappings, SqlTransaction transaction, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, BulkInsertResponse response) at Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkInsertAll(DbContext ctx, IList1 entities, SqlTransaction transaction, Boolean recursive, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, Dictionary2 savedEntities, BulkInsertResponse response) at Tanneryd.BulkOperations.EF6.DbContextExtensions.BulkInsertAll[T](DbContext ctx, BulkInsertRequest1 request) at Tanneryd.BulkOperations.EF6.DbContextExtensions.BulkInsertAll[T](DbContext ctx, IList`1 entities, SqlTransaction transaction, Boolean recursive) at DataMigrator.Main.Migrate() in D:\Dev\DataMigrator\Modules\Main.vb:line 47 at DataMigrator.Main.Main() in D:\Dev\DataMigrator\Modules\Main.vb:line 5 — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[InteXX]

That's great, thanks.

[mtanneryd]

Ok. I have replicated the bug and will fix it asap. Might be a day or two though since I am currently under the dire influence of the flu. (pun intended) :-)

[mtanneryd]

There is a potential fix ready now available in 1.2.3-beta1. Please try it out and let me know how if it works for you. This beta version also contains some, not fully tested, fixes for problems with many-to-many relationship inserts.

[InteXX]

D³ + Iodine. I've been at it daily for four years, and I haven't been down once. Get well :-)

Alas, the problem persists.

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="AutoMapper" version="8.0.0" targetFramework="net472" />
  <package id="EntityFramework" version="6.2.0" targetFramework="net472" />
  <package id="Intexx.ServiceModel" version="2017.12.1.1" targetFramework="net472" />
  <package id="System.ValueTuple" version="4.5.0" targetFramework="net472" />
  <package id="Tanneryd.BulkOperations.EF6" version="1.2.3-beta1" targetFramework="net472" />
</packages>

Here's the SQL statement I'm using to create the Shift computed column:

ALTER TABLE [LogEntries] ADD [Shift] AS (IIF(DATEPART(HOUR, DATEADD(HOUR, -7, [LogTime])) < 12, 1, 2)) PERSISTED

Is there anything further I can do to help? (Within limits... I'm not a C#; been a VB since '99.)

[mtanneryd]

Hi!

It would be helpful if you could show me how you have configured your context. Fluent API? Attributes?

[InteXX]

Fluent API. Here's the 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

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()

      '
      ' We can't index [OffsetTime] here, as it's an ignored property.
      ' We'll do it as an explicit SQL command in the migration instead.
      '
      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")

      '
      ' Computed db columns
      '
      Me.Ignore(Function(Entry) Entry.CorrectionFactor)
      Me.Ignore(Function(Entry) Entry.MinutesOffline)
      Me.Ignore(Function(Entry) Entry.OffsetTime)
      Me.Ignore(Function(Entry) Entry.CoOnline)
      Me.Ignore(Function(Entry) Entry.Shift)
      Me.Ignore(Function(Entry) Entry.Co7)
    End Sub
  End Class
End Namespace

[mtanneryd]

It seems to me as if you are simply "ignoring" your computed columns in your fluent config. Is this correct? If so, that is probably why it does not work. Unless you configure the computed columns as such EF will think that they are regular columns and try to treat them accordingly.

You need something like this

modelBuilder.Entity(Of LogEntry)() .Property(Function(t) t.Shift) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)

[InteXX]

Yes, I tried that first. But I ran into this problem.

I'll try again.

[InteXX]

I'm working on this.

If you have any insight into this problem, I'll be pleased to hear about it.

[mtanneryd]

No such insight unfortunatly but there is a new beta release 1.2.3-beta2 available in a couple of minutes that might help with your initial problem.

/Måns

Måns Tånneryd Tånneryd IT AB Barrskogsvägen 19 186 53 Vallentuna +46-705140093 https://se.linkedin.com/in/manstanneryd https://www.facebook.com/matkollen

Den ons 16 jan. 2019 kl 04:04 skrev InteXX notifications@github.com:

I'm working on this.

If you have any insight into this problem https://stackoverflow.com/q/54209808/722393, I'll be pleased to hear about it.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/mtanneryd/ef6-bulk-operations/issues/12#issuecomment-454634699, or mute the thread https://github.com/notifications/unsubscribe-auth/ADQZRd0Pd4XkV-cLHPJQuSbjjCShUaJEks5vDpbPgaJpZM4Z9Kqc .

[InteXX]

That was quite a bit of work you did for 1.2.3-beta1, followed up with a simple SQL statement fix for 1.2.3-beta2 :-)

So I'm pleased to be able to report that it works (using the Ignore() function).

I was just about to junk my computed columns and retool for a view, but then your beta2 came along and saved the day.

Good job. Thanks :-)

I'm curious... have you tested this with SQLCE?

[mtanneryd]

No. Have not had the time to test with anything else than SQL Server and LocalDb. Also plan to make a version available for EF Core but it will take a while. Lots on the agenda at the moment.

[InteXX]

I understand. I'll give it a try.