ASP.NET query to get data from database-CodePudding

In my ASP.NET MVC application, in the signup form, I want to check the user-entered values with my current database table and get the Id if a record is matched.

Here from the user's end, I'm getting their email address, surname, date of birth .

Then in the controller, I'm trying to match any record from the above details to get the existing record Id.

The issue is that it happens takes more time to run this query and returns as timeout.

Is there any way of searching the record more efficiently way?

This is my Controller code

public JsonResult SignUpCustomer(string emailAddress, string password, string surName, string name, DateTime dateOfBirth, string timeZone)
{

  int customerId = 0;

  try

  {
    customerId = db.Customer.AsEnumerable().Where(x => x.Sur_Name.ToLower().Contains(surName.ToLower()) && x.Date_of_birth.Date == dateOfBirth.Date && x.Email_Primary.ToLower() == emailAddress.ToLower()).Select(x => x.Id).FirstOrDefault();

    if (customerId == 0) {
      customerId = db.Customer.AsEnumerable().Where(x => x.Email_Primary.ToLower() == emailAddress.ToLower() && x.Date_of_birth.Date == dateOfBirth.Date).Select(x => x.Id).FirstOrDefault();

      if (customerId == 0) {
        customerId = db.Customer.AsEnumerable().Where(x => x.Sur_Name.ToLower().Contains(surName.ToLower()) && x.Date_of_birth.Date == dateOfBirth.Date).Select(x => x.Id).FirstOrDefault();

      }
    }

    if (customerId != 0) {
      UserAccounts accounts = new UserAccounts();
      accounts.Email_Address = emailAddress;
      accounts.Surname = surName;
      accounts.Name = name;
      accounts.Password = Crypto.Hash(password);
      accounts.Status = true;
      accounts.Created_Date = DateTime.UtcNow.AddMinutes(int.Parse(timeZone));
      accounts.Customer_Id = customerId;
      dbs.UserAccounts.Add(accounts);
      dbs.SaveChanges();

    } else {
      UserAccounts accounts = new UserAccounts();
      accounts.Email_Address = emailAddress;
      accounts.Surname = surName;
      accounts.Name = name;
      accounts.Password = Crypto.Hash(password);;
      accounts.Status = true;
      accounts.Created_Date = DateTime.UtcNow.AddMinutes(int.Parse(timeZone));
      accounts.Customer_Id = customerId;
      dbs.UserAccounts.Add(accounts);
      dbs.SaveChanges();
    }

    return Json(new {
      Success = true,

    }, JsonRequestBehavior.AllowGet);

  } catch (Exception ex) {

    throw;
  }

}

CodePudding user response：

You can clear your Linq query to something like this:

var loweredName=surName.ToLower();
var loweredEmailAddress=surName.ToLower();
var dateOfBirthDateDatePart=dateOfBirth.Date;
customerID = db.Customer.FirstOrDefault(
                 x => x.Sur_Name.ToLower().Contains(loweredName)
                   && x.Date_of_birth.Year== dateOfBirthDateDatePart.Year
                   && x.Date_of_birth.Month == dateOfBirthDateDatePart.Month 
                   && x.Date_of_birth.Day == dateOfBirthDateDatePart.Day 
                   && x.Email_Primary.ToLower() == loweredEmailAddress)?.Id;

Change other selects too.

Date comparison options are totally diffrenet depending on the version of Ef of efCore you are using. For choosing the best way check here