Entity Framework 6 使用 SQL Server 2016 Always Encrypted 的限制與解決方案

SQL Server Always Encrypted 可以保護我們的資料,但同時也帶來了一些不便,比如索引跟內建的預存無法使用,強制使用參數化查詢,這裡列出我已知的開發限制,下次碰到就可以直接避開

接續上篇,https://dotblogs.com.tw/yc421206/2019/05/18/ef6_connect_sql_server_2016_always_encrypted

上篇提到了要如何設定,這裡要談談開發上的限制

本文連結

 

開發環境

  • VS 2017
  • SQL Server 2016 Express Localdb

專案位置
https://github.com/yaochangyu/sample.dotblog/tree/master/ORM/EF6/Lab.EF6.AlwaysEncrypt

專案取出來之後,在 SQL Project  部署資料庫並把憑證匯入到 LocalMachine\My

資料結構

 

加密欄位

 

開發使用限制

查詢、異動需要使用參數化

在 SSMS 要異動、查詢資料就沒有辦法像以前那樣直接,必須要透過參數化的方式來處理

查詢

這是很常見的查詢語法,可是用了加密 Always Encrypted,就不能直接這樣寫了

USE [Lab.EF6.AlwaysEncrypt]
GO

SELECT
  e.Id
 ,e.Name
 ,e.Age
 ,e.CreateAt
 ,e.ModifyAt
 ,e.Bonus
 ,e.Birthday
 ,e.SequenceId
FROM dbo.Employee e
WHERE e.Name = '小章'
;
GO

Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'yao', column_encryption_key_database_name = 'Lab.EF6.AlwaysEncrypt') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

 

改用參數化

USE [Lab.EF6.AlwaysEncrypt]
GO
DECLARE @Name NVARCHAR(10) = '小章'
SELECT
  Id
 ,Name
 ,Age
 ,CreateAt
 ,ModifyAt
 ,Bonus
 ,Birthday
 ,SequenceId
FROM dbo.Employee
WHERE Name=@Name

 

新增

USE [Lab.EF6.AlwaysEncrypt]
DECLARE @Id UNIQUEIDENTIFIER = 'E8BECBF2-2AA4-446C-8E5C-FE01E1FBC91C'
       ,@Name NVARCHAR(10) = 'yao'
       ,@Age INT = 10
       ,@CreateAt DATETIME = GETDATE()
       ,@ModifyAt DATETIME = GETDATE()
       ,@Bonus NUMERIC(3, 1) = 20.0
       ,@Birthday DATE = GETDATE()
INSERT INTO dbo.Employee (Id,
Name,
Age,
CreateAt,
ModifyAt,
Bonus,
Birthday)
  VALUES (@Id, @Name, @Age, @CreateAt, @ModifyAt, @Bonus, @Birthday)

 

新增預存


CREATE PROCEDURE dbo.Insert_Employee @Id UNIQUEIDENTIFIER,
@Name NVARCHAR(10),
@Age INT,
@CreateAt DATETIME,
@ModifyAt DATETIME NULL,
@Bonus NUMERIC(3, 1) NULL,
@Birthday DATE NULL
AS
BEGIN
  IF EXISTS (SELECT
        *
      FROM dbo.Employee
      WHERE Id = @Id)
    UPDATE dbo.Employee
    SET Name = @Name
       ,Age = @Age
       ,CreateAt = @CreateAt
       ,ModifyAt = @ModifyAt
       ,Bonus = @Bonus
       ,Birthday = @Birthday

    WHERE Id = @Id
  ELSE
    INSERT INTO dbo.Employee (Id,
    Name,
    Age,
    CreateAt,
    ModifyAt,
    Bonus,
    Birthday)
      VALUES (@Id, @Name, @Age, @CreateAt, @ModifyAt, @Bonus, @Birthday)
END
GO

 

對於 EF ,異動資料原本就是參數化,影響不大,查詢欄位要拉出來放在變數裡面,這樣轉譯成 SQL 語法的時候就會變成參數化

[TestMethod]
public void 過濾使用參數()
{
    var name = "小章";
    using (var dbContext = new TestDbContext())
    {
        var employee = dbContext.Employees
                                .Where(p => p.Name == name)
                                .AsNoTracking()
                                .FirstOrDefault();
        Assert.AreEqual(name, employee.Name);
    }
}

 

無法使用排序

當該欄位的資料已經被加密了,不能排序也是理所當然的

USE [Lab.EF6.AlwaysEncrypt]
GO

SELECT
  e.Id
 ,e.Name
 ,e.Age
