Server-side data with ASP.NET Core

This tutorial shows how to wire Handsontable’s dataProvider plugin to an ASP.NET Core Web API backend. The backend handles pagination, sorting, and filtering on the server. The frontend displays results and sends every edit back to the API.

Overview

Difficulty: Intermediate
Time: ~30 minutes
Backend: .NET 8 SDK, ASP.NET Core MVC controllers, EF Core 8 with SQLite

What You’ll Build

An Order Management grid that:

• Loads orders page by page from an ASP.NET Core API
• Sorts orders by any column on the server
• Filters orders by column value on the server
• Creates, updates, and deletes rows via dedicated collection endpoints
• Uses ASP.NET Core’s default camelCase JSON so Handsontable’s column keys match the API without extra configuration

Before you begin

• .NET 8 SDK installed
• Node.js 18 or later and npm installed

No database server is required — the backend stores data in a local SQLite file.

1. Create the project and add EF Core

   Create a Web API project that uses MVC controllers:

   dotnet new webapi --use-controllers -n OrdersApi
   cd OrdersApi

   Add the EF Core SQLite provider:

   dotnet add package Microsoft.EntityFrameworkCore.Sqlite
   dotnet add package Microsoft.EntityFrameworkCore.Design

   Why these choices?

   • --use-controllers scaffolds the project with [ApiController] MVC controllers instead of Minimal API endpoints. Controllers group the pagination, sorting, filtering, and batch-CRUD logic this recipe needs into one typed class, instead of several app.MapGet/app.MapPost lambdas in Program.cs.
   • SQLite requires no separate database server or Docker setup — the whole backend runs with dotnet run. Microsoft.EntityFrameworkCore.Design adds the tooling EF Core needs even though this recipe uses EnsureCreated() instead of migrations.

2. Define the Order entity and the DbContext

   Create Order.cs:

   C#

   namespace OrdersApi.Models;
   
   public class Order
   {
       public int Id { get; set; }
       public string OrderNumber { get; set; } = string.Empty;
       public string Customer { get; set; } = string.Empty;
       public string Status { get; set; } = "pending";
       public decimal Total { get; set; }
       public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
   }

   Create AppDbContext.cs:

   C#

   using Microsoft.EntityFrameworkCore;
   using OrdersApi.Models;
   
   namespace OrdersApi.Data;
   
   public class AppDbContext : DbContext
   {
       public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
       {
       }
   
       public DbSet<Order> Orders => Set<Order>();
   
       protected override void OnModelCreating(ModelBuilder modelBuilder)
       {
           // SQLite has no native decimal type. Without this conversion, Total is
           // stored as TEXT, and ORDER BY and range filters (gt/gte/lt/lte) on it
           // sort lexicographically instead of numerically.
           modelBuilder.Entity<Order>()
               .Property(o => o.Total)
               .HasConversion<double>();
       }
   }

   What’s happening:

   • The primary key Id is auto-incremented by SQLite. It becomes the rowId value on the Handsontable side.
   • SQLite has no native decimal column type — EF Core stores decimal properties as TEXT by default, which sorts and compares lexicographically instead of numerically. HasConversion<double>() on Total stores it as a real number instead, so sorting by total and filtering with gt/gte/lt/lte behave correctly. This conversion is SQLite-specific — PostgreSQL or SQL Server don’t need it.

3. Seed the database

   Create DbInitializer.cs:

   C#

   using OrdersApi.Models;
   
   namespace OrdersApi.Data;
   
   public static class DbInitializer
   {
       private static readonly string[] Statuses = { "pending", "paid", "shipped", "delivered", "cancelled" };
   
       private static readonly string[] Customers =
       {
           "Ana García", "James Okafor", "Li Wei", "Marta Nowak", "Diego Fernández",
           "Priya Sharma", "Tom Becker", "Fatima Al-Sayed", "Noah Kim", "Elena Rossi",
       };
   
       // Seeds 50 orders so pagination, sorting, and filtering are meaningful from
       // the first load. Guarded so re-running the app doesn't duplicate rows.
       public static void Seed(AppDbContext db)
       {
           if (db.Orders.Any())
           {
               return;
           }
   
           var random = new Random(42);
           var orders = new List<Order>();
   
           for (var i = 1; i <= 50; i++)
           {
               orders.Add(new Order
               {
                   OrderNumber = $"ORD-{1000 + i}",
                   Customer = Customers[random.Next(Customers.Length)],
                   Status = Statuses[random.Next(Statuses.Length)],
                   Total = Math.Round((decimal)(random.NextDouble() * 480 + 20), 2),
                   CreatedAt = DateTime.UtcNow.AddDays(-random.Next(1, 180)),
               });
           }
   
           db.Orders.AddRange(orders);
           db.SaveChanges();
       }
   }

   What’s happening:

   • The seed script inserts 50 orders across realistic statuses (pending, paid, shipped, delivered, cancelled). It checks whether data already exists, so restarting the app doesn’t duplicate rows.
   • This recipe calls Database.EnsureCreated() in Program.cs (Step 4) instead of running EF Core migrations. EnsureCreated() creates the schema directly from the model, which is enough for a tutorial. For a production app, use dotnet ef migrations add and Database.Migrate() instead, so schema changes are versioned.

