Click here to Skip to main content
14,978,337 members
Articles / Web Development / ASP.NET / ASP.NET Core
Article
Posted 7 Jan 2018

Stats

18.7K views
19 bookmarked

How to Implement Generic Queries by Combining EntityFramework Core and GraphQL.NET?

Rate me:
Please Sign up or sign in to vote.
5.00/5 (16 votes)
8 Jan 2018CPOL5 min read
This article will show you how to expose the database schema to the APIs, then query from it. No boilerplate code for simple query actions. Go and read it.

Introduction

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

I spent a long time to have a look at OData project (parser part actually), but was not satisfied. Maybe because it is too complex and a lot of schemas are needed to be defined up-front. And moreover, it is only used by .NET ecosystem (Microsoft Dynamic CRM, Microsoft Sharepoint… used that).

I wanted a solution that could work and adapt well to other ecosystems like front-end, mobile or maybe IoT devices can consume the data output easily. That is a reason I chose GraphQL by Facebook. About this one, I think I don’t need to explain much 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. We can query from the front-end using gql DSL language and query every entity in your database.

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

Prerequisites

Database Schema

Image 1

Graph Schema Model

We define the model for columns in database as follows:

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

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

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

Because we do the prefix for the table in the database (e.g., dbo.crm_Tasks) so that we need the mapping table that will help us to resolve the friendly name (e.g., tasks when we do the query in UI).

C#
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 let's put all the schema models into the database metadata as below:

C#
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 it will be populated all the schema information of EfCore entities when the application started.

Graph Type

Let's now define the type of the GraphQL as below:

C#
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<IdGraphType> { Name = "first" });
        TableArgs.Add(new QueryArgument<IdGraphType> { Name = "offset" });
    }

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

        return typeof(String);
    }
}

This punch of code above will 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 show you the result of the query.

Graph Resolver

To make the GraphQL understand the database schema that we want to populate, we need to create some of the revolvers as follows:

C#
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 the resolver for each field in the database schema:

C#
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", id);
        }
    }
}

Have you seen 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 more work. And one more thing that makes the query of the assembly name as:

C#
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);
}

You can refer to this link for the solution.

Graph Query

Now is the time we need to define the query for our application. There are two things to notice as follows:

  • If we want to query the entity like { tasks (id: “<id here>”) {id, name} }, then we point it based on the database schema that we have defined so far and the result will only have one record in the output.
  • But if we want to query the list of entities like { tasks_list (offset:1, first:10) { id, name } } query the list of page 1 and get 10 records, then the result should be a list of records.

As you see above, we need to define 2 fields for each entity 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 the code:

C#
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. Does it make sense to you so far?

Graph Controller

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

C#
[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 the things to the IOC container as:

C#
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

After finishing all the code above, the structure of this is as below:

Image 2

This is only a POC project so that I will have more refactoring in the future. Let's run it by pressing F5, I will show you how it works.

Image 3

Let's input some of the GraphQL query above, then click Try it out!

Image 4

The database schema automatically loads as:

Image 5

And it builds out all the GraphQL fields as:

Image 6

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

Image 7

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

Image 8

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

Source Code

All the source code can be found at CRMCore.Module.GraphQL. If you like it, give me a star, then I will have more motivation to it better for the community.

Points of Interest

This article is just the POC that I have done for query side. Now you know we can dynamically use some of the libraries in .NET for making the query more dynamic and flexible in the wild world. But some of the caveats I didn’t mention in this article are as follows:

  • Authentication and authorization for entities didn’t mention yet.
  • Maybe we can hide and don’t expose everything out of the world like this.
  • I will continue to work on a mutation side and other concepts of GraphQL in other articles. And also, I will integrate with the front-end (react/redux with Apollo client lib) next time.
  • Solution for child relationships with the current entity (I haven’t had a solution yet.)
  • You name it and let me know in the comments, then I can improve it later.

Additional Reading

History

  • 9th January, 2018: Corrected typos
  • 8th January, 2018: Wrote out the article

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

thangchung
Architect NashTech
Vietnam Vietnam
Thang has a great passion in .NET, JavaScript, and Node. You can visit Thang's blog at https://weblogs.asp.net/thangchung to read about his thoughts on software development.

Comments and Discussions

 
QuestionAlso source code on GitHub seems be a much larger thing than this article Pin
Sacha Barber8-Jan-18 21:39
mvaSacha Barber8-Jan-18 21:39 
AnswerRe: Also source code on GitHub seems be a much larger thing than this article Pin
thangchung8-Jan-18 21:44
professionalthangchung8-Jan-18 21:44 
GeneralRe: Also source code on GitHub seems be a much larger thing than this article Pin
Sacha Barber9-Jan-18 0:42
mvaSacha Barber9-Jan-18 0:42 
QuestionHardcoded to work with this simple example? Pin
Sacha Barber8-Jan-18 21:30
mvaSacha Barber8-Jan-18 21:30 
AnswerRe: Hardcoded to work with this simple example? Pin
thangchung8-Jan-18 21:39
professionalthangchung8-Jan-18 21:39 
GeneralRe: Hardcoded to work with this simple example? Pin
Sacha Barber9-Jan-18 0:42
mvaSacha Barber9-Jan-18 0:42 
OK, understood

GeneralThanks a lot Pin
Ming Xin7-Jan-18 21:00
professionalMing Xin7-Jan-18 21:00 
GeneralRe: Thanks a lot Pin
thangchung8-Jan-18 21:45
professionalthangchung8-Jan-18 21:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.