Click here to Skip to main content
15,742,120 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

SQL
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:

C#
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();
                }
                // Only update the changed columns - EF Core will detect 
                // changed columns by baselining against the query above
                // A copy constructor must be used. 
                // Setting databasePassenger 
                // to a new instance of Passenger disrupts EF Core's 
                // change tracking.
                databasePassenger.Copy(passenger);
                if (this.newEntry)
                {
                    repoContext.PassengerMaster.Add(databasePassenger);
                }
            }

            repoContext.SaveChanges();


This is an extract from the class being managed:

C#
#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...[^]
Posted
Updated 6-May-22 8:34am
v4

SQL's datetime type[^] can only hold values after 1st January 1753. This is since the Gregorian calendar was only adopted in September 1752[^], so earlier dates may not be accurate.

The .NET DateTime type, and SQL's newer datetime2 type, can hold any date from 1st January 0001 to 31st December 9999. If you try to save a DateTime value earlier than 1753-01-01 in a SQL datetime column, you will get this error. And behold, that is precisely what you are trying to do:
SQL
@p6='0001-01-01 00:00:00'

You either need to debug your code to find out why you are passing an out-of-range date to SQL, or change your SQL columns to datetime2.
 
Share this answer
 
Comments
Ger Hayden 4-May-22 7:51am    
Hi Richard, I've updated the question to describe what is happening in the debugger. I dont see a means to upload a screen shot to back it up. Right up to the point where the context executes SaveChanges, today's date is present, but sql profiler is illustrating that this value is not used.
This may be a wild goose chase but the problem could be related to change tracking.

If all fields are used when checking the existence of a row or while detecting the changes this could probably cause a situation where all non-null fields have the smallest possible default value (from the database point of view). This default value for the date field in the database would be something that is not convertible to C# type in this case.

To test this scenario, try disabling change detection.
 
Share this answer
 
Comments
Ger Hayden 4-May-22 16:42pm    
Now there's a thought. I will explore that in the morning.
This is very much a Workaround rather than a solution. When I populate this particular last activity date time in the client application rather than when I am in the process of saving it via the Entity Framework repository as posted, then the problem does not occur.

It does not solve the issue but it allows me to avoid it and move on.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900