[SQL SERVER] Dealing with nulls values and timestamp columns during insert

localCtx.Dbo.WriteStorages.Create(x.ColumnValues) を使用する SQL Server パスから始め、その後、insert 時に null 値と timestamp 列がどのように扱われるかを確認します。完了条件は、同期処理が null を DBNull.Value に置き換えたり timestamp 列を手動でフィルタリングしたりせずに、これらのレコードを挿入できることです。

Description

As an exercise I've ported a small console application that sync tables in SQL SERVER DB from C# EF to F# SQLProvider.

In C# I was able to do the following:

     foreach (Type t in types)
                {
                    List<object> remoteEntities = await remoteContext.Set(t).ToListAsync();
                    var localEntities = await localContext.Set(t).ToListAsync();

                    localContext.Set(t).RemoveRange(localEntities);
                    localContext.Set(t).AddRange(remoteEntities);
                }
               await localContext.SaveChangesAsync();

In F# It became something like this:

                            ...deleting old entities...
remoteCtx.Dbo.WriteStorages |> Seq.map (fun x-> localCtx.Dbo.WriteStorages.Create(x.ColumnValues)) |>  Seq.toList

Actual behavior

This approach was failing due to 2 errors:

• null had to be replaced with System.DBNull.Value and it was producing errors like:
  An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll: 'The parameterized query '(@param0 uniqueidentifier,@param1 datetime,@param2 nvarchar(14),' expects the parameter '@param3', which was not supplied.'

• should filter Timestamp column from ColumnValues because of errors like:
  An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll: 'Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.'

For EF It's working out-of-box, so maybe SQLProvider could also take care of these cases?

Known workarounds

Do the replacements mentioned above.

Related information

• SQL SERVER
• Windows
• .NET Runtime