4. Wire up Program.cs

   Replace the generated Program.cs with:

   C#

   using Microsoft.EntityFrameworkCore;
   using OrdersApi.Data;
   using OrdersApi.Services;
   
   var builder = WebApplication.CreateBuilder(args);
   
   builder.Services.AddControllers();
   builder.Services.AddDbContext<AppDbContext>(options =>
       options.UseSqlite("Data Source=orders.db"));
   builder.Services.AddScoped<OrdersService>();
   
   builder.Services.AddCors(options =>
   {
       options.AddPolicy("AllowFrontend", policy =>
           policy.WithOrigins("http://localhost:5173")
               .AllowAnyHeader()
               .AllowAnyMethod());
   });
   
   var app = builder.Build();
   
   // General ASP.NET Core middleware order: CORS runs after routing and before
   // authentication/authorization. Keep this placement even without auth yet, so
   // adding UseAuthentication/UseAuthorization later doesn't reject preflight
   // requests.
   app.UseCors("AllowFrontend");
   app.MapControllers();
   
   using (var scope = app.Services.CreateScope())
   {
       var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
       db.Database.EnsureCreated();
       DbInitializer.Seed(db);
   }
   
   app.Run();

   What’s happening:

   • AddDbContext<AppDbContext> registers the database connection as a scoped service, so each request gets its own DbContext instance.
   • AddScoped<OrdersService> registers the service built in Step 8.
   • The using (var scope = ...) block runs once at startup, before app.Run(), to create the SQLite file and seed it.

   The resulting routes, all under /api/orders, are:

   Method	URL	Handsontable callback
   GET	/api/orders	fetchRows
   POST	/api/orders/create_rows	onRowsCreate
   PATCH	/api/orders/update_rows	onRowsUpdate
   DELETE	/api/orders/remove_rows	onRowsRemove

5. Configure CORS

   CORS is registered in Program.cs (Step 4). This step explains why.

   What’s happening:

   • AddCors with AllowFrontend permits requests from http://localhost:5173, the default Vite dev server port. Without it, the browser blocks every request from the frontend before ASP.NET Core sees it.
   • app.UseCors("AllowFrontend") is registered before app.MapControllers() to follow the general ASP.NET Core middleware order — CORS runs after routing and before authentication/authorization. This project has neither an explicit UseRouting call nor any authentication, so swapping these two specific lines wouldn’t break anything here. The convention still matters the moment you add UseAuthentication/UseAuthorization, because CORS has to run before them so a preflight OPTIONS request never gets rejected by an auth check.

   Production note: Replace http://localhost:5173 with your deployed frontend’s domain. If you need cookies or an Authorization header to travel with cross-origin requests, call AllowCredentials() on the policy and list explicit origins — ASP.NET Core throws at startup if you combine AllowCredentials() with AllowAnyOrigin().

6. Decide on the case convention

   ASP.NET Core Web API projects serialize JSON with JsonSerializerDefaults.Web by default, which camelCases every property: the C# property OrderNumber becomes orderNumber in the response, and an incoming { "orderNumber": "ORD-1001" } body binds to OrderNumber because property-name matching is case-insensitive. This means Handsontable’s column data keys can use the same camelCase names as the JSON payload with no extra configuration on either side.

   The pitfall: overriding JsonSerializerOptions.PropertyNamingPolicy to null — for example, to match a legacy client that expects PascalCase — changes every response key at once. If the Handsontable columns still declare data: 'orderNumber', the grid renders empty cells because the server now returns OrderNumber.

   Why this matters for the whitelist dictionaries in Step 8: the sort and filter column whitelists must be keyed by the wire-format name (orderNumber), not the C# property name (OrderNumber), because that’s the string Handsontable actually sends in the query string.

