I'm using HotChocolate as my GraphQL server and Dapper to access the database in my project. Currently With each graphql query requesting "some" fields of an entity, the whole row is queried from database and this is wasting resources especially when querying lists of data. I want to customize the sql query according to the requested fields in the graphql query. So, this
{
product(id: 3) {
title,
price
}
}
becomes:
SELECT title, price FROM products WHERE id = 3;
There is a feature in HotChocolate called projection that I think is related to my problem. But unfortunately the documentation is uncomplete and just shows some examples of using projection with entity framework. Is there a way to implement this feature using Dapper? How?
Great Question!
I was in the same boat you are…needing to implement our own execution of data retrieval without EF or IQueryable, etc. while also getting the value of projections, especially using micro-ORM or Repository pattern where we can’t and don’t want to return IQueryable using HC's deferred execution, etc…
It is certainly possible, unfortunately it’s just not as trivial or easy at it would seem…it can get complex, mainly due to the broad functionality defined by the GraphQL spec.
In a simple query, you should use the IResolverContext.GetSelctions() API and retrieve list of selection names; as that is the supported API to get the data needed and there are many low level reasons it must be used.
But it won’t take long for you to find that it is itself a low level utility, and may no longer sufficient as it gets more much complex as soon as you use an Interface Type or Union Type that result in the use of GraphQL query fragments.
Then when you add offset paging or cursor paging the query structure changes again so you need to handle that (or both as these are two different structures)…
Now to answer your question specifically on Dapper you'll have to do the following key steps:
For posterity I’ll post code here to show you exactly what you could implement to handle these cases as an extension method… but it’s taken directly from my open source project I’ve shared out (yes I’m the author) to make this much easier with a simplified facade for HC’s IResolverContext; it offers easier access to other important things other than selections, which is all this code below focuses on.
I also implemented a full micro-ORM wrapper using this facade for RepoDB, but the similar could be done with Dapper (albeit more work as RepoDB offers great functionality for dynamically processing the models and queries from the string selection names).
Step 1: Get Selections
So here's the code to get your selections and handle the types of use cases highlighted above for Union Types, Interface Types, Paging, etc. (taken from the GraphQL.PreProcessingExtensions library shared on Github.
public static class IResolverContextSelectionExtensions
{
/// <summary>
/// Similar to CollectFields in v10, this uses GetSelections but safely validates that the current context
/// has selections before returning them, it will safely return null if unable to do so.
/// This is a variation of the helper method provided by HotChocolate team here:
/// https://github.com/ChilliCream/hotchocolate/issues/1527#issuecomment-596175928
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public static IReadOnlyList<PreProcessingSelection> GetPreProcessingSelections(this IResolverContext? context)
{
if (context == null)
return null!;
var selectionResults = new List<PreProcessingSelection>();
var selections = GatherChildSelections(context!);
if (selections.Any())
{
//BBernard
//Determine if the Selection is for a Connection, and dive deeper to get the real
// selections from the node {} field.
var lookup = selections.ToLookup(s => s.SelectionName.ToString().ToLower());
//Handle paging cases; current Node is a Connection so we have to look for selections inside
// ->edges->nodes, or inside the ->nodes (shortcut per Relay spec); both of which may exist(?)
if (lookup.Contains(SelectionNodeName.Nodes) || lookup.Contains(SelectionNodeName.Edges) || lookup.Contains(SelectionNodeName.Items))
{
//Cursor & Offset Paging are mutually exclusive so this small optimization prevents unnecessary processing...
var searchOffsetPagingEnabled = true;
//CURSOR PAGING SUPPORT - results are in either a 'Nodes' or 'Edges' Node!
//NOTE: nodes and edges are not mutually exclusive per Relay spec so
// we gather from all if they are defined...
if (lookup.Contains(SelectionNodeName.Nodes))
{
var nodesSelectionField = lookup[SelectionNodeName.Nodes].FirstOrDefault();
var childSelections = GatherChildSelections(context, nodesSelectionField);
selectionResults.AddRange(childSelections);
searchOffsetPagingEnabled = false;
}
if (lookup.Contains(SelectionNodeName.Edges))
{
var edgesSelectionField = lookup[SelectionNodeName.Edges].FirstOrDefault();
//If Edges are specified then Selections are actually inside a nested 'Node' (singular, not plural) that we need to traverse...
var nodesSelectionField = FindChildSelectionByName(context, SelectionNodeName.EdgeNode, edgesSelectionField);
var childSelections = GatherChildSelections(context, nodesSelectionField);
selectionResults.AddRange(childSelections);
searchOffsetPagingEnabled = false;
}
//OFFSET PAGING SUPPORT - results are in an 'Items' Node!
if (searchOffsetPagingEnabled && lookup.Contains(SelectionNodeName.Items))
{
var nodesSelectionField = lookup[SelectionNodeName.Items].FirstOrDefault();
var childSelections = GatherChildSelections(context, nodesSelectionField);
selectionResults.AddRange(childSelections);
}
}
//Handle Non-paging cases; current Node is an Entity...
else
{
selectionResults.AddRange(selections);
}
}
return selectionResults;
}
/// <summary>
/// Find the selection that matches the specified name.
/// For more info. on Node parsing logic see here:
/// https://github.com/ChilliCream/hotchocolate/blob/a1f2438b74b19e965b560ca464a9a4a896dab79a/src/Core/Core.Tests/Execution/ResolverContextTests.cs#L83-L89
/// </summary>
/// <param name="context"></param>
/// <param name="baseSelection"></param>
/// <param name="selectionFieldName"></param>
/// <returns></returns>
private static PreProcessingSelection FindChildSelectionByName(IResolverContext? context, string selectionFieldName, PreProcessingSelection? baseSelection)
{
if (context == null)
return null!;
var childSelections = GatherChildSelections(context!, baseSelection);
var resultSelection = childSelections?.FirstOrDefault(
s => s.SelectionName.Equals(selectionFieldName, StringComparison.OrdinalIgnoreCase)
)!;
return resultSelection!;
}
/// <summary>
/// Gather all child selections of the specified Selection
/// For more info. on Node parsing logic see here:
/// https://github.com/ChilliCream/hotchocolate/blob/a1f2438b74b19e965b560ca464a9a4a896dab79a/src/Core/Core.Tests/Execution/ResolverContextTests.cs#L83-L89
/// </summary>
/// <param name="context"></param>
/// <param name="baseSelection"></param>
/// <returns></returns>
private static List<PreProcessingSelection> GatherChildSelections(IResolverContext? context, PreProcessingSelection? baseSelection = null)
{
if (context == null)
return null!;
var gathered = new List<PreProcessingSelection>();
//Initialize the optional base field selection if specified...
var baseFieldSelection = baseSelection?.GraphQLFieldSelection;
//Dynamically support re-basing to the specified baseSelection or fallback to current Context.Field
var field = baseFieldSelection?.Field ?? context.Field;
//Initialize the optional SelectionSet to rebase processing as the root for GetSelections()
// if specified (but is optional & null safe)...
SelectionSetNode? baseSelectionSetNode = baseFieldSelection is ISelection baseISelection
? baseISelection.SelectionSet
: null!;
//Get all possible ObjectType(s); InterfaceTypes & UnionTypes will have more than one...
var objectTypes = GetObjectTypesSafely(field.Type, context.Schema);
//Map all object types into PreProcessingSelection (adapter classes)...
foreach (var objectType in objectTypes)
{
//Now we can process the ObjectType with the correct context (selectionSet may be null resulting
// in default behavior for current field.
var childSelections = context.GetSelections(objectType, baseSelectionSetNode);
var preprocessSelections = childSelections.Select(s => new PreProcessingSelection(objectType, s));
gathered.AddRange(preprocessSelections);
}
return gathered;
}
/// <summary>
/// ObjectType resolver function to get the current object type enhanced with support
/// for InterfaceTypes & UnionTypes; initially modeled after from HotChocolate source:
/// HotChocolate.Data -> SelectionVisitor`1.cs
/// </summary>
/// <param name="type"></param>
/// <param name="objectType"></param>
/// <param name="schema"></param>
/// <returns></returns>
private static List<ObjectType> GetObjectTypesSafely(IType type, ISchema schema)
{
var results = new List<ObjectType>();
switch (type)
{
case NonNullType nonNullType:
results.AddRange(GetObjectTypesSafely(nonNullType.NamedType(), schema));
break;
case ObjectType objType:
results.Add(objType);
break;
case ListType listType:
results.AddRange(GetObjectTypesSafely(listType.InnerType(), schema));
break;
case InterfaceType interfaceType:
var possibleInterfaceTypes = schema.GetPossibleTypes(interfaceType);
var objectTypesForInterface = possibleInterfaceTypes.SelectMany(t => GetObjectTypesSafely(t, schema));
results.AddRange(objectTypesForInterface);
break;
case UnionType unionType:
var possibleUnionTypes = schema.GetPossibleTypes(unionType);
var objectTypesForUnion = possibleUnionTypes.SelectMany(t => GetObjectTypesSafely(t, schema));
results.AddRange(objectTypesForUnion);
break;
}
return results;
}
}
Step 2: Get Argument for Filtering
This is a lot more straightforward for simple arguments and objects as HC does a really nice job at giving us access:
[GraphQLName("products")]
public async Task<IEnumerable<Products> GetProductsByIdAsync(
IResolverContext context,
[Service] ProductsService productsService,
CancellationToken cancellationToken,
int id
)
{
//Per the Annotation based Resolver signature here HC will inject the 'id' argument for us!
//Otherwise this is just normal Resolver stuff...
var productId = id;
//Also you could get the argument from the IResolverContext...
var productId = context.Argument<int>("id");. . .
}
Step 3: Get Sorting Arguments
Note: Getting the Argument here is easy, however, sort args have both a name and a sort-direction, and those need to be mapped to model names when using a micro-orm. So again it's not trivial, but very possible:
public static class IResolverContextSortingExtensions
{
/// <summary>
/// Safely process the GraphQL context to retrieve the Order argument;
/// matches the default name used by HotChocolate Sorting middleware (order: {{field1}: ASC, {field2}: DESC).
/// Will return null if the order arguments/info is not available.
/// </summary>
/// <returns></returns>
public static List<ISortOrderField>? GetSortingArgsSafely(this IResolverContext context, string sortOrderArgName = null!)
{
var results = new List<ISortOrderField>();
//Unfortunately the Try/Catch is required to make this safe for easier coding when the argument is not specified,
// because the ResolverContext doesn't expose a method to check if an argument exists...
try
{
var sortArgName = sortOrderArgName ?? SortConventionDefinition.DefaultArgumentName;
//Get Sort Argument Fields and current Values...
//NOTE: In order to correctly be able to Map names from GraphQL Schema to property/member names
// we need to get both the Fields (Schema) and the current order values...
//NOTE: Not all Queries have Fields (e.g. no Selections, just a literal result), so .Field may
// throw internal NullReferenceException, hence we have the wrapper Try/Catch.
IInputField sortArgField = context.Field.Arguments[sortArgName];
ObjectValueNode sortArgValue = context.ArgumentLiteral<ObjectValueNode>(sortArgName);
//Validate that we have some sort args specified and that the Type is correct (ListType of SortInputType values)...
//NOTE: The Following processing logic was adapted from 'QueryableSortProvider' implementation in HotChocolate.Data core.
//FIX: The types changed in v11.0.1/v11.0.2 the Sort Field types need to be checked with IsNull() method, and
// then against NonNullType.NamedType() is ISortInputType instead.
if (!sortArgValue.IsNull()
&& sortArgField.Type is ListType lt
&& lt.ElementType is NonNullType nn
&& nn.NamedType() is ISortInputType sortInputType)
{
//Create a Lookup for the Fields...
var sortFieldLookup = sortInputType.Fields.OfType<SortField>().ToLookup(f => f.Name.ToString().ToLower());
//Now only process the values provided, but initialize with the corresponding Field (metadata) for each value...
var sortOrderFields = sortArgValue.Fields.Select(
f => new SortOrderField(
sortFieldLookup[f.Name.ToString().ToLower()].FirstOrDefault(),
f.Value.ToString()
)
);
results.AddRange(sortOrderFields);
}
return results;
}
catch
{
//Always safely return at least an Empty List to help minimize Null Reference issues.
return results;
}
}
}
Step 4: Convert all of this into a valid SQL statement...
Now you have easy to work with list of Selection names, Sort Arguments, and even a filter argument value. But, these selection names are likely not the same thing as your C# Model names or SQL Table Field names. So you'll probably have to implement some reflection processing to get the actual Field names in case Dapper annotations were used, etc.
But once you've mapped your Selection names to actual SQL Table Field names; which is implementation specific and probably needs another question to answer... then you can convert those into valid SQL.
There are many ways to do that, but one good way might be to pull in an awesome SQL builder package like SqlKata which I definitely recommend as doing this correctly/safely with mitigation of SQL Injection, etc. is important and these libraries make it much much easier.
Then you can use the SQL built and execute it via Dapper . . . you do not have to use the SqlKata execution engine.
However, this is one of the many reasons I've moved my projects over to RepoDB because this processing was a easier and only uses one package -- but I'd have no concerns what-so-ever using SqlKata.
All of this is readily available in a Nuget package to make life a lot easier...
Extensions and simplified facade for processing data inside the resolver using micro-ORM: https://github.com/cajuncoding/GraphQL.RepoDB/tree/main/GraphQL.PreProcessingExtensions
And a full implementation using RepoDB: https://github.com/cajuncoding/GraphQL.RepoDB