Presentation in Norwegian presenting demo repository with GraphQL and Hotchocolate/Strawberryshake

I have written a presentation about GraphQL demo repository of mine here. It is in Norwegian, so it will not be translated to english here. For Norwegian readers : Jeg har skrevet en presentasjon på norsk om GraphQL som går igjennom et demo repository som benytter GraphQL, med HotChocolate i backend, sammen med Entity Framework Core 6 og .net 6 (C# selvsagt) og som i frontend benytter Blazor og StrawberryShake ! Dere kan lese de 43 slidesene vedlagt OneDrive lenken under. I foredraget går jeg igjennom key giveaways om GraphQL, inkludert case insensitive søk, omtale om paginerte data og projisering og gir et overblikk av hva GraphQL går ut på og hvilke fordeler man kan få ut av det. Sentrale fordeler med GraphQL er : * Fleksibilitet - spesifiser hvilke felter du vil ha for å unngå "overfetching" * -Ytelse - færre API kall og unngår waterfall opphenting hvor man må hente opp stadig flere ressurser som i fra et REST API, men får en aggreggert tilpasset struktur av de data man faktisk vil ha tilbake * Ett felles endepunkt /graphql - man slipper å lage controllere som i REST API - som ofte føles som unødvendig. GraphQL er ikke noe som kan løse alle utfordringer i API design, men det kan gi klienter mye mer fleksibilitet og også unngå at API designere må stadig lage flere metoder og som har både "overfetching" eller enda verre - underfetching - som gir flere API kall og dårligere ytelse. Man utnytter båndbredde og serverressurser bedre ved å kun hente ut informasjon man trenger. Og GraphQL er ikke bare orientert rundt spørringer, men også endringer (mutations), pub sub event pattern (Subscriptions) og en hel del annen funksjonalitet som tilhører API design ! Du kan lese Powerpoint presentasjonen her (43 slides, lesetid ca 1 time om du vil studere det nøye, en 15 minutt om du vil skumlese mer). #blazor #hotchocolate #strawberryshake #chillicream #apidesign #csharp #dotnet #codinggrounds The presentation is here :

Powerpoint presentation (Norwegian, 11th december 2022) : https://1drv.ms/u/s!AhGGDxs-tzqJcFrls6Fue8Xnjx4?e=lWYYwU

Case insensitive search in HotChocolate GraphQL

I tested out the contains operator on string fields today in GraphQL. It is actually case sensitive, and this is counter-intuitive since I have connected the GraphQL database to a SQL database, which performs usually a case insensitive search with the 'contains' operator (using 'LIKE' operator
under the hood). The following adjustments need to be made to make it work : First off, define a class inheriting QueryableStringOperationHandler

 

using HotChocolate.Data.Filters;
using HotChocolate.Data.Filters.Expressions;
using HotChocolate.Language;
using System.Linq.Expressions;
using System.Reflection;

namespace AspNetGraphQLDemoV2.Server
{
    public class QueryableStringInvariantContainsHandler : QueryableStringOperationHandler
    {
        private static readonly MethodInfo _contains = typeof(string).GetMethod(nameof(string.Contains), new[] { typeof(string) })!;

        public QueryableStringInvariantContainsHandler(InputParser inputParser) : base(inputParser)
        {
        }

        protected override int Operation => DefaultFilterOperations.Contains;
        public override Expression HandleOperation(QueryableFilterContext context, IFilterOperationField field, 
            IValueNode value, object? parsedValue)
        {
            Expression property = context.GetInstance();
            if (parsedValue is string str)
            {
                var toLower = Expression.Call(property, typeof(string).GetMethod("ToLower", Type.EmptyTypes)!); //get the ToLower method of string class via reflection. The Type.EmptyTypes will retrieve the method overload of ToLower which accept no arguments.
                var finalExpression = Expression.Call(toLower, _contains, Expression.Constant(str.ToLower()));
                return finalExpression;

            }
            throw new InvalidOperationException();
        }
    }
}

 

We overload the Operation to 'Contains' so we are going to adjust how we treat the expression tree of GraphQL, overriding the HandleOperation. This is similar to the QueryableStringOperationHandler presented here: https://chillicream.com/docs/hotchocolate/api-reference/extending-filtering, in our case we support the contains method instead. The finalExpression 'DebugView' evaluates to :
.Call (.Call ($_s0.OfficialName).ToLower()).Contains("tind") To actually use this adaption of filtering of string properties in HotChocolate, we do the following in program.cs (startup class in .net 6) to not only add filtering support to HotChocolate, but also add a filter convention extension first to make it easier to register and avoid adding cluttering to the startup code in program.cs :

 
 using HotChocolate.Data.Filters;
using HotChocolate.Data.Filters.Expressions;

namespace AspNetGraphQLDemoV2.Server
{
    public class FilterConventionExtensionForInvariantContainsStrings : FilterConventionExtension
    {
        protected override void Configure(IFilterConventionDescriptor descriptor)
        {
            descriptor.AddProviderExtension(new QueryableFilterProviderExtension(
                                    y => y.AddFieldHandler<QueryableStringInvariantContainsHandler>()));
        }    
    }
}

 

You can see an example how I register this case insensitive contains filter in the program.cs example code below. Note both the usage of .AddFiltering() and the .AddConvention() call.

 
 
var builder = WebApplication.CreateBuilder(args);
var connectionString = builder.Configuration.GetConnectionString("MountainsV2Db");
builder.Services
    .AddDbContext<MountainDbContext>(options =>
    {
        options.UseSqlServer(connectionString);
    })
    .AddCors()
    .AddGraphQLServer()
    .AddProjections()
    .AddFiltering()
    .AddConvention<IFilterConvention, FilterConventionExtensionForInvariantContainsStrings>()
    .AddSorting()
    .RegisterDbContext<MountainDbContext>()
    .AddQueryType<MountainQueries>()
    .AddMutationType<MountainMutations>()
    .AddSubscriptionType<MountainSubscriptions>()
    .AddInMemorySubscriptions();

var app = builder.Build();
 

Now, sample .graphql file (containing a GraphQL query) that shows how the new filtering capability can be used :

 
 query {
  mountains (where: { officialName: {contains: "TiND"}}) {
    officialName
  }
}
 

The backend code retrieves a list of data from a table and I have added the [UseFiltering] attribute to specify for HotChocolate that filtering should be supported to the method.

 
   public class MountainQueries
    {
        [UseFiltering]
        [UseSorting]
        public async Task<List<Mountain>> GetMountains([Service] MountainDbContext mountainDb)
        {
            return await mountainDb.Mountains.ToListAsync();
        }

//..
 

Faster expansion of databases in Sql Server Management Studio

I have observed a slow expansion of databases tree in Sql Server Management Studio. This can actually be fixed on many Sql Server instances by running this T-SQL :

 
EXECUTE sp_MSforeachdb
'
IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))
   EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')'
 
 

Now, this does not look that much similar to SQL, since it is T-SQL. But it will execute to disable auto_close for every database on your database server (that is, 'instance') and this will make expanding databases in your database tree fast again in SSMS ! This is a variant of what Pinal Dave mentions here, I just made an iterative variant of what he suggests : https://blog.sqlauthority.com/2016/09/22/sql-server-set-auto_close-database-option-off-better-performance/