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/

DynamicObject : Thread safety and anonymous type initialization

This article will present code that shows how we can create a custom dynamic object and control its behavior, supporting thread safety when setting and getting members and allowing anonymous type initialization. The code is available in this GitHub repo : git clone https://github.com/toreaurstadboss/DynamicObjectThreadSafe.git The custom dynamic object inherits from the class DynamicObject and uses a thread safe dictionary.

 
 
 using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using System.Text.Json;
using System.Text.Json.Serialization;

namespace DynamicObjectThreadSafe
{
	public class ThreadSafeDynamicObject : DynamicObject, IEnumerable<KeyValuePair<string, object>>
	{

		public ThreadSafeDynamicObject()
		{
		}

		public ThreadSafeDynamicObject(dynamic members)
		{
			dynamic membersDict = ToDictionary(members);
			InitMembers(membersDict);
		}

		private IDictionary<string, object> ToDictionary(object data)
		{
			var attr = BindingFlags.Public | BindingFlags.Instance;
			var dict = new Dictionary<string, object>();
			foreach (var property in data.GetType().GetProperties(attr))
			{
				if (property.CanRead)
				{
					dict.Add(property.Name, property.GetValue(data, null));
				}
			}
			return dict;
		}

		private void InitMembers(IDictionary<string, object> membersDict)
		{
			foreach (KeyValuePair<string, object> member in membersDict)
			{
				_members.AddOrUpdate(member.Key, member.Value, (key, oldValue) => member.Value);
			}
		}

		private readonly ConcurrentDictionary<string, object> _members = new ConcurrentDictionary<string, object>();

		public override bool TryGetMember(GetMemberBinder binder, out object result)
		{
			return _members.TryGetValue(binder.Name, out result);
		}

		public override bool TrySetMember(SetMemberBinder binder, object value)
		{
			_members.AddOrUpdate(binder.Name, value, (key, oldvalue) => value);
			return true;
		}

		public override IEnumerable<string> GetDynamicMemberNames()
		{
			return _members.Keys.ToList().AsReadOnly();
		}

		public override string ToString()
		{
			return JsonSerializer.Serialize(_members);
		}

		public IEnumerator<KeyValuePair<string, object>> GetEnumerator()
		{
			return _members.GetEnumerator();
		}

		IEnumerator IEnumerable.GetEnumerator()
		{
			return _members.GetEnumerator();
		}
	}

}
 
 

The method ToDictionary transforms an input object, for example an anonymous class object via boxing (by accepting an object as parameter and implicitly 'boxing' it as a object) and the InitMember method will then populate the ConcurrentDictionary<string,object> object. This will allow us to pass anonymous objects and cast the object into a dynamic object, for further consumption. For example outputting fields of it. Now why would you use dynamic objects like this would you say? Dynamic objects are practical in many situations where you do not know the type until runtime. Many imlpementations of custom dynamic objects use Dictionary as a 'backing store' for the fields/properties/members of the object. This implementation uses ConcurrentDictionary and there should be thread safe concerning retrieving or setting members as shown in the overrides of methods TryGetMember and TrySetMember. The override for method GetDynamicMemberNames is for showing members in the debugger in the 'Dynamic view' to inspect the dynamic object properly. The GetEnumerator method overrides are to support casting the dynamic object to IDictionary<string,object> The following tests is then passing :

 
 
        [Fact]
        public void It_Can_Accept_Anonymous_Type_initialization()
        {
			dynamic threadSafeToyota = new ThreadSafeDynamicObject(new
			{
				Make = "Toyota",
				Model = "CR-H",
				Propulsion = new
				{
					IsHybrid = true,
					UsesPetrol = true,
					ElectricMotor = true
				}
			});

			Assert.Equal("Toyota", threadSafeToyota.Make);
			Assert.Equal("CR-H", threadSafeToyota.Model);
			Assert.Equal(true, threadSafeToyota.Propulsion.IsHybrid);
			Assert.Equal(true, threadSafeToyota.Propulsion.UsesPetrol);
			Assert.Equal(true, threadSafeToyota.Propulsion.ElectricMotor);
		}
 
 

And since this object is dynamic, we can extend it and adjust its members as dynamic allows you and as you can see we both can instantiate the dynamic object via anonymous type instance or populating it manually one property at a time. And doing so in a thread-safe many, for better support in multithreaded environments which are to expected many places today.