landmarkhw / dapper.graphql Goto Github PK

Hi, I'm trying to implement Dapper.GraphQL and streamline the process of adding our several models.

The existing examples are great but I'm having a bit of difficulty on understanding how to put the pieces together when considering several models in a big project. Based on the existing examples, is it possible to have a single Schema for all of our queries, for example? If not, how should I properly DI them into my single GraphQL controller? The example seems a bit locked on PersonSchema.

Also, should I just assume we'll need an EntityMapper, Query, QueryBuilder, Schema, Type and Model for each Model in our business logic, from which we manually add each Type, Query, Schema and QueryBuilder to DapperGraphQL options on services configuration @ Startup.cs? Would that be best practice? Is there any way to do so dynamically?

Some hints would be greatly appreciated!

I appreciate this probably isn't specific to Dapper/GraphQL but never the less I am struggling to get a cache to work at all:-

First of all in ConfigureServices in Startup.cs, I am bringing in the DistributedMemoryCache like so:

services.AddDistributedMemoryCache();

and in Configure, I am getting some values from the DB and caching them:

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory, IDbConnection dbConnection, IDistributedCache cache)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            loggerFactory.AddConsole(Configuration.GetSection("Logging"));
            loggerFactory.AddDebug();

            app.UseStaticFiles();
            app.UseMvc();

            using (var connection = dbConnection)
            {
                connection.Open();
                SqlCommand command = new SqlCommand("SELECT [AttributeName],[ObjectTypeCode],[AttributeValue],[Value] FROM StringMapBase WHERE LangId = 1033 ORDER BY [AttributeName]", connection as SqlConnection);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var attributeName = reader.GetString(0);
                        var objectTypeCode = reader.GetInt32(1);
                        var attributeValue = reader.GetInt32(2);
                        var value = reader.GetString(3);

                        // Add to cache
                        var key = $"optionset_{attributeName}_{objectTypeCode}_{attributeValue}";
                        cache.SetString(key, value);
                    }
                }
            }
        }

This works ok and I can break point after setting the cache to confirm entries are added.

In my GraphQL type, ContactType I have the following code:-

public class ContactType : ObjectGraphType<Contact>
    {
        IDistributedCache _cache;
        public ContactType(IDistributedCache cache)
        {
            _cache = cache;

            Name = "contact";
            Description = "A contact.";

            Field<StringGraphType>(
                "t4a_title_name",
                description: "The t4a_title_name of the contact.",
                resolve: context =>
                {
                    var attributeName = context.FieldName.Replace("_name", "");
                    var objectTypeCode = 2;
                    var attributeValue = context.Source?.t4a_title;
                    var key = $"optionset_{attributeName}_{objectTypeCode}_{attributeValue}";
                    var value = _cache.GetString(key);
                    return value;
                });
        }
    }

Problem here is that the whole cache is empty, IDistributedCache is suppose to be a singleton so not sure why there is nothing there. Likewise, I have tried this with the standard IMemoryCache implementation which I know is a singleton but I get the same results, an empty cache.

I spent hours trying to work it out but if anyone here can help me with getting a value from a cache on resolve using GraphQL/Dapper I would really appreciate it and thanks in advance.

Is there a test case or an example on how to setup a mutation?

I'm not sure if I'm just tired and stupid, but I have gone through all the examples and I cannot seem to find a way to limit the number of results returned...

Is this a limitation at the moment?

Any particular reason where there wouldn't be a space or line break before my FROM and WHERE keywords? I'm following the sample exactly and the generated SQL won't run because the select fields don't have a space or line break before the FROM so it looks like this:

SELECT table.Column1, table.Column2FROM Table tableWHERE 1 = 1

How does one go about implementing a many to many relationship using a through table? Do we have an example? i.e. Do I have to create an entity mapper/builder for the through table or is there a way to go from source entity to destination entity?

Adding the following:

IHaveSelectionSet.GetInlineFragment()
EntityMapContext.NextFragment()

I am having trouble when trying to map two of the same model type in my entity mapper. Is there a way to handle this?

Model

public class Account
{
public Guid AccountId { get; set; }
public string Name { get; set; }
public Contact PrimaryContact { get; set; }
public Contact SecondaryContact { get; set; }
}

EntityMapper

public class AccountEntityMapper :
IEntityMapper
{
public Func<Account, Account> ResolveEntity { get; set; }
public Account Map(IEnumerable objs)
{
Account account = null;
foreach (var obj in objs)
{
if (obj is Account p)
{
account = ResolveEntity(p);
continue;
}
if (obj is Contact primaryContact)
{
account.PrimaryContact = primaryContact;
continue;
}
if (obj is Contact secondaryContact)
{
account.SecondaryContact = secondaryContact;
continue;
}
}
return account;
}
}

