How to implement Generic Queries by combining EntityFramework Core and GraphQL.NET?

Currently, I have struggled with the problems such as I must implement the query many times for any entity in my data model. I thought that would be great if we can expose all entities to the APIs or maybe a way to limited some of the sensitive entities, and other sides or components subsequently simply to query it (just like a generic query to avoid boilerplate code when we implemented over and over the query function for all entities in the system).

I spend a long time look deeply in OData project (the parser component actually), but it couldn’t end up with the satisfaction. Maybe because it is too complex and a lot of schemas need to define in the up-front time. And moreover, it is only used by .NET ecosystem (e.g. Microsoft Dynamic CRM, Microsoft Sharepoint…).

I really want a solution that can work, adapt and consume well to other ecosystems or components like front-end, mobile or maybe IoT devices. That is a reason I choose GraphQL by Facebook. I think I don’t need to explain more about why it is so cool at these times. For more information about it, I highly recommend you to read this article 2017: The year in GraphQL.

In this article, I will show you about my POC project that tries to combine Entity Framework Core and GraphQL.NET, then we can use it to submit gql DSL language at the front-end and query every entity in your database via the back-end.

Thank you, Joe McBride and Stef Heyenrath for your great libraries (y)

Prerequisites

Database Schema

Graph Schema Model

We define the model for columns (metadata for a table in the database) in database as following

public class ColumnMetadata{public string ColumnName { get; set }public string DataType { get; set; }}

Then, we do the same thing for the table as below

public class TableMetadata{public string TableName { get; set; }public string AssemblyFullName { get; set; }public IEnumerable<ColumnMetadata> Columns { get; set; }}

Because we have attached the prefix for every table in the database (e.g. dbo.crm_Tasks) so we need a mapping table which helps us to resolve the friendly name for the entity (e.g. tasks when we submitted the query in UI)

public interface ITableNameLookup{bool InsertKeyName(string friendlyName);string GetFriendlyName(string correctName);}

public class TableNameLookup : ITableNameLookup{private IDictionary<string, string> _lookupTable = new Dictionary<string, string>();

public bool InsertKeyName(string correctName)  
{  
    if(!\_lookupTable.ContainsKey(correctName))  
    {  
        var friendlyName = CanonicalName(correctName);  
        \_lookupTable.Add(correctName, friendlyName);  
        return true;  
    }  
    return false;  
}

public string GetFriendlyName(string correctName)  
{  
    if (!\_lookupTable.TryGetValue(correctName, out string value))  
        throw new Exception($"Could not get {correctName} out of the list.");  
    return value;  
}

private string CanonicalName(string correctName)  
{  
    var index = correctName.LastIndexOf("\_");

    var result = correctName.Substring(  
            index + 1,  
            correctName.Length - index - 1);

    return Char.ToLowerInvariant(result\[0\]) + result.Substring(1);  
}

}

Now is the time to put all the schema models into the database metadata as below

public interface IDatabaseMetadata{void ReloadMetadata();IEnumerable<TableMetadata> GetTableMetadatas();}

