2

I am querying Entity Framework using AsQueryable as I don't want it to go into memory yet as there are a lot of records that it will bring back. But I need to decrypt certain values when selecting the values into an object.

If I were to use ToList(), the code first then decrypt it after in a foreach loop, it works - but it is slow.

Here is some of my code:

var myList = db.Accounts.Include("ProfileDetails").AsNoTracking().Where(x => !x.IsDeleted &&  x.ProfileDetails.IC_Garage.Where(u=>u.Id == garage.Id).Any())
                    .Select(a => new { a.Id, a.RoleId, a.ProfileId, a.Email })
                    .AsQueryable()
                    .Join(db.Profiles
                    .Select(p => new { p.Id, p.FirstName, p.LastName, p.IC_Units, p.EmergencyContact1, p.EmergencyContact2, p.IC_TelephoneBook, p.Salt, p.NextOfKin })
                    .AsQueryable(), profile => profile.ProfileId, userProfile => userProfile.Id, (profile, userProfile) => new { profile, userProfile })
                             .Select(s => new MyGarageList
                             {
                                 EmergencyContact1 = s.userProfile.EmergencyContact1, Salt = s.userProfile.Salt
            }).ToList();

This is the bit I don't really want to do as it slows the process down

List<MyGarageList> filteredGarages = new List<MyGarageList>();

foreach (MyGarageList garage in myList)
{
    string emrgName1 = !string.IsNullOrEmpty(garage.EmergencyContact1) ? EL.DecryptText(garage.EmergencyContact1, garage.Salt, WebsiteKey) : "";
    MyGarageList newMyList = new MyGarageList()
            {
                EmergencyContact1 = emrgName1
            };
    filteredGarages.Add(newMyList);
}

Any ideas on how I can speed up the process? Can I decrypt the values in the first call?

Jon

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jonathan Roberts
  • 43
  • 4
  • 1
    All EF does is translate your code into SQL, so it will not know how to translate the `DecryptText` method. So you'd have to add that logic to your DB (maybe via a function and then a view you can query). Also you don't need all those `AsQueryable` calls as you should already be dealing with an `IQueryable` in the first place. – juharr Oct 25 '18 at 14:48
  • You can't (easily) avoid pulling the objects into memory from the database before decrypting, but you shouldn't need to use `ToList` on `myList` as `foreach` will run the query. Why are you selecting so many fields that you aren't using in the `Join` or final select? Also, you can add that to the query by putting `AsEnumerable` after the final `Select` and then decrypting after that (and possibly using an anonymous object rather than creating a bad and then good `MyGarageList` (bad name) object). – NetMage Oct 26 '18 at 19:05

0 Answers0