An error occurs in the search function.

[Pylori]

Hi,

I'm using grid with Syncfusion.EJ2.AspNet.Core(20.2.0.43).

I used UrlAdaptor to improve responsiveness because I have a lot of data to display in the grid.

But I get an error with the search function.

Please tell me how to solve it.

Below is the content of the error and my source.

(error)

System.InvalidOperationException: 'The LINQ expression 'DbSet<TradeData>()

    .Where(t => (t.Group ?? "Blanks").ToLower().Contains("eurus") || (t.Currency ?? "Blanks").ToLower().Contains("eurus") || t.Login.ToString().ToLower().Contains("eurus") || (t.Name ?? "Blanks").ToLower().Contains("eurus") || t.Order.ToString().ToLower().Contains("eurus") || t.DateTimeOffset.ToString().ToLower().Contains("eurus") || t.Action.ToString().ToLower().Contains("eurus") || (t.Symbol ?? "Blanks").ToLower().Contains("eurus") || t.Lots.ToString().ToLower().Contains("eurus") || t.Commission.ToString().ToLower().Contains("eurus"))' could not be translated. Additional information: Translation of method 'long.ToString' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information.

Translation of method 'long.ToString' failed.

(cshtml)

        <ejs-grid id="Grid"

                  actionComplete="actionComplete"

                  dataBound="dataBound"

                  queryCellInfo="queryCellInfo"

                  height="70vh"

                  toolbar="@(new List<string>() {"Search"})"

                  allowPaging="true"

                  allowSelection="false"

                  allowSorting="true">

            <e-grid-columns>

                <e-grid-column field="Group"></e-grid-column>

                <e-grid-column field="Currency"></e-grid-column>

                <e-grid-column field="Login"></e-grid-column>

                <e-grid-column field="Name"></e-grid-column>

                <e-grid-column field="Order" isPrimaryKey="true"></e-grid-column>

                <e-grid-column field="DateTimeOffset" customFormat="@(new { type ="date", format="yyyy.MM.dd HH:mm:ss" })"></e-grid-column>

                <e-grid-column field="Action"></e-grid-column>

                <e-grid-column field="Symbol"></e-grid-column>

                <e-grid-column field="Lots" textAlign="Right" format="N2"></e-grid-column>

                <e-grid-column field="Commission" textAlign="Right" format="N2"></e-grid-column>

            </e-grid-columns>

            <e-data-manager url="/Users/UrlDataSource" adaptor="UrlAdaptor"></e-data-manager>

            <e-grid-pagesettings pageSize="20" pageSizes="@(new int[] { 20, 50, 100, 200 })"></e-grid-pagesettings>

            <e-grid-sortsettings columns="@(new List<object>() { new { field = "Login", direction = "Ascending" } })"></e-grid-sortsettings>

        </ejs-grid>

(Model)

    public class TradeData

    {

        [Key]

        [JsonProperty(PropertyName = "id")]

        public string Id { get; set; }

        [JsonProperty(PropertyName = "partitionKey")]

        public string PartitionKey { get; set; }

        public string Group { get; set; }

        public string Currency { get; set; }

        public long Login { get; set; }

        public string Name { get; set; }

        public long Order { get; set; }

        public long Time { get; set; }

        public DateTimeOffset DateTimeOffset { get; set; }

        public int Action { get; set; }

        public string Symbol { get; set; }

        public double Lots { get; set; }

        public double Commission { get; set; }

    }

(Controller)

        public IActionResult UrlDatasource([FromBody] DataManagerRequest dm)

        {

            IQueryable<TradeData> dataSource = _cosmosDbContext.Trades;

            QueryableOperation operation = new();

            if (dm.Search != null && 0 < dm.Search.Count)

                dataSource = operation.PerformSearching(dataSource, dm.Search);

            //if (dm.Where != null && 0 < dm.Where.Count)

            //    dataSource = operation.PerformFiltering(dataSource, dm.Where, dm.Where[0].Operator);

            int count = dataSource.Count(); // I get an error here

            if (dm.Sorted != null && 0 < dm.Sorted.Count)

                dataSource = operation.PerformSorting(dataSource, dm.Sorted);

            if (dm.Skip != 0)

                dataSource = operation.PerformSkip(dataSource, dm.Skip);

            if (dm.Take != 0)

                dataSource = operation.PerformTake(dataSource, dm.Take);

            return dm.RequiresCounts ? Json(new { result = dataSource, count = count }) : Json(dataSource);

        }

