EF.Functions.Like support

[yoramda]

Is there support for doing LIKE on encrypted fields?

EF.Functions.Like

for example var x = await _context.Contact.Where(x => EF.Functions.Like(x.FirstName, "jon%")).ToListAsync();

[Eastrall]

I don't know how work the EF.Functions.Like library, but I believe that it should work with encrypted fields since the EntityFrameworkCore.DataEncryption decrypts the fields before querying.

[alirazazulfi]

HI when we can have this feature built in to your library. i m facing the issue when i apply the Like with % or contains. if you can solve this using Contains then it will also be very great

[Gibrid89]

I have same problem. It's can be fixed?

[Eastrall]

Hi @yoramda, @alirazazulfi, @Gibrid89, After doing some research, this scenario might not be possible to include in the library, let me explain: According to the documentation of EF.Functions.Like():

This DbFunction method has no in-memory implementation and will throw if the query switches to client-evaluation. This can happen if the query contains one or more expressions that could not be translated to the store.

This means, that the Like() method written as bellow:

context.Users.Where(x => EF.Functions.Like(x.Email, "%@%"));

will be executed on the database side, and generating the following query:

SELECT [u].[Email]
FROM [Users] AS [u]
WHERE [u].[Email] LIKE N'XvNDuGHMg++NHcsGNnKZEw=='

Note that the given pattern ('%@%') has also been encrypted into a base64 string to do the comparison on database side.

Let's assume that the Email is defined as an encrypted property in your EF model and stored as a Base64 string, the output of this request will give you an empty result, because the "actual value" of the Email field will probably be something like: SGVsbG8sIFdvcmxkIQ== therefor, applying a LIKE '%@% on this fields will generate an incorect SQL query and give you an empty result set.

[planeteleven0]

I did CLR integration with same algorithm that is used in EF, and manually decrypted in strored proc. side , but CLR integration it need to be done in .net framework