How to filter GET query Blazor-Server web app for a small number of records from a million rows table

Question

I have a C# Blazor server web app with Entity Framework Core + a SQL Server database. I need to have a number of fetch/GET filters and want to know the best way to code them in C# and in T-SQL. The database table can have multi-million rows. When we ask for filtered data from this table, we expect no more than 500 rows to be returned.

I am looking for the best / fastest way to get the data returned to the user's browser page -- either using a C# query similar to this one...

// Filter query for the customer.
IEnumerable<Table1> getQuery =
      from recs in _dbContext.Table1()
      where (recs.UID_CUSTOMER == pUID_CUSTOMER)
      select recs;

// Fetch the data 
return getQuery.ToList();

Or...from using stored-procedures in this example.

int customerUID = 7;
FormattableString uspCommand = $"EXEC {storedProcName} @CustomerUID = {customerUID}";

returnValue = _dbContext.Table1.FromSqlInterpolated(sqlCommand).ToList();

Which is a better way?

Answer 1

Fastest way within EF: Use projection to fetch just what you need from the entities.

This code is terrible:

IEnumerable<Table1> getQuery =
    from recs in await this.GetAllCustomers()
    where (recs.UID_CUSTOMER == pUID_CUSTOMER)
    select recs;

... when "GetAllCustomers()" is doing something like:

await _dbContext.Table1!.ToListAsync();

The above is loading ALL rows into memory.

Better would just be:

IEnumerable<Table1> getQuery = await _dbContext.Table1
    .Where (recs.UID_CUSTOMER == pUID_CUSTOMER)
    .ToListAsync();

This tells EF to generate an SQL query to only return the X number of rows you are interested in.

Even better would be to look at what fields from Table1 you need and compose a ViewModel with just those columns. For instance if the Table1 table consists of 50 columns but you only need 10, and/or there are values to be pulled from related tables then leveraging Select will have EF build an efficient SQL to fetch just the columns needed rather than pulling back all 50 columns and/or generating a Cartesian Product (eager loading) or a SELECT N+1 scenario (lazy loading)

IEnumerable<Table1ViewModel> getQuery = await _dbContext.Table1
    .Where (recs => recs.UID_CUSTOMER == pUID_CUSTOMER)
    .Select(recs => new Table1ViewModel
    {
        Id = recs.Id,
        Value1 = recs.Value1,
        // ...
    }).ToListAsync();

From here you can inspect index usage and optimize as needed. It is a good idea to design for sorting and pagination when fetching data to ensure users are getting the most relevant data first and avoiding land mines where "most" data should be a manageable volume, but some edge cases see big, slow result sets being pulled.

For read operations leverage projection. For update operations fetch entities since you will typically be reading individual rows by PK which is extremely fast.