Regards,

Pylori.

[Rajapandi Ravi]

Hi Pylori,

Greetings from Syncfusion support

Based on your shared information we have prepared a sample and tried to reproduce your reported problem, but it was unsuccessful. Please refer the below sample for more information.

Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/sample1043436721.zip

If you still face the issue, please share the below details that will be helpful for us to validate the problem.

1)            Please share your exact requirement with detailed description.

2)           Please share any simple issue reproducible sample and or try to reproduce the issue with our above attached sample.

3)           Share the issue scenario in video demonstration format, we would like to check what value you are trying to search.

Note: Please change the physical path in the appsettings.JSON file and run the application.

Regards,

Rajapandi R

[Pylori]

Hi,

Thanks for the answer and the sample provided.

I was able to confirm that it is not reproducible with the provided sample.

I made a sample too. Attach.

The database I'm using is CosmosDB (SQL API). Line 10 of Program.cs is the connection destination definition. Please change it and use it.

The name of the table (container) I am using is Products.

The test data of Products are the following three cases.

{

    "id": "9105252",

    "partitionKey": "114178",

    "Group": "demo\\aaaaaa\\group1",

    "Currency": "JPY",

    "Login": 114178,

    "Name": "EA TS-03 100man0.25",

    "Order": 9105252,

    "Time": 1643140800,

    "DateTimeOffset": "2022-01-25T20:00:00+00:00",

    "Action": 1,

    "Symbol": "AUDCAD.1a",

    "Lots": 0.25,

    "Commission": 0

}

{

    "id": "9105283",

    "partitionKey": "114178",

    "Group": "demo\\aaaaaa\\group1",

    "Currency": "JPY",

    "Login": 114178,

    "Name": "EA TS-03 100man0.25",

    "Order": 9105283,

    "Time": 1643148000,

    "DateTimeOffset": "2022-01-25T22:00:00+00:00",

    "Action": 0,

    "Symbol": "GBPAUD.1a",

    "Lots": 0.25,

    "Commission": 0

}

{

    "id": "9105284",

    "partitionKey": "114178",

    "Group": "demo\\aaaaaa\\group1",

    "Currency": "JPY",

    "Login": 114178,

    "Name": "EA TS-03 100man0.25",

    "Order": 9105284,

    "Time": 1643148000,

    "DateTimeOffset": "2022-01-25T22:00:00+00:00",

    "Action": 1,

    "Symbol": "AUDCAD.1a",

    "Lots": 0.25,

    "Commission": 0

}

If you start the web application in the above environment and search for "AUD", the following error will occur.

Please give me a solution.

Regards,

Pylori.

Attachment: SyncfusionWebApplication1_778d64bc.zip

[Rajapandi Ravi]

Hi Pylori,

Thanks for your patience

We have checked your provided sample, but we cannot be able to run your project. So, to proceed further validation please try to reproduce the issue in our sample which was shared in our previous update that would be helpful for us to provide better solution.

Regards,

Rajapandi R

[Pylori]

Hi,

Thank you for your answer.

I slightly modified the sample shared in the previous update to reproduce the issue.

I am attaching the sample.

In the attached sample I have a problem when I start the application and perform a search.

Regards,

Pylori.

Attachment: inventorymanagement_b10af174.zip

[Rajapandi Ravi]

Hi pylori,

Thanks for your update

We have checked your shared sample and we could see that in your starup.cs file you have used UseCosmos in the connection, it was the cause of the problem. When we perform searching the DataSource variable contain no records. Please refer the below screenshot for more information.

