Server-side .NET query builder for JavaScript data tables
$ dotnet add package DataTableQueryBuilder.GenericThis builder automatically transforms an AJAX request coming from a JavaScript datatable into a LINQ query against the Entity Framework data model according to the provided configuration.
Can be used with ANY JavaScript datatable component that supports server-side processing (currently tested on datatables.net and vue-good-table only).
The full documentation is available here - https://entrypointdev.github.io/DataTableQueryBuilder/
A live demo of using DataTables with DataTableQueryBuilder.
A live demo of using vue-good-table with DataTableQueryBuilder.
A source code and OpenAPI specification of server-side API that is used in the above demos.
If you're using DataTables or wrappers around it, install the DataTableQueryBuilder.DataTables NuGet package:
dotnet add package DataTableQueryBuilder.DataTables
Then register the model binder to bind incoming AJAX requests from DataTables to a DataTableRequest model:
using DataTableQueryBuilder.DataTables;
public class Startup
{
//...
public void ConfigureServices(IServiceCollection services)
{
//...
services.RegisterDataTables();
}
}
For other JavaScript datatable components, install the DataTableQueryBuilder.Generic NuGet package instead:
dotnet add package DataTableQueryBuilder.GenericNothing else is needed.
Let's suppose that you want to show a searchable and sortable list of users, with all sorting, paging and filtering happening on the server-side.
In case of using Datatables, your configuration will look something like this:
<div id="filters">
<input type="text" data-column="id" placeholder="Id" />
<input type="text" data-column="fullName" placeholder="Full Name" />
<input type="text" data-column="email" placeholder="Email" />
<input type="text" data-column="posts" placeholder="Posts" />
<input type="text" data-column="createDate" placeholder="MM/DD/YYYY" />
</div>
<table id="user-list">
<thead>
<tr>
<th>Id</th>
<th>Full name</th>
<th>Email</th>
<th>Company</th>
<th>Posts</th>
<th>Create Date</th>
</tr>
</thead>
</table>
<script>
const apiUrl = "https://query-builder-sample-api.entrypointdev.com/API/UserList.DataTables";
$(document).ready(function () {
let dt = $("#user-list").DataTable({
processing: true,
serverSide: true,
ajax: {
url: apiUrl,
type: "POST"
},
columns: [
{ name: "id", data: "id" },
{ name: "fullName", data: "fullName" },
{ name: "email", data: "email" },
{ name: "companyName", data: "companyName" },
{ name: "posts", data: "posts" },
{ name: "createDate", data: "createDate" }
]
});
$("#filters input").each(function () {
let columnName = $(this).data("column");
$(this).on("change", function () {
let col = dt.column(columnName + ":name");
if (col.search() !== this.value)
col.search(this.value).draw();
});
});
</script>Your datatable will send requests to the back-end and expect server to return the correct rows (in form of a JSON array) to display them in the UI.
A data property in column configuration contains a property name of a row object in the returned JSON array, for example:
[
{
'id': 1,
'fullName': 'John Smith',
'email': 'john@example.com',
'companyName': '',
'posts' : 0,
'createDate': '2021-01-05T19:38:23.551Z'
}
{
'id': 2,
'fullName': 'Michael Smith',
'email': 'michael@example.com',
'companyName': 'Apple',
'posts' : 5,
'createDate': '2021-04-23T18:15:43.511Z'
}
{
'id': 3,
'fullName': 'Mary Smith',
'email': 'mary@example.com',
'companyName': 'Google',
'posts' : 10,
'createDate': '2020-09-12T10:11:45.712Z'
}
]Create your Entity Framework data model. We'll use the following simple data model in this example:
public class User
{
public int Id { get; set; }
public string FullName { get; set; } = "";
public string Email { get; set; } = "";
public int? CompanyId { get; set; }
public Company? Company { get; set; }
public virtual ICollection<Post> Posts { get; } = new List<Post>();
}
public class Company
{
public int Id { get; set; }
public string Name { get; set; } = "";
public ICollection<User> Users { get; set; } = new List<User>();
}
public class Post
{
public int Id { get; set; }
public string Title { get; set; } = "";
public string Content { get; set; } = "";
public int UserId { get; set; }
public User User { get; set; }
}Create a strongly typed projection model that represents the fields expected by your JS datatable and returned by server:
public class UserListData
{
public int Id { get; set; }
public string FullName { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public string CompanyName { get; set; } = string.Empty;
public int Posts { get; set; }
public DateTime CreateDate { get; set; }
}Create a base LINQ query that will be used by query builder to request users from a database and return the required fields:
public class UserService
{
public IQueryable<UserListData> GetAllForUserList()
{
return dataContext.Users
.Select(u => new UserListData()
{
Id = u.Id,
FullName = u.FullName,
Email = u.Email,
CompanyName = u.Company != null ? u.Company.Name : string.Empty,
Posts = u.Posts.Count(),
CreateDate = u.CreateDate
});
}
}Create an action that will receive an AJAX request from your JS datatable, transform it to a LINQ query and return the data:
public IActionResult UserList(DataTableRequest request)
{
// returns IQueryable<UserListData>
var users = userService.GetAllForUserList();
var qb = new DataTableQueryBuilder<UserListData>(request);
var result = qb.Build(users);
return result.CreateResponse();
}That's all! Please refer to the documentation for available configuration options and detailed information.