Filters,Paging,Sorting and Mutations with GraphQL in ASP.NET

In my previous earlier article, I demonstrated how to integrate GraphQL with ASP.NET core.

This article builds upon that article by exploring the implementation of additional features such as

• Filters

• Paging

• Sorting

• Mutations

Filters

With HotChcolate its possible to expose your data through complex filter objects that get translated to native database queries. By default, the filters use theIQueryable interface.

To implement filters, we have to first add .AddFiltering() extension in Program.cs of your ASP.NET core project

builder.Services
    .AddGraphQLServer()
    .AddFiltering();

and then use the [useFiltering] directive in the Query Type

  [UseFiltering]
  public IQueryable<SalesOrderHeader> GetOrderHeaders([Service] _DbContext dbContext) =>
  dbContext.SalesOrderHeaders.Include(oh => oh.SalesOrderDetails);

This will expose all the possible combinations of filters on all the attributes of the underlying objects of the schema. By default filters for all fields of the type are generated.

However, the filters can be customized by multiple ways. It can done by specifying the filters by changing the default behavior with BindFields, BindFieldsExplicitly or BindFieldsImplicitly. More details are found here.

Filters can also be created through parameterized LINQ queries that fetches the data through Entity framework. To implement it, we have to modify the code of Query type that was implemented in my previous article to the following

 public class SalesOrderQuery
 {

     public IQueryable<SalesOrderHeader> GetOrderHeaders([Service] _DbContext dbContext, int? salesOrderId,string? PONumber,DateTime? OrderDate)
     {

         IQueryable<SalesOrderHeader> query = dbContext.SalesOrderHeaders.Include(oh => oh.SalesOrderDetails);

         if (salesOrderId > 0)
         {
             query = query.Where(p => p.SalesOrderId == salesOrderId.Value);
         }

         if (!string.IsNullOrEmpty(PONumber))
         {
             query = query.Where(p => p.PurchaseOrderNumber == PONumber); 
         }
         if (!string.IsNullOrEmpty(OrderDate.ToString()))
         {
             query = query.Where(d => d.OrderDate <= OrderDate);
         }
         return query;
     }
 }

In the code above, we have set three parameters to the Query Type : salesOrderId ,PONumber and OrderDate.

The filter returns all the records where salesOrderId ,PONumber match and OrderdDate is less than the supplied values. These parameters are optional.

Note : With the custom method we no longer require the [UseFiltering] directive but registering the .AddFiltering() extension has to be in place.

Filtering by OrderDate

Filtering by SalesOrderId

Paging

GraphQL supports two types of paging

• Offset-based Pagination

• Cursor-based Pagination

GraphQL by default supports Cursor-based Pagination.

Cursor-based pagination contains

• Edges

• Nodes

• Cursor

• PageInfo

In essence, GraphQL Nodes are similar to a row of a table. Edges representing a collections of nodes for a given page while cursor represents the position of the node in the list.

Lets look at an example of a typical GraphQL query with paging :

query {
  users(first: 2) {
    edges {
      node {
        id
        name       
      }
      cursor
    }
    pageInfo {
      hasNextPage
      endCursor
    }
  }
}

The query results in the following output

{
  "data": {
    "users": {
      "edges": [
        {
          "node": {
            "id": "1",
            "name": "Name_1"
          },
          "cursor": "cursor1"
        },
        {
          "node": {
            "id": "2",
            "name": "Name_2"
          },
          "cursor": "cursor2"
        }
      ],
      "pageInfo": {
        "hasNextPage": true,
        "endCursor": "cursor2"
      }
    }
  }
}

In the example above, we have two rows (or nodes) with the cursor serving as an identifier for each node. The pageInfo provides information whether next page exists and an identifier of the last cursor in the current list.

Querying for the next set of rows withafter: "cursor2", specifies the end cursor of the previous list

query {
  users(first: 2, after: "cursor2") {
    edges {
      node {
        id
        name       
      }
      cursor
    }
    pageInfo {
      hasNextPage
      endCursor
    }
  }
}

{
  "data": {
    "users": {
      "edges": [
        {
          "node": {
            "id": "3",
            "name": "Name_3"
          },
          "cursor": "cursor3"
        },
        {
          "node": {
            "id": "4",
            "name": "Name_4"
          },
          "cursor": "cursor4"
        }
      ],
      "pageInfo": {
        "hasNextPage": false,
        "endCursor": "cursor4"
      }
    }
  }
}