7. Bind Handsontable’s query parameters

   Create OrdersQuery.cs:

   C#

   namespace OrdersApi.Models;
   
   public class SortDto
   {
       public string? Prop { get; set; }
       public string? Order { get; set; }
   }
   
   public class FilterDto
   {
       public string Prop { get; set; } = string.Empty;
       public string Condition { get; set; } = string.Empty;
       public string? Value { get; set; }
   }
   
   // Bound from the query string via [FromQuery]. ASP.NET Core's model binder
   // reads nested properties from dot notation (sort.prop, sort.order) and
   // indexed collections from filters[N].prop / .condition / .value -- no
   // custom binder or [FromQuery] attribute per property is needed.
   public class OrdersQuery
   {
       public int Page { get; set; } = 1;
       public int PageSize { get; set; } = 10;
       public SortDto? Sort { get; set; }
       public List<FilterDto> Filters { get; set; } = new();
   }

   Create Payloads.cs:

   C#

   using System.Text.Json;
   
   namespace OrdersApi.Models;
   
   // No Id or CreatedAt property -- the type system blocks clients from setting
   // system-managed fields, instead of filtering them out of a dictionary at
   // runtime.
   public class OrderCreateDto
   {
       public string OrderNumber { get; set; } = string.Empty;
       public string Customer { get; set; } = string.Empty;
       public string Status { get; set; } = "pending";
       public decimal Total { get; set; }
   }
   
   public class CreateRowsRequest
   {
       public List<OrderCreateDto> Rows { get; set; } = new();
   }
   
   public class UpdateRowDto
   {
       public int Id { get; set; }
       public Dictionary<string, JsonElement> Changes { get; set; } = new();
   }
   
   public class UpdateRowsRequest
   {
       public List<UpdateRowDto> Rows { get; set; } = new();
   }
   
   public class RemoveRowsRequest
   {
       public List<int> RowIds { get; set; } = new();
   }

   What’s happening:

   Backends built on Express, Rails, or PHP typically parse Handsontable’s filters from bracket-notation query parameters: filters[0][prop]=status. ASP.NET Core’s default model binder doesn’t understand that bracket syntax for binding a request into a C# object. Instead, it binds nested properties from dot notation and collections of complex types from indexed dot notation:

   ?page=1&pageSize=10&sort.prop=total&sort.order=desc&filters[0].prop=status&filters[0].condition=eq&filters[0].value=shipped

   Given [FromQuery] OrdersQuery query, sort.prop and sort.order populate query.Sort, and each filters[N].* triple populates one FilterDto in query.Filters — all without a custom model binder or per-property [FromQuery] attributes. The frontend buildUrl function (Step 10) targets this format, which is the only part of this recipe’s frontend code that differs from the Express or Rails recipes’ buildUrl.

   OrderCreateDto has no Id or CreatedAt property. Where a dynamically typed backend blocks system-managed fields by filtering a dictionary at runtime, here the type system does it structurally: there’s no property for a client to set.

