BulkInsert Using Default values

[talkwaqar]

We are using database first approach with entity framework core. We found one bug that if database column has default value and when we do bulk insert library do not save values instead use database column default values.

[JonathanMagnan]

Hello Waqar,

Thank for letting us know. We will investigate this issue and fix it by adding an option (We cannot change the default behavior to stay backward compatible).

Could you let me know the provider you are using? (ex: SQL Server).

Best Regards,

Jonathan

[talkwaqar]

We are using SQL Server yes.

[JonathanMagnan]

Hello @talkwaqar ,

Just to let you know that this request will take us more time than estimated.

We successfully implemented our first solution, however, we find out that this solution had some side impact. So obviously we cannot use it.

We are currently continuing to investigate it to find a working solution.

Best Regards,

Jonathan

[talkwaqar]

@JonathanMagnan is this fixed in latest release? https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/2.1.45

[JonathanMagnan]

Hello @talkwaqar ,

We tried some solutions before the holiday break but nothing worked since our code either limit us or SQL Server syntax.

Our library is optimized for "batching" entities and currently handling this scenario with database default value require a different logic by entity since sometime we need to insert in a column and sometime not.

Currently, the only way found on our side is to create different list depending on value and recall the BulkInsert or BulkMerge. For example, a list that require to insert in the field A and a list that keep the default database value for the field A.

[JonathanMagnan]

Hello @talkwaqar ,

We will close this issue. As said, this options is very hard to support for "Batching" entities.

However, we will keep this request open in our internal issue tracker.

It might be perhaps easier to fix as our product evolve.

Best Regards,

Jonathan

[DeepSkyJees]

@JonathanMagnan is this fixed in latest release?

[JonathanMagnan]

Hello @NigelYu ,

No

[kwasak]

I'm interested in this as well. We have a lot of default values in our database design. It would be great if there was an option to override the default value when inserting.

[JonathanMagnan]

Hello all,

I re-opened this issue since we are currently working on a version that will allow it. I will update this issue as soon as it's ready

[kwasak]

Sounds great, that’s good to hear!

[fcleto]

I've been going crazy for a month to understand the reason for the problem, and now I find that the bulk insert ignores the value to insert because a default value is defined.

I hope a fix release will come soon

p.s. ...I am new licensed customer

[patcor]

Hello all,

I re-opened this issue since we are currently working on a version that will allow it. I will update this issue as soon as it's ready

Any ETA on this?

[JonathanMagnan]

Hello all,

At this moment, the BulkInsert alone doesn't support it yet.

However, the BulkInsert + IncludeGraph = true should be now supported in the latest version. (A different strategy is used, that's why).

We have very high hope that all cases for the BulkInsert will be supported very soon.

If you have the chance to try it with the IncludeGraph = true, just let us know if something is not working correctly.

[Zarkos69]

Hello,

I have tried this, but got the exception:

'Oops! An error with IncludeGraph occurred, see the InnerException. If you use Proxy entities, the option UnsafeMode must be turned on (options.UnsafeMode = true). Contact our support team for more information: info@zzzprojects.com'

That's because I set a custom connectionstring when creating a new Entities, I don't use the connectionstring in the config file. When turning UnsafeMode ON I directly get the error for 'No connection string named ....'

[skycelik]

Hi,

I tried like the following but still the fields that has default value ignored. context.Details.BulkInsert(details.Values, opt => opt.IncludeGraph = true);

Is there any news about this issue.

[JonathanMagnan]

Hello @skycelik ,

Which version of EF are you using? (ex: EF6, EF Core 3.x, EF Core 5.x)

[skycelik]

Hello @JonathanMagnan ,

EF Core 5.x

[JonathanMagnan]

Hello @skycelik ,

We tried with IncludeGraph = true and everything seems to works fine for EF Core 5.x

All SQL was generated correctly in the same way SaveChanges was saving default value.

Is it possible for you to provide a runnable project with only this issue? Perhaps we are missing something else.

You can send it in private here if needed: info@zzzprojects.com

[skycelik]

Hello @JonathanMagnan ,

Unfortinately i can not send the project because it is a big and confidential. I have to extract related code from it and prepare a sample project to send it. But i will retest, may be i did some mistakes. If i see same result, i will prepare a sample project in my first suitable time and send it to you.

Actually i only use BulkInsert in Code first migration and seed operation. There is a json file and it includes 80000 records for initial data for test environment. The data includes three boolean fields. Actually i wasn't aware the issue and i tought everything is ok. But last week i realized all the three fields in database only contains default values. So i tried to find the missing part to fix this issue. I tried with IncludeGraph = true but the result was not change. For quick fix i commented the HasDefaultValue parts for related entity in the DbContext and it worked.

As i said before. i will retest again to be sure. After testing done, i will write here the result.

[skycelik]

Hello @JonathanMagnan ,

I did retest and got the same result. So i tried to prepare a sample project. But when i preparing the project i realized that this issue is not related with you. So accept my apologies.

The issue is related with the EF Core itself. But i don't know if it is a bug or by design. If a boolean property has default value as true (HasDefaultValue(true)) and the property value is false at the insert time, the property returns to true after SaveChanges. I'm sure it is not related with Z.EntityFrameworkCore BulkInsert because i removed the library reference and deleted all the bulk insert codes. Just added 10 entity to DbSet and called SaveChanges.

[JonathanMagnan]

No need to apologize ;)

