Extended GridDataProviderRequest for Server side searching

[bluisanajmr]

Hello,

I have been using the HXGrid with client-side sorting, filtering, and paging for a while now. Recently, I needed to implement all of these functionalities server-side to handle datasets with millions of records. I wanted to share how I extended the GridDataProviderRequest object to accommodate filter terms (referred to as "search" in my code) in case someone else might find this useful or it could be integrated into the HXGrid.

My goal was to create a consistent way to pass filter terms to my API endpoints and then apply these filters to my LINQ to SQL queries without hardcoding for each entity. When my extended GridProviderRequest object is instantiated, it generates a list of PropertyName and SearchValue objects based on the type of entity that the grid is using as its data source. This approach allows me to automatically generate filters in my LINQ queries using a foreach loop in my API, without the need to hardcode column names or other details.

This concept could be expanded to become part of the default GridDataProviderRequest, and optionally used by developers who choose to implement filtering within HXGrids.

To start with I implement filtering much like the online example: https://havit.blazor.eu/components/hxgrid#header-filtering

Inside of my GetGridData (the datasource for my grid) method I implement my extendedGridProviderRequest and add filter variables that are bound to my Grid Header inputs.

private async Task<GridDataProviderResult<EnrollemntDatum>> GetGridData(GridDataProviderRequest<EnrollemntDatum> request)
{
    try
    {
        //create our extended Request object and generate a list of search objects from our EnrollmentDatum Entity
        ExtendedGridDataProviderRequest<EnrollemntDatum> extrequest = new ExtendedGridDataProviderRequest<EnrollemntDatum>(request);

        //set the values for our search terms that are going to be applied to our Linq to SQL queries from the API
        extrequest.SetSearchValue(nameof(EnrollemntDatum.DateEntered), enteredsearch);
        extrequest.SetSearchValue(nameof(EnrollemntDatum.EnrollStatusName), statussearch);
        extrequest.SetSearchValue(nameof(EnrollemntDatum.SchoolName), schoolsearch);

Then in my API that gets called to provide the data for the grid I use the Filter(search) terms like this.

//simple example of inline sql statement
//this idea can be applied to a querable object as well but I wanted to explain this as 
//simply as possible for demo purposes. 
//I have implmented this using an entity queryable entity object with .where clauses. 
string sqlstr = "select * from enrollment where ";
foreach (SearchItem<EnrollemntDatum> searchstr in request.Search)
{
    if (!String.IsNullOrEmpty(searchstr.SearchValue))
    {
        //apply our filters to a querable object or an inline sql statement
        //example
        //Simplest example of inline sql statement
        sqlstr += searchstr.PropertyName + " like '%" + searchstr.SearchValue + "%' and ";

    }
}

// Remove the last " and " from sqlstr if it exists
if (sqlstr.EndsWith(" and "))
{
    sqlstr = sqlstr.Remove(sqlstr.Length - 5);
}

This is ExtendedGridDataProviderRequest

public class ExtendedGridDataProviderRequest<TItem> : GridDataProviderRequest<TItem>
{
    public List<SearchItem<TItem>> Search { get; set; }

    public ExtendedGridDataProviderRequest(GridDataProviderRequest<TItem> request)
    {
        // Copy existing properties from the original request
        // Assuming there's a way to do this, otherwise manually copy each relevant property
        // Initialize Search property
        Search = GenerateSearchItemsFromTItem();
        this.Sorting = request.Sorting;
        this.Count = request.Count;
        this.StartIndex = request.StartIndex;
    }

    // Default constructor for deserialization
    public ExtendedGridDataProviderRequest()
    {
        // Initialize any necessary properties
        Search = new List<SearchItem<TItem>>();
    }

    private List<SearchItem<TItem>> GenerateSearchItemsFromTItem()
    {
        var searchItems = new List<SearchItem<TItem>>();
        var properties = typeof(TItem).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        foreach (var property in properties)
        {
            searchItems.Add(new SearchItem<TItem>(property.Name, string.Empty));
        }

        return searchItems;
    }

    public void SetSearchValue(string propertyName, string searchValue)
    {
        var searchItem = Search.FirstOrDefault(s => s.PropertyName == propertyName);
        if (searchItem != null)
        {
            searchItem.SearchValue = searchValue;
        }
    }
}

public class SearchItem<TItem>
{
    public string PropertyName { get; set; }
    public string SearchValue { get; set; }

    public SearchItem(string propertyName, string searchValue)
    {
        PropertyName = propertyName;
        SearchValue = searchValue;
    }
}

[hakenr]

Hello @bluisanajmr,

Thanks for providing this proposal for enhancement.

We decided not to provide any built-in filtering capabilities, as the way to apply the filtering criteria depends on the architecture of the application (hosting model, data layer, etc.).

PS: Be careful, your example is vulnerable to SQL injection attacks. You must avoid assembling SQL commands from user-originated input. In your code, this line...

        sqlstr += searchstr.PropertyName + " like '%" + searchstr.SearchValue + "%' and ";

...can be exploited by malicious input, such as searchstr.SearchValue = "xxx'; TRUNCATE TABLE enrollment; ---"

[bluisanajmr]

Very true about the sql injection. Our backend code is applying the filters through entity framework not concatenated SQL statements. Our code is very complicated to explain. I just put this together as a very dirty example of this could be used.

[bluisanajmr]

In my view this idea is architecture agnostic and very useful. All this does is build a structure to pass filters to your backend in a uniform way. The developer still has to decide how to apply the filters to their data, which will be different for each architecture as you point out.