0

I've recently started to use Always Encrypted with SQL Server 2016 to encrypt sensitive information.

For the most part, everything has been plain sailing with regards to seamless transition.

One thing has cropped up though, the following error -

Operand type clash: nvarchar(255) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_AutoK2', column_encryption_key_database_name = 'hrsys') is incompatible with nvarchar Statement(s) could not be prepared.

The method that is throwing this exception is the following - 

public ActionResult GetStaffList(string term)
    {

        var staffs = from e in db.Staffs
                        where e.FirstName.Contains(term) || e.LastName.Contains(term)
                        && e.EmploymentStatus == "Active"
                        select new
                        {
                            label = e.FirstName + " " + e.LastName,
                            id = e.Id
                        };


        return (Json(staffs, JsonRequestBehavior.AllowGet));
    }

But, if I modify the method, to this- 

public ActionResult GetStaffList(string term)
        {

            var staffSearch= db.Staffs
                             .ToList()
                             .Where(e => e.FirstName.Contains(term) || e.LastName.Contains(term))
                             .Where(e => e.EmploymentStatus == "Active");

            var staffs = from e in staffSearch
                            select new
                            {
                                label = e.FirstName + " " + e.LastName,
                                id = e.Id
                            };


            return (Json(staffs, JsonRequestBehavior.AllowGet));
        }

It doesn't throw an error.

Is there anyway of consolidating the two 'query' variables to a single one, making sure that the data is returned as JSON with the following block - 

select new
                            {
                                label = e.FirstName + " " + e.LastName,
                                id = e.Id
                            }

Also what I cant get my head around is that when querying with 'from e .....' it throws an error, but when querying with 'db.Staff ...' it doesn't.

SK2017
  • 753
  • 9
  • 38
  • I guess it's ToList which makes it "work", not syntax you are using. – Evk Apr 13 '18 at 11:11
  • `.ToList()` materializes the entities and switches to LINQ to Objects, where you no longer have to bother about encryption. Without it, LINQ will forward the `WHERE x LIKE '..'` predicate as SQL, which will not work, as encrypted columns do not support `LIKE` (or however `Contains` is translated, exactly -- might be `PATINDEX`). The use of separate variables is not required, but removing the `.Contains` part from the LINQ to EF bit is. (The `EmploymentStatus` filter can stay.) – Jeroen Mostert Apr 13 '18 at 12:02

0 Answers0