Get customer by phone number

1 week ago
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...
1 week ago
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!
1 week ago
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)
1 week ago
New York wrote:
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)


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();
1 week ago
what is your nopCommerce version?
Have you tried store procedure approach yet?
1 week ago
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'
6 days ago
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'
6 days ago
kamleshtarsariya wrote:
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.

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
6 days ago
Rashed Khan wrote:
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.
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


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...
5 days ago
Did you try what I suggested above?
Create an additional Index on the  GenericAttribute table.
E.g.    [Value], [Key], [KeyGroup]