Large dataset & custom data adapter - How can we filter based on the entire dataset, but return a small list?

[Sam Mason]

Hello All,

I would like to show a Syncfusion Datagrid for a very large dataset using the custom data adapter, while providing a very responsive site.

The users will use this grid with its filter bar to find the row of data they are looking for (which will drive several other datagrids that contain related information.

My problem is that the examples provided for the custom data adapter follow this approach:

1. Query the database (EF Core + Linq) and convert the data into a list of objects List<Item>.
2. Use the DataManagerRequest inputs, search, filter, skip, take, or group that list of information.
3. Return the filtered view of the list.

Since my initial list is > 100,000 rows of data, it takes some time (~4 seconds, enough to notice a delay) to load that information into memory!

Is there a method to ​do it in this order instead?

1. Grid observes the various inputs (filters, sorts, page number or the skip/take values)
2. Pass these values into the Linq query that hits the database.
3. Return a much smaller list of values.

Here is my existing code for reference.

Razor Page:



@page "/imv"
@rendermode InteractiveServer
<h3>ImvPage</h3>




<SfGrid  TValue="IMV" AllowPaging="true" AllowFiltering="true">
    <SfDataManager Adaptor="Adaptors.CustomAdaptor" AdaptorInstance="@typeof(ImvDataAdaptor)"></SfDataManager>
    <GridPageSettings PageSize="20"></GridPageSettings>
    <GridFilterSettings Type="Syncfusion.Blazor.Grids.FilterType.FilterBar"></GridFilterSettings>
    <GridColumns>
        <GridColumn Field=@nameof(IMV.IMV_RecordID) HeaderText="ID" IsPrimaryKey="true" Visible="false"></GridColumn>
    	<GridColumn Field=@nameof(IMV.IMV_ItemID) HeaderText="IMV_ItemID"  Width="150">
            <FilterTemplate> 
                <SfAutoComplete TValue="string" TItem="AutoSelectHelper" DataSource="@possibleItemIds" FilterType="Syncfusion.Blazor.DropDowns.FilterType.Contains" @bind-Value="@((context as PredicateModel<string>).Value)">
                       <AutoCompleteFieldSettings Value="SuggestionText"></AutoCompleteFieldSettings>
                </SfAutoComplete>
            </FilterTemplate>
        </GridColumn>
        <GridColumn Field=@nameof(IMV.IMV_MfgItemID) HeaderText="IMV_MfgItemID"  Width="150">
        </GridColumn>
        <GridColumn Field=@nameof(IMV.IMV_VendorID) HeaderText="IMV_VendorID"  Width="150">
        </GridColumn>
        <GridColumn Field=@nameof(IMV.IMV_MfgName) HeaderText="IMV_MfgName"  Width="150">
        </GridColumn>
        <GridColumn Field=@nameof(IMV.IMV_VendorName) HeaderText="IMV_VendorName"  Width="150">
        </GridColumn>
        <GridColumn Field=@nameof(IMV.IMV_LeadTime) HeaderText="IMV_LeadTime"  Width="150">
        </GridColumn>
    </GridColumns>
</SfGrid>

Data Adapter Page:

namespace NS.DataAdaptors
{
    public class ImvDataAdaptor : DataAdaptor
    {
        public IDbContextFactory<MyContext> contextFactory { get; set; }


        public ImvDataAdaptor(IDbContextFactory<MyContext> contextFactory)
        {
            this.contextFactory = contextFactory;
        }


        public override object Read(DataManagerRequest dataManagerRequest, string additionalParam = null)
        {
            var a = 1;
            using var db = contextFactory.CreateDbContext();
    		IEnumerable<IMV> DataSource = db.IMV.ToList(); // --> this is where I return > 100,000 rows of data!
//and below this line is where I filter on the list
    		if (dataManagerRequest.Search != null && dataManagerRequest.Search.Count > 0)
    		{
      // Searching
      DataSource = DataOperations.PerformSearching(DataSource, dataManagerRequest.Search);
    		}
    		if (dataManagerRequest.Sorted != null && dataManagerRequest.Sorted.Count > 0)
    		{
    			// Sorting
    			DataSource = DataOperations.PerformSorting(DataSource, dataManagerRequest.Sorted);
    		}
    		if (dataManagerRequest.Where != null && dataManagerRequest.Where.Count > 0)
    		{
    			// Filtering
    			DataSource = DataOperations.PerformFiltering(DataSource, dataManagerRequest.Where, dataManagerRequest.Where[0].Operator);
    		}
    		int count = DataSource.Cast<IMV>().Count();
    		if (dataManagerRequest.Skip != 0)
    		{
    			//Paging
    			DataSource = DataOperations.PerformSkip(DataSource, dataManagerRequest.Skip);
    		}
    		if (dataManagerRequest.Take != 0)
    		{
    			DataSource = DataOperations.PerformTake(DataSource, dataManagerRequest.Take);
    		}
    		DataResult DataObject = new DataResult();
    		if (dataManagerRequest.Group != null)
    		{
    			IEnumerable ResultData = DataSource.ToList();
    			// Grouping
    			foreach (var group in dataManagerRequest.Group)
    			{
        ResultData = DataUtil.Group<IMV>(ResultData, group, dataManagerRequest.Aggregates, 0, dataManagerRequest.GroupByFormatter);
    			}
    			DataObject.Result = ResultData;
    			DataObject.Count = count;
    			return dataManagerRequest.RequiresCounts ? DataObject : (object)ResultData;
    		}
    		return dataManagerRequest.RequiresCounts ? new DataResult() { Result = DataSource, Count = count } : (object)DataSource;
        }


    }
}

[Naveen Palanivel]

Hi Sam Mason,

Based on your requirements, we suggest using the approach documented in our resources for SQL data binding with a custom adaptor to achieve your needs. Please refer to the following documentation for detailed information:

https://blazor.syncfusion.com/documentation/datagrid/data-binding#sql-server-data-bindingsql-client

If you need filtering, sorting, and search functionality in the DataManagerRequest, you can achieve your requirements by leveraging the details provided there.

API Reference: https://help.syncfusion.com/cr/blazor/Syncfusion.Blazor.DataManagerRequest.html

Regards,
Naveen

[Sam Mason]

Thanks for the input Naveen!

I came up with this fix, which is pretty close to what I was looking for:

public class GridCustomAdaptor : DataAdaptor
{
    private IDbContextFactory<MyContext> contextFactory { get; set; }
    public GridCustomAdaptor(IDbContextFactory<MyContext> db)
    {
        this.contextFactory = db;
    }
    public override object Read(DataManagerRequest dm, string key = null)
    {
        using var SfGridDataContext = contextFactory.CreateDbContext();


        //Step 1: Base Query
        IQueryable<IMV> query = SfGridDataContext.IMV;


        //Step 2: Add Where Clauses
        if(dm.Where != null && dm.Where.Count > 0)
        {
            query = QueryableOperation.PerformFiltering(query, dm.Where, dm.Where[0].Operator);
        }


        //Step 3: Add Search Clauses
        if(dm.Search != null && dm.Search.Count > 0)
        {
            query = QueryableOperation.PerformSearching(query, dm.Search);
        }


        //Step 4: Add Sorting Clauses
        if(dm.Sorted != null && dm.Sorted.Count > 0)
        {
            query = QueryableOperation.PerformSorting(query, dm.Sorted);
        }


        //Step 5: Skip and Take
        if(dm.Skip != 0)
        {
            query = QueryableOperation.PerformSkip(query, dm.Skip);
        }
        if(dm.Take != 0)
        {
            query = QueryableOperation.PerformTake(query, dm.Take);
        }


        DataResult dataResult = new DataResult();
        List<IMV> datasource = query.ToList();


        if(dm.Group != null)
        {
            IEnumerable groupResult = datasource.ToList();
            foreach(var group in dm.Group)
            {
                groupResult = DataUtil.Group<IMV>(groupResult, group, dm.Aggregates, 0, dm.GroupByFormatter);
            }
            dataResult.Result = groupResult;
            dataResult.Count = datasource.Count();
            return dm.RequiresCounts ? dataResult : (object)groupResult;
        }


        return dm.RequiresCounts ? new DataResult() { Result = datasource, Count = datasource.Count() } : (object)datasource;






        //IEnumerable<IMV> DataSource = SfGridDataContext.IMV.ToList();
   		//if (dm.Search != null && dm.Search.Count > 0)
   		//{
   		//  // Searching
   		//  DataSource = DataOperations.PerformSearching(DataSource, dm.Search);
   		//}
   		//if (dm.Sorted != null && dm.Sorted.Count > 0)
   		//{
   		//	// Sorting
   		//	DataSource = DataOperations.PerformSorting(DataSource, dm.Sorted);
   		//}
   		//if (dm.Where != null && dm.Where.Count > 0)
   		//{
   		//	// Filtering
   		//	DataSource = DataOperations.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator);
   		//}
   		//int count = DataSource.Cast<IMV>().Count();
   		//if (dm.Skip != 0)
   		//{
   		//	//Paging
   		//	DataSource = DataOperations.PerformSkip(DataSource, dm.Skip);
   		//}
   		//if (dm.Take != 0)
   		//{
   		//	DataSource = DataOperations.PerformTake(DataSource, dm.Take);
   		//}
   		//DataResult DataObject = new DataResult();
   		//if (dm.Group != null)
   		//{
   		//	IEnumerable ResultData = DataSource.ToList();
   		//	// Grouping
   		//	foreach (var group in dm.Group)
   		//	{
   		//	    ResultData = DataUtil.Group<IMV>(ResultData, group, dm.Aggregates, 0, dm.GroupByFormatter);
   		//	}
   		//	DataObject.Result = ResultData;
   		//	DataObject.Count = count;
   		//	return dm.RequiresCounts ? DataObject : (object)ResultData;
   		//}
   		//return dm.RequiresCounts ? new DataResult() { Result = DataSource, Count = count } : (object)DataSource;
   	}
}

[Naveen Palanivel]

Hi Sam.

We suspect that you have found a solution. Please get back to us if you still face any difficulties with your requirements.

Regards,
Naveen