8. Build the service

   Create OrdersService.cs:

   C#

   using System.Globalization;
   using System.Text.Json;
   using Microsoft.EntityFrameworkCore;
   using OrdersApi.Data;
   using OrdersApi.Models;
   
   namespace OrdersApi.Services;
   
   public class OrdersService
   {
       // Maps the wire-format column names Handsontable sends (camelCase) to the
       // C# property names EF.Property<T> needs. Any prop not in this dictionary
       // is rejected instead of reaching a query -- this is the whitelist.
       private static readonly Dictionary<string, string> ColumnMap = new()
       {
           ["orderNumber"] = nameof(Order.OrderNumber),
           ["customer"] = nameof(Order.Customer),
           ["status"] = nameof(Order.Status),
           ["total"] = nameof(Order.Total),
           ["createdAt"] = nameof(Order.CreatedAt),
       };
   
       private static readonly HashSet<string> StringColumns = new() { "orderNumber", "customer", "status" };
       private static readonly HashSet<string> DateColumns = new() { "createdAt" };
   
       // System-managed fields (id, createdAt) are deliberately excluded so
       // update_rows can never overwrite them.
       private static readonly HashSet<string> EditableColumns = new() { "orderNumber", "customer", "status", "total" };
   
       // Caps how many rows a single request can pull, so a client-supplied
       // pageSize can't force the server to materialize the whole table.
       private const int MaxPageSize = 100;
   
       private readonly AppDbContext _db;
   
       public OrdersService(AppDbContext db)
       {
           _db = db;
       }
   
       public async Task<(List<Order> Rows, int TotalRows)> GetOrdersAsync(OrdersQuery query, CancellationToken cancellationToken)
       {
           var page = Math.Max(query.Page, 1);
           var pageSize = Math.Clamp(query.PageSize, 1, MaxPageSize);
   
           var filtered = ApplyFilters(_db.Orders.AsNoTracking(), query.Filters);
           var totalRows = await filtered.CountAsync(cancellationToken);
   
           var rows = await ApplySort(filtered, query.Sort)
               .Skip((page - 1) * pageSize)
               .Take(pageSize)
               .ToListAsync(cancellationToken);
   
           return (rows, totalRows);
       }
   
       public async Task<List<Order>> CreateRowsAsync(List<OrderCreateDto> rows, CancellationToken cancellationToken)
       {
           await using var transaction = await _db.Database.BeginTransactionAsync(cancellationToken);
   
           var created = rows.Select(row => new Order
           {
               OrderNumber = row.OrderNumber,
               Customer = row.Customer,
               Status = row.Status,
               Total = row.Total,
               CreatedAt = DateTime.UtcNow,
           }).ToList();
   
           _db.Orders.AddRange(created);
           await _db.SaveChangesAsync(cancellationToken);
           await transaction.CommitAsync(cancellationToken);
   
           // Returned with their database-assigned Id so dataProvider can replace
           // the client's placeholder rows.
           return created;
       }
   
       public async Task<List<Order>> UpdateRowsAsync(List<UpdateRowDto> rows, CancellationToken cancellationToken)
       {
           await using var transaction = await _db.Database.BeginTransactionAsync(cancellationToken);
           var updated = new List<Order>();
   
           foreach (var row in rows)
           {
               var order = await _db.Orders.FindAsync(new object[] { row.Id }, cancellationToken);
               if (order is null)
               {
                   continue;
               }
   
               ApplyChanges(order, row.Changes);
               updated.Add(order);
           }
   
           await _db.SaveChangesAsync(cancellationToken);
           await transaction.CommitAsync(cancellationToken);
   
           return updated;
       }
   
       public async Task RemoveRowsAsync(List<int> rowIds, CancellationToken cancellationToken)
       {
           // A single DELETE ... WHERE Id IN (...) instead of one round trip per row.
           await _db.Orders
               .Where(o => rowIds.Contains(o.Id))
               .ExecuteDeleteAsync(cancellationToken);
       }
   
       private static void ApplyChanges(Order order, Dictionary<string, JsonElement> changes)
       {
           foreach (var (key, element) in changes)
           {
               if (!EditableColumns.Contains(key))
               {
                   continue;
               }
   
               switch (key)
               {
                   case "orderNumber":
                       order.OrderNumber = element.GetString() ?? order.OrderNumber;
                       break;
                   case "customer":
                       order.Customer = element.GetString() ?? order.Customer;
                       break;
                   case "status":
                       order.Status = element.GetString() ?? order.Status;
                       break;
                   case "total":
                       // A cleared cell sends JSON null -- GetDecimal() throws on
                       // anything that isn't a JSON number, so check first instead
                       // of leaving Total unchanged from a bad request.
                       if (element.ValueKind == JsonValueKind.Number)
                       {
                           order.Total = element.GetDecimal();
                       }
                       break;
               }
           }
       }
   
       private static IQueryable<Order> ApplySort(IQueryable<Order> query, SortDto? sort)
       {
           if (sort?.Prop == null || !ColumnMap.TryGetValue(sort.Prop, out var property))
           {
               return query.OrderByDescending(o => o.CreatedAt);
           }
   
           var descending = string.Equals(sort.Order, "desc", StringComparison.OrdinalIgnoreCase);
   
           return descending
               ? query.OrderByDescending(o => EF.Property<object>(o, property))
               : query.OrderBy(o => EF.Property<object>(o, property));
       }
   
       private static IQueryable<Order> ApplyFilters(IQueryable<Order> query, List<FilterDto> filters)
       {
           foreach (var filter in filters)
           {
               if (!ColumnMap.TryGetValue(filter.Prop, out var property))
               {
                   continue; // unknown column name -- ignored instead of trusted
               }
   
               if (StringColumns.Contains(filter.Prop))
               {
                   query = ApplyStringFilter(query, property, filter);
               }
               else if (DateColumns.Contains(filter.Prop))
               {
                   query = ApplyDateFilter(query, property, filter);
               }
               else
               {
                   query = ApplyNumericFilter(query, property, filter);
               }
           }
   
           return query;
       }
   
       private static IQueryable<Order> ApplyStringFilter(IQueryable<Order> query, string property, FilterDto filter)
       {
           var value = filter.Value ?? string.Empty;
           var likeValue = EscapeLike(value);
   
           return filter.Condition switch
           {
               "eq" => query.Where(o => EF.Property<string>(o, property) == value),
               "neq" => query.Where(o => EF.Property<string>(o, property) != value),
               "contains" => query.Where(o => EF.Functions.Like(EF.Property<string>(o, property), $"%{likeValue}%", "\\")),
               "not_contains" => query.Where(o => !EF.Functions.Like(EF.Property<string>(o, property), $"%{likeValue}%", "\\")),
               "begins_with" => query.Where(o => EF.Functions.Like(EF.Property<string>(o, property), $"{likeValue}%", "\\")),
               "ends_with" => query.Where(o => EF.Functions.Like(EF.Property<string>(o, property), $"%{likeValue}", "\\")),
               "empty" => query.Where(o => EF.Property<string>(o, property) == null || EF.Property<string>(o, property) == string.Empty),
               "not_empty" => query.Where(o => EF.Property<string>(o, property) != null && EF.Property<string>(o, property) != string.Empty),
               _ => query,
           };
       }
   
       private static IQueryable<Order> ApplyDateFilter(IQueryable<Order> query, string property, FilterDto filter)
       {
           if (filter.Condition is "empty" or "not_empty")
           {
               // CreatedAt is non-nullable -- there's no empty date state to check.
               return query;
           }
   
           if (!DateTime.TryParse(filter.Value, CultureInfo.InvariantCulture, DateTimeStyles.None, out var value))
           {
               return query; // malformed date input -- ignored rather than thrown
           }
   
           return filter.Condition switch
           {
               "eq" => query.Where(o => EF.Property<DateTime>(o, property).Date == value.Date),
               "neq" => query.Where(o => EF.Property<DateTime>(o, property).Date != value.Date),
               "gt" => query.Where(o => EF.Property<DateTime>(o, property) > value),
               "gte" => query.Where(o => EF.Property<DateTime>(o, property) >= value),
               "lt" => query.Where(o => EF.Property<DateTime>(o, property) < value),
               "lte" => query.Where(o => EF.Property<DateTime>(o, property) <= value),
               _ => query,
           };
       }
   
       private static IQueryable<Order> ApplyNumericFilter(IQueryable<Order> query, string property, FilterDto filter)
       {
           if (filter.Condition is "empty" or "not_empty")
           {
               // Total is non-nullable -- there's no empty numeric state to check.
               return query;
           }
   
           if (!decimal.TryParse(filter.Value, NumberStyles.Number, CultureInfo.InvariantCulture, out var value))
           {
               return query; // malformed numeric input -- ignored rather than thrown
           }
   
           return filter.Condition switch
           {
               "eq" => query.Where(o => EF.Property<decimal>(o, property) == value),
               "neq" => query.Where(o => EF.Property<decimal>(o, property) != value),
               "gt" => query.Where(o => EF.Property<decimal>(o, property) > value),
               "gte" => query.Where(o => EF.Property<decimal>(o, property) >= value),
               "lt" => query.Where(o => EF.Property<decimal>(o, property) < value),
               "lte" => query.Where(o => EF.Property<decimal>(o, property) <= value),
               _ => query,
           };
       }
   
       // Escapes LIKE metacharacters in user input so they're treated as literals,
       // not wildcards. Paired with the ESCAPE '\' clause passed to EF.Functions.Like.
       private static string EscapeLike(string value) =>
           value.Replace("\\", "\\\\").Replace("%", "\\%").Replace("_", "\\_");
   }

   Whitelist sortable and filterable columns

   ColumnMap maps every wire-format column name Handsontable can send to the matching C# property name. ApplySort and ApplyFilters both call ColumnMap.TryGetValue before touching the query — an unrecognized column name is silently ignored instead of reaching EF.Property<T>. LINQ parameterizes filter values automatically, but a column name that comes from client input still needs validation before it becomes part of a dynamically built expression.

   Sort helper

   ApplySort reads query.Sort?.Prop. If it’s missing or not in ColumnMap, the query falls back to CreatedAt DESC. Otherwise it calls EF.Property<object>(o, property) inside OrderBy/OrderByDescending so the property to sort by is chosen at runtime, from a name that already passed the whitelist check.

   Filter helper

   ApplyFilters loops over query.Filters and dispatches each one to ApplyStringFilter, ApplyDateFilter, or ApplyNumericFilter, based on whether the column is in StringColumns, DateColumns, or neither. This three-way split matters because EF Core is strongly typed: calling EF.Property<decimal> against a DateTime column throws, so each column’s filter values must be parsed and compared as the type that column actually is.

   • String columns (orderNumber, customer, status) support eq, neq, contains, not_contains, begins_with, ends_with, empty, and not_empty. contains/begins_with/ends_with use EF.Functions.Like with an explicit ESCAPE '\' clause, and EscapeLike escapes %, _, and \ in the user-supplied value first, so those characters are matched literally instead of acting as LIKE wildcards.
   • The date column (createdAt) supports eq, neq, gt, gte, lt, and lte. DateTime.TryParse — with CultureInfo.InvariantCulture explicitly, so the parse doesn’t depend on the server’s OS culture — guards against malformed date input. eq/neq compare .Date on both sides so a filter value of a single day matches every CreatedAt timestamp on that day, not just an exact-to-the-second match.
   • The numeric column (total) supports eq, neq, gt, gte, lt, and lte. empty/not_empty are rejected for both total and createdAt because they’re non-nullable columns — there’s no empty state to check. decimal.TryParse — also pinned to CultureInfo.InvariantCulture — guards against malformed numeric input; a value that doesn’t parse is ignored rather than throwing a 500. Without the invariant culture, a value like 142.5 fails to parse on a server whose default culture uses a comma as the decimal separator, and the filter would silently no-op.
   • Handsontable’s Filters plugin offers more conditions than this whitelist covers — for example, between/not_between on total, and date-specific conditions like date_before/date_after on createdAt. Selecting one of those in the UI produces a filter the server doesn’t recognize, so it’s ignored and the grid shows unfiltered results. Extending ApplyNumericFilter/ApplyDateFilter with more case arms is a natural next step; this recipe sticks to the condition set the other server-side recipes support.
   • Multiple filters combine with AND, because each call reassigns query to a further-restricted IQueryable. dataProvider doesn’t send OR groups by default.

   Pagination

   GetOrdersAsync runs CountAsync() on the filtered-but-unsorted query to get totalRows, then applies sorting and Skip/Take for the current page. Handsontable sends a 1-based page index, so Skip uses (page - 1) * pageSize. pageSize is clamped to MaxPageSize (100) with Math.Clamp, so a client can’t force the server to materialize the entire table in one request.

   Batch CRUD

   • CreateRowsAsync builds new Order entities from OrderCreateDto, inserts them inside a transaction, and returns them with their database-assigned Id. dataProvider uses the returned rows to replace its client-side placeholder IDs.
   • UpdateRowsAsync loads each order by Id and applies only the keys present in EditableColumns from the Changes dictionary — id and createdAt are never in that set, so they can’t be overwritten even if a client sends them. The total case also checks element.ValueKind == JsonValueKind.Number before calling GetDecimal() — a cleared cell sends JSON null, and GetDecimal() throws on anything that isn’t a JSON number.
   • RemoveRowsAsync calls EF Core’s ExecuteDeleteAsync(), which issues a single DELETE FROM Orders WHERE Id IN (...) instead of loading and deleting each row individually.

