Some Extension Methods for SqlKata to Avoid Using Hardcoded Strings

Introduction

A coworker recently introduced me to SqlKata which I find useful for working with Dapper (which SqlKata uses behind the scenes) as I can write a fluent Linq-like syntax rather than hard-coding SQL statements.

Unfortunately, as the examples illustrate, this all uses hardcoded strings:

var query = db.Query("Books").OrderByDesc("PublishingDate");

Not my cup of tea!

Some Basic Extension Methods

This tip & trick gives you a flavor for extension methods that you can write to avoid hardcoded strings using explicitgeneric parameters. For example, I use this query in another article:

var query = db.Query<Role>()
  .Join<Role, UserRole>()
  .Join<Role, EntityRole>()
  .JoinChild<EntityRole, Entity>()
  .Where<Entity>(nameof(Entity.TableName), entityName)
  .Where<UserRole>(nameof(UserRole.UserId), userId);

Rather than:

var query = db.Query("Role")
  .Join("UserRole", "Role.Id", "UserRole.RoleId")
  .Join("EntityRole", "Role.Id", "EntityRole.RoleId")
  .Join("Entity", "Entity.Id", "EntityRole.EntityId")
  .Where("Entity.TableName", entityName)
  .Where("UserRole.UserId", userId);

This requires four extension methods:

public static class SqlKataExtensionMethods
{
    public static Query Query<T>(this QueryFactory qf)
    {
        return qf.Query(typeof(T).Name);
    }

    public static Query Join<R, T>(this Query q)
    {
        var rname = typeof(R).Name;
        var tname = typeof(T).Name; 

        return q.Join($"{tname}", $"{rname}.Id", $"{tname}.{rname}Id");
    }

    public static Query JoinChild<R, T>(this Query q)
    {
        var rname = typeof(R).Name;
        var tname = typeof(T).Name;

        return q.Join($"{tname}", $"{tname}.Id", $"{rname}.{tname}Id");
    }

    public static Query Where<T>(this Query q, string field, object val)
    {
        return q.Where($"{typeof(T).Name}.{field}", val);
    }
}

and, of course, requires some minimal model implementation for this example:

public class Role { }
public class UserRole 
{
    public int UserId { get; set; }
}
public class EntityRole { }
public class Entity 
{
    public string TableName { get; set; }
}

Conclusion

That's really all there is to it. You probably have never heard of SqlKata and neither had I until a couple weeks ago, and using it is nice but I dislike having to use string constants. If you are using SqlKata and like this concept, I'm sure you can add more extension methods for the other functions that SqlKata can be used for. If you do write some, post them here!

History

• 8^th February, 2022: Initial version