0

If I have the following query where Companies.Name is encrypted:

Obviously executing the following query is going to be invalid because we're searching against encrypted names.

IQueryable<File> files = GetFiles(f => f.Clients.Any(fc => fc.Contacts.Any(c => c.Companies.Any(x => x.Name.Contains(searchText)))));

I'm after a way to decrypt Companies.Name before the query is executed via a Decrypt() function we already have in place.

EDIT 1:

I do have a partial class Company with a decrypted property called DisplayName that returns the decrypted Name property, however LINQ is telling me the specified type member DisplayName is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

IQueryable<File> files = GetFiles(f => f.Clients.Any(fc => fc.Contacts.Any(c => c.Companies.Any(x => x.DisplayName.Contains(searchText)))));

If there's a way to use a partial class property inside a LINQ statement I guess it would work.

EDIT 2:

I ended up creating a SQL CLR function that decrypts the company names and I can now call dataContext.SearchCompanies(searchText) in code to return a list of Companies.

I'm not sure however, how to easily replace Companies within the original list?

gotnull
  • 26,454
  • 22
  • 137
  • 203
  • Do you have any decrypt function ? – Rajeev Kumar May 21 '13 at 05:52
  • Apologies. I forgot to mention we already have a `Decrypt()` function available. – gotnull May 21 '13 at 05:53
  • I think you need to decript it *before* you use it in your expression. – Jens Kloster May 21 '13 at 05:57
  • 2
    If you didn't have EF, and you used SQL, how would you do it? If the answer is "it's not possible", it's still not possible with EF-generated SQL. You'd need to load all the companies, have the figure out which contain the search text, and create a query that uses those companies' contacts. –  May 21 '13 at 06:00
  • I have a `MetadataType` as a partial class of `Company` with a property called `DisplayName` that decrypts the `Name` property, however LINQ is telling me the specified type member `DisplayName` is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. – gotnull May 21 '13 at 06:02
  • It looks like the answer got deleted that had a good idea of encrypting your search text before searching. This should be a viable solution if your encrypt/decrypt is not dependent on a company specific seed. – TyCobb May 21 '13 at 06:08
  • 2
    it was deleted, because author realised it wouldnt work :-) – phil soady May 21 '13 at 06:10
  • @TyCobb Well the `searchText` itself doesn't need to be decrypted. It's the `Companies.Name` data that's encrypted. – gotnull May 21 '13 at 06:10
  • personally i expect that you will be forced to do a full table scan especially since it is a contains pattern. I will watch in case EF provider can do more than i know. Instant tick if it can because I expect it cant. – phil soady May 21 '13 at 06:13
  • I understand that, but in your example, that is the only thing you are searching on. No clue how many companies you are working with, but perhaps you should cache your Company list with decrypted names and do your search against your cache first. Any results you find there should then be passed into your LINQ query with the encrypted names. It's the only way I see this working. – TyCobb May 21 '13 at 06:19

1 Answers1

0

You could define a stored procedure to perform the search:

create procedure SearchCompanies @searchText nvarchar(100)
as begin
    select * from Companies c  -- Implement your logic here...
    where dbo.Decrypt(c.Name) like @searchText
end

Then, add your stored procedure as a function import to the data context and get your data:

using(var dataContext = new DataContext())
{
    return dataContext.SearchCompanies(searchText);
}
RePierre
  • 9,358
  • 2
  • 20
  • 37