Search query evaluated locally instead of on DB server

[Koen]

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

[Vignesh Sivagnanam]

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. 

Sample: https://www.syncfusion.com/downloads/support/forum/160065/ze/EJ2Grid1699189954.zip 

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. 

 

https://ej2.syncfusion.com/aspnetcore/documentation/grid/searching/#initial-search 

 

Regards, 

Vignesh Sivagnanam 

[Koen]

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. 

Sample: https://www.syncfusion.com/downloads/support/forum/160065/ze/EJ2Grid1699189954.zip 

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. 

 

https://ej2.syncfusion.com/aspnetcore/documentation/grid/searching/#initial-search 

 

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 full output: https://www.dropbox.com/s/qonwcw425x1zdpe/localEvaluation.txt?dl=0

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

[Vignesh Sivagnanam]

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, 

https://ej2.syncfusion.com/aspnetcore/documentation/grid/sorting/#initial-sort 

Regards, 

Vignesh Sivagnanam 

[Koen]

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, 

https://ej2.syncfusion.com/aspnetcore/documentation/grid/sorting/#initial-sort 

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

[Vignesh Sivagnanam]

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