MudBlazor DataGrid loads entire database when used with Entity Framework

Question

I'm using a MudBlazor DataGrid to display records from SQL Server, which I am accessing via Entity Framework. The table I'm querying might contain anywhere from several thousand to several million rows.

On small test data sets, the DataGrid performs just fine, however when I connect it to production-scale test data, the performance becomes extremely slow, taking roughly the same time a SELECT * FROM MyTable takes on SQL Server to load or change pages. The UI on the client side also becomes terribly slow (which suggests to me that the server may be transmitting a lot of data to the client and using up lots of memory).

My DataGrid looks as follows:

<MudDataGrid Items="MyContext.MyTable"
             FixedHeader="true" FixedFooter="true" Hover="true"
             SortMode="SortMode.Single" Filterable="true"
             FilterMode="DataGridFilterMode.ColumnFilterMenu"
             RowClick="NavigateToWork" T="IMyRecord" Hideable="true"
             FilterCaseSensitivity="DataGridFilterCaseSensitivity.CaseInsensitive"
             DragDropColumnReordering="true" ColumnsPanelReordering="true"
             ColumnResizeMode="ResizeMode.Column"
             ShowMenuIcon="true">

    <Columns>
        <PropertyColumn Property="@(x => x.Id)" />
        @* etc *@
    </Columns>

    <PagerContent>
        <MudDataGridPager T="IMyRecord" />
    </PagerContent>
</MudDataGrid>

Is there something I'm missing to specify that it shouldn't load the whole table, or does the DataGrid internally use AsEnumerable or ToList on the IQueryable I pass to it and force the download somewhere beyond my control?

Answer 1

when I connect it to production-scale test data, the performance becomes extremely slow, taking roughly the same time a SELECT * FROM MyTable takes on the SQL server to load or change pages.

That's because that's what it's doing.

You need to provide a delegate to the ServerData Parameter on MudDataGrid to let the control make paged data requests into the data pipeline. GridState is the request, containing the page data [and sort and filter data if specified]. GridData is the returned result, containing the page dataset and the total unpaged record count.

Here's a demo using the MudBlazor template Weather page.

First the data pipeline - in this case a simple singleton data provider. The important bit is GetDataAsync which matches the delegate signature of ServerData. It just gets the data page from the data source.

using MudBlazor;

namespace SO78621960.Components;

public class WeatherForecast
{
    public DateOnly Date { get; set; }
    public int TemperatureC { get; set; }
    public string? Summary { get; set; }
    public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);
}

public class WeatherProvider
{
    private List<WeatherForecast> _forecasts;

    private WeatherProvider()
    {
        _forecasts = InitializeData();
    }

    private List<WeatherForecast> InitializeData()
    {
        var startDate = DateOnly.FromDateTime(DateTime.Now);
        var summaries = new[] { "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching" };
        return Enumerable.Range(1, 50000).Select(index => new WeatherForecast
        {
            Date = startDate.AddDays(index),
            TemperatureC = Random.Shared.Next(-20, 55),
            Summary = summaries[Random.Shared.Next(summaries.Length)]
        }).ToList();
    }

    private static WeatherProvider? _instance;

    public static WeatherProvider GetInstance()
    {
        if (_instance == null)
            _instance = new WeatherProvider();
        
        return _instance;
    }

    // This code would normally query the DbSet in the DbContext instance to minimize the data requested
    public static async Task<GridData<WeatherForecast>> GetDataAsync(GridState<WeatherForecast> request)
    {
        // Fake async behaviour
        await Task.Delay(100);

        var instance = WeatherProvider.GetInstance();

        // construct a Query
        // may also contain sorting and filtering
        var startIndex = (request.Page) * request.PageSize;

        var query = instance._forecasts
            .Skip(startIndex)
            .Take(request.PageSize);

        // execute the queries - async in a live DbContext pipeline
        var items = query.ToList();
        var totalItems = instance._forecasts.Count;

        // construct a GridData object to return
        return new GridData<WeatherForecast>() { Items = items, TotalItems = totalItems };
        
    }
}

And then the demo page:

@page "/weather"

<PageTitle>Weather</PageTitle>

<MudText Typo="Typo.h3" GutterBottom="true">Weather forecast</MudText>
<MudText Class="mb-8">This component demonstrates fetching data from the server.</MudText>

<MudDataGrid T="WeatherForecast" ServerData="WeatherProvider.GetDataAsync">
    <Columns>
        <PropertyColumn Property="x => x.Date" />
        <PropertyColumn Property="x => x.TemperatureC" />
        <PropertyColumn Property="x => x.TemperatureF" />
        <PropertyColumn Property="x => x.Summary" />
    </Columns>
    <PagerContent>
        <MudDataGridPager T="WeatherForecast" />
    </PagerContent>
</MudDataGrid>

@code {
}

[Polite] Two broader points:

1. You should never make unrestricted queries against large data sets. It's a recipe for poor performance. Only get what you can display.

2. If your live dataset has 1 million records, your test data set should also have 1 million records.