SqlProgrammability provider fails to update/bulk copy into tables with computed persistent columns

[ntr]

Description

SqlProgrammability provider fails to update/bulk copy into tables with computed persistent columns - you get exception when you call update method: System.Data.NoNullAllowedException: Column '{persistentColumn}' does not allow nulls.

Repro steps

Please provide the steps required to reproduce the problem

1. Create table:

CREATE TABLE [dbo].[T1](
	[A] [int] NOT NULL,
	B  AS ((0)) PERSISTED NOT NULL
)

2. Step B

type Database = SqlProgrammabilityProvider<"name=someConnectionString">
let t = new Database.dbo.Tables.T1()
t.AddRow(3)
t.Update() 

Expected behavior

New row is inserted into the table.

Actual behavior

System.Data.NoNullAllowedException: Column 'B' does not allow nulls.

Known workarounds

Following code can be used - works for update only:

    let t = new Database.dbo.Tables.T1()
    let r = t.NewRow(3)
    r.["B"] <- 0
    t.Rows.Add(r)
    t.Update()

Related information

.Net Standard 4.6.2 SqlServer 2016 SqlClient 1.8.1

[smoothdeveloper]

I've added a test case for this issue in a branch:

https://github.com/smoothdeveloper/FSharp.Data.SqlClient/tree/issue_bulkcopy_computed_columns

FSharp.Data.DataTablesTests.can bulk copy with computed column [FAIL]                                                                                                                                                                                                                   
   System.Data.SqlClient.SqlException : The column "year" cannot be modified because it is either a computed column or is the result of a UNION operator.                                                                                                                               
   Stack Trace:                                                                                                                                                                                                                                                                         
      at System.Data.SqlClient.SqlConnection.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.SubmitUpdateBulkCommand(String TDSCommand)                                                                                                                                                                                                   
      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(DataTable table, DataRowState rowState)                                                                                                                                                                                        
      C:\dev\projects\github.com\fsprojects\FSharp.Data.SqlClient\src\SqlClient\DataTable.fs(104,0): at FSharp.Data.DataTable`1.BulkCopy(FSharpOption`1 connection, FSharpOption`1 copyOptions, FSharpOption`1 transaction, FSharpOption`1 batchSize, FSharpOption`1 timeout)           

[samhanes]

By my reading of https://github.com/dotnet/SqlClient/issues/275, it looks like this is an issue for System.Data.SqlClient on netcore, but it has been fixed in Microsoft.Data.SqlClient. (I have not tested this.)

This issue might be resolved by migrating to Microsoft.Data.SqlClient as suggested in #374