Some Extension Methods for SqlKata to Avoid Using Hardcoded Strings

Marc Clifton

Rate me:

5.00/5 (5 votes)

8 Feb 2022CPOL

7.2K

SqlKata is cool but having to use hardcoded strings for tables and fields in not my cup of tea.

SqlKata is useful for working with Dapper as I can write a fluent Linq-like syntax rather than hard-coding SQL statements. Unfortunately, SqlKata relies on hard-coded strings, so I implemented some extension methods to use explicit generic parameters instead.

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

License

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

Written By

Marc Clifton

Architect Interacx

United States

Blog: https://marcclifton.wordpress.com/
Home Page: http://www.marcclifton.com
Research: http://www.higherorderprogramming.com/
GitHub: https://github.com/cliftonm

All my life I have been passionate about architecture / software design, as this is the cornerstone to a maintainable and extensible application. As such, I have enjoyed exploring some crazy ideas and discovering that they are not so crazy after all. I also love writing about my ideas and seeing the community response. As a consultant, I've enjoyed working in a wide range of industries such as aerospace, boatyard management, remote sensing, emergency services / data management, and casino operations. I've done a variety of pro-bono work non-profit organizations related to nature conservancy, drug recovery and women's health.

// get table var tableName = Db.T<MyClass>(); // get column var columnNameWithTablePrefix = Db.C<MyClass>(t => t.Title); // query database (this could be improved with more Expression parsing to db.GetAsync<MyClass>(c => c.Id === idValue).ConfigureAwait(false);) var item = await db.GetAsync<MyClass>(q => q.Where<MyClass>(c => c.Id, idValue)).ConfigureAwait(false); // extension methods public static class Db { private static readonly ConcurrentDictionary<Type, string> TableNames = new(); /// <summary> /// Gets the full SQL table name (includes schema) /// </summary> public static string GetTableName(this Type type) { return TableNames.GetOrAdd(type, t => { var tableAttribute = t.GetCustomAttribute<TableAttribute>(false); return tableAttribute?.Name ?? type.Name; }); } /// <summary> /// Gets the full SQL table name (includes schema) /// </summary> public static string T<T>() { return typeof(T).GetTableName(); } /// <summary> /// Gets the full SQL column name (includes schema, table name, and column name) /// </summary> public static string C<T>(Expression<Func<T, object>> expression) { var tableName = T<T>(); var columnName = ExpressionUtils.GetMemberName(expression); return $"{tableName}.{columnName}"; } /// <summary> /// Gets the full SQL column names (includes schema, table name, and column name) /// </summary> public static string[] C<T>(params Expression<Func<T, object>>[] expressions) { var tableName = T<T>(); return ExpressionUtils.GetMemberNames(expressions) .Select(c => $"{tableName}.{c}").ToArray(); } /* IEnumerable<T> GetMulti<T>(this IDbConnection db, Func<Query, Query> queryBuilder = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) { ... } Task<IEnumerable<T>> GetMultiAsync<T>(this IDbConnection db, Func<Query, Query> queryBuilder = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) { ... } IEnumerable<TPoco> GetMulti<T, TPoco>(this IDbConnection db, Func<Query, Query> queryBuilder = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) { ... } ... Others include: - Get<T>, GetAsync<T>, Count<T>, CountAsync<T>, Scalar, Exists, Delete, Update ... Query extensions that can be leveraged in the queryBuilder clauses in methods above Query Where<T>(this Query target, Expression<Func<T, object>> column, string op, object value) { ... } // 100s of these - WhereNot<T>, OrWhere<T>, OrWhereNot<T>, WhereColumns<T>, OrWhereColumns<T>, WhereNull<T>, OrWhereNull<T>, WhereNotNull<T>, OrWhereNotNull<T>, Date, Avg, Min, Max, Sum, Having, OrderBy, GroupBy, ... Also Joins (left, right, cross, ...) Query Join<T, TOther>(this Query target, Expression<Func<T, object>> first, Expression<Func<TOther, object>> second, string op = "=", string type = "inner join") where TOther : IDao Query LeftJoin<T, TOther>(this Query target, Expression<Func<T, object>> first, Expression<Func<TOther, object>> second, string op = "=") where TOther : IDao ... with different query types Query AsInsert<T>(this Query target, IEnumerable<Expression<Func<T, object>>> columns, IEnumerable<IEnumerable<object>> rowsValues) also: AsUpdate, AsDelete, etc... */ }

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