As seen above, we have reached the end of the listing with "hasNextPage": false indicating no further pages are available.

To implement cursor paging in our source code, all we need to do is use the [UsePaging] directive in our query

using GraphQLApp.DatabaseContext;
using GraphQLApp.Models;
using Microsoft.EntityFrameworkCore;

namespace GraphQLApp.Query
{

    public class SalesOrderQuery
    {
       [UsePaging]     
        public IQueryable<SalesOrderHeader> GetOrderHeaders([Service] _DbContext dbContext, int? salesOrderId,string? PONumber,DateTime? OrderDate)
        {

            IQueryable<SalesOrderHeader> query = dbContext.SalesOrderHeaders.Include(oh => oh.SalesOrderDetails);

            if (salesOrderId > 0)
            {
                query = query.Where(p => p.SalesOrderId == salesOrderId.Value);
            }

            if (!string.IsNullOrEmpty(PONumber))
            {
                query = query.Where(p => p.PurchaseOrderNumber == PONumber); 
            }
            if (!string.IsNullOrEmpty(OrderDate.ToString()))
            {
                query = query.Where(d => d.OrderDate <= OrderDate);
            }
            return query;
        }
    }
}

Notice the [UsePaging] directive used in the GetOrderHeaders function above.

Lets now write a simple GraphQL query that returns two rows for each page for OrderDates that are lesser than 2012-01-10

query sales {
  orderHeaders(first: 2, orderDate: "2012-01-10T00:00:00Z") {
    edges {
      node {
        dueDate
        salesOrderId
      }
    }
    pageInfo {
      endCursor
      hasNextPage
      hasPreviousPage
    }
    nodes {
      purchaseOrderNumber
    }
  }
}

Below is the output of the query

The output contains columns dueDate, SalesOrderId, and purchaseOrderNumber in the node, along with totalDues in the collection of nodes.

The endCusor is indicated as MQ== which helps us to move to the next page of the items.

Below is the query used to traverse to the next page

query sales {
  orderHeaders(
    first: 2, 
    orderDate: "2012-01-10T00:00:00Z",
    after:"MQ==")
     {
    edges {
      node {
        dueDate
        salesOrderId
        purchaseOrderNumber
      }
    }
    pageInfo {
      endCursor
      hasNextPage
      hasPreviousPage
    }
    nodes {
      totalDue
    }
  }
}

Output

We can use "endCursor": "Mw==" as an indicator to retrieve the next set of records and so on. Essentially using it to grab the next two rows.

This can be continued till the "hasNextPage": true does not become false.

There are a few optional paging settings that could be used to customize the paging behavior

Lets use the following setting with the paging directive

  [UsePaging(IncludeTotalCount =true ,DefaultPageSize =1)]

using GraphQLApp.DatabaseContext;
using GraphQLApp.Models;
using Microsoft.EntityFrameworkCore;

namespace GraphQLApp.Query
{
    public class SalesOrderQuery
    {
        [UsePaging(IncludeTotalCount =true ,DefaultPageSize =1)]
        public IQueryable<SalesOrderHeader> GetOrderHeaders([Service] _DbContext dbContext, int? salesOrderId,string? PONumber,DateTime? OrderDate)
        {
            IQueryable<SalesOrderHeader> query = dbContext.SalesOrderHeaders.Include(oh => oh.SalesOrderDetails);

            if (salesOrderId > 0)
            {
                query = query.Where(p => p.SalesOrderId == salesOrderId.Value);
            }

            if (!string.IsNullOrEmpty(PONumber))
            {
                query = query.Where(p => p.PurchaseOrderNumber == PONumber); 
            }
            if (!string.IsNullOrEmpty(OrderDate.ToString()))
            {
                query = query.Where(d => d.OrderDate <= OrderDate);
            }
            return query;
        }
    }
}

With DefaultPageSize =1 there is no need to specify first: N as the query will display one item on each page

query sales {
  orderHeaders(
    orderDate: "2012-01-10T00:00:00Z")
     {
    edges {
      node {
        dueDate
        salesOrderId
      }
    }
    pageInfo {
      endCursor
      hasNextPage
      hasPreviousPage
    }
    nodes {
      purchaseOrderNumber
    }
    totalCount
  }
}