Try to make your property nullable such as bool?, I believe it might fix your problem.

EF Core probably considers false as no value has been set (since false is the default value), so it insert true.

However, if your property is nullable, null will be the default value. So when you will explicitly set it to false, then false will be inserted.

Best Regards,

Jon

[skycelik]

Actually i thought this too, but the interesting part is; when i remove the .HasDefaultValue(true) for property from the context, everything is ok and EF Core sends the property values to the Sql with either true or false values.

So, probably i will report bug to the EF Core team.

Thanks for your helps Jhon,

Best Regards.

[reginald81]

Issue persist Today on Bulk Merge

[GimmieAUserName]

Any update on this issue (without using the IncludeGraph option) as we have just hit it with EF Core 6?.

Thanks

[JonathanMagnan]

Hello @GimmieAUserName ,

In most recent version, you should be able to avoid this issue by using the following options ForceValueGeneratedStrategy == ValueGeneratedStrategyType.OnAdd (We need to rename this option since it's really not obvious) or ForceValueGeneratedStrategy == ValueGeneratedStrategyType.OnAddOrUpdate (for BulkUpdate)

It will force our library to take the value from your entity instead of using the database default value.

Best Regards,

Jon

[GimmieAUserName]

Hello @GimmieAUserName ,

In most recent version, you should be able to avoid this issue by using the following options ForceValueGeneratedStrategy == ValueGeneratedStrategyType.OnAdd (We need to rename this option since it's really not obvious) or ForceValueGeneratedStrategy == ValueGeneratedStrategyType.OnAddOrUpdate (for BulkUpdate)

It will force our library to take the value from your entity instead of using the database default value.

Best Regards,

Jon

Will give it a try, thanks

[BEagle1984]

In most recent version, you should be able to avoid this issue by using the following options ForceValueGeneratedStrategy == ValueGeneratedStrategyType.OnAdd (We need to rename this option since it's really not obvious) or ForceValueGeneratedStrategy == ValueGeneratedStrategyType.OnAddOrUpdate (for BulkUpdate)

I had the same issue and I can confirm that setting ForceValueGeneratedStrategy solves it (currently testing with SQLite, if it matters at all). :+1:

Since I wasted quite a bit of time until I found this issue, it's maybe a good idea to add an hint in your website. In my opinion, you could also invest a bit more into writing meaningful summaries. Gets or sets the force value generated strategy (Might not work in some scenario with default value). and no summary at all on the enumeration doesn't make it very easy to discover.

[VittorioMorellini]

Hello @GimmieAUserName ,

In most recent version, you should be able to avoid this issue by using the following options ForceValueGeneratedStrategy == ValueGeneratedStrategyType.OnAdd (We need to rename this option since it's really not obvious) or ForceValueGeneratedStrategy == ValueGeneratedStrategyType.OnAddOrUpdate (for BulkUpdate)

It will force our library to take the value from your entity instead of using the database default value.

Best Regards,

Jon

Where do I have to set this option? I have the same problem on net5 and it is solved by using: includeGraph = true

in the single instructions If I want to set the option: ForceValueGeneratedStrategy == ValueGeneratedStrategyType.OnAdd where do I have to set it? I am not able to see this option nor in BulkInsert neither in BulkMerge

Best Regards Vittorio

[psham-arch]

I have used these options as explained but found out this option forces a one-way street. It is either using the value in code or using the value defined in SQL default constraint. I cannot have it both ways. I have a big table with many columns with defaults, and I only want to deal with the columns I need. I use the database-first approach and use scaffolding to generate my code. So if I use this ForceValueGeneratedStrategy option, I have to either comment out the HasDefaultValueSql in the generated DbContext or make sure I fill all columns with the proper default. Is this issue still being investigated? Do we have a better solution?

[JonathanMagnan]

Hello @psham-arch ,

At this moment, we indeed don't support mixed scenarios as described in the ForceValueGeneratedStrategy - Mixed Scenario

You have to handle it on your side by performing 2 different bulk operations.

However, we hope to solve this issue during the summer. We already created a lot of code, which was a prerequisite to making it happen. This issue is reported so often, so that's something we would like to fix.

Best Regards,

Jon

[psham-arch]

Hello Jon,

Thank you for explaining. I have no doubt that this is a piece of very complex code. I appreciate you and your team’s dedication to your product.

Regards,

Peter Sham Arch Capital Services LLC. @.**@.> | www.archgroup.comhttp://www.archgroup.com Direct: (914) 517-3069

From: Jonathan Magnan @.> Sent: Sunday, April 14, 2024 9:41 PM To: zzzprojects/EntityFramework-Extensions @.> Cc: Sham, Peter @.>; Mention @.> Subject: Re: [zzzprojects/EntityFramework-Extensions] BulkInsert Using Default values (#173)

CAUTION: External email.

Hello @psham-archhttps://github.com/psham-arch ,

At this moment, we indeed don't support mixed scenarios as described in the ForceValueGeneratedStrategy - Mixed Scenariohttps://entityframework-extensions.net/forcevaluegeneratedstrategy#mixed-scenario

You have to handle it on your side by performing 2 different bulk operations.

However, we hope to solve this issue during the summer. We already created a lot of code, which was a prerequisite to making it happen. This issue is reported so often, so that's something we would like to fix.

Best Regards,

Jon

— Reply to this email directly, view it on GitHubhttps://github.com/zzzprojects/EntityFramework-Extensions/issues/173#issuecomment-2054288959, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AQLEONZMJRJVQZNRJ3QM6QDY5MVZRAVCNFSM4GKNPRMKU5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBVGQZDQOBZGU4Q. You are receiving this because you were mentioned.Message ID: @.**@.>>

---

The information contained in this e-mail message may be privileged and confidential information and is intended only for the use of the individual and/or entity identified in the alias address of this message. If the reader of this message is not the intended recipient, or an employee or agent responsible to deliver it to the intended recipient, you are hereby requested not to distribute or copy this communication.

If you have received this communication in error, please notify us immediately by telephone or return e-mail and delete the original message from your system.

---

[james-juju]

Hello,

I have tried this, but got the exception:

'Oops! An error with IncludeGraph occurred, see the InnerException. If you use Proxy entities, the option UnsafeMode must be turned on (options.UnsafeMode = true). Contact our support team for more information: info@zzzprojects.com'

That's because I set a custom connectionstring when creating a new Entities, I don't use the connectionstring in the config file. When turning UnsafeMode ON I directly get the error for 'No connection string named ....'

Hi, were you able to solve this error? I have the same problem.

[JonathanMagnan]

Hello @james-juju ,

Sorry for the delay, we just noticed your reply.

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: info@zzzprojects.com

When turning the UnsafeMode = true, it allows our library to go further and show the reel error No connection string named, which could be caused by the ContextFactory depending on how you set it.

We are not sure why you get the error at this moment on your side, but surely having a prototype project will help us to discover the reason.

Best Regards,

Jon