Async Database Access ?

ADO.NET recently added asynchronous programming methods and these were added in the .NET Framework 4.5. It's a fundamental programming change that EntitySpaces will probably adopt in the future. However, C# makes this still all very easy to do right now with EntitySpaces.

The Problem

Because we have thousands of employees we need to offer an API that allows paging. We want to return our JSON as follows (see below) so the consumer of our REST API has the proper information to display to the user, including whether or not they need to disable the "Next Page" button.

We want to get the total count in one query and the actual data in another query but not synchronously. For this we are going to use the Parallel.Invoke() method from System.Threading.Tasks. Parallel.Invoke() allows you to make any number of calls at the same time and wait for them to return.

Our Desired Return Response

{
  "hasMore": boolean,
  "totalCount": integer,
  "totalReturned": integer,
  "data": [] // employees list
}

The code below is our GetEmployees() call we have exposed in our REST API.

public dynamic GetEmployees(int skip, int take)
{
    EmployeesCollection coll = null;
    int count = 0;

    Parallel.Invoke(
    () =>
    {
        // Get the total count
        count = new EmployeesQuery("e", out var q)
          .Select(q.Count())
          .ExecuteScalar<int>();
    },
    () =>
    {
        // Get "paged" list, must order by when paging 
        coll = new EmployeesQuery("e", out var q)
          .Select(q.EmployeeID, q.LastName).Skip(skip).Take(take)
          .OrderBy(q.LastName.Ascending)
          .ToCollection<EmployeesCollection>();
    });
    
    // For "blog post" simplicity, we return an anonymous object
    return new
    {
        hasMore = count > (skip + take),
        totalCount = count,
        totalReturned = coll.Count,
        data = coll
    };
}

This is the JSON as it would be returned if the "take" was 2 and the "skip" was 0 (our first page). Technically, in the GetEmployees() method above we would limit the "take" to 50, however for brevity sake that was left out of the code.

{
  "hasMore": true,
  "totalCount": 22,
  "totalReturned": 2,
  "data": [
    {
      "EmployeeID": 8,
      "LastName": "Callahan"
    },
    {
      "EmployeeID": 1,
      "LastName": "Davolio"
    }
  ]
}

So, next time you need to fetch several sets of data from your database with EntitySpaces consider Parallel.Invoke(). In my next blog post I will show you how to get the total count and the data in a single query using EntitySpaces.