public sealed class DatabaseMetadata : IDatabaseMetadata{private readonly DbContext _dbContext;private readonly ITableNameLookup _tableNameLookup;

private string \_databaseName;  
private IEnumerable<TableMetadata> \_tables;

public DatabaseMetadata(DbContext dbContext, ITableNameLookup tableNameLookup)  
{  
    \_dbContext = dbContext;  
    \_tableNameLookup = tableNameLookup;

    \_databaseName = \_dbContext.Database.GetDbConnection().Database;

    if (\_tables == null)  
        ReloadMetadata();  
}

public IEnumerable<TableMetadata> GetTableMetadatas()  
{  
    if (\_tables == null)  
        return new List<TableMetadata>();

    return \_tables;  
}

public void ReloadMetadata()  
{  
    \_tables = FetchTableMetaData();  
}

private IReadOnlyList<TableMetadata> FetchTableMetaData()  
{  
    var metaTables = new List<TableMetadata>();

    foreach (var entityType in **\_dbContext.Model.GetEntityTypes()**)  
    {  
        var tableName = **entityType.Relational().TableName**;

        metaTables.Add(new TableMetadata  
        {  
            TableName = tableName,  
            AssemblyFullName = **entityType.ClrType.FullName**,  
            Columns = GetColumnsMetadata(entityType)  
        });

        \_tableNameLookup.InsertKeyName(tableName);  
    }  
          
    return metaTables;  
}

private IReadOnlyList<ColumnMetadata> GetColumnsMetadata(IEntityType entityType)  
{  
    var tableColumns = new List<ColumnMetadata>();

    foreach (var propertyType in entityType.GetProperties())  
    {  
        var relational = propertyType.Relational();  
        tableColumns.Add(new ColumnMetadata  
        {  
            ColumnName = relational.ColumnName,  
            DataType = relational.ColumnType  
        });  
    }

    return tableColumns;  
}

}

We have the schema models on the codes which will be populated all schema information of EfCore entities when the application started.

Graph Type

Let define the type of the GraphQL as below

public class TableType : ObjectGraphType<object>{public QueryArguments TableArgs{get; set;}

private IDictionary<string, Type> \_databaseTypeToSystemType;  
protected IDictionary<string, Type> DatabaseTypeToSystemType  
{  
    get  
    {  
        if (\_databaseTypeToSystemType == null)  
        {  
            \_databaseTypeToSystemType = new Dictionary<string, Type> {  
                { "uniqueidentifier", typeof(String) },  
                { "char", typeof(String) },  
                { "nvarchar", typeof(String) },  
                { "int", typeof(int) },  
                { "decimal", typeof(decimal) },  
                { "bit", typeof(bool) }  
            };  
        }  
        return \_databaseTypeToSystemType;  
    }  
}

public TableType(TableMetadata tableMetadata)  
{  
    Name = tableMetadata.TableName;  
    foreach (var tableColumn in tableMetadata.Columns)  
    {  
        InitGraphTableColumn(tableColumn);  
    }  
}

private void InitGraphTableColumn(ColumnMetadata columnMetadata)  
{  
    var graphQLType = (ResolveColumnMetaType(columnMetadata.DataType)).GetGraphTypeFromType(true);  
    var columnField = Field(  
        graphQLType,  
        columnMetadata.ColumnName  
    );

    columnField.Resolver = new NameFieldResolver();  
    FillArgs(columnMetadata.ColumnName);  
}

private void FillArgs(string columnName)  
{  
    if (TableArgs == null)  
    {  
        TableArgs = new QueryArguments(  
            new QueryArgument<StringGraphType>()  
            {  
                Name = columnName  
            }  
        );  
    }  
    else  
    {  
        TableArgs.Add(new QueryArgument<StringGraphType> { Name = columnName });  
    }

    TableArgs.Add(new QueryArgument<IdGraphType> { Name = "id" });  
    TableArgs.Add(new QueryArgument<IntGraphType> { Name = "first" });  
    TableArgs.Add(new QueryArgument<IntGraphType> { Name = "offset" });  
}

private Type ResolveColumnMetaType(string dbType)  
{  
    if (DatabaseTypeToSystemType.ContainsKey(dbType))  
        return DatabaseTypeToSystemType\[dbType\];

    return typeof(String);  
}

}

The above codes help the application to identify the data type and some of the query arguments like pagination, projection… See the final part of this article when I showed you the result of the query.

Graph Resolver

To make the GraphQL understands the database schema which is populated, we need to create some of the revolvers as following

public class NameFieldResolver : IFieldResolver{public object Resolve(ResolveFieldContext context){var source = context.Source;

    if (source == null)  
    {  
        return null;  
    }