9. Create the controller

   Create OrdersController.cs:

   C#

   using Microsoft.AspNetCore.Mvc;
   using OrdersApi.Models;
   using OrdersApi.Services;
   
   namespace OrdersApi.Controllers;
   
   [ApiController]
   [Route("api/orders")]
   public class OrdersController : ControllerBase
   {
       private readonly OrdersService _service;
   
       public OrdersController(OrdersService service)
       {
           _service = service;
       }
   
       [HttpGet]
       public async Task<IActionResult> GetOrders([FromQuery] OrdersQuery query, CancellationToken cancellationToken)
       {
           var (rows, totalRows) = await _service.GetOrdersAsync(query, cancellationToken);
   
           return Ok(new { rows, totalRows });
       }
   
       [HttpPost("create_rows")]
       public async Task<IActionResult> CreateRows([FromBody] CreateRowsRequest request, CancellationToken cancellationToken)
       {
           var rows = await _service.CreateRowsAsync(request.Rows, cancellationToken);
   
           return StatusCode(StatusCodes.Status201Created, new { rows });
       }
   
       [HttpPatch("update_rows")]
       public async Task<IActionResult> UpdateRows([FromBody] UpdateRowsRequest request, CancellationToken cancellationToken)
       {
           var rows = await _service.UpdateRowsAsync(request.Rows, cancellationToken);
   
           return Ok(new { rows });
       }
   
       [HttpDelete("remove_rows")]
       public async Task<IActionResult> RemoveRows([FromBody] RemoveRowsRequest request, CancellationToken cancellationToken)
       {
           await _service.RemoveRowsAsync(request.RowIds, cancellationToken);
   
           return NoContent();
       }
   }

   This thin controller has no business logic of its own — every method delegates to OrdersService and maps the result to an HTTP response: 201 Created for new rows, 200 OK with the updated rows for edits, and 204 No Content for deletes.

   Antiforgery in API projects: dotnet new webapi --use-controllers scaffolds a project with no antiforgery validation enabled by default — AddAntiforgery and [ValidateAntiForgeryToken] are opt-in, and only matter when the app uses cookie-based authentication. A fetch() call from the browser using an Authorization header (or no authentication at all, as in this recipe) never needs an antiforgery token, because the browser doesn’t attach that header automatically the way it attaches cookies.