FROM Employee e
ORDER BY e.Name

Msg 33299, Level 16, State 2, Line 4
Encryption scheme mismatch for columns/variables 'Name'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'yao', column_encryption_key_database_name = 'Lab.EF6.AlwaysEncrypt') and the expression near line '2' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).

 

沒意外應該會得到上述錯誤,接著看 EF 

//無法使用SQL排序
using (var dbContext = new TestDbContext())
{
    var employees = dbContext.Employees.AsNoTracking().OrderBy(p => p.Name).ToList();
}

Test method Lab.EF6.AlwaysEncrypt.UnitTest.EF6_Solution.無法使用SQL排序 threw exception:
System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Encryption scheme mismatch for columns/variables 'Name'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'yao', column_encryption_key_database_name = 'Lab.EF6.AlwaysEncrypt') and the expression near line '3' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).

 

果然,跟 T-SQL 的錯誤一樣

 

硬是要做的話就全部撈回來再排序

[TestMethod]
public void 無法使用SQL排序()
{
    var expected = new[]
    {
        new {Name = "小章", Age = 18},
        new {Name = "小英", Age = 23},
        new {Name = "小明", Age = 33}
    };
    using (var dbContext = new TestDbContext())
    {
        dbContext.Configuration.LazyLoadingEnabled   = false;
        dbContext.Configuration.ProxyCreationEnaled = false;
 
        var employees = dbContext.Employees
                                 .AsNoTracking()
                                 .ToList()
                                 .OrderBy(p => p.Age)
            ;
        employees.Should().BeEquivalentTo(expected, option =>
                                                    {
                                                        option.WithStrictOrdering();
                                                        return option;
                                                    });
    }
}

 

無法使用分組

在 SSMS 試了一下沒有辦法針對單一欄位分組

USE [Lab.EF6.AlwaysEncrypt]
GO

SELECT
  e.Id
 ,e.Name
 ,e.Age
 ,e.CreateAt
FROM dbo.Employee e
GROUP BY e.Name

'dbo.Employee.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

EF 也是沒有辦法,也是一樣全撈後再分組

[TestMethod]
public void 無法使用SQL分組()
{
    var expected = new[]
    {
        new {Name = "小明", Age = 33},
        new {Name = "小英", Age = 23},
        new {Name = "小章", Age = 18}
    };
 
    using (var dbContext = new TestDbContext())
    {
        dbContext.Configuration.LazyLoadingEnabled   = false;
        dbContext.Configuration.ProxyCreationEnabled = false;
        var employeeGroups = dbContext.Employees
                                      .AsNoTracking()
                                      .ToList()
                                      .GroupBy(p => p.Age)
            ;
        var employee = employeeGroups.First().First();
 
        employee.Should().BeEquivalentTo(expected[0]);
    }
}

 

PK為加密欄位時,無法直接投影複數導覽屬性

using (var dbContext = new TestDbContext())
{
    var orders = dbContext.Employees.Select(e => e.Orders).ToList();
}

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Encryption scheme mismatch for columns/variables 'Employee_Id', 'Id'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'yao', column_encryption_key_database_name = 'Lab.EF6.AlwaysEncrypt') and the expression near line '2' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).

為什麼這樣不行??原來轉譯出來的 T-SQL 有 Order By

 

解法,撈出來再分組

以下這個方法是交集(Inner Join)

[TestMethod]
public void 無法直接投影集合_1()
{
    ////無法直接投影集合
    //using (var dbContext = new TestDbContext())
    //{
    //    var orders = dbContext.Employees.Select(p => p.Orders).AsNoTracking().ToList();
    //}
    var expected = new[]
    {
        new
        {
            Name = "小章", Age = 18, Orders = new[]
            {
                new {Price = (decimal) 20.00, ProductName = "滑鼠"},
                new {Price = (decimal) 18.00, ProductName = "鍵盤"}
            }
        }
    };
 
    using (var dbContext = new TestDbContext())
    {
        dbContext.Configuration.LazyLoadingEnabled = false;
        dbContext.Configuration.ProxyCreationEnabled = false;
        var employees = dbContext.Employees
                                 .SelectMany(o => o.Orders, (employee, order) => new
                                 {
                                     employee.Id,
                                     employee.Age,
                                     employee.Name,
                                     Order = new { order.Id, order.Price, order.ProductName }
                                 })
                                 .AsNoTracking()
                                 .ToList()
            ;
 
        var group = employees.GroupBy(e => new { e.Id, e.Name, e.Age },
                                      e => e.Order,
                                      (e, o) => new { e.Id, e.Name, e.Age, Orders = o })
                             .ToList()
            ;
 
        group.Should()
             .BeEquivalentTo(expected, option =>
                                                    {
                                                        option.WithStrictOrdering();
                                                        return option;
                                                    });
 
    }
}

 