Hi,

First off thanks for the awesome work, looks like this might work for me. But first I have a few questions to get my head around this.

Am I right in saying that your library effectively maps graphql queries to dapper/SQL queries in a way that avoids the n+1 problem when one:many relationships are involved?

If so, is it doing this by effectively resolving the data for all the fields down the graph by the entry resolver, i.e. the resolver that the query is rooted at? Would this still work with the graphql-net's authorization toolset?

Thanks, Ben

Hi,

Interesting, is this compatible with graphql-dotnet's dataloader?

Setup a quick test project - this library works very well and is a real layer of sanity on top of graphql-dotnet. I noticed the querying is all sync, and presumably async would be better?

Looks like graphql-dotnet supports awaiting resolvers:
graphql-dotnet/graphql-dotnet#205 (comment)

At first glance, it looks like just SqlQueryContext needs an ExecuteAsync companion method.

Would this make sense/be within the goals of your library?

Hello, Doug,

I understand things have been a bit busy lately. Is there any plans to keep going with this library? There are couple of outstanding PRs/questions. I'd also like to see underlying graphql-dotnet library version get updated. It's on 3.0 pre-release with lots of bugs fixed some of which are helpful outside of Dapper.GraphQL context. Anyways, thought I'd just see where things were :) Thanks!

Could we support pluralized table names for all the generic methods like Insert<T>? I see it just defaults to a table with a name the same as type T. I prefer using Users as my table names instead of User.

We could use an attribute on the T classes, like TableName("Users") that is checked before the default is used. What do you think? Or go down a fluent-style declaration in Startup.cs with some extension methods. Or support both even.

Current implementation of ExecuteWithSqlIdentity and the equivalent SqlLite implementation require the developer to specify the type of their entity's identifier (int, long, etc). Problem is this could change over time, e.g. changing User.Id from int to long as your user base grows. This would be a tricky change as the ExecuteWithSqlIdentity<TIdentityType> method isn't linked (in a refactoring sense) to the TEntity it identifies.

Can we use a method signature like:
TIdentityType ExecuteWithSqlIdentity<TEntity, TIdentityType>(this SqlInsertContext context, IDbConnection dbConnection, Func<TEntity, TIdentityType> identityTypeSelector);

I'm just building up a basic solution with the example docs and this method isn't resolving from me. Doesn't look like it's in the nuget released assembly. Thanks!

I know this topic is not popular with GraphQL community for a set of valid reasons. Nevertheless, there is definitely a demand for this functionality for applications that are DB driven. If we don't have a semi-built-in way of dealing with operators, every person writing resolvers that eventually translate into SQL statements will have to roll their own. Should this be a part of this library similar to EF-backed one?

I go back and forth on the whole idea. There is definitely performance implications around querying DB in an uncontrolled way with potentially multiple nested comparison statements. But I struggle to find an alternative for the advanced filtering that would be both, safe and performant.

Was wondering if there's a timeline for upgrading graphql-dotnet to 2.x? I understand there's one breaking change but it sounds like @benmccallum was able to work around it. Any other reasons to not move forward with it?

This is a part of code

using System;
using System.Collections.Generic;
using System.Text;

namespace Main.GraphQL.Models {
  public class User {
    //public string FirstName { get; set; }
    public int id { get; set; }
    //public string LastName { get; set; }
    public string nick { get; set; }
    public string name { get; set; }
    public string photo { get; set; }
    public string password { get; set; }
    public IList<Message> messages { get; set; }
    public IList<Channel> channels { get; set; }

    public User() {
      this.messages = new List<Message>();
      this.channels = new List<Channel>();
    }
  }
}

using System;
using System.Collections.Generic;
using System.Text;
using GraphQL.Language.AST;
using Dapper.GraphQL;
using Main.GraphQL.Models;
using Main;

namespace Main.GraphQL.QueryBuilders {
  public class UserQueryBuilder : IQueryBuilder<UserQueryBuilder> {
    private IQueryBuilder<MessageQueryBuilder> message;
    private IQueryBuilder<ChannelQueryBuilder> channel;

    public void User(IQueryBuilder<MessageQueryBuilder> message, IQueryBuilder<ChannelQueryBuilder> channel) {
      this.message = message;
      this.channel = channel;
    }

    public string id { get; }
    public string nick { get; set; }
    public string name { get; set; }
    public string photo { get; set; }
    public string password { get; set; }

