using Dapper;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
namespace DapperUtils.ToreAurstadIT
{
/// <summary>
/// Original is fetched from: https://raw.githubusercontent.com/DapperLib/Dapper/main/Dapper.SqlBuilder/SqlBuilder.cs
///
/// </summary>
public class SqlBuilder
{
private readonly Dictionary<string, Clauses> _data = new Dictionary<string, Clauses>();
private int _seq;
private class Clause
{
public string Sql { get; set; }
public object Parameters { get; set; }
public bool IsInclusive { get; set; }
}
private class Clauses : List<Clause>
{
private readonly string _joiner, _prefix, _postfix;
public Clauses(string joiner, string prefix = "", string postfix = "")
{
_joiner = joiner;
_prefix = prefix;
_postfix = postfix;
}
public string ResolveClauses(DynamicParameters p)
{
foreach (var item in this)
{
p.AddDynamicParams(item.Parameters);
}
return this.Any(a => a.IsInclusive)
? _prefix +
string.Join(_joiner,
this.Where(a => !a.IsInclusive)
.Select(c => c.Sql)
.Union(new[]
{
" ( " +
string.Join(" OR ", this.Where(a => a.IsInclusive).Select(c => c.Sql).ToArray()) +
" ) "
}).ToArray()) + _postfix
: _prefix + string.Join(_joiner, this.Select(c => c.Sql).ToArray()) + _postfix;
}
}
public class Template
{
private readonly string _sql;
private readonly SqlBuilder _builder;
private readonly object _initParams;
private int _dataSeq = -1; // Unresolved
public Template(SqlBuilder builder, string sql, dynamic parameters)
{
_initParams = parameters;
_sql = sql;
_builder = builder;
}
private static readonly Regex _regex = new Regex(@"\/\*\*.+?\*\*\/", RegexOptions.Compiled | RegexOptions.Multiline);
private void ResolveSql()
{
if (_dataSeq != _builder._seq)
{
var p = new DynamicParameters(_initParams);
rawSql = _sql;
foreach (var pair in _builder._data)
{
rawSql = rawSql.Replace("/**" + pair.Key + "**/", pair.Value.ResolveClauses(p));
}
parameters = p;
// replace all that is left with empty
rawSql = _regex.Replace(rawSql, "");
_dataSeq = _builder._seq;
}
}
private string rawSql;
private object parameters;
public string RawSql
{
get { ResolveSql(); return rawSql; }
}
public object Parameters
{
get { ResolveSql(); return parameters; }
}
}
public Template AddTemplate(string sql, dynamic parameters = null) =>
new Template(this, sql, parameters);
protected SqlBuilder AddClause(string name, string sql, object parameters, string joiner, string prefix = "", string postfix = "", bool isInclusive = false)
{
if (!_data.TryGetValue(name, out Clauses clauses))
{
clauses = new Clauses(joiner, prefix, postfix);
_data[name] = clauses;
}
clauses.Add(new Clause { Sql = sql, Parameters = parameters, IsInclusive = isInclusive });
_seq++;
return this;
}
public SqlBuilder Intersect(string sql, dynamic parameters = null) =>
AddClause("intersect", sql, parameters, "\nINTERSECT\n ", "\n ", "\n", false);
public SqlBuilder InnerJoin(string sql, dynamic parameters = null) =>
AddClause("innerjoin", sql, parameters, "\nINNER JOIN ", "\nINNER JOIN ", "\n", false);
public SqlBuilder LeftJoin(string sql, dynamic parameters = null) =>
AddClause("leftjoin", sql, parameters, "\nLEFT JOIN ", "\nLEFT JOIN ", "\n", false);
public SqlBuilder RightJoin(string sql, dynamic parameters = null) =>
AddClause("rightjoin", sql, parameters, "\nRIGHT JOIN ", "\nRIGHT JOIN ", "\n", false);
public SqlBuilder Where(string sql, dynamic parameters = null) =>
AddClause("where", sql, parameters, " AND ", "WHERE ", "\n", false);
public SqlBuilder OrWhere(string sql, dynamic parameters = null) =>
AddClause("where", sql, parameters, " OR ", "WHERE ", "\n", true);
public SqlBuilder OrderBy(string sql, dynamic parameters = null) =>
AddClause("orderby", sql, parameters, " , ", "ORDER BY ", "\n", false);
public SqlBuilder Select(string sql, dynamic parameters = null) =>
AddClause("select", sql, parameters, " , ", "", "\n", false);
public SqlBuilder AddParameters(dynamic parameters) =>
AddClause("--parameters", "", parameters, "", "", "", false);
public SqlBuilder Join(string sql, dynamic parameters = null) =>
AddClause("join", sql, parameters, "\nJOIN ", "\nJOIN ", "\n", false);
public SqlBuilder GroupBy(string sql, dynamic parameters = null) =>
AddClause("groupby", sql, parameters, " , ", "\nGROUP BY ", "\n", false);
public SqlBuilder Having(string sql, dynamic parameters = null) =>
AddClause("having", sql, parameters, "\nAND ", "HAVING ", "\n", false);
public SqlBuilder Set(string sql, dynamic parameters = null) =>
AddClause("set", sql, parameters, " , ", "SET ", "\n", false);
}
}
Using SqlBuilder, we can define a Sql template and add extension methods and helper methods required to build and retrieve the inner join.
The helper methods in use are added also below the extension method InnerJoin. Make note that we use SqlBuilder here to do much of the SQL template processing to end up with the SQL that is sent to the DB (RawSql property of SqlBuilder instance).
/// <summary>
/// Inner joins the left and right tables by specified left and right key expression lambdas.
/// This uses a template builder and a shortcut to join two tables without having to specify any SQL manually
/// and gives you the entire inner join result set. It is an implicit requirement that the <paramref name="leftKey"/>
/// and <paramref name="rightKey"/> are compatible data types as they are used for the join.
/// This method do for now not allow specifying any filtering (where-clause) or logic around the joining besides
/// just specifying the two columns to join.
/// </summary>
/// <typeparam name="TLeftTable">Type of left table</typeparam>
/// <typeparam name="TRightTable">Type of right table</typeparam>
/// <param name="connection">IDbConnection to the DB</param>
/// <param name="leftKey">Member expression of the left table in the join</param>
/// <param name="rightKey">Member expression to the right table in the join</param>
/// <returns>IEnumerable of ExpandoObject. Tip: Iterate through the IEnumerable and save each ExpandoObject into a variable of type dynamic to access the variables more conveniently if desired.</returns>
public static IEnumerable<ExpandoObject> InnerJoin<TLeftTable, TRightTable>(this IDbConnection connection,
Expression<Func<TLeftTable, object>> leftKey, Expression<Func<TRightTable, object>> rightKey)
{
var builder = new SqlBuilder();
string leftTableSelectClause = string.Join(",", GetPublicPropertyNames<TLeftTable>("l"));
string rightTableSelectClause = string.Join(",", GetPublicPropertyNames<TRightTable>("r"));
string leftKeyName = GetMemberName(leftKey);
string rightKeyName = GetMemberName(rightKey);
string leftTableName = GetDbTableName<TLeftTable>();
string rightTableName = GetDbTableName<TRightTable>();
string joinSelectClause = $"select {leftTableSelectClause}, {rightTableSelectClause} from {leftTableName} l /**innerjoin**/";
var selector = builder.AddTemplate(joinSelectClause);
builder.InnerJoin($"{rightTableName} r on l.{leftKeyName} = r.{rightKeyName}");
var joinedResults = connection.Query(selector.RawSql, selector.Parameters)
.Select(x => (ExpandoObject)DapperUtilsExtensions.ToExpandoObject(x)).ToList();
return joinedResults;
}
private static string[] GetPublicPropertyNames<T>(string tableQualifierPrefix = null) {
return typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance)
.Where(x => !IsNotMapped(x))
.Select(x => !string.IsNullOrEmpty(tableQualifierPrefix) ? tableQualifierPrefix + "." + x.Name : x.Name).ToArray();
}
private static bool IsNotMapped(PropertyInfo x)
{
var notmappedAttr = x.GetCustomAttributes<NotMappedAttribute>()?.OfType<NotMappedAttribute>().FirstOrDefault();
return notmappedAttr != null;
}
/// <summary>
/// Returns database table name, either via the System.ComponentModel.DataAnnotations.Schema.Table attribute
/// if it exists, or just the name of the <typeparamref name="TClass"/> type parameter.
/// </summary>
/// <typeparam name="TClass"></typeparam>
/// <returns></returns>
private static string GetDbTableName<TClass>()
{
var tableAttribute = typeof(TClass).GetCustomAttributes(typeof(TableAttribute), false)?.FirstOrDefault() as TableAttribute;
if (tableAttribute != null)
{
if (!string.IsNullOrEmpty(tableAttribute.Schema))
{
return $"[{tableAttribute.Schema}].[{tableAttribute.Name}]";
}
return tableAttribute.Name;
}
return typeof(TClass).Name;
}
private static string GetMemberName<T>(Expression<Func<T, object>> expression)
{
switch (expression.Body)
{
case MemberExpression m:
return m.Member.Name;
case UnaryExpression u when u.Operand is MemberExpression m:
return m.Member.Name;
default:
throw new NotImplementedException(expression.GetType().ToString());
}
}
/// <summary>
/// Returns database table name, either via the System.ComponentModel.DataAnnotations.Schema.Table attribute
/// if it exists, or just the name of the <typeparamref name="TClass"/> type parameter.
/// </summary>
/// <typeparam name="TClass"></typeparam>
/// <returns></returns>
private static string GetDbTableName<TClass>()
{
var tableAttribute = typeof(TClass).GetCustomAttributes(typeof(TableAttribute), false)?.FirstOrDefault() as TableAttribute;
if (tableAttribute != null)
{
if (!string.IsNullOrEmpty(tableAttribute.Schema))
{
return $"[{tableAttribute.Schema}].[{tableAttribute.Name}]";
}
return tableAttribute.Name;
}
return typeof(TClass).Name;
}
public static ExpandoObject ToExpandoObject(object value)
{
IDictionary<string, object> dapperRowProperties = value as IDictionary<string, object>;
IDictionary<string, object> expando = new ExpandoObject();
if (dapperRowProperties == null)
{
return expando as ExpandoObject;
}
foreach (KeyValuePair<string, object> property in dapperRowProperties)
{
if (!expando.ContainsKey(property.Key))
{
expando.Add(property.Key, property.Value);
}
else
{
//prefix the colliding key with a random guid suffixed
expando.Add(property.Key + Guid.NewGuid().ToString("N"), property.Value);
}
}
return expando as ExpandoObject;
}
Here are some Nuget packages in use in the small lib functions here are in test project too:
<!-- lib project .NET 5 -->
<PackageReference Include="Dapper" Version="2.0.90" />
<PackageReference Include="Microsoft.CSharp" Version="4.7.0" />
<PackageReference Include="System.ComponentModel.Annotations" Version="5.0.0" />
<!-- test project-->
<PackageReference Include="FluentAssertions" Version="5.10.3" />
<PackageReference Include="Microsoft.CSharp" Version="4.7.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="3.1.16" />
<PackageReference Include="Microsoft.Extensions.Options.ConfigurationExtensions" Version="3.1.16" />
<PackageReference Include="Microsoft.NET.Test.Sdk" Version="16.10.0" />
<PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
<PackageReference Include="NUnit" Version="3.13.2" />
<PackageReference Include="NUnit3TestAdapter" Version="4.0.0" />
<PackageReference Include="System.ComponentModel.Annotations" Version="5.0.0" />
<PackageReference Include="System.Data.SqlClient" Version="4.8.2" />
Two unit tests shows how easier syntax we get with this helper method. The downside is that you cant fully control the sql yourself, but the benefit is quicker to implement.
[Test]
public void InnerJoinWithManualSqlReturnsExpected()
{
var builder = new SqlBuilder();
var selector = builder.AddTemplate("select p.ProductID, p.ProductName, p.CategoryID, c.CategoryName, s.SupplierID, s.City from products p /**innerjoin**/");
builder.InnerJoin("categories c on c.CategoryID = p.CategoryID");
builder.InnerJoin("suppliers s on p.SupplierID = s.SupplierID");
dynamic joinedproductsandcategoryandsuppliers = Connection.Query(selector.RawSql, selector.Parameters).Select(x => (ExpandoObject)DapperUtilsExtensions.ToExpandoObject(x)).ToList();
var firstRow = joinedproductsandcategoryandsuppliers[0];
Assert.AreEqual(firstRow.ProductID + firstRow.ProductName + firstRow.CategoryID + firstRow.CategoryName + firstRow.SupplierID + firstRow.City, "1Chai1Beverages1London");
}
[Test]
public void InnerJoinWithoutManualSqlReturnsExpected()
{
var joinedproductsandcategory = Connection.InnerJoin<Product, Category>(l => l.CategoryID, r => r.CategoryID);
dynamic firstRow = joinedproductsandcategory.ElementAt(0);
Assert.AreEqual(firstRow.ProductID + firstRow.ProductName + firstRow.CategoryID + firstRow.CategoryName + firstRow.SupplierID, "1Chai1Beverages1");
}
Our POCO classes used in the tests are these two. We use the Nuget package System.ComponentModel.Annotations and attributes TableName and NotMapped to control the SQL built here
to specify the DB table name for the POCO (if they are the same, the name of the type is used as fallback if attribute TableName is missing) and NotMapped in case there are properties like relationship properties ("navigation properties in EF for Dapper") that should not be used in the SQL select clause.
using System.ComponentModel.DataAnnotations.Schema;
namespace DapperUtils.ToreAurstadIT.Tests
{
[Table("Products")]
public class Product
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public int? SupplierID { get; set; }
public int? CategoryID { get; set; }
public string QuantityPerUnit { get; set; }
public decimal? UnitPrice { get; set; }
public short? UnitsInStock { get; set; }
public short? UnitsOnOrder { get; set; }
public short? ReorderLevel { get; set; }
public bool? Discontinued { get; set; }
[NotMapped]
public Category Category { get; set; }
}
}
using System.ComponentModel.DataAnnotations.Schema;
namespace DapperUtils.ToreAurstadIT.Tests
{
[Table("Categories")]
public class Category
{
public int CategoryID { get; set; }
public string CategoryName { get; set; }
public string Description { get; set; }
public byte Picture { get; set; }
}
}
In the end, we have a easy way to do a standard join. An improvement here could be the following:
- Support for where predicates to filter the joins
- More control on the join condition if desired
- Support for joins accross three tables (or more?) - SqlBuilder already supports this, what is missing is lambda expression support for Intellisense support
- What if a property does not match against db column ? Should support ColumnName attribute from System.ComponentModel.DataAnnotations.
- Investigate other join types such as left outer joins - this should be just a minor adjustment actually.
This comment has been removed by the author.
ReplyDeleteCool, thanks for this new method for me!
ReplyDeleteI use it in my work in the nearshoring company Oekraïne