C#DateTime到EF Core 3.1 DateTime2的转换会随机删除日期和时间之间的空间

I have a small console application which is to import a CSV file into a database. It is in .NET CORE 3.1. The CSV file gets imported without any issues. The issue arises with trying to save the data to the table. The error being received is "String or binary data would be truncated. The statement has been terminated." This is received during the context.SaveChanges() call. To determine exactly what the error is, loaded up Profiler to see the offending statement. The offending piece of code was related to a field that holds a date and time. To start from the beginning and lead up to the issue.

导入的数据在一列中,表示如下:

"20200404121655500" 
"20200404121755500"

导入模型属性定义如下:

public string Date_And_Time { get; set; }

数据模型属性定义如下:

[Column(TypeName = "DATETIME2(7)")]
public DateTime? Date_And_Time { get; set; }

用于将导入的字符串获取到数据模型字段的转换如下:

if (DateTime.TryParseExact(Date_And_Time.Trim()
.PadRight(21, '0')
.Substring(0, 21), "yyyyMMddHHmmssFFFFFFF", CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime newDateTime)) 
{ model.Date_And_Time = newDateTime; }
else { model.Date_And_Time = null; }

While debugging when the 2 different dates are processed then are set in the model.Date_And_Time as expected. The object expands producing all the correct information (year, day, hour, etc.). Prior to executing SaveChanges, inspecting the objects shows they both have correct DateTime objects. However upon executing that command throws the above SQL exception. When inspecting the merge command created by EF the dates are differently formatted. They are as follows:

'2020-04-0412:16:55.5000000' and '2020-04-04 12:17:55.5000000'

The first does not have a space between the date and time where as the second has a space. The first is what is causing the exception. When doing the SaveChanges there are on average 20 records being saved and some have the space and some do not there is no pattern that I can find. I have tried using the exact same date time and that also has some with and some without space.

评论