Get customer by phone number

During registration time i entered both email and phone number.
We get customer by email easily because it is unique.
But how to get customer by phone number ?  it saved in generic attribute table not in customer table.
I tried to get customer by phone using following method but it take some time to response. I need faster output for this. Please suggest solution for this.

var query = (from ga in _genericAttributeRepository.Table
                        where ga.Value == phoneNumber && ga.KeyGroup == "Customer" && ga.Key == NopCustomerDefaults.PhoneAttribute
                        select ga).FirstOrDefault()

Thank you...

To make it faster, I would recommend you to do some custom development, Include a new Table from Plugin named CustomerAdditional with Column (Id, NopCustomerId, PhoneNumber)
then consume customer registration event and store phone number attribute data on this table.
Then create index on this table, so that query executing on this table become more faster. Proper indexing is very important here. Might take knowledge from here, https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes

This will help you retrieve PhoneNumber data in faster approach!

Create an additional Index on the  GenericAttribute table.
E.g.    [Value], [Key], [KeyGroup]
(yes, in that order - i.e., more specific up front)

However, consider that even a full search on the GenericAttribute table should not take so long.  Are you *deleting* Guests?  (Use the scheduled task)

New York wrote:

Yes, my schedule task is enabled for "Delete Guests" and its interval is 600 seconds.
How to do full search on the generic attribute table ? There is not any service for get all generic attributes like search products, orders, etc. You have idea then suggest me

Also i tried using following methods but also it take time and threw error for timeout
var customer = _customerService.GetAllCustomers(phone :  1234567890).FirstOrDefault();

what is your nopCommerce version?
Have you tried store procedure approach yet?

Run this query in SQL tool (like SSMS).  How long does it take to run?  

SELECT TOP (1) *
  FROM [GenericAttribute]
  where 
       [KeyGroup] = 'Customer' AND
       [Key] = 'Phone' AND
       [Value] = '<phonenumber>'

Run this query in SQL tool.  How many do you have?

SELECT count(1) as Count
  FROM [GenericAttribute]
  where 
       [KeyGroup] = 'Customer' AND
       [Key] = 'Phone' 

My nopCommerce Version is 4.10.
Yes, i also tried using following stored procedure, but it also take time when it execute from controller side. There are around 3.5 Lakhs records in my GenericAttribute table.

CREATE PROCEDURE [dbo].[GetCustomerByPhone]
  @phoneNumber nvarchar(MAX)
AS
BEGIN  
  SET NOCOUNT ON;
  SELECT TOP (1) *
  FROM [GenericAttribute]
  where
       [KeyGroup] = 'Customer' AND
       [Key] = 'Phone' AND
       [Value] = @phoneNumber
END

When i execute following query in MSSQL it take around 30 second to get result for 1st time.
After one time execute query, second time it send respond faster then first time execute.

SELECT TOP (1) *
  FROM [GenericAttribute]
  where
       [KeyGroup] = 'Customer' AND
       [Key] = 'Phone' AND
       [Value] = '9632587410'

kamleshtarsariya wrote:
from nopCommerce 4.60 all possible customer fields have moved from GenericAttribute table to the customer table for better performance.
here is the list of fields that moved https://github.com/nopSolutions/nopCommerce/issues/4601

Rashed Khan wrote:

Okay, Thanks.
For temporary solution I have to do customization and move "phone number" column to customer table as you suggest.
Once nopCommerce 4.60 release then i have to upgrade this project from 4.10 to 4.60...

Did you try what I suggested above?

Create an additional Index on the  GenericAttribute table.
E.g.    [Value], [Key], [KeyGroup]