10. Build the request URL and initialize Handsontable

    Run the backend:

    dotnet run --urls http://localhost:5000

    Pinning the URL keeps the port stable — otherwise the template’s launchSettings.json can assign a random port on each machine, and the frontend’s hardcoded API address (below) would need to change to match. Then start your frontend dev server (for example, npm run dev with Vite) and open it in the browser. The complete frontend code is in the files below.

    JavaScript

    import { useCallback, useMemo, useRef } from 'react';
    import { HotTable } from '@handsontable/react-wrapper';
    import { registerAllModules } from 'handsontable/registry';
    
    registerAllModules();
    
    const API_BASE = 'http://localhost:5000/api/orders';
    
    // Serializes fetchRows query parameters into a URL the ASP.NET Core model
    // binder understands.
    //
    // Handsontable sends:
    //   sort:    { prop: 'orderNumber', order: 'asc' }  or  null
    //   filters: [{ prop, conditions: [{ name, args }] }]  or  null
    //
    // ASP.NET Core's default model binder reads nested properties from dot
    // notation and collections from bracket-indexed dot notation:
    //   pageSize, sort.prop, sort.order, filters[N].prop/condition/value
    function buildUrl(base, { page, pageSize, sort, filters }) {
      const params = new URLSearchParams();
    
      params.set('page', String(page));
      params.set('pageSize', String(pageSize));
    
      if (sort?.prop) {
        params.set('sort.prop', sort.prop);
        params.set('sort.order', sort.order ?? 'asc');
      }
    
      if (filters?.length) {
        let idx = 0;
        filters.forEach(({ prop, conditions }) => {
          (conditions || []).forEach(({ name, args }) => {
            if (!name) return;
            params.set(`filters[${idx}].prop`, prop);
            params.set(`filters[${idx}].condition`, name);
            const a = args ?? [];
            if (a[0] != null) params.set(`filters[${idx}].value`, String(a[0]));
            idx++;
          });
        });
      }
    
      return `${base}?${params.toString()}`;
    }
    
    const ExampleComponent = () => {
      const hotRef = useRef(null);
      const removeConfirmedRef = useRef(false);
    
      const fetchRows = useCallback(async ({ page, pageSize, sort, filters }, { signal }) => {
        const url = buildUrl(API_BASE, { page, pageSize, sort, filters });
        const res = await fetch(url, { signal });
    
        if (!res.ok) throw new Error(`Fetch failed: ${res.status}`);
    
        // ASP.NET Core returns: { rows: [...], totalRows: n }
        const json = await res.json();
    
        return { rows: json.rows, totalRows: json.totalRows };
      }, []);
    
      const onRowsCreate = useCallback(async ({ rowsAmount }) => {
        const newRows = Array.from({ length: rowsAmount }, () => ({
          orderNumber: `ORD-${Date.now()}-${Math.random().toString(36).slice(2, 6).toUpperCase()}`,
          customer: 'New Customer',
          status: 'pending',
          total: 0,
        }));
    
        const res = await fetch(`${API_BASE}/create_rows`, {
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ rows: newRows }),
        });
    
        if (!res.ok) {
          const err = await res.json().catch(() => ({}));
          throw new Error(err.error || `Create failed: ${res.status}`);
        }
    
        const json = await res.json();
        const info = json.rows.map(r => `(order: ${r.orderNumber})`).join(', ');
        hotRef.current?.hotInstance?.getPlugin('notification').showMessage({
          variant: 'success',
          title: 'Row added',
          message: `Created: ${info}`,
          duration: 3000,
        });
        return json.rows;
      }, []);
    
      const onRowsUpdate = useCallback(async (rows) => {
        const res = await fetch(`${API_BASE}/update_rows`, {
          method: 'PATCH',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({
            rows: rows.map((r) => ({ id: r.id, changes: r.changes })),
          }),
        });
    
        if (!res.ok) {
          const err = await res.json().catch(() => ({}));
          throw new Error(err.error || `Update failed: ${res.status}`);
        }
      }, []);
    
      const onRowsRemove = useCallback(async (rowIds) => {
        const res = await fetch(`${API_BASE}/remove_rows`, {
          method: 'DELETE',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ rowIds }),
        });
    
        if (!res.ok) throw new Error(`Delete failed: ${res.status}`);
      }, []);
    
      const dataProvider = useMemo(
        () => ({ rowId: 'id', fetchRows, onRowsCreate, onRowsUpdate, onRowsRemove }),
        [fetchRows, onRowsCreate, onRowsUpdate, onRowsRemove]
      );
    
      // beforeRowsMutation is sync (checks for strict === false return).
      // Cancel the first attempt, show a notification with Delete/Cancel actions,
      // and on Delete re-issue the remove via the DataProvider API.
      const beforeRowsMutation = useCallback((operation, payload) => {
        if (operation === 'remove' && !removeConfirmedRef.current) {
          const count = payload.rowsRemove.length;
          const hot = hotRef.current?.hotInstance;
          if (!hot) return false;
    
          const notification = hot.getPlugin('notification');
          const id = notification.showMessage({
            variant: 'warning',
            title: 'Delete rows',
            message: `Delete ${count} row${count !== 1 ? 's' : ''}? This cannot be undone.`,
            duration: 0,
            actions: [
              {
                label: 'Delete',
                type: 'primary',
                callback: () => {
                  notification.hide(id);
                  removeConfirmedRef.current = true;
                  hot.getPlugin('dataProvider').removeRows(payload.rowsRemove).finally(() => {
                    removeConfirmedRef.current = false;
                  });
                },
              },
              {
                label: 'Cancel',
                type: 'secondary',
                callback: () => notification.hide(id),
              },
            ],
          });
          return false;
        }
      }, []);
    
      return (
        <div>
          <HotTable
            ref={hotRef}
            dataProvider={dataProvider}
            beforeRowsMutation={beforeRowsMutation}
            pagination={{ pageSize: 10 }}
            columnSorting={true}
            filters={true}
            dropdownMenu={['filter_by_condition', 'filter_action_bar']}
            contextMenu={true}
            emptyDataState={true}
            notification={true}
            colHeaders={['Order #', 'Customer', 'Status', 'Total', 'Created']}
            columns={[
              { data: 'orderNumber', type: 'text' },
              { data: 'customer',    type: 'text' },
              { data: 'status',      type: 'text' },
              { data: 'total',       type: 'numeric', numericFormat: { style: 'currency', currency: 'USD', minimumFractionDigits: 2, maximumFractionDigits: 2 } },
              { data: 'createdAt',   type: 'date', dateFormat: { year: 'numeric', month: '2-digit', day: '2-digit' }, readOnly: true },
            ]}
            rowHeaders={true}
            height="auto"
            licenseKey="non-commercial-and-evaluation"
          />
        </div>
      );
    };
    
    export default ExampleComponent;

    Key options explained:

    Option	What it does
    rowId: 'id'	Tells dataProvider which field uniquely identifies a row. Must match the entity’s primary key property, serialized to camelCase (id).
    { signal } in fetchRows	Pass the AbortSignal to fetch() so in-flight requests are canceled when the user sorts or filters before the previous response arrives.
    { rowsAmount } in onRowsCreate	dataProvider passes the number of rows to add. The frontend builds default objects and sends them as { rows: [...] }. Returning json.rows lets dataProvider replace client-side placeholder IDs with the ones assigned by SQLite.
    beforeRowsMutation	Intercepts mutations before they run. Return false to cancel. Used here to show a delete-confirmation notification with Delete/Cancel actions instead of deleting immediately.
    pagination: { pageSize: 10 }	Enables the pagination toolbar. dataProvider passes the current page and size to fetchRows automatically.
    columnSorting: true	Enables column header click-to-sort. The sort state is passed to fetchRows.
    filters: true with dropdownMenu	Renders the column filter UI. Active conditions are passed to fetchRows.
    contextMenu: true	Enables right-click context menu with Insert row above / below and Remove row options.
    emptyDataState: true	Shows a friendly illustration when the API returns zero rows (for example, when a filter matches nothing).
    notification: true	Shows automatic error toasts when fetchRows or a mutation callback throws. Fetch failures include a Refetch action. The delete-confirmation prompt in beforeRowsMutation is also built on notification’s actionable toasts, not a separate dialog.

