CODEWITHSUNDEEP
sql-servergraphqlentity-framework-corehotchocolate
user978139
user978139

Reputation: 579

Hot Chocolate GraphQL batching Queries and applying Selection and Filtering middleware to EF SQL

Using HotChocolate Version: 10.5.5

And given the following Customer domain model:

public class Customer
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int CustomerID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string CompanyName { get; set; }
    public string SalesPerson { get; set; }
}

When exposing the Entity Framework Customers dbset in a query interface:

[UseSelection]
[UseFiltering]
public IQueryable<Customer> GetCustomers([Service] AdventureWorksContext ctx) => ctx.Customers;

And executing the following graphql query:

query {
  friendlyBikeShop: customers (where: { companyName: "Friendly Bike Shop"}){
    firstName,
    lastName
  },
  frontSportingGoods: customers (where: { companyName: "Front Sporting Goods"}){
    firstName,
    lastName
  }
}

Results in the following two SQL statements:

SELECT [c].[FirstName], [c].[LastName]
FROM [SalesLT].[Customer] AS [c]
WHERE [c].[CompanyName] = N'Front Sporting Goods'

SELECT [c].[FirstName], [c].[LastName]
FROM [SalesLT].[Customer] AS [c]
WHERE [c].[CompanyName] = N'Front Sporting Goods'

This isn't ideal as I would like to merge this into one SQL statement:

SELECT [c].[FirstName], [c].[LastName]
FROM [SalesLT].[Customer] AS [c]
WHERE [c].[CompanyName] in (N'Front Sporting Goods', N'Front Sporting Goods')

A first attempt to solve this problem using a GroupDataLoader is as follows:

[UseSelection]
[UseFiltering]
public Task<Customer[]> GetCustomersByCompanyName(IResolverContext resolverCtx, [Service] AdventureWorksContext ctx, string companyName) =>
    resolverCtx.GroupDataLoader<string, Customer>("customers", async keys =>
    {
        var customers = await ctx.Customers.Where(customer => keys.Any(key => key == customer.CompanyName)).ToListAsync();
        return customers.ToLookup(customer => customer.CompanyName);
    }).LoadAsync(companyName, default);

And executing the following graphql:

query {
  friendlyBikeShop: customersByCompanyName(companyName: "Friendly Bike Shop"){
    firstName,
    lastName
  },
  frontSportingGoods: customersByCompanyName(companyName: "Front Sporting Goods"){
    firstName,
    lastName
  }
}

Results in the following single SQL statement:

SELECT [c].[CustomerID], [c].[CompanyName], [c].[FirstName], [c].[LastName], [c].[SalesPerson]
FROM [SalesLT].[Customer] AS [c]
WHERE [c].[CompanyName] IN (N'Friendly Bike Shop', N'Front Sporting Goods')

The drawback to this approach is that Filtering and Selection isn't factored into the dynamically created SQL statement generated by EF, this is only applied AFTER the result set has returned from the database.

How do I return an IQueryable on the query interface so that the Filtering and Selection middleware will apply and be factored into the the SQL statement which is dynamically generated and still force this to be executed in a single batch?

Upvotes: 2

Views: 2641

Answers (1)

Pascal Senn
Pascal Senn

Reputation: 1912

The data API of HotChocolate and the Dataloaders do not work well together. These are two different approaches that cannot be mixed in the same resolver.

Filtering works on the resolver level whereas data loaders do run outside of the resolver execution.

This query contains two resolvers:

query {
  friendlyBikeShop: customers (where: { companyName: "Friendly Bike Shop"}){
    firstName
    lastName
  }
  frontSportingGoods: customers (where: { companyName: "Front Sporting Goods"}){
    firstName
    lastName
  }
}

Therefor you can either go with filtering and projections and two queries or you go with data loaders and only execute one query.

As an alternative query you could also do this:

query {
  customers (
    where: { 
        companyName_in: [
            "Friendly Bike Shop",
            "Front Sporting Goods"
      ]
  }){
    firstName
    lastName
  }
}

This would return both results in a single request

Upvotes: 2

Related Questions