DataGrid FilterSettings IgnoreAccent property doesn't work

[David Lozada]

Hello,

I'm using Grid with DataManager to bind remote data. The filter is type "Menu" and I'm trying to use the property IgnoreAccent in the filterSettings object but doesn't work:

<e-grid-filtersettings ignoreAccent="true" type="Menu" operators="@(new { stringOperator = Model.GridFilterOperators })"></e-grid-filtersettings>

I check the request and the value is send correcty:

In the server side im using DataManagerRequest to perform the operations, following this example:

https://ej2.syncfusion.com/aspnetcore/documentation/grid/data-binding/remote-data#handling-on-demand-grid-actions

Api Doc about IgnoreAccent: https://help.syncfusion.com/cr/aspnetcore-js2/Syncfusion.EJ2.Grids.GridFilterSettings.html#Syncfusion_EJ2_Grids_GridFilterSettings_IgnoreAccent

I'm using 25.2.7 version

[Aishwarya Rameshbabu]

Hi David Lozada,

Greetings from Syncfusion support.

In the Syncfusion Grid we don’t have support for ‘ignoreAccent’ in filtering and searching with remote data. We have considered this as a feature request and have logged a feature task titled "Need to provide support for ignoreAccent property with remote data".  During the planning stage for each release cycle, we review all open features and identify those for implementation based on specific parameters such as product vision, technological feasibility, and customer interest. This feature will be included in one of our upcoming releases.

You can now track the current status of your request, review the proposed resolution timeline, and contact us for any further inquiries through this link.

Feedback:  https://www.syncfusion.com/feedback/57941/setting-ignoreaccent-to-true-in-searchsettings-is-not-working-properly-with-remote

We do not have an immediate plan to implement this feature, but it will be included in one of our upcoming releases. Please cast your vote on this feature. Based on the customer demand we will prioritize the features in our upcoming road map. Until then, you can use the sample level solution to achieve this requirement. Please refer to the code example and attached sample.

HomeController.cs

 

public IActionResult UrlDatasource([FromBody] DataManagerRequest dm)

        {

            IEnumerable DataSource = order;

            DataOperations operation = new DataOperations();

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

            {

                customFilterData = order;

                DataSource = CustomSearch((List<Orders>)DataSource, dm.Search, operation);

                dummy = new List<Orders>();

                //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

            {

                customFilterData = DataSource.Cast<Orders>().ToList();

                DataSource = CustomFilter((List<Orders>)DataSource, dm.Where, dm.Where[0].Operator);

                if (isNotString)

                {

                    isNotString = false;

                    dm.Where[0].predicates = customPredicate;

                    customPredicate = new List<WhereFilter>();

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

                }

            }

            int count = DataSource.Cast<Orders>().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);

        }

 

        public IEnumerable CustomSearch(IEnumerable dataSource, List<SearchFilter> searchFilter, DataOperations operation)

        {

            foreach (var filter in searchFilter)

            {

                foreach (string fields in filter.Fields)

                {

                    for (var i = 0; i < customFilterData.Count(); i++)

                    {

                        System.Reflection.PropertyInfo pi = customFilterData[i].GetType().GetProperty(fields);

                        var type = pi.GetValue(customFilterData[i], null).GetType().Name;

                        if (type == "String")

                        {

                            String name = (String)(pi.GetValue(customFilterData[i], null));

                            if (AccentFilter.RemoveDiacritics(name.ToLower()).Contains(filter.Key.ToString().ToLower()))

                            {

                                dummy.Add(customFilterData[i]);

                            }

                        }

                        else if (type == "Double")

                        {

                            double number = 0;

                            bool result = double.TryParse(filter.Key, out number);

                            if (result && pi.GetValue(customFilterData[i], null).ToString() == double.Parse(filter.Key).ToString())

                            {

                                dummy.Add(customFilterData[i]);

                            }

                        }

                        else

                        {

                            if (pi.GetValue(customFilterData[i], null).ToString() == filter.Key)

                            {

                                dummy.Add(customFilterData[i]);

                            }

                        }

                    }

                }

            }

            return dummy;

        }

 

 

        public IEnumerable CustomFilter(List<Orders> dataSource, List<WhereFilter> whereFilter, string condition)

        {

            IEnumerable predicate = null;

            foreach (var filter in whereFilter)

            {

                if (filter.IsComplex)

                {

                    if (predicate == null)

                    {

                        predicate = CustomFilter(customFilterData, filter.predicates, filter.Condition);

                    }

                }

                else

                {

                    if (filter.value.GetType().Name == "String")

                    {

                        for (var i = 0; i < customFilterData.Count(); i++)

                        {

                            System.Reflection.PropertyInfo pi = customFilterData[i].GetType().GetProperty(filter.Field);

                            String name = (String)(pi.GetValue(customFilterData[i], null));

                            if (AccentFilter.RemoveDiacritics(name.ToLower()).Contains(filter.value.ToString().ToLower()))

                            {

                                dummy.Add(customFilterData[i]);

                            }

                        }

                        customFilterData = dummy;

                        dummy = new List<Orders>();

                    }

                    else

                    {

                        customPredicate.Add(filter);

                        isNotString = true;

                    }

                }

            }

            return customFilterData;

        }

 

 

If you need any further assistance or have additional questions, please feel free to let us know.

Regards

Aishwarya R

Attachment: IgnoreAccentWithRemoteData_dcf19b96.zip

[David Lozada]

​Hi,

Thanks for the answer. I hope you can add this feature soon.

Thanks for the solution provided, but it doesn't work for my case. I'm using remote data and URLAdaptor because i have +230k records in a table and need the pagination. 

If I tried this example, in the line:  customFilterData = DataSource.Cast<Orders>().ToList();  its loading the 230k records in memory and is impossible to work like this.

[David Lozada]

Hello,

There is some another solution? The client of my product claim me a solution for this problem. They need that the grid filter ignoring accents.

Thanks.

[Aishwarya Rameshbabu]

Hi David Lozada,

We have a solution for the issue you are encountering and updated the sample in accordance with your requirement, specifically by converting the data type from IEnumerable to IQueryable. This modification allows for processing the data without loading the entire dataset (230k) into memory. Please review the code example and attached sample for your reference.

HomeController.cs

 

public IActionResult UrlDatasource([FromBody] DataManagerRequest dm)

{

    IQueryable<Orders> DataSource = order.AsQueryable();

    QueryableOperation operation = new QueryableOperation();

   

    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).AsQueryable();

    }

   

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

    {

        customFilterData = DataSource.ToList();

        DataSource = CustomFilter((List<Orders>)DataSource.ToList(), dm.Where, dm.Where[0].Operator).AsQueryable();

       

        if (isNotString)

        {

            isNotString = false;

            dm.Where[0].predicates = customPredicate;

            customPredicate = new List<WhereFilter>();

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

        }

    }

   

    int count = DataSource.Count();

   

    if (dm.Skip != 0)

    {

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

    }

   

    if (dm.Take != 0)

    {

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

    }

   

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

}

 

public IEnumerable<Orders> CustomFilter(List<Orders> dataSource, List<WhereFilter> whereFilter, string condition)

{

    IEnumerable<Orders> filteredData = null;

    foreach (var filter in whereFilter)

    {

        if (filter.IsComplex)

        {

            if (filteredData == null)

            {

                        filteredData = CustomFilter(dataSource, filter.predicates, filter.Condition);

            }

        }

        else

        {

            if (filter.value.GetType().Name == "String")

            {

                for (var i = 0; i < dataSource.Count(); i++)

                {

                    System.Reflection.PropertyInfo pi = dataSource[i].GetType().GetProperty(filter.Field);

                    String name = (String)(pi.GetValue(dataSource[i], null));

                    if (AccentFilter.RemoveDiacritics(name.ToLower()).Contains(filter.value.ToString().ToLower()))

                    {

                        dummy.Add(dataSource[i]);

                    }

                }

 

                        filteredData = dummy;

                dummy = new List<Orders>();

            }

            else

            {

                customPredicate.Add(filter);

                isNotString = true;

            }

        }

    }

    return filteredData;

}

 

 

 

Sample: Please find in the attachment.

Please get back to us if you need any further assistance.

Regards

Aishwarya R

Attachment: 188645UpdatedSample_d92ce4f2.zip

[David Lozada]

Hello,

Thanks for the new solution, but it doesn't solve my problem because it's still loading all the records in the call of the CustomFilter function:

  DataSource = CustomFilter((List<Orders>)DataSource.ToList(), dm.Where, dm.Where[0].Operator).AsQueryable();

Adding a AsQueryable() at the end of the line, it doesn't change that in the call is using DataSource.ToList() to load all the records.

 I hope that you can give another solution. Thanks

[Vasanthakumar K]

Hi David Lozada,

We have validated your query and understand that you need a way to handle the ignoreAccent customFilter on a sample level with IQueryable alone instead of converting to a List. We have modified our custom way of handling the filter using IQueryable alone to meet your requirement. As we previously mentioned, we have already logged this as a feature request. We will provide support for both IEnumerable and IQueryable with multiple dynamic operator switching. Currently, we are providing a custom workaround for your sample level handling purpose alone. We have prepared a sample with a contains-based filter alone. You can customize it at the application level to change your required operator and perform the filter. Please refer to the code example and sample below for more details.

[code example]

public IActionResult UrlDatasource([FromBody] DataManagerRequest dm)

{

    IQueryable<Orders> DataSource = order.AsQueryable();

    QueryableOperation operation = new QueryableOperation();

    . . . . . .

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

    {

        DataSource = CustomFilter(DataSource, dm.Where, dm.Where[0].Operator).AsQueryable();

        if (isNotString)

        {

            isNotString = false;

            dm.Where[0].predicates = customPredicate;

            customPredicate = new List<WhereFilter>();

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

        }

    }

    . . . . . .

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

}

 

public IQueryable<Orders> CustomFilter(IQueryable<Orders> dataSource, List<WhereFilter> whereFilter, string condition)

{

    IQueryable<Orders> filteredData = null;

    foreach (var filter in whereFilter)

    {

        if (filter.IsComplex)

        {

            if (filteredData == null)

            {

                        filteredData = CustomFilter(dataSource, filter.predicates, filter.Condition);

            }

        }

        else

        {

            // Ensure the property is a string

            if (filter.value.GetType().Name == "String")

            {

                        // Build the expression dynamically

                        var parameter = Expression.Parameter(typeof(Orders), "c");

                        var property = Expression.Property(parameter, filter.Field);

 

                        // Normalize and remove accents from the property, then convert to lowercase

                        var normalizeMethod = typeof(string).GetMethod("Normalize", new[] { typeof(NormalizationForm) });

                        var normalizedProperty = Expression.Call(property, normalizeMethod, Expression.Constant(NormalizationForm.FormD));

                        var removeAccentsMethod = typeof(StringExtensions).GetMethod(nameof(StringExtensions.RemoveAccents));

                        var cleanedProperty = Expression.Call(null, removeAccentsMethod, normalizedProperty);

 

                        var toLowerMethod = typeof(string).GetMethod("ToLowerInvariant", Type.EmptyTypes);

                        var lowerCleanedProperty = Expression.Call(cleanedProperty, toLowerMethod);

 

                        var toUpperMethod = typeof(string).GetMethod("ToUpperInvariant", Type.EmptyTypes);

                        var upperCleanedProperty = Expression.Call(cleanedProperty, toUpperMethod);

 

                        // Normalize and remove accents from the value, then convert to lowercase and uppercase

                        var cleanedValueLower = filter.value.ToString().ToLowerInvariantRemoveAccents();

                        var cleanedValueUpper = filter.value.ToString().ToUpperInvariantRemoveAccents();

 

                        var lowerConstant = Expression.Constant(cleanedValueLower);

                        var upperConstant = Expression.Constant(cleanedValueUpper);

 

                        // Create the 'Contains' method call expression for both lower and upper cases

                        var containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });

                        var lowerContainsCall = Expression.Call(lowerCleanedProperty, containsMethod, lowerConstant);

                        var upperContainsCall = Expression.Call(upperCleanedProperty, containsMethod, upperConstant);

 

                        var orExpression = Expression.OrElse(lowerContainsCall, upperContainsCall);

                        var lambda = Expression.Lambda<Func<Orders, bool>>(orExpression, parameter);

 

                        // Apply the filter to the data source

                        filteredData = dataSource.Where(lambda);

                dummy = new List<Orders>();

            }

            else

            {

                customPredicate.Add(filter);

                isNotString = true;

            }

        }

    }

    return filteredData;

}

Sample: please find the attachment.

Regards,

Vasanthakumar K

Attachment: 188645UpdatedSampleIgnoreAccent_355cbde1.zip

[David Lozada]

Hello, with some changes I have been able to adapt the code to my project. Now the problem is when the query is returned to the grid and executes, its give me this error:

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

    .Where(e => e.ApellidosNombre.Normalize(FormD)

        .RemoveAccents().ToLowerInvariant().Contains("garcia") || e.ApellidosNombre.Normalize(FormD)

        .RemoveAccents().ToUpperInvariant().Contains("GARCIA"))' could not be translated. Additional information: Translation of method 'string.Normalize' 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 'string.Normalize' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

I understand is because LINQ cant execute the custom filters we adapt. I don't know if it will be possible to achive this works 😔

Thanks for the supporting.

[Vasanthakumar K]

Hi David Lozada,

We have validated your query and understand that you are facing an exception related to LINQ technique. Microsoft documentation itself suggests using ToList or AsEnumerable (in memory) for forming queries with large sets of data. This issue is not related to Syncfusion grid script error, as you mentioned LINQ cannot execute the custom filters you have adapted. Therefore, you can use our previously suggested method of using ToList with IEnumerable (in memory) dataSource for your large set of data to resolve the issue, as per Microsoft's documentation suggestion.

Regards,

Vasanthakumar K