    var name = Char.ToUpperInvariant(context.FieldAst.Name\[0\]) + context.FieldAst.Name.Substring(1);  
    var value = GetPropValue(source, name);

    if (value == null)  
    {  
        throw new InvalidOperationException($"Expected to find property {context.FieldAst.Name} on {context.Source.GetType().Name} but it does not exist.");  
    }

    return value;  
}

private static object GetPropValue(object src, string propName)  
{  
    return src.GetType().GetProperty(propName).GetValue(src, null);  
}

}

And moreover, we define the resolver for each field in the database schema

public class MyFieldResolver : IFieldResolver{private TableMetadata _tableMetadata;private DbContext _dbContext;

public MyFieldResolver(TableMetadata tableMetadata, DbContext dbContext)  
{  
    \_tableMetadata = tableMetadata;  
    \_dbContext = dbContext;  
}

public object Resolve(ResolveFieldContext context)  
{  
    var queryable = **\_dbContext.Query(\_tableMetadata.AssemblyFullName)**;  
    if (context.FieldName.Contains("\_list"))  
    {  
          
        var first = context.Arguments\["first"\] != null ?   
            context.GetArgument("first", int.MaxValue) :  
            int.MaxValue;

        var offset = context.Arguments\["offset"\] != null ?   
            context.GetArgument("offset", 0) :   
            0;

        return queryable  
            .Skip(offset)  
            .Take(first)  
            .ToDynamicList<object>();  
    }  
    else  
    {  
        var id = context.GetArgument<Guid>("id");  
        return **queryable.FirstOrDefault($"Id ==** [**@0**](http://twitter.com/0 "Twitter profile for @0")**", id)**;  
    }  
}

}

Noticing to the dynamic LINQ in the code above (bold text colors). Thank you for System.Linq.Dynamic.Core lib, without it we need to do a lot of works to get the same things like this. Now adding one more thing which makes us can do a query by using its assembly name as

public static class DbContextExtensions{public static IQueryable Query(this DbContext context, string entityName) =>context.Query(context.Model.FindEntityType(entityName).ClrType);

static readonly MethodInfo SetMethod = typeof(DbContext).GetMethod(nameof(DbContext.Set));

public static IQueryable Query(this DbContext context, Type entityType) =>  
 (IQueryable)SetMethod.MakeGenericMethod(entityType).Invoke(context, null);

}

If you want to know more about the solution, deep dive more at https://stackoverflow.com/questions/48041821/dynamically-access-table-in-ef-core-2-0.

Graph Query

Now is the time we need to define the query for our application. Two things to notice as following

If we want to query task entity , then we would like to define as { tasks (id: “<id here>”) {id, name} }, based on the database schema that we have defined. The result will be returned by only one record in the output
If we want to query the list of task entities, we need to do just like { tasks_list (offset:1, first:10) { id, name } } which has a meaning to query the list of task entities from page 1 and get 10 records firstly. The result should be a list of task records in the database via the back-end

As you see above, we define 2 fields for each entity which we have in the database. For example, if you have 10 entities in DbContext, then we have 10 x 2=20 fields in the GraphQL definition. Let me show you a code

public class GraphQLQuery : ObjectGraphType<object>{private IDatabaseMetadata _dbMetadata;private ITableNameLookup _tableNameLookup;private DbContext _dbContext;

public GraphQLQuery(  
    DbContext dbContext,   
    IDatabaseMetadata dbMetadata,  
    ITableNameLookup tableNameLookup)  
{  
    \_dbMetadata = dbMetadata;  
    \_tableNameLookup = tableNameLookup;  
    \_dbContext = dbContext;

    Name = "Query";

    foreach (var metaTable in \_dbMetadata.GetTableMetadatas())  
    {  
        var tableType = new TableType(metaTable);  
        var friendlyTableName = \_tableNameLookup.GetFriendlyName(metaTable.TableName);

        AddField(new FieldType  
        {  
            Name = friendlyTableName,  
            Type = tableType.GetType(),  
            ResolvedType = tableType,  
            Resolver = new MyFieldResolver(metaTable, \_dbContext),  
            Arguments = new QueryArguments(  
                tableType.TableArgs  
            )  
        });

        // lets add key to get list of current table  
        var listType = new ListGraphType(tableType);  
        AddField(new FieldType  
        {  
            Name = $"{friendlyTableName}\_list",  
            Type = listType.GetType(),  
            ResolvedType = listType,  
            Resolver = new MyFieldResolver(metaTable, \_dbContext),  
            Arguments = new QueryArguments(  
                tableType.TableArgs                          
            )  
        });  
    }  
}

}