    public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias) {
      var mergedAlias = $"{alias}Merged";

      var fields = context.GetSelectedFields();

      SQLBuilder.field("id", ref fields, ref query, mergedAlias);
      SQLBuilder.field("nick", ref fields, ref query, mergedAlias);
      SQLBuilder.field("name", ref fields, ref query, mergedAlias);
      SQLBuilder.field("photo", ref fields, ref query, mergedAlias);
      SQLBuilder.field("password", ref fields, ref query, mergedAlias);

      SQLBuilder.join<MessageQueryBuilder>("messages", $"{alias}Message", mergedAlias, ref fields, ref query, this.message);
      SQLBuilder.join<ChannelQueryBuilder>("channels", $"{alias}Channel", mergedAlias, ref fields, ref query, this.channel);

      return query;
    }
  }
}

using System;
using System.Collections.Generic;
using System.Text;
using GraphQL.Language.AST;
using Dapper.GraphQL;
using Main.GraphQL.Models;
using Main;

namespace Main {
  public static class SQLBuilder {
    public static void field(string field, ref IDictionary<string, Field> fields, ref SqlQueryContext query, string alias) {
      if (fields.ContainsKey(field)) {
        query.Select($"{alias}.{field}");
      }
    }

    public static void join<T>(string field, string alias, string parent, ref IDictionary<string, Field> fields, ref SqlQueryContext query, IQueryBuilder<T> obj/*, ref Fields func*/) {
      ;
      if (fields.ContainsKey(field)) {
        query.LeftJoin($"Phone {alias} ON {parent}.id_{alias} = {alias}.id");
        query = obj.Build(query, fields[field], alias);
      }
    }
  }
}

using Main.GraphQL.Models;
using GraphQL.Types;
using System;
using System.Collections.Generic;
using System.Text;

namespace Main.GraphQL.Types {
  public class UserType : ObjectGraphType<User> {
    public UserType() {
      Name = "user";
      Description = "An user.";

      Field<IntGraphType>(
        "id",
        description: "A unique identifier for the user.",
        resolve: context => context.Source?.id
      );

      Field<StringGraphType>(
        "nick",
        description: "The nickname of the user.",
        resolve: context => context.Source?.nick
      );

      Field<StringGraphType>(
        "name",
        description: "The name of the user.",
        resolve: context => context.Source?.name
      );

      Field<StringGraphType>(
        "photo",
        description: "The photo of the user.",
        resolve: context => context.Source?.photo
      );

      Field<StringGraphType>(
        "password",
        description: "The password of the user.",
        resolve: context => context.Source?.password
      );

      Field<ListGraphType<MessageType>>(
        "messages",
        description: "A list of messages for the user.",
        resolve: context => context.Source?.messages
      );

      Field<ListGraphType<ChannelType>>(
        "channels",
        description: "A list of channels for the user.",
        resolve: context => context.Source?.channels
      );
    }
  }
}

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.SpaServices.ReactDevelopmentServer;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Dapper.GraphQL;
using Main.GraphQL.Models;
using Main.GraphQL.QueryBuilders;
using Main.GraphQL.Types;

namespace Main {
  public class Startup {

    ...

    public void ConfigureServices(IServiceCollection services) {
      services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

      // In production, the React files will be served from this directory
      services.AddSpaStaticFiles(configuration => {
        configuration.RootPath = "ClientApp/build";
      });

      services.AddDapperGraphQL(options => {
        // Add GraphQL types
        options.AddType<UserType>();
        options.AddType<ChannelType>();
        options.AddType<MessageType>();
        options.AddType<FallacyType>();

        // Add the GraphQL schema
        //options.AddSchema<ArgumentSchema>();
        //options.AddSchema<ChannelSchema>();

        // Add query builders for dapper
        options.AddQueryBuilder<User, UserQueryBuilder>();
        options.AddQueryBuilder<Channel, ChannelQueryBuilder>();
        options.AddQueryBuilder<Message, MessageQueryBuilder>();
        options.AddQueryBuilder<Fallacy, FallacyQueryBuilder>();
      });
    }

    ...

  }
}