Output : A single item is displayed along with the TotalCount property

This pretty much sums up about the cursor based pagination.

Offset Based Pagination

It is the simplest form of pagination. In offset based pagination we only set the limit and offset arguments to the query. There is no concept of edges, nodes and cursor in offset based pagination.

To implement offset pagination all you have to do is just use the [UseOffsetPaging] directive in the GetOrderHeaders function

using GraphQLApp.DatabaseContext;
using GraphQLApp.Models;
using Microsoft.EntityFrameworkCore;

namespace GraphQLApp.Query
{
    public class SalesOrderQuery
    {
        [UseOffsetPaging]
        public IQueryable<SalesOrderHeader> GetOrderHeaders([Service] _DbContext dbContext, int? salesOrderId,string? PONumber,DateTime? OrderDate)
        {

            IQueryable<SalesOrderHeader> query = dbContext.SalesOrderHeaders.Include(oh => oh.SalesOrderDetails);

            if (salesOrderId > 0)
            {
                query = query.Where(p => p.SalesOrderId == salesOrderId.Value);
            }

            if (!string.IsNullOrEmpty(PONumber))
            {
                query = query.Where(p => p.PurchaseOrderNumber == PONumber); 
            }
            if (!string.IsNullOrEmpty(OrderDate.ToString()))
            {
                query = query.Where(d => d.OrderDate <= OrderDate);
            }
            return query;
        }
    }
}

Query

query sales {
  orderHeaders(orderDate: 
  "2012-01-10T00:00:00Z", 
  skip: 2, 
  take: 3) {
    items {
      purchaseOrderNumber
      revisionNumber
      salesOrderId
      taxAmt
      totalDue      
    }
    pageInfo {
      hasNextPage
      hasPreviousPage
    }
  }
}

Output

The above query skips two rows and fetches three rows alongside ageInfo indicating if there are any additional items that could be retrieved.

This is SQL query generated by offset paging

As was the case with [UsePaging], we can use additional properties with [UseOffsetPaging] as well.

Using the [UseOffsetPaging(IncludeTotalCount = true, DefaultPageSize = 3)] , we get three items in the page along with the totalCount

Note : We don’t have skip and take values in the query above. If those values are provided in the query, they will override the default values specified in the [UseOffsetPaging] directive.

Sorting

Implementing sorting isn't exactly straightforward and there are multiple approaches to achieve it. I might as well write a separate article on the topic. However, in this article I will focus on one of the methods of implementation.

The most easiest way to implement sorting, is by registering .AddSorting() in Program.cs and then using [UseSorting] directive for the query. But this will expose sort properties for all the columns in the source.

We definitely wouldn’t want that, so we have to implement a type of customization where the sorting property is limited only on a few columns to perform sorting operation.

To do so, we first define an Enum for the columns on which we want to apply sorting properties.

 public Enum SalesOrderSortField
 {
     OrderDate,
     TotalDue,
     SalesOrderId
 }

Then we create a class SortType that extends the DefaultSortEnumType class and define the sort properties in the class.

public class SortType : DefaultSortEnumType
{
    protected override void Configure(ISortEnumTypeDescriptor descriptor)
    {
        descriptor.Operation(DefaultSortOperations.Ascending);
        descriptor.Operation(DefaultSortOperations.Descending);
    }
}

We then bind the columns in a new class that overrides the Configure of the base class through the ISortInputTypeDescriptor<T> interface.

 public class SalesSortType : SortInputType<SalesOrderHeader>
 {
     protected override void Configure(ISortInputTypeDescriptor<SalesOrderHeader> descriptor)
     {
          descriptor.BindFieldsExplicitly();
          descriptor.Field(f => f.SalesOrderId).Type<SortType>();
          descriptor.Field(f => f.OrderDate).Type<SortType>();
          descriptor.Field(f => f.TotalDue).Type<SortType>();
     }
 }

In the final step we define the default sorting of type SalesSortType that we just created.

using GraphQLApp.DatabaseContext;
using GraphQLApp.Models;
using Microsoft.EntityFrameworkCore;

