Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / C#
Tip/Trick

Some Extension Methods for SqlKata to Avoid Using Hardcoded Strings

Rate me:
Please Sign up or sign in to vote.
5.00/5 (5 votes)
8 Feb 2022CPOL 7K   2   2
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:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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

  • 8th 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
Architect Interacx
United States 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.

Comments and Discussions

 
GeneralVery nice, reminds me of ... Pin
Matt Cowan29-Sep-22 16:40
Matt Cowan29-Sep-22 16:40 
GeneralMy vote of 5 Pin
LightTempler9-Feb-22 7:17
LightTempler9-Feb-22 7:17 
Really an elegant twist! Usefull for many situations.
LiTe

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

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