ASP.NET query to get data from database

Question

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;
  }

}

Why do you add _AsEnumerable_ after each entity call? _db_ is an instance of your EF DbContext right? — Steve, Dec 08 '22 at 08:21
@DA Hi, I don't know how to do it. Can you share any example ? — Dev Beginner, Dec 08 '22 at 08:29
Check this article: https://www.sqlshack.com/sql-index-overview-and-strategy/ — D A, Dec 08 '22 at 08:30
You don't need AsEnumberable over a _DbSet_ Also, your use of Contains seems to be illogical. Contains search for a substring, you need an exact match so better use Equals with a stringcomparison option to remove the ToLower over all the records. — Steve, Dec 08 '22 at 08:39
Consider also that Contains and ToLower probably makes the presence of Indexes on those columns useless — Steve, Dec 08 '22 at 08:41
@Steve the problem is if I used the query without ``AsEnumerable()``, then in the query ``x.Date_of_birth.Date == dateOfBirth.Date`` this returns an error. ``The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported`` — Dev Beginner, Dec 08 '22 at 08:44

MD Zand · Accepted Answer · 2022-12-08T09:35:20.833

0

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

edited Dec 08 '22 at 09:35

answered Dec 08 '22 at 08:33

MD Zand

2,366
3
14
25

Thanks. But when I applied this I got an error that ``The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported``. That's why I use ``AsEnumerable()`` there. Is there any way of avoiding this error? – Dev Beginner Dec 08 '22 at 08:53
OK, I edited the answer to handle that – MD Zand Dec 08 '22 at 08:56
Thanks for the answer. Still have doubts. When I debug this code from visual studio data pass to the controller and the process is going normally, But when I host this on my remote server and tried to do the same from my mobile device or pc, took more time and returns with a ``timeout`` error. Is this related to the query or some other reason? – Dev Beginner Dec 09 '22 at 04:45

ASP.NET query to get data from database

1 Answers1