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

[rus-vladimir]

trafficstars

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

[Thorium]

What does your table look like? Do you have default values to columns?

[rus-vladimir]

My table looks like this:

CREATE TABLE [dbo].[WriteStorages](
	[ID] [uniqueidentifier] NOT NULL,
	[StorageID] [bigint] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Path] [nvarchar](450) NOT NULL,
	[Version] [timestamp] NOT NULL,
	[InsertDate] [datetime] NOT NULL,
	[ModifyDate] [datetime] NULL,
	[InsertUser] [nvarchar](50) NULL,
	[ModifyUser] [nvarchar](50) NULL,
	[RecycleBinPath] [nvarchar](450) NOT NULL DEFAULT (''),
 CONSTRAINT [PK_dbo.WriteStorages] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)
) ON [PRIMARY]

Columns that were causing issues are:

• Insert/Modify Date/User: it contained null values that I had to convert to System.DBNull.Value.
• Version: I had to exclude manually

Probably EF ignores Timestamp columns on insert/update and converts null to DBNull behind the scenes, so I was expecting similar behavior.

[Thorium]

Ok, I was thinking if something like this could help:

ALTER TABLE [dbo].[WriteStorages] ADD  DEFAULT (getdate()) FOR [Version]
GO

[rus-vladimir]

To me it looks like another workaround. I got it already working by the workarounds I've mentioned above, so it was more a question about the behavior of SQLProvider, rather finding a way to make it work. In the CRUD page there is this example:

employees 
    |> Seq.map (fun x ->
                employee.Create(x.ColumnValues)) // create twins
    |>  Seq.toList

let twins = ctx.GetUpdates() // Retrieve the FSharp.Data.Sql.Common.SqlEntity objects

ctx.SubmitUpdates() // no record is added

which wasn't working in scenario I've mentioned. If this doesn't sound like an issue of SQLProvider, then this can be closed.