Closed yoramda closed 1 year ago
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.
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
I have same problem. It's can be fixed?
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.
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
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();