How It Works — Complete Flow

1. Initial load: dataProvider calls fetchRows({ page: 1, pageSize: 10 }). ASP.NET Core returns the first 10 orders and the total row count.
2. User clicks a column header: columnSorting updates its sort state and dataProvider calls fetchRows again with sort: { prop: 'total', order: 'desc' }. The frontend builds sort.prop=total&sort.order=desc; the controller’s OrdersQuery.Sort binds it, and ApplySort checks ColumnMap before calling OrderByDescending.
3. User applies a column filter: Filters updates its condition list and dataProvider calls fetchRows with the filters array. The frontend serializes each condition as filters[N].prop/condition/value; the model binder populates OrdersQuery.Filters, and ApplyFilters chains .Where calls.
4. User navigates to page 2: dataProvider calls fetchRows({ page: 2, pageSize: 10, ... }). Skip(10).Take(10) returns rows 11-20.
5. User edits a cell: dataProvider calls onRowsUpdate with [{ id: 7, changes: { total: 142.5 } }]. The frontend sends { rows: [{ id: 7, changes: { total: 142.5 } }] }. UpdateRowsAsync applies only the total key inside a transaction.
6. User adds a row: dataProvider calls onRowsCreate. CreateRowsAsync inserts the row and returns it with the database-assigned id. dataProvider updates its row map so subsequent edits target the correct ID.
7. User deletes rows: dataProvider calls onRowsRemove([3, 7, 14]). RemoveRowsAsync issues a single DELETE FROM Orders WHERE Id IN (3, 7, 14).