Startup.cs(42,9): error CS0311: The type 'Main.GraphQL.QueryBuilders.UserQueryBuilder' cannot be used as type parameter 'TQueryBuilder' in the generic type or method 'DapperGraphQLOptions.AddQueryBuilder<TModelType, TQueryBuilder>()'. There is no implicit reference conversion from 'Main.GraphQL.QueryBuilders.UserQueryBuilder' to 'Dapper.GraphQL.IQueryBuilder<Main.GraphQL.Models.User>'. [/home/jefer/prog/main/main.csproj]
Startup.cs(43,9): error CS0311: The type 'Main.GraphQL.QueryBuilders.ChannelQueryBuilder' cannot be used as type parameter 'TQueryBuilder' in the generic type or method 'DapperGraphQLOptions.AddQueryBuilder<TModelType, TQueryBuilder>()'. There is no implicit reference conversion from 'Main.GraphQL.QueryBuilders.ChannelQueryBuilder' to 'Dapper.GraphQL.IQueryBuilder<Main.GraphQL.Models.Channel>'. [/home/jefer/prog/main/main.csproj]
Startup.cs(44,9): error CS0311: The type 'Main.GraphQL.QueryBuilders.MessageQueryBuilder' cannot be used as type parameter 'TQueryBuilder' in the generic type or method 'DapperGraphQLOptions.AddQueryBuilder<TModelType, TQueryBuilder>()'. There is no implicit reference conversion from 'Main.GraphQL.QueryBuilders.MessageQueryBuilder' to 'Dapper.GraphQL.IQueryBuilder<Main.GraphQL.Models.Message>'. [/home/jefer/prog/main/main.csproj]
Startup.cs(45,9): error CS0311: The type 'Main.GraphQL.QueryBuilders.FallacyQueryBuilder' cannot be used as type parameter 'TQueryBuilder' in the generic type or method 'DapperGraphQLOptions.AddQueryBuilder<TModelType, TQueryBuilder>()'. There is no implicit reference conversion from 'Main.GraphQL.QueryBuilders.FallacyQueryBuilder' to 'Dapper.GraphQL.IQueryBuilder<Main.GraphQL.Models.Fallacy>'. [/home/jefer/prog/main/main.csproj]

The build failed. Please fix the build errors and run again.

I want my QueryBuilder.Build() to look like this:

            query
                .Select($"{alias}.EmployeeId", $"{alias}.Forename", $"{alias}.Surname", $"{alias}.MobileNumber", $"{alias}.Postcode")
                .AndWhere($"{alias}.IsDeleted = 0")
                .SplitOn<Employee>($"{alias}.EmployeeId");

            var fields = context.GetSelectedFields();

            if (fields.ContainsKey("depot"))
            {
                var depotAlias = "depot";
                query.LeftJoin($"Depot {depotAlias} ON {alias}.DepotId = {depotAlias}.DepotId");
                _depotQueryBuilder.Build(query, fields["depot"], depotAlias);
            }

            if (fields.ContainsKey("weeklyRosters"))
            {
                var weeklyRosterAlias = "weeklyRoster";
                query.LeftJoin($"WeeklyRoster {weeklyRosterAlias} ON {alias}.DepotId = {weeklyRosterAlias}.DepotId");
                _weeklyRosterQueryBuilder.Build(query, fields["weeklyRosters"], weeklyRosterAlias);
            }

            return query;

But this throws an ArgumentException in Dapper.SqlMapper.GetNextSplit() saying "When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id". Adding a call to SqlQueryContext.RemoveSingleTableQueryItems() in SqlQueryContext.SplitOn() fixes the problem, so that we get the right number of entries in SqlQueryContext._types.

We need performance-based unit tests, to ensure expected operations are as fast as they should be, and we don't see any undue or unexpected declines in performance.

Add the ability to pass commandTimeout value through to Dapper on query.Execute. There are some use-cases where the default commandTimeout is not enough to run lengthy queries:-

example below but would be great to have this passed through as queryOptions or something:

public IEnumerable<TEntityType> Execute<TEntityType>(IDbConnection connection, Func<object[], TEntityType> map)
        {
            var results = connection.Query<TEntityType>(
                commandTimeout: 300,
                sql: this.ToString(),
                types: this._types.ToArray(),
                param: this.Parameters,
                map: map,
                splitOn: string.Join(",", this._splitOn)
            );
            return results.Where(e => e != null);
        }

Hi, what is the point of calling serviceCollection.AddDapperGraphQL? Looking at the test project, it does not look like any of the added instances are ever retrieved via serviceProvider.GetRequiredService calls. I've also seen an example work without using AddDapperGraphQL.

Please explain and/or add an example to the readme file explaining why one would use AddDapperGraphGL.

Thanks

Is there anyway to force the column names in select to have brackets around them?

Looks like a pretty cool library!

Not sure if it is well known, but the ResolveFieldContext does have a SubFields property which is the fields that should be requested. It looks like that Dapper.GraphQL may have a bug in that even though a field is in the SelectionSet, it may not necessarily be allowed to be returned (due to directives mainly).

I was just in the process of evaluating building something with this project, it looks very useful.

But having poked around just a little bit, I was wondering what the support status is for the new 3.0 version of graphql-dotnet. Apologies if I'm missing something, but it looks, just from examining the current .csproj file here on GitHub, that it currently supports graphql-dotnet 2.3.

I was wondering if this is something which is currently under active consideration - or something which the project might be interested in, as a PR, if someone else was to look at it - esp. since it appears there are several breaking changes between 2.3/2.4 and 3.0 of graphql-dotnet.