Grid Filter Row Large Data Source

When we have a large data source and the amount of possible items in a filter dropdown is too big, loading all the items in the filter dropdowns on each grid load is not an option.
For example if "Food" dropdown has 1 million items it would be impractical to load them all.
One easy solution would be to use a textbox instead, like we've done for the Person column.

Another option is to use a dropdown with remote search, as done for the Food column, initially only a few items are loaded, and additional items are loaded when we make a search (SearchFunc is used for this). We've set o-nfload on the country dropdown so it won't load each time the grid loads, this is only necessary for editors that get their data from the grid model (Tag.frow set in the grid action).

Country, Chef and Meals also use SearchFunc like "Country" except grid filter parameters (order, selected values) are also sent to the searchFunc so the resulting items are filtered based on the previous selected filters.
So if you select a certain "Food" and a certain "Country" and after search in the "Meals" editor, the result will contain only meals that have rows with the selected "Food" and "Country" (Meals also gets filtered by previously selected meals).
On the initial load only the filter dropdowns items for the selected value(s) are received from the Grid Model.

Demos/Grid/GridFilterRowServerSideDataController.cs

    public async Task<IActionResult> LunchFilterGridLarge(GridParams g, LunchFilter filter)
    {
        var query = mcx.Lunches
            .Include(o => o.Food)
            .Include(o => o.Chef)
            .Include(o => o.Country)
            .Include(o => o.Meals)
            .AsQueryable();

        var filterBuilder = new FilterBuilder<Lunch>();

        setupLunchFilter(filterBuilder, query, filter);

        query = await filterBuilder.ApplyAsync();

        var gmb = new GridModelBuilder<Lunch>(query, g)
        {
            KeyProp = o => o.Id,
            Map = MapToGridModel,
            Tag = filterBuilder.GetGridData()
        };

        return Json(await gmb.EFBuildAsync());
    }

    private void setupLunchFilter(
        FilterBuilder<Lunch> filterBuilder,
        IQueryable<Lunch> query,
        LunchFilter filter)
    {
        filterBuilder.Query = query;
        filterBuilder.Filter = filter;

        filterBuilder
            .StringFilter(nameof(LunchFilter.Person))

            // Food filter dropdown is not using the grid filter query to get data
            // so it is possible to select an item from this filter dropdown
            // that will give us zero results in the grid (if there are other filters set)
            .Add(nameof(LunchFilter.Food), new FilterRule<Lunch> { Query = itm => itm.Food.Id == filter.Food })

            // returning data for the selected filter values
            .Add(nameof(LunchFilter.Country),
            new FilterRule<Lunch>
            {
                Query = itm => itm.Country.Id == filter.Country,

                GetData = async q =>
                {
                    if (filter.Country == null) return null;

                    return (await q.Select(o => o.Country).Where(o => o.Id == filter.Country).Distinct().ToArrayAsync())
                        .Select(o => new KeyContent(o.Id, o.Name));
                }
            })

            .Add(nameof(LunchFilter.Meals),
            new FilterRule<Lunch>
            {
                QueryFunc = () =>
                {
                    var ids = filter.Meals;
                    var count = ids.Count();
                    return itm => itm.Meals.Count(m => ids.Contains(m.Id)) == count;
                },

                GetData = async q =>
                {
                    if (filter.Meals == null) return null;

                    return (await q.SelectMany(o => o.Meals).Where(o => filter.Meals.Contains(o.Id)).Distinct().ToArrayAsync())
                        .OrderBy(o => o.Id)
                        .Select(o => new KeyContent(o.Id, o.Name));
                },

                // get data after "Meals" QueryFunc has been applied to the Query (q)
                SelfQuery = true
            })

            .Add(nameof(LunchFilter.Chef),
            new FilterRule<Lunch>
            {
                Query = itm => itm.Chef.Id == filter.Chef,

                GetData = async q =>
                {
                    if (filter.Chef == null) return null;

                    return (await q.Select(lunch => lunch.Chef)
                        .Where(chef => chef.Id == filter.Chef)
                        .Take(1)
                        .ToArrayAsync())
                        .Select(o => new KeyContent(o.Id, o.FullName));
                }
            });
    }

    public async Task<IActionResult> SearchFood(string term)
    {
        var items = (await mcx.Foods
            .Filter(term, o => o.Name)                
            .Take(10)
            .ToArrayAsync())
            .Select(o => new MealDisplay(o.Id, o.Name, o.Pic));

        return Json(items);
    }

    public async Task<IActionResult> GetFoodsInit(int? v)
    {
        var query = mcx.Foods.OrderBy(o => o.Id);
        var items = await query.Take(3).ToListAsync();
        var selected = await query.FirstOrDefaultAsync(o => o.Id == v);

        if (selected != null && !items.Contains(selected))
        {
            items.Add(selected);
        }

        var lst = new List<MealDisplay> { new MealDisplay("", "any", "pasta.png") };
        lst.AddRange(items.Select(o => new MealDisplay(o.Id, o.Name, o.Pic)));

        return Json(lst);
    }

    public async Task<IActionResult> SearchCountry(LunchFilter filter, string term)
    {
        var query = mcx.Lunches.AsQueryable();
        var fb = new FilterBuilder<Lunch>();

        setupLunchFilter(fb, query, filter);

        query = fb.GetQueryFor(nameof(LunchFilter.Country));

        var items = (await query.Select(o => o.Country)
            .Filter(term, o => o.Name)
            .Distinct()
            .Take(10)
            .ToArrayAsync())
            .Select(o => new KeyContent(o.Id, o.Name));

        return Json(items);
    }

    public async Task<IActionResult> SearchMeals(string term, LunchFilter filter)
    {
        var query = mcx.Lunches.AsQueryable();

        var fb = new FilterBuilder<Lunch>();

        // apply other grid filters in order
        setupLunchFilter(fb, query, filter);

        query = fb.GetQueryFor(nameof(LunchFilter.Meals));

        var data = (await query.SelectMany(o => o.Meals)
            .Filter(term, o => o.Name)
            .Distinct()
            .Take(10)
            .OrderBy(o => o.Id)
            .ToArrayAsync())
            .Select(o => new KeyContent(o.Id, o.Name));

        return Json(data);
    }

    public async Task<IActionResult> SearchChef(string term, LunchFilter fp)
    {
        var query = mcx.Lunches.AsQueryable();

        var fb = new FilterBuilder<Lunch>();

        setupLunchFilter(fb, query, fp);

        query = fb.GetQueryFor(nameof(LunchFilter.Chef));

        var data = (await query.Select(o => o.Chef)
                        .Filter(term, o => o.FirstName, o => o.LastName)
                        .Distinct()
                        .Take(10)
                        .OrderBy(o => o.Id)
                        .ToArrayAsync())
                        .Select(o => new KeyContent(o.Id, o.FullName));

        return Json(data);
    }
    #endregion
}

Comments