Linqdatasource filter post database retrieval

Question

Thanks for any insights you can help with!

Very simply, I'm calling a set of Contacts from the Database using LinqToSql. Two of those columns "FirstName" and "LastName" are encrypted.

I decrypt them on the fly as you see in the included code. However, I also want to filter based on LastName. The problem is if I do a compare in the statement, it's comparing the encrypted value to the text value.

I need to somehow get the resulting data for my gridview, then filter it after the fact, not based on the database values again, but with the data (and decrypted data) that I already have.

Ideas?? Thanks!

    protected void ContactsLDS_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
    DatabaseDataContext db = new DatabaseDataContext();

    MyAES aes = new MyAES();

    var v1 = from s in db.Contacts
             select new Contact()
             {
                 ContactId = s.ContactId,
                 FirstName = (s.FirstName == null ? "" : aes.DecryptString(s.FirstName)),
                 LastName = (s.LastName == null ? "" : aes.DecryptString(s.LastName)),
             };

    e.Result = v1;
}

Can you reverse things and encrypt the LastName you want to compare to instead of decrypting and then comparing to the plain text one? something like .Where(w => w.LastName == aes.EncryptString(LastNameToTestFor); — nycdan, Sep 01 '11 at 03:56
ahh - good thinking, except then I'd need to do exact match searching. I need to able to use 'starts-with', to filter based on first letter of name, etc. thanks for the thought! — localman, Sep 04 '11 at 05:06

score 1 · Accepted Answer · edited May 23 '17 at 12:12

These two posts helped me answer this:

Linq "Could not translate expression... into SQL and could not treat it as a local expression."

Gridview using a generic list as DataSource and Auto-generating columns

I just ended up grabbing the data in encrypted format to start with, then running a second query over it and decrypting it. Finally, when I wanted to filter the data, I'd run a 3rd over, running the "where str.startswith()".

A bit of extra processing, but get's the job done.

Linqdatasource filter post database retrieval

1 Answers1