Here’s a new project I finally got put up on NuGet which is a more generalized version of a set of extension methods that I helped write for a project at work (note: this is all new code as the stuff for work was highly coupled to our code base).

Why “Collate”? Let’s ask Mirriam-Webster:

col·late

transitive verb

to collect, compare carefully in order to verify, and often to integrate or arrange in order

collated the data for publication

When working on a data-intensive project, you will often find yourself implementing pages that use grid controls like Telerik’s Kendo UI Grid or DevExpress’ Data Grid Control.

The problem is, if you’re not careful, these sorts of controls can be very unperformant, if you just deliver the full dataset to the client and let them handle all the filtering, sorting and paging on the client side.

The alternative can be pretty onerous and painful: implementing custom filtering, sorting and paging for each request endpoint in your application. Take this contrived example:

public class GetCustomersRequest
{
    public string FilterField { get; set; }
    public string FilterValue { get; set; }
    public string SortField { get; set; }
    public string SortValue { get; set; }
    public int PageNumber { get; set; }
    public int PageSize { get; set; }
}

[HttpPost]
public ActionResult GetCustomers(GetCustomersRequest request)
{
    IEnumerable<Customer> data = null;

    using (var dbContext = MyDataContext())
    {
        var customers = dbContext.Customers;

        switch (request.FilterField)
        {
            case "FirstName":
                customers = customers.Where(x => x.FirstName.Contains(request.FilterValue));
                break;

            case "LastName":
                customers = customers.Where(x => x.LastName.Contains(request.FilterValue));
                break;

            // repeat for every field that you want to filter by
        }

        switch (request.SortField)
        {
            case "FirstName":                
                customers = requst.SortDirection == "asc"
                    ? customers.OrderBy(x => x.FirstName)
                    : customers.OrderByDescending(x => x.FirstName);
                break;

            case "LastName":
                customers = request.SortDirection == "asc"
                    ? customers.OrderBy(x => x.LastName)
                    : customers.OrderByDescending(x => x.LastName);
                break;

            // repeat for every field that you want to sort by
        }

        data = customers
            .Skip((request.PageNumber - 1) * request.PageSize)
            .Take(request.PageSize)
            .ToList();
    }

    return Json(data);
}

Now, I’m sure you can imagine how quickly it can become painful and time consuming to write that kind of code for every field on every endpoint in your application. And what happens if you want to filter by multiple fields at the same time, or apply sorting by multiple fields at the same time?

Now, if you use Collate.NET, you can write the same endpoint like this:

[HttpPost]
public ActionResult GetCustomers(Collate.Implementation.PageAndFilterAndSortRequest request)
{
    IEnumerable<Customer> data = null;

    using (var dbContext = new MyDbContext())
    {
        data = dbContext.Customers
            .Filter(request)
            .Sort(request)
            .Page(request)
            .ToList();
    }

    return Json(data);
}

Collate.NET will handle doing all the fancy LINQ expression building to handle the filtering, sorting and paging before the request is ever sent to the database.

Here’s the SQL that would get generated by Entity Framework for the query above (assuming you’re using Entity Framework and MS SQL Server):

SELECT TOP (25) 
    [top].[CustomerId] AS [CustomerId], 
    [top].[FirstName] AS [FirstName], 
    [top].[LastName] AS [LastName], 
    [top].[Company] AS [Company], 
    [top].[Address] AS [Address], 
    [top].[City] AS [City], 
    [top].[State] AS [State], 
    [top].[Country] AS [Country], 
    [top].[PostalCode] AS [PostalCode], 
    [top].[Phone] AS [Phone], 
    [top].[Fax] AS [Fax], 
    [top].[Email] AS [Email], 
    [top].[SupportRepId] AS [SupportRepId]
    FROM ( SELECT [Extent1].[CustomerId] AS [CustomerId], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent1].[Company] AS [Company], [Extent1].[Address] AS [Address], [Extent1].[City] AS [City], [Extent1].[State] AS [State], [Extent1].[Country] AS [Country], [Extent1].[PostalCode] AS [PostalCode], [Extent1].[Phone] AS [Phone], [Extent1].[Fax] AS [Fax], [Extent1].[Email] AS [Email], [Extent1].[SupportRepId] AS [SupportRepId]
        FROM [Customer] AS [Extent1]
        WHERE [Extent1].[FirstName] LIKE N'%Jane%'
        ORDER BY [Extent1].[LastName] ASC
        OFFSET 50 ROWS 
    )  AS [top]

As you can see, all the filtering, paging and sorting is done in-database, so you get a nice, performant query that only returns a single page’s worth of results.

You could go with the nuclear option and use OData all the way up and down your application, but I felt like that was an overly onerous dependency for what I wanted to do. Think of Collate.NET as being to OData as Dapper.NET is to Entity Framework.

If you want to view the source and/or contribute to the project, it’s up on GitHub with a super-permissive MIT license.

Collate.NET is also available as a NuGet package for easy consumption in .NET projects.