We based on the schema that we got from previous steps to define the fields for the GraphQL query. Is it make sense to you?

Graph Controller

This final step of this article, we define the controller so that we can run the application. It is really simple as below

[Route("graphql/api/query")]public class GraphQLController : Controller{private readonly Schema graphQLSchema;

public GraphQLController(Schema schema)  
{  
    graphQLSchema = schema;  
}

\[HttpPost\]  
public async Task<string> Get(\[FromQuery\] string query = "{ tasks\_list(offset:1, first:10) { id, name } }")  
{  
    var result = await new DocumentExecuter().ExecuteAsync(  
        new ExecutionOptions()  
        {  
            Schema = graphQLSchema,  
            Query = query                      
        }  
    ).ConfigureAwait(false);

    if (result.Errors?.Count > 0)  
    {  
        return result.Errors.ToString();  
    }

    var json = new DocumentWriter(indent: true).Write(result.Data);

    return json;  
}

}

Don’t forget to wire up all components to the IOC container as

public static class ServiceCollectionExtensions{public static IServiceCollection AddMyGraphQL(this IServiceCollection services){services.AddScoped<ITableNameLookup, TableNameLookup>();services.AddScoped<IDatabaseMetadata, DatabaseMetadata>();services.AddScoped((resolver) =>{var dbContext = resolver.GetRequiredService<ApplicationDbContext>();var metaDatabase = resolver.GetRequiredService<IDatabaseMetadata>();var tableNameLookup = resolver.GetRequiredService<ITableNameLookup>();

        var schema = new Schema { Query = new GraphQLQuery(dbContext, metaDatabase, tableNameLookup) };  
        schema.Initialize();

        return schema;  
    });

    return services;  
}

}

Put it all together

Source

After finished all the steps above, the structure of this as below

This is just a POC project which I will have to do more refactor in the future. Not sure just yet. Let run it by press F5, I will show you how it works

Let input some of GraphQL query above then click Try it out!

The database schema automatically loads as

And it builds out all the GraphQL fields as

With { tasks_list(offset:1, first:10) { id, name } } for the input, you will receive

If I change it to { tasks(id: “621CFF32-A15D-4622–9938–0028EA0C3FEE”) { name, id, taskStatus } }, it should be

That enough for today :) Let me know how do you feel.

Source code

All the source code can be found at https://github.com/vietnam-devs/crmcore. If you like it give me a clap, I will actually have more motivation to it better.

Recap

This article is all about the POC that I have done for combining between Entity Framework Core and GraphQL.NET. The result that we can dynamically use some of the libraries in .NET for making the query more dynamic and flexible in the real business cases. But there are some of the caveats as following

Authentication and authorization for entities didn’t mention yet.
Maybe we can hide and don’t expose everything out of the world like this article.
Mutation side and other concepts of GraphQL were not mentioned just yet this article.
Not integrating with the front-end (react/redux with Apollo client lib).
Solution for child relationships with the current entity (I haven’t had a solution yet, and welcome if anyone can help on that :p)
You name it and let me know in the comments then I can improve it later.

Additional Readings

Thanks for reading! If you enjoyed this article, be sure to CLICK 👏 symbol below so OTHERS will SEE it.

13 Feb 2019: Correct some of meanings and typos in this article