What you learned

• ASP.NET Core Web API projects camelCase JSON by default, so Handsontable’s column keys usually need no transformation — but overriding the naming policy on one side without the other silently breaks the grid.
• ASP.NET Core’s model binder reads nested query parameters from dot notation (sort.prop) and indexed collections from filters[N].prop, unlike the bracket notation (filters[0][prop]) that Express, Rails, and PHP backends parse natively.
• Validate every column name that reaches EF.Property<T> against a fixed whitelist dictionary. Never trust Sort.Prop or a filter’s Prop directly.
• HasConversion<double>() is necessary for decimal columns on SQLite, because SQLite has no native decimal type and would otherwise sort and filter the column as text.
• A typed create DTO with no Id or CreatedAt property blocks system-managed fields structurally, without runtime filtering.
• EF.Functions.Like with an explicit ESCAPE clause, paired with manual escaping of %, _, and \, keeps LIKE-based filters safe from wildcard injection.
• ASP.NET Core’s general middleware order runs CORS after routing and before authentication/authorization — keep that convention even in a project like this one that has neither yet, so adding auth later doesn’t silently reject preflight requests.
• Pass CultureInfo.InvariantCulture to decimal.TryParse/DateTime.TryParse in a Web API. Without it, parsing depends on the server’s OS culture, and a value the client always formats the same way (JavaScript’s String(142.5)) can fail or parse differently depending on where the server happens to run.
• Clamp a client-supplied pageSize to a server-side maximum. Without a cap, a single request can force the server to materialize and serialize the entire table.

Next steps

• Server-side data with Spring Boot
• Server-side data with Express.js
• Server-side data with NestJS
• Rows pagination guide
• Column filter guide
• Rows sorting guide