Screenshot:

But in our shared sample, we have used connection string in the starup.cs file and while performing the Grid actions it will fetch the DataSource properly. Please refer the below screenshot.

Screenshot:

So please ensure your connection and whenever you are performing Grid actions like Searching, Filtering. Please ensure that the DataSource variable contains records properly or not. This problem was not related to the Grid, so please ensure your connection and resolve the problem with your application.

Regards,

Rajapandi R

[Pylori]

Hi,

Thank you for your answer.

I am using Azure Cosmos DB in my application.

If I don't use the UrlAdaptor the search functionality works fine as shown in the screenshot below.

However, the search functionality did not work when using the UrlAdaptor.

If I use Azure Cosmos DB for database, does that mean I can't search using UrlAdaptor?

If so, please let me know, other than using a UrlAdaptor, is there a way to quickly display large amounts of data in a grid?

Since it is not possible to change from Azure Cosmos DB to another database, I want to implement a grid that can quickly display data using Cosmos DB.

It would be helpful if you could tell me if there is a better way.

Regards,

Pylori.

[Rajapandi Ravi]

Hi Pylori,

In your query you have used Cosmos DB in your application, Currently, we are validating your query with your shared information, and we will update you the details on or before 16^th Sep 2022. Until then we appreciate your patience.

Regards,

Rajapandi R

[Rajapandi Ravi]

Hi Pylori,

Thanks for the update

We have checked your shared sample and we could see that when we perform actions in the Grid, the DataSource variable contain no records and it will throw the exception. Please refer the below screenshot for more information.

Screenshot:

To overcome the problem, we need to convert this to ToList() and make this as IEnumerable. Please refer the below code example and sample for more information.

 

public ActionResult UrlDatasource([FromBody]DataManagerRequest dm)

        {

            IEnumerable<Products> DataSource = _context.Products.ToList();

            DataOperations operation = new DataOperations();

            if (dm.Search != null && dm.Search.Count > 0)

            {

                DataSource = operation.PerformSearching(DataSource, dm.Search);  //Search

            }

            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting

            {

                DataSource = operation.PerformSorting(DataSource, dm.Sorted);

            }

            if (dm.Where != null && dm.Where.Count > 0) //Filtering

            {

                DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator);

            }

            int count = DataSource.Cast<Products>().Count();

            if (dm.Skip != 0)

            {

                DataSource = operation.PerformSkip(DataSource, dm.Skip);   //Paging

            }

            if (dm.Take != 0)

            {

                DataSource = operation.PerformTake(DataSource, dm.Take);

            }

            return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource);

        }

 

Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/sample-1596452425.zip

Since it was a common query, we suggest you follow the below general discussion link to achieve your requirement.

General Link: https://stackoverflow.com/questions/21648040/how-to-resolve-unable-to-cast-generic-list-to-linq-iqueryable-using-automapper

Regards,

Rajapandi R

[Pylori]

Hi,

Thanks for your reply.

Certainly using ToList() would not result in an error, but as you point out, it would not accomplish my requirement (to quickly display a large amount of data).

I also verified the AutoMapper in the general link provided, but it did not solve the problem.

However, I was able to solve the problem in another way and I am attaching a sample of it.

Regarding the error, it seemed to be due to the fact that the LINQ created by PerformSearching could not be translated into SQL.

I have tried creating various LINQs without PerformSearching and they never translate correctly to SQL.

So I looked for a way to execute SQL directly without LINQ, and the problem was solved by implementing it.

Below is a screenshot from the verification.

Previously an error occurred with Count(), but no longer. The count values are also normal.

(For some reason, "all threads to run" is still occurring in DataSource, but it seems to be working correctly)

That is all.

Thank you very much.

Regards,

Pylori.

Attachment: inventorymanagement_9e572d4a.zip

[Rajapandi Ravi]

Hi Pylori,

We are happy to hear that you have found the solution at your end.

Please get back to us if you need further assistance.

Regards,

Rajapandi R