自己寫 Outer Join,我已經懶得寫比對了

[TestMethod]
public void 無法直接投影集合2()
{
    using (var dbContext = new TestDbContext())
    {
        var employees = (from employee in dbContext.Employees
                         join order in dbContext.Orders on employee.Id equals order.Employee_Id into orders
                         from order in orders.DefaultIfEmpty()
                         select new
                         {
                             employee.Id,
                             employee.Name,
                             employee.Age,
                             Order = order == null
                                         ? null
                                         : new
                                         {
                                             order.Id,
                                             order.Price,
                                             order.ProductName
                                         }
                         }).ToList();
 
        var result = new Dictionary<Guid, EmployeeViewModel>();
        foreach (var element in employees)
        {
            var employee = new EmployeeViewModel
            {
                Id = element.Id,
                Name = element.Name,
                Age = element.Age.Value
            };
            OrderViewModel order = null;
            if (element.Order != null)
            {
                order = new OrderViewModel
                {
                    Id = element.Order.Id,
                    ProductName = element.Order.ProductName
                };
            }
 
            if (!result.ContainsKey(element.Id))
            {
                result.Add(element.Id, employee);
            }
 
            if (order != null)
            {
                result[element.Id].Orders.Add(order);
            }
        }
    }
}

 

無法自訂對應欄位

自訂對應欄位用在加密欄位會噴例外,估計是內部還沒有解密就進行了判斷

using (var dbContext = new TestDbContext())
{
    var employees = dbContext.Employees
                             .Select(p => new ViewModel.Employee
                             {
                                 Id   = p.Id,
                                 Name = p.Name == "小章" ? "yao" : p.Name,
                                 Age  = p.Age  == null ? 0 : p.Age
                             })
                             .AsNoTracking()
                             .ToList()
        ;
}

Test method Lab.EF6.AlwaysEncrypt.UnitTest.EF6_Solution.無法使用對應 threw exception:
System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The data types nvarchar and nvarchar(10) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'yao', column_encryption_key_database_name = 'Lab.EF6.AlwaysEncrypt') are incompatible in the equal to operator

 

沒有辦法像以前那樣直接把對應結果寫在 Select 區段了,要分兩段寫,必須要解密後再另外投影(Select)

以下是使用匿名型別的方式對應

[TestMethod]
public void 無法使用自訂對應_1()
{
    var expected = new[]
    {
        new {Name = "小明", Age  = 33},
        new {Name = "小英", Age  = 23},
        new {Name = "yao", Age = 18},
        new {Name = "小歪", Age = 0}
    };
  
    using (var dbContext = new TestDbContext())
    {
        var employees = dbContext.Employees
                                 .Select(p => new
                                 {
                                     p.Id,
                                     p.Name,
                                     p.Age
                                 })
                                 .AsNoTracking()
                                 .ToList()
                                 .Select(p => new
                                 {
                                     p.Id,
                                     Name = p.Name == "小章" ? "yao" : p.Name,
                                     Age  = p.Age  == null ? 0 : p.Age
                                 })
            ;
        employees.Should().BeEquivalentTo(expected);
    }
}

 

具名型別的對應

[Required]
[StringLength(10)]
public string Name
{
    get
    {
        if (this._name == "小章")
        {
            this._name = "yao";
        }
        return this._name;
    }
    set => this._name = value;
}

 

[TestMethod]
public void 無法使用自訂對應_2()
{
    var expected = new[]
    {
        new {Name = "小明", Age  = 33},
        new {Name = "小英", Age  = 23},
        new {Name = "yao", Age = 18}
    };
 
    using (var dbContext = new TestDbContext())
    {
        var employees = dbContext.Employees
                                 .Select(p => new
                                 {
                                     p.Id,
                                     p.Name,
                                     p.Age
                                 })
                                 .AsNoTracking()
                                 .ToList()
                                 .Select(p => new ViewModel.Employee
                                 {
                                     Id   = p.Id,
                                     Name = p.Name,
                                     Age  = p.Age
                                 })
            ;
        employees.Should().BeEquivalentTo(expected);
    }
}

 

若有謬誤,煩請告知,新手發帖請多包涵


Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET

Image result for microsoft+mvp+logo