BulkSaveChanges inconsistent behavior when saving value that exceeds max field length

[ibootbg]

Description

BulkSaveChanges behaves inconsistently depending on the number of updated records. When I set a value that exceeds the field's size I expect BulkSaveChanges to fail. This is consistent with SaveChanges. However if I only change a few records the update succeeds and the values in the table are truncated. When I update many records (e.g. 1000) the update correctly fails. Please see this example.

Further technical details

• EF version: 6.4.0
• EF Extensions version: 4.0.69
• Database Provider: SQL Server

[JonathanMagnan]

Hello @ibootbg ,

You are 100% right.

This behavior is depending on if a SqlCommand or SqlBulkCopy is executed. The SqlCommand auto truncate varchar while the SqlBulkCopy will throw an error.

It is possible to always truncate using the options.AutoTruncate = true options.

Unfortunately, for backward compatibility reason, we never fixed this issue (and we don't know which one should be the default behavior either!)

Let me know if that answers correctly to your question.

Best Regards,

Jon

[ibootbg]

Hi Jon,
My problem is that BulkSaveChanges truncates the string and I couldn't find a way to disable this behavior. Setting options.AutoTruncate = false did not help. Do you have any other suggestions?

[JonathanMagnan]

My problem is that BulkSaveChanges truncates the string

You would like it to throw an error? I'm correct?

[ibootbg]

I would like it to not truncate the string. And then when it executes the SQL command, the DB will raise an error. This is how SaveChanges behaves.
Here's the SQL from SaveChanges:

exec sp_executesql N'UPDATE [dbo].[Contacts]
SET [Address] = @0
WHERE ([ContactId] = @1)
',N'@0 nvarchar(max) ,@1 int',@0=N'AddressMax10AddressMax20AddressMax30AddressMax40AddressMax50',@1=1001

And here's the SQL from BulkSaveChanges:

exec sp_executesql N'MERGE INTO [dbo].[Contacts]  AS DestinationTable
USING
(
SELECT TOP 100 PERCENT * FROM (SELECT @0_0 AS [ContactId], @0_1 AS [Address], @0_2 AS ZZZ_Index) AS StagingTable ORDER BY ZZZ_Index
) AS StagingTable
ON DestinationTable.[ContactId] = StagingTable.[ContactId]
WHEN MATCHED   THEN
    UPDATE
    SET     [Address] = StagingTable.[Address]
;',N'@0_0 int,@0_1 varchar(50),@0_2 int',@0_0=1001,@0_1='AddressMax10AddressMax20AddressMax30AddressMax40Ad',@0_2=0

SaveChanges sends the entire string while BulkSaveChanges truncates it to 50 characters.
You mentioned that when BulkCopy is used the string will not be truncated. Is there a way to force BulkSaveChanges to use BulkCopy? And what will be the performance impact?

[christophpre]

@JonathanMagnan Any updates on this?

We are facing the same issue and would like to have the option to always throw an error, if the string size exceeds the column length. Regardless of SqlCommand or SqlBulkCopy being used internally.

[rizi]

@JonathanMagnan Any updates on this?

We are facing the same issue and would like to have the option to always throw an error, if the string size exceeds the column length. Regardless of SqlCommand or SqlBulkCopy being used internally.

+1 for an option to configure this behaviour.
Br

[JonathanMagnan]

Hello @christophpre , @rizi ,

We are currently looking at it with option (we need to keep the default behavior for backward compatibility).

The major difference between SaveChanges and our library is that we specify the varchar size while SaveChanges use varchar(max) which is why the full string is passed and the SQL error is thrown. So when that option will be enabled, for all kinds of char/varchar types, we will no longer specify the size.

Best Regards,

Jon

[rizi]

Hello @christophpre , @rizi ,

We are currently looking at it with option (we need to keep the default behavior for backward compatibility).

The major difference between SaveChanges and our library is that we specify the varchar size while SaveChanges use varchar(max) which is why the full string is passed and the SQL error is thrown. So when that option will be enabled, for all kinds of char/varchar types, we will no longer specify the size.

Best Regards,

Jon

Thank you very much !

[JonathanMagnan]

Hello all,

The v6.13.10 has been released with a fix for the AutoTruncate option.

The default value for AutoTruncate is null (no value). so when nothing is specified:

• The SqlCommand will always work
• The SqlBulkCopy will throw an error when the length is greater than the one in the database

When AutoTruncate = true:

• If the length is invalid, no error will be thrown (SqlCommand and SqlBulkCopy). The value is automatically truncated.

When AutoTruncate = false:

• If the length is invalid, an error should always be thrown (SqlCommand and SqlBulkCopy)

As said, we currently cannot change the current behavior for backward compatibility. But you can choose which kind of behavior you want.

You can always change the current behavior by default for all operations:

EntityFrameworkManager.BulkOperationBuilder = builder => AutoTruncate = false;

Let me know if that option solves everything on this issue.

Best Regards,

Jon

[d-57u3ndl]

Hello @JonathanMagnan,

thanks for the quick implementation.

The AutoTruncate option behaves as described and solves my problem!

[JonathanMagnan]

Great to hear!

Don't hesitate to contact us if you need further assistance.

BEst regards,

Jon