1. Home
  2. Forum
  3. ASP.NET Core - EJ 2
  4. Search query evaluated locally instead of on DB server

Search query evaluated locally instead of on DB server

Dear,

I  have a ejs-grid that uses a UrlAdaptor as its source.
When using the search function, the following warning is generated:

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where ((((([PaymentOverview].CustomerDisplayName ?? "Blanks").ToString().ToLower().Contains("mystringtosearch") Or ([PaymentOverview].InvoiceNumber ?? "Blanks").ToString().ToLower().Contains("mystringtosearch")) Or ([PaymentOverview].DeliveryNumber ?? "Blanks").ToString().ToLower().Contains("mystringtosearch")) Or [PaymentOverview].Amount.ToString().ToLower().Contains("mystringtosearch")) Or ([PaymentOverview].PaymentTypeName ?? "Blanks").ToString().ToLower().Contains("mystringtosearch"))' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Skip(__p_0)' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Take(__p_1)' could not be translated and will be evaluated locally.
How can I make sure that a search function (on multiple fields, with "contains") is evaluated on the SQL server?

Thanks,
Koen

5 Replies
VS Vignesh Sivagnanam Syncfusion Team November 26, 2020 01:16 PM UTC

Hi Koen 

Greeting From Syncfusion support. 

By default in EJ2 Grid the grid searches all the bound column values. So, when we perform search operation it will generate a query with all the bound column values.  

We have prepared a sample with URL Adaptor but we did not face any issue or warning at our end. For your convenience we have attached the sample for your reference. 


Code Example: 
<ejs-grid id="Grid" height="273" allowPaging="true" toolbar="@(new List<string>() { "Search" })"> 
    <e-data-manager url="/Home/UrlDataSource" adaptor="UrlAdaptor"></e-data-manager> 
    <e-grid-columns> 
      ……………………… 
      ……………………… 
    </e-grid-columns> 
  </ejs-grid> 

If you still faced the issue, could you please share the below details, which will be helpful for us to validate further about issue. 

1. Share the complete grid rendering code. 
 
2. Share the dataSource structure that you have bounded to the Grid. 
 
3. If possible, share the issue reproducible sample. 
 
Note: If we need to search operation only with the particular fields we suggest you to use the fields property of e-grid-searchSettings tag helper. 
 
 
Regards, 
Vignesh Sivagnanam 


KO Koen replied to Vignesh Sivagnanam November 27, 2020 11:03 AM UTC

Hi Koen 

Greeting From Syncfusion support. 

By default in EJ2 Grid the grid searches all the bound column values. So, when we perform search operation it will generate a query with all the bound column values.  

We have prepared a sample with URL Adaptor but we did not face any issue or warning at our end. For your convenience we have attached the sample for your reference. 


Code Example: 
Search" })"> 
     
     
      ……………………… 
      ……………………… 
     
   

If you still faced the issue, could you please share the below details, which will be helpful for us to validate further about issue. 

1. Share the complete grid rendering code. 
 
2. Share the dataSource structure that you have bounded to the Grid. 
 
3. If possible, share the issue reproducible sample. 
 
Note: If we need to search operation only with the particular fields we suggest you to use the fields property of e-grid-searchSettings tag helper. 
 
 
Regards, 
Vignesh Sivagnanam 



Dear,

I have tested your sample, and indeed, it does not show what I see. However, I have added EF logging, and then it does show the same message:

warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'where ((([Customers].CustomerID.ToString().ToLower().Contains("tanna") Or ([Customers].ContactName ?? "Blanks").ToString().ToLower().Contains("tanna")) Or ([Customers].ContactTitle ?? "Blanks").ToString().ToLower().Contains("tanna")) Or ([Customers].City ?? "Blanks").ToString().ToLower().Contains("tanna"))' could not be translated and will be evaluated locally.



See this file for the updated solution (with logging): https://www.dropbox.com/s/y0wfcf0dj5aee83/EJ2Grid1699189954-withlogging.zip?dl=0
Can you please test it at your side and try to find a solution?

Thanks,
Koen

VS Vignesh Sivagnanam Syncfusion Team December 7, 2020 11:56 AM UTC

Hi Koen, 

Sorry for the late reply. 

We validate the warning message and the warning message indicates that before perform search operation need to sort the records. In the attached sample when we perform initial sort and after that when we perform search operation the warning message will not be displayed. It is the default behavior. 

If we change the behavior it leads to change the whole behavior of search operation so we suggest you to use the sample with warning message. If you need to avoid the warning we suggest you to perform initial sort using sortSettings API. 

For more information please refer the below help document, 


Regards, 
Vignesh Sivagnanam 


KO Koen replied to Vignesh Sivagnanam December 7, 2020 12:20 PM UTC

Hi Koen, 

Sorry for the late reply. 

We validate the warning message and the warning message indicates that before perform search operation need to sort the records. In the attached sample when we perform initial sort and after that when we perform search operation the warning message will not be displayed. It is the default behavior. 

If we change the behavior it leads to change the whole behavior of search operation so we suggest you to use the sample with warning message. If you need to avoid the warning we suggest you to perform initial sort using sortSettings API. 

For more information please refer the below help document, 


Regards, 
Vignesh Sivagnanam 


Dear,

Thank you for your response. However, the issue is not about sorting, but about where the query is performed.

Currently the query is evaluated locally, meaning *ALL* records are first fetched from the DB and the search/filter is done in-memory. It should do the filtering on the DB itself and only fetching the results from that filter.

This problem is causing me headaches...

Thanks,
Koen


VS Vignesh Sivagnanam Syncfusion Team December 11, 2020 01:20 PM UTC

Hi Koen 

We have created a new incident under your account to follow up with this query. We suggest you follow up the incident for further updates. 

Regards 
Vignesh Sivagnanam 


Loader.
Up arrow icon