namespace GraphQLApp.Query
{
    public class SalesOrderQuery
    {
       [UseSorting(typeof(SalesSortType))]
        public IQueryable<SalesOrderHeader> GetOrderHeaders([Service] _DbContext dbContext, int? salesOrderId,string? PONumber,DateTime? OrderDate)
        {

            IQueryable<SalesOrderHeader> query = dbContext.SalesOrderHeaders.Include(oh => oh.SalesOrderDetails);

            if (salesOrderId > 0)
            {
                query = query.Where(p => p.SalesOrderId == salesOrderId.Value);
            }

            if (!string.IsNullOrEmpty(PONumber))
            {
                query = query.Where(p => p.PurchaseOrderNumber == PONumber); 
            }
            if (!string.IsNullOrEmpty(OrderDate.ToString()))
            {
                query = query.Where(d => d.OrderDate <= OrderDate);
            }
            return query;
        }
    }
}

Now executing the query that sorts on SalesOrderId descending, we get the following output with SalesOrderId sorted in descending order.

Below is the generated SQL query

In the query we see the SalesOrderID sorted in descending order. Similarly, sorting on TotalDue in descending order returns the result set sorted on TotalDue descending

That’s all for Sorting. As mentioned earlier, I might pen down a separate article exclusively on Sorting with GraphQL to elaborate the topic.

Mutations

In GraphQL, mutations are used for creating, updating, or deleting records. Mutations perform operations that change the state of the data. Each mutation typically returns an object with the updated data or a status message, allowing clients to know the result of the operation. Mutations are defined in the GraphQL schema, and clients use them by sending requests that specify the desired operation and any necessary input.

To demonstrate mutations, I will perform mutations on a separate entity called Location from the AdventureWorks database for brevity, as comparatively it has fewer columns.

We first create the Model class Location for the Location table using scaffolding.

Scaffold-DbContext "Data Source=##########;Initial Catalog=Adventureworks;User ID=******;Password=******" Microsoft.EntityFrameworkCore.SqlServer -Tables Production.Location

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace GraphQLApp.Models;

/// <summary>
/// Product inventory and manufacturing locations.
/// </summary>
public partial class Location
{
    /// <summary>
    /// Primary key for Location records.
    /// </summary>
    /// 
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public short ?LocationId { get; set; }

    /// <summary>
    /// Location description.
    /// </summary>
    public string Name { get; set; } = null!;

    /// <summary>
    /// Standard hourly cost of the manufacturing location.
    /// </summary>
    public decimal CostRate { get; set; }

    /// <summary>
    /// Work capacity (in hours) of the manufacturing location.
    /// </summary>
    public decimal Availability { get; set; }

    /// <summary>
    /// Date and time the record was last updated.
    /// </summary>
    public DateTime ?ModifiedDate { get; set; }
}

Note : Since we are performing Mutations, we dont require a Query class instead we require a Mutation class.

We create a Mutation class for the Location entity.

using GraphQLApp.DatabaseContext;
using Location = GraphQLApp.Models.Location;

namespace GraphQLApp.Mutations
{
    public class LocationMutations
    {
        public IQueryable<Location> AddLocationAsync(Location location, [Service] _DbContext dbContext)
        {

            var newLocation = new Location
            {
                Name = location.Name,
                CostRate=location.CostRate,
                Availability=location.Availability                
            };


            dbContext.Locations.Add(newLocation);           
            dbContext.SaveChanges();        
            return dbContext.Locations;
        }
    }
}

Now we define the ObjectType for Location to override the base method.

 public class LocationType : ObjectType<Location>
 {
     protected override void Configure(IObjectTypeDescriptor<Location> descriptor)
     {
         descriptor.BindFieldsExplicitly();
         descriptor.Field(od => od.LocationId).Type<NonNullType<IntType>>();
         descriptor.Field(od => od.Name).Type<NonNullType<StringType>>();
         descriptor.Field(od => od.CostRate).Type<FloatType>();
         descriptor.Field(od => od.Availability).Type<NonNullType<FloatType>>();
     }
 }

That’s all is required for performing Mutations.

Check out the a small walkthrough below that shows how mutations work.

That’s all about mutations in GraphQL.

For more in depth documentation on the topics I covered in this article you can refer to the official HotChocolate documentation here.

Conclusion

In this article, we've explored how to implement Filters, Paging, Sorting, and Mutations in a GraphQL API using ASP.NET Core and HotChocolate. These features are essential for building flexible, efficient, and scalable APIs.

With the combination of ASP.NET Core and HotChocolate the functionalities covered in this article can be easily implemented and customized that cater to modern application requirements.

Thanks for reading !!!