Importing data to an AlwaysEncrypted database with BulkOperation.BulkInsert is failing on a TinyInt column

[AridTag]

Hi we are currently using Z.EntityFramework.Extensions v3.17.8 against SQLSERVER 2017 with many encrypted columns of varying types across many tables.

We have a data transport facility for moving data between clients that are not connected to the same database. This process involves pulling the tables down into a DataSet and converting that DataSet to XML (The data is in it's unencrypted form) Then that output file is encrypted. That XML file is then decrypted, loaded into a DataSet and we are attempting to use BulkOperation to insert the data from the tables in that dataset.

Everything works great until it hits a column of type TinyInt that is encrypted. (Up to this point say half a dozen encrypted columns of types (varchar, nvarchar) were successfully imported)

We were previously on version 3.14.something which I couldn't get to import ANY of the data, but upgrading to a more recent version 3.17.8 fixed that, but fails on the TinyInt.

I'm not certain that this is a problem with your library or not, but I haven't been able to spot an issue from my side. I don't have a reproducible sample project for you, but might be able to build one if it's essential to you.

SqlException: Operand type clash: tinyint is incompatible with tinyint encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'C2defaultCEK1', column_encryption_key_database_name = 'TestDb')
Statement(s) could not be prepared.

Any help on this would be greatly appreciated

[JonathanMagnan]

Hello @AridTag ,

Thank you for reporting.

We successfully reproduced a similar issue.

We will look at it.

Best Regards,

Jonathan

[JonathanMagnan]

Hello @AridTag ,

It looks that finally, we were not able to reproduce it. On our side, we had forgotten to add in the connection string the Column Encryption Setting=enabled part.

If you can create a project that reproduces it, that will be greatly appreciated.

Here is one of the basic test we made that is working fine on our side (we encrypt the column once the table is generated by Entity Framework).

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Windows.Forms;

namespace Z.EntityFramework.Extensions.Lab
{
    public partial class Form_Request_BulkSaveChanges_False : Form
    {
        public Form_Request_BulkSaveChanges_False()
        {
            InitializeComponent();

            // CLEAR
            {
                using (var ctx = new CurrentContext())
                {
                    ctx.EntitySimples.RemoveRange(ctx.EntitySimples);
                    ctx.BulkSaveChanges();
                }
            }

            // SEED
            {
            }

            // TEST
            {
                EntityFrameworkManager.BulkOperationBuilder = builder => builder.HasColumnEncrypted = true;

                using (var ctx = new CurrentContext())
                {
                    var list = new List<EntitySimple>();

                    for (byte i = 0; i < 3; i++)
                    {
                        list.Add(new EntitySimple() { ColumnInt = i, ColumnInt16 = i, ColumnInt8 = i});
                    }

                    ctx.EntitySimples.AddRange(list);

                    ctx.BulkSaveChanges();
                }

                using (var ctx = new CurrentContext())
                {
                    var list = ctx.EntitySimples.ToList();

                }
            }
        }

        public class CurrentContext : DbContext
        {
            public CurrentContext()
                : base("Server=localhost;Initial Catalog=Z.EntityFramework.Extensions.Lab;Integrated Security=true;Connection Timeout=180;MultipleActiveResultSets=True;Column Encryption Setting=enabled")
            {
            }

            public DbSet<EntitySimple> EntitySimples { get; set; }

            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Types().Configure(x => x.ToTable(GetType().DeclaringType != null ? GetType().DeclaringType.FullName.Replace(".", "_") + "_" + x.ClrType.Name : ""));

                base.OnModelCreating(modelBuilder);
            }
        }

        public class EntitySimple
        {
            public int ID { get; set; }
            public int ColumnInt { get; set; }

            public short ColumnInt16 { get; set; }

            public byte ColumnInt8 { get; set; }
        }
    }
}

[AridTag]

Dang that's unfortunate you had me hopeful haha. I will dig into this further when I get back in the office.

[AridTag]

What I originally reported appears to not be a problem at all. I don't know why it wasn't working for me before but is now. I started down the path of converting my code to work similarly to your example, actually using the DbContext and DbSets and ran into another issue that isn't making sense to me. I ended up taking a step back and went back to my original code and I'm not sure if I had forgotten to set one of these properties or what but this snippet DOES work

using (var bulkOp = new BulkOperation(connection))
{
    bulkOp.BatchSize = 500;
    bulkOp.BatchTimeout = 0;
    bulkOp.DestinationTableName = destinationTableName;
    bulkOp.InsertIfNotExists = true;
    bulkOp.InsertKeepIdentity = true;
    bulkOp.HasColumnEncrypted = true;
    bulkOp.BulkInsert(table); // table is a DataTable
}

This was my attempt at making my code work similarly to yours using the BulkOperationBuilder/DbContext/DbSet

var currentBuilder = EntityFrameworkManager.BulkOperationBuilder;
try
{
    EntityFrameworkManager.BulkOperationBuilder = builder =>
    {
        builder.InsertKeepIdentity = true;
        builder.HasColumnEncrypted = true;
    };
    var tests = new List<TestIndex>();
    foreach (DataRow row in table.Rows)
    {
        var test = new TestIndex();
        test.TestId = (Guid)row["TestId"];
        <.. snip many many more columns ..>
        tests.Add(test)
    }

    using (var ctx = _ContextFactory.Create())
    {
        ctx.TestIndex.AddRange(tests);
        ctx.BulkSaveChanges();
    }
}
finally
{
    EntityFrameworkManager.BulkOperationBuilder = currentBuilder;
}

However this is throwing an exception InvalidOperationException: Column 'TestId' does not allow DBNull.Value.

If I print out all the TestId properties for every TestIndex in the tests collection I end up with this output

tests.Select(t => t.TestId).ToList()
Count = 35
   [0]: {aa7329e3-5c60-e911-a824-00155d01ce22}
   [1]: {c7d1ee23-9e54-4125-ac21-03b6f7ef132d}
   [2]: {1b920dcc-022d-443e-b0f0-17df2711eedb}
   [3]: {9f376884-4b1f-4985-ba64-2763b7ca3808}
   [4]: {17a7c5bc-fd52-42f0-8948-2839a7909ff6}
   [5]: {5f9143b0-279c-4d06-bef2-37d19ae4252f}
   [6]: {6e604cf8-e59c-4b40-9277-3efeeea99b7f}
   [7]: {111fe9d0-0339-4375-8d73-3f12b1680da6}
   [8]: {33ea780e-a2a8-4eba-990d-41c7c7269262}
   [9]: {e17b2159-5573-4b22-98a8-43389b7b305d}
   [10]: {b4689e08-f95f-4cac-b220-44be7ef2a9e8}
   [11]: {7e6a77aa-5f57-4111-b5a4-4b0205d21e23}
   [12]: {cdf76126-f46b-4d97-8c31-5856914ea3e5}
   [13]: {1bf9ccd4-e9b2-456e-bb2f-597a69928a5d}
   [14]: {497c78fb-10bc-4587-ab52-7a96e41b7456}
   [15]: {942b50d3-d005-4053-b120-7aeeb8623aa4}
   [16]: {94accac9-9ff6-4ab0-9f1d-820970fbc0b1}
   [17]: {86bc4f6c-8108-4485-8f7e-82d89bcbced5}
   [18]: {804f9901-183e-4d79-875a-832398ed3475}
   [19]: {6c8f83f1-0294-49c7-8924-aa1fd220df9c}
   [20]: {ed13d357-dcff-4226-9966-b3cac1ab7129}
   [21]: {0e078a22-ecad-4d91-863c-ba6d018ff2a2}
   [22]: {89363407-533b-4d7b-8278-bf50b7773560}
   [23]: {976fa9aa-b622-41c3-a0cd-c43f217f07f2}
   [24]: {80fae700-0a6b-4065-97d5-c49c3121b978}
   [25]: {ff1f03aa-8e1d-47d0-bb6d-ce0b25660010}
   [26]: {94ffe6ae-df50-4804-b895-d089cd1a3025}
   [27]: {c7ed5d1a-d5b7-4d9b-85f1-d1d162c79f44}
   [28]: {f7ec28cf-973d-4fb6-834b-d45d48274897}
   [29]: {faad98c5-bffd-4a5e-9b97-d5df56dae6fc}
   [30]: {9e2e620f-399d-4280-b125-df0e1212f0a5}
   [31]: {8d1990cb-5682-4fcf-8e39-e5382d8018cb}
   [32]: {2c77d5b3-f23e-47da-96ad-f99c43d6f99b}
   [33]: {9c7c5a95-b695-4e15-8d64-fa0198fe00ac}
   [34]: {e7cad6a3-a5a7-40be-8dbc-fc31397de15d}

With InsertKeepIdentity being true I'm not sure what's going on here? However this is unrelated to my originally reported problem and not really an issue for me since I can go back to the first snippet in this comment which is preferable to me anyway

[JonathanMagnan]

Oh, great ;) So we will close internally the first issue and open another for this new issue.

We will try to look at this new issue on Wednesday.

Best Regards,

Jonathan

[JonathanMagnan]

Hello @AridTag ,

How to use EntityFrameworkManager.BulkOperationBuilder? This value must be set only once when the application starts. So only global options such as HasColumnEncrypted , BatchSize, Timeout should be specified here.

The InsertKeepIdentity option is more a case by case options and should probably not be specified here.

Why it doesn't work? Unfortunately, BulkSaveChanges() is incompatible with the option InsertKeepIdentity. The reason is that we take values from command generated by Entity Framework.

However, there are two possible solutions:

ctx.BulkSaveChanges(false); // more stuff will be generated by our library
ctx.BulkInsert(tests); // directly insert the list

When you specify BulkSaveChanges(false), less code will be generated by Entity Framework which allows to go faster and support more stuff. See more about it here: https://entityframework-extensions.net/improve-bulk-savechanges

Let me know if that answer to the second issue.

Best Regards,

Jonathan

[AridTag]

Hi Jonathan, that explanation is good enough for me. Thank you.