Server-side filtering with Angular 6 ag-grid and ASP.NET and EF Core 2.1

Question

I am trying to implement server-side filtering in ag-grid (infinite scrolling mode).

Problem is - documentation about filterModel is very obscure and I am slowly discovering things using console.log which is getting frustrating because of different information filterModel can provide which also makes mapping to server side classes very tedious. Has anyone found proper documentation about filterModel?

Also, has anyone found helper methods for ASP.NET Core and EF Core to apply this filterModel? It seems like A LOT of work to cover every possible scenario and my current approach requires System.DynamicLinq (not sure if this is optimal solution).

Thanks, Mario

Embalm

Answer 1

I got this sorted, so if anyone needs it, here it is.

Infinite row model requires data source which I defined in onGridReady event like this:

const dataSource = {
        rowCount: null,
        getRows: (params) => {
            this.svc.GetDrivingData(params.startRow, params.endRow, params.sortModel, params.filterModel)
                .subscribe((d) => {
                    // console.log(JSON.stringify(d, null, 4));
                    params.successCallback(d, null);
                });


        }
    };

Then GetDrivingData calls Web Api:

    GetDrivingData(startRow: number, endRow: number,
    sortModel: any, filterModel: any): Observable<DrivingData[]>
{
    const body = {
        startRow,
        endRow,
        sortModel,
        filterModel
    };

    return this.httpClient.post<DrivingData[]>(`${this.baseUrl}/api/carfleet/DrivingDataPocoLo/GetDrivingData`, body);
}

Finally, on server side it takes some processing of filterModel and sortModel. Following code is not optimised at all, it is demonstration of different values of filterModel. For instance, if you select second logic operator in ag-grid, JSON changes and includes condition1 and condition2 objects with logicOperator parameter. This code could contain bugs because I did not test all possible combinations. Also, the code uses System.DynamicLinq.

        [HttpPost("[action]")]
    public IActionResult GetDrivingData([FromBody] GridOperationsModel gom)
    {
        var query = ctx.DrivingData.AsQueryable();

        Func<string, FilterModel, List<object>, string> getConditionFromModel =
        (string colName, FilterModel model, List<object> values) =>
        {
            string modelResult = "";

            switch (model.filterType)
            {
                case "text":
                    switch (model.type)
                    {
                        case "equals":
                            modelResult = $"{colName} = \"{model.filter}\"";
                            break;
                        case "notEqual":
                            modelResult = $"{colName} = \"{model.filter}\"";
                            break;
                        case "contains":
                            modelResult = $"{colName}.Contains(@{values.Count})";
                            values.Add(model.filter);
                            break;
                        case "notContains":
                            modelResult = $"!{colName}.Contains(@{values.Count})";
                            values.Add(model.filter);
                            break;
                        case "startsWith":
                            modelResult = $"{colName}.StartsWith(@{values.Count})";
                            values.Add(model.filter);
                            break;
                        case "endsWith":
                            modelResult = $"!{colName}.StartsWith(@{values.Count})";
                            values.Add(model.filter);
                            break;
                    }
                    break;
                case "number":
                    switch (model.type)
                    {
                        case "equals":
                            modelResult = $"{colName} = {model.filter}";
                            break;
                        case "notEqual":
                            modelResult = $"{colName} <> {model.filter}";
                            break;
                        case "lessThan":
                            modelResult = $"{colName} < {model.filter}";
                            break;
                        case "lessThanOrEqual":
                            modelResult = $"{colName} <= {model.filter}";
                            break;
                        case "greaterThan":
                            modelResult = $"{colName} > {model.filter}";
                            break;
                        case "greaterThanOrEqual":
                            modelResult = $"{colName} >= {model.filter}";
                            break;
                        case "inRange":
                            modelResult = $"({colName} >= {model.filter} AND {colName} <= {model.filterTo})";
                            break;
                    }
                    break;
                case "date":
                    values.Add(model.dateFrom);

                    switch (model.type)
                    {
                        case "equals":
                            modelResult = $"{colName} = @{values.Count - 1}";
                            break;
                        case "notEqual":
                            modelResult = $"{colName} <> @{values.Count - 1}";
                            break;
                        case "lessThan":
                            modelResult = $"{colName} < @{values.Count - 1}";
                            break;
                        case "lessThanOrEqual":
                            modelResult = $"{colName} <= @{values.Count - 1}";
                            break;
                        case "greaterThan":
                            modelResult = $"{colName} > @{values.Count - 1}";
                            break;
                        case "greaterThanOrEqual":
                            modelResult = $"{colName} >= @{values.Count - 1}";
                            break;
                        case "inRange":
                            values.Add(model.dateTo);
                            modelResult = $"({colName} >= @{values.Count - 2} AND {colName} <= @{values.Count - 1})";
                            break;
                    }
                    break;
            }
            return modelResult;
        };

        foreach (var f in gom.filterModel)
        {
            string condition, tmp;
            List<object> conditionValues = new List<object>();

            if (!string.IsNullOrWhiteSpace(f.Value.logicOperator))
            {
                tmp = getConditionFromModel(f.Key, f.Value.condition1, conditionValues);
                condition = tmp;

                tmp = getConditionFromModel(f.Key, f.Value.condition2, conditionValues);
                condition = $"{condition} {f.Value.logicOperator} {tmp}";
            }
            else
            {
                tmp = getConditionFromModel(f.Key, f.Value, conditionValues);
                condition = tmp;
            }

            if (conditionValues.Count == 0) query = query.Where(condition);
            else query = query.Where(condition, conditionValues.ToArray());
        }

        foreach (var s in gom.sortModel)
        {
            switch (s.sort)
            {
                case "asc":
                    query = query.OrderBy(s.colId);
                    break;
                case "desc":
                    query = query.OrderBy($"{s.colId} descending");
                    break;
            };
        };

        if (gom.sortModel.Count() == 0)
        {
            query = query.OrderBy(x => x.Oid);
        }


        query = query
            .Include(dd => dd.CarNavigation)
            .Include(dd => dd.DriverNavigation)
            .Skip(gom.startRow)
            .Take(gom.endRow - gom.startRow);


        var result = query
            .AsNoTracking()
            .ToArray();

        return Ok(result);
    }

Embalm

could you share your GridOperationsModel? – Yahweh

@ArielMoraes - sorry for delay, I added code for all models I used – Embalm

This has helped me a lot. Have you tried grouping and pivoting? – Outtalk

Answer 2

Here are all models I used for grid.

    public class SortModel
{
    public string colId { get; set; }
    public string sort { get; set; }
}

public class FilterModel
{
    public FilterModel condition1 { get; set; }
    public FilterModel condition2 { get; set; }
    [JsonProperty("operator")]
    public string logicOperator { get; set; }
    public string type { get; set; }
    public string filter { get; set; }
    public string filterTo { get; set; }
    public DateTime? dateFrom { get; set; }
    public DateTime? dateTo { get; set; }
    public string filterType { get; set; }
}

public class GridOperationsModel
{
    public int startRow { get; set; }
    public int endRow { get; set; }
    public SortModel[] sortModel { get; set; }
    public Dictionary<string, FilterModel> filterModel { get; set; }
}

Embalm

Answer 3

As Ag-grid Official docs doesn't give us any example in ASP.NET Core, so I thought I can make the one example in the support of Dotnet developers.

Here is my Github

Wilcox