I'm getting an exception on an EF Core save that is not responding to treatment.
Some of the exception text is:
ERROR 03-05-2022 06:33:09 An exception occurred in the database while saving changes for context type 'VT_LocalDataHandler.Models.Contexts.ApplicationDbContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
My model has various date fields - but only one of them is non null, and that is my problem field. I could work around it by making it
DateTime2, but while that would clear the exception, it would not solve the problem of it not saving it's assigned value. I cannot see why having assigned the value to the data row, it is not present when the row is attempting to save.
When I run in debug and stop execution after the new record is added to the context, the date is as I expect, but when the context is saved it comes out as 0001-01-01 which is not valid for
DateTime.
This is a dump from SQL Profiler:
exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [PassengerMaster] ([Person_ID], [Account_Balance], [Allow_Credit_Journeys],
[Allow_Credit_Sales], [Credit_Journeys], [Current_Ticket_Type], [DT_Last_Activity], [Date_Paid],
[End_Stage_ID], [Export], [FlagSettings], [Journeys_Issued], [Journeys_Used],
[Max_Credit_Journeys], [Orig_Person_ID], [Paid], [Receipt_Print], [Route_ID], [Stage_ID],
[Ticket_Expiry_Date], [Ticket_Issue_Date], [Ticket_Issue_No], [Ticket_Valid_From], [eTicket],
[mTicket])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15,
@p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24);
',N'@p0 int,@p1 nvarchar(4000),@p2 bit,@p3 bit,@p4 int,@p5 nvarchar(4000),@p6 datetime2(7),@p7
datetime2(7),@p8 smallint,@p9 int,@p10 int,@p11 smallint,@p12 smallint,@p13 smallint,@p14
int,@p15 nvarchar(4000),@p16 bit,@p17 smallint,@p18 smallint,@p19 datetime2(7),@p20
datetime2(7),@p21 int,@p22 datetime2(7),@p23 nvarchar(4000),@p24
nvarchar(4000)',@p0=87,@p1=NULL,@p2=0,@p3=0,@p4=NULL,@p5=NULL,
@p6='0001-01-01 00:00:00',
@p7=NULL,@p8=NULL,@p9=0,@p10=1,@p11=NULL,@p12=NULL,@p13=NULL,@p14=0,@p15=NULL,@p16=0,@p17=NULL,
@p18=NULL,@p19=NULL,@p20=NULL,@p21=NULL,@p22=NULL,@p23=NULL,@p24=NULL
When I change the value passed to
@p6 to a valid date, the statement executes as expected, so I am confident that this parameter is the issue. Further, if I am inserting two or more rows in the same call to
SaveChanges then, the date is correct in all entries except the last row. This suggests some sort of async type issue.
This is the code that is managing the save:
foreach (CPassengerMasterBase passenger in passengers)
{
if (passenger.p_FlagSettings ==
CPassengerMasterBase.m_FlagBits.NONE)
{
continue;
}
CPassengerMasterBase databasePassenger =
new CPassengerMasterBase();
databasePassenger = GetPassenger(passenger.p_Person_ID);
this.newEntry = false;
if (databasePassenger == null)
{
this.newEntry = true;
var now = DateTime.Now;
var date = new DateTime(now.Year, now.Month, now.Day,
now.Hour, now.Minute,
now.Second);
passenger.p_DateLastActivity = date;
databasePassenger = new CPassengerMasterBase();
}
databasePassenger.Copy(passenger);
if (this.newEntry)
{
repoContext.PassengerMaster.Add(databasePassenger);
}
}
repoContext.SaveChanges();
This is an extract from the class being managed:
#region PROTECTED VARIABLES
protected Int32 m_Person_ID;
protected Int32 m_Orig_Person_ID;
protected bool m_Allow_Credit_Journeys;
protected Nullable<short> m_Max_Credit_Journeys;
protected bool m_Allow_Credit_Sales;
protected bool m_Receipt_Print;
protected Nullable<short> m_Route_ID;
protected Nullable<short> m_Stage_ID;
protected Nullable<short> m_End_Stage_ID;
protected string m_Current_Ticket_Type;
protected string m_Ticket_Name;
protected DateTime ?m_Ticket_Issue_Date;
protected DateTime? m_Ticket_Expired_Date;
protected Nullable<short> m_Journeys_Issued;
protected Nullable<short> m_Journeys_Used;
protected DateTime? m_Ticket_Valid_From;
protected Nullable<bool> m_Paid;
protected DateTime? m_Date_Paid;
protected Int32 m_Export;
protected Nullable<decimal> m_Account_Balance;
protected Nullable<int> m_Credit_Journeys;
protected Nullable<int> m_Credit_Journey_Increment;
protected Nullable<int> m_Credit_Journey_Decrement;
protected Nullable<Int32> m_Ticket_Issue_No;
protected Nullable<bool> m_eTicket;
protected Nullable<bool> m_mTicket;
protected DateTime m_DateLastActivity;
#endregion
What I have tried:
I have tried extensive debugging. Recommendations like changing to
Datetime2 do not apply because the data assigned is not being written out on the query. While it would clear the exception, my database would have an incorrect entry. These are some of the sites I referenced:
C# - EF6 datetime2 conversion to datetime resulted in an out-of-range value - Stack Overflow[
^]
C# - How to fix the datetime2 out-of-range conversion error using DbContext and SetInitializer? - Stack Overflow[
^]
Conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value[
^]
C# - Conversion of a datetime2 data type to a datetime data type results out-of-range value - Stack Overflow[
^]
[SOLVED] => why i can't save the current DateTime.Now using Entity...[
^]
Submit an article or tip