System.Data.SqlClient.SqlException: "Cannot insert the value NULL into colum..." (DateTime)

[c0d3-4-F00d]

Hello,
not sure if this is an issue of Dapper, or Dapper.Contrib.

I tried to insert some data into an existing table via a self made REST API, but it always fails with this (partially German) exception:

Ausnahme ausgelöst: "System.Data.SqlClient.SqlException" in Dapper.dll
Eine Ausnahme vom Typ "System.Data.SqlClient.SqlException" ist in Dapper.dll aufgetreten, doch wurde diese im Benutzercode nicht verarbeitet.
Cannot insert the value NULL into column 'TI_STAMP', table '[..].CU_UNIKATHISTORY'; column does not allow nulls. INSERT fails.
The statement has been terminated.

The create statement for the table (table not invented by me):

CREATE TABLE [dbo].[CU_UNIKATHISTORY](
	[UNIKATSTART] [numeric](12, 0) NOT NULL,
	[UNIKATCOUNT] [numeric](12, 0) NULL,
	[PRODUCT] [nvarchar](50) NULL,
	[TI_STAMP] [datetime] NOT NULL,
	[NOTES] [nvarchar](255) NULL,
 CONSTRAINT [PK_CU_UNIKATHISTORY] PRIMARY KEY CLUSTERED 
(
	[TI_STAMP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

The Model:

using System;
using D=Dapper.Contrib.Extensions;
using System.ComponentModel.DataAnnotations;

namespace MesApi.Data.Model
{
	[D.Table( "CU_UNIKATHISTORY" )]
	public class CU_UnikatHistory
	{
		[Required]
		public int? Unikatstart { get; set; }
		
		public int? Unikatcount { get; set; }

		[MaxLength( 50 )]
		public string Product { get; set; }

		[D.Key]
		[Required]
		public DateTime? TI_Stamp { get; set; }

		[MaxLength(255)]
		public string Notes { get; set; }
	}
}

Also tried it with non-nullable variables - without success.

The code that fails:

public void WriteUnikatHistory( CU_UnikatHistory history )
{
	using( var connection = new SqlConnection( csb.ConnectionString ) )
	{
		connection.Open();
		connection.Insert( history ); //<--- System.Data.SqlClient.SqlException
	}
}

A working oldschool solution without Dapper.Contrib:

public void WriteUnikatHistory( CU_UnikatHistory history )
{
	var sql = "INSERT INTO [dbo].[CU_UNIKATHISTORY] ([UNIKATSTART],[UNIKATCOUNT],[PRODUCT],[TI_STAMP],[NOTES])"
		+ " VALUES (@unikatstart, @unikatcount, @product, @ti_stamp, @notes);";

	using( var connection = new SqlConnection( csb.ConnectionString ) )
	{
		connection.Open();
		connection.Execute( sql, history );
	}
}

Example data for the history-object in json format for insertion:

{
  "Unikatstart": 1,
  "Unikatcount": 2,
  "Product": "string1",
  "TI_Stamp": "2022-07-28T08:44:18.705Z",
  "Notes": "string2"
}

As you might have noticed: I do supply a value for TI_Stamp.

When I debug the code, there is no null value being passed to connection.Insert():

Perhaps some conversion problems between .Net and SQL Datetime? Might it be related to #33?

Software used:

• Dapper: 2.0.123
• Dapper.Contrib: 2.0.78
• MS SQL: 14.0.3445.2
• Visual Studio 2022
• .NET Core 3.1
• Swashbuckle.AspNetCore: 6.4.0
• Swashbuckle.AspNetCore.Annotations: 6.4.0
• Swashbuckle.AspNetCore.SwaggerGen: 6.4.0

[PTwr]

I think you basically have same problem as I do here:
#141

Key columns are always excluded from list of updateable/insertable fields.

And you can't remove [Key] from TI_Stamp field because otherwise you'll get "Entity must have at least one [Key] or [ExplicitKey] property..." exception.

As far as I can tell problem is not related to any specific .NET framework version, nor type of SQL server/driver in use, and appears to be present for quite some time already.