BulkMerge Error on DateTime upgrading from net5 to net6

[VittorioMorellini]

1. Description

Error executing BulkMerge with EFcore 6, it did not happen with bulkmerge in dotnet5

Error condition: EFcore 6.0.5, Z.EntityFramework.Plus.EFCore 6.16.1
Updating entity with DateTime equal 01/01/0001 raise an overflow error, I did not initialize datetime, and it raise an error while executin bulkmerge.
Database Sql Server 14.0.1000169

It did not happen on EFcore 5 and Z.EntityFramework.Extensions.EFCore 5.9.1

2. Exception

sqldatetime overflow. must be between 1/1/1753 12:00:00 am and 12/31/9999 11:59:59 pm

System.Data.SqlTypes.SqlTypeException: 'SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

3. Fiddle or Project

//STATEMENT THAT RAISE AN ERROR
context.BulkMerge(new TEntity[] { entity });  This FAILS

THE ENTITY HAS THE datetime not initialized to value: 01/01/0001
and this raise the exception.
It does not happen with Ef5 and z.EntityFramework.extensions.EFCore: 5.9.1
 
//Instead The following statement, 
If I create an array with one item, the following statement works fine
This WORKS with option includeGraph = true, with false it fails:
 context.BulkMerge(entities, options => options.IncludeGraph = includeGraph);

4. Any further technical details

It happens the error while upgrading from EF core 5 and net5 :

TO

EF core 6.0.5 and net6, Plus.EFCore 6.16.1

Further technical details

• EF version: [EF Core v6.0.5]
• EF Plus version: [EF Plus Core v6.16.1]
• Database Server version: [SQL Server 14.0.1000.169]
• Database Provider version (NuGet): [Microsoft.Data.SqlClient v5.2.1]

[JonathanMagnan]

Hello @VittorioMorellini ,

Could you try if the issue also happens to the latest version of EFE for EF Core 6? https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/6.102.3

Do you think you could create a runnable project with the issue? It doesn’t need to be your project, just a new solution with the minimum code to reproduce the issue. You can send it in private here: [email protected]

Best Regards,

Jon

[VittorioMorellini]

Hi Jonathan, thank you for your answer.
I'm going to try Immediatly with the last version of Z.Extensions
I used:
https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/6.102.3

and the error is still happening.

To recreate the issue it is very easy
One table, a simple Entity with a date null that is set to 01/01/0001. I have a default date on DB for the date, null is not allowed.

and the error appears if I execute:
context.BulkMerge(new TEntity[] { entity });

But if I execute:

context.BulkMerge(new TEntity[] { entity }, options => options.IncludeGraph = true);
The statement is right and it complete without error
I am going to test with simple Unit test (xUnit) to reproduce the error
I have to try with dotnet8, maybe it is better

There is a difference between:
Z.EntityFramework.Extensions.EFCore
And
Z.EntityFramework.Plus.EFCore

Which do I have to use??? I usually use the second release

Best Regards
Vittorio

[JonathanMagnan]

Hello @VittorioMorellini ,

You can just use Z.EntityFramework.Plus.EFCore (commonly named EF Plus).

EF Plus already has a dependency on Z.EntityFramework.Extensions.EFCore.

So in your case, you can just upgrade to https://www.nuget.org/packages/Z.EntityFramework.Plus.EFCore/6.102.3 instead

Best Regards,

Jon

[JonathanMagnan]

Hello @VittorioMorellini ,

Indeed, there is some difference in the behavior in the v5.x (until a specific version) and v6.x

Before, the default value was skipped during the insert and update phase of the BulkMerge, but we fixed this problem to ensure we only skip property with a default value configuration during the insert phase.

The IncludeGraph also has a breaking change in the version x.100.0.0. Prior to this version, the update part was also always ignored for a default value but is no longer ignored.

I currently have a hard time explaining as this is the combination of both that makes it even worse to understand.

With the latest version, you should be able to get the same behavior by using either LegacyIncludeGraph or by ignoring this property with IgnoreOnMergeUpdate

Best Regards,

Jon

[VittorioMorellini]

Hi Jon,
thank you for the answer

I have problem during Insert, this solution can help me during Update statement but not during Insert statement completed with bulkMerge.
The Only solution I found is to wrap the statement in try and catch and to use the bulkinsert if we are in insert mode:

try
{
         ctx.BulkMerge(new TEntity[] { item }, options => options.IncludeGraph = includeGraph);
}
catch (Exception ex)
{
    if ((long)item.GetType().GetProperty("Id").GetValue(item) == 0)
    {
            ctx.BulkInsert(new TEntity[] { item }, options => options.IncludeGraph = includeGraph);
    }
    else
    {
           ctx.BulkUpdate(new TEntity[] { item }, options => options.IncludeGraph = includeGraph);
    }
}

Or the other solution is to initialize the datetime with a right value.
Vittorio

[VittorioMorellini]

I am thinking to keep the technical debit that I hace and Stay to release dotnet5 because I don't like this problem and I am not sure of what happen with this workaround I found

I am thinking to stay with EF core 5

and

Z.EntityFramework.Extensions.EFCore 5.1.9

Vittorio

[JonathanMagnan]

Hello @VittorioMorellini ,

If you can provide a prototype project (starting a new project) that contains only your example and the behavior you want, we could look at it and provide you with the right solution for your requirements. You can send it privately here: [email protected]

We can already reproduce this issue with the date, but I want to ensure my developer will give you the right answer to your exact scenario.

Best Regards,

Jon

[VittorioMorellini]

Hi Jon,
thank you for your availability.
The problem in only on datetime that have a default value and they are not nullable.
The default value is a datetime
I will work to give you a standard project with my Configuration and with some Unit Test.

I am going to work to a simple console application to reproduce the error

Vittorio