Checking that we have added test adapter for NUnit so that our tests in Azure Devops are run
A challenge with running tests inside Powershell can be if NUnit test adapter Nuget package is missing from the solution.
If you run test using NUnit 2.x, you require NUnitTestAdapter. If you use NUnit 3.x, NUnit3TestAdapter is required.
The following Powershell script can be used to check if we have added a Nuget package reference at least to one such test project in the
solution. We have here some tests that will list up all PackageReference in csproj files of the solution.
Note: this requires the following setup of your Nuget package references listed in the solution.
You have to have csproj projects in the solution
You must use PackageReference, i.e. list up nuget packages in the csproj file. This will not work if you instead use packages lock json format or packages.config.
For example, we could run the function call :
List-PackagesOfTestProjectInSolution "C:\dev\someacme\someacme.sln"
And we get our lists of package references in that solution (here we only look inside projects with a name containing "Test":
This article will discuss the immutable collections in C#, more precisely immutable lists of generic type T wrapped inside a class. This makes it possible to easier use immutable lists and these lists can only be
altered via functional calls. Remember that an immutable list always returns a new immutable list. For easier use, we can have a wrapper for this.
First of, inside Linqpad 5, being used in this article, hit F4. In case you want to use Visual Studio instead, the same code should work there (except Linqpad's Dump method). In the tab Additional referencesNow choose Add Nuget.. Then seach for System.Collections.Immutable. After selecting this Nuget package, choose the tab Additional Namesapce Imports.
Now paste this demo code:
As we can see, the wrapper class can add items to the immutable collections and we also reassign the result modifying operation to the same _internalList field, which has a private setter and is initialized to an empty array in the constructor. This gives you mutability to the immutable collection without having to remember to reassign the variable, which is error prone in itself. Note - we have called the _internalList and you see that we can get thi
What is the benefit of this ? Well, although we can reach into the internal collection with the Contents method here, the immutable list is still immutable. If you want to change it, you have to call specific methods here on it offered in the wrapping class. So, data-integrity wise, we have data that only can change via the methods offered in the wrapping class. A collection which is not immutable can be changed in many ways only by giving access to it. We still have control over the data via the wrapper and we make it easier to consume the immutable class by reassigning the collection.
Many developers use Entity Framework (EF) today as the library of their data access library to communicate against the database. EF
is a ORM, object-relational mapper and while it boasts much functionality like change tracking and mapping relationships, Dapper at the other
line of ORMs is a Micro-ORM. A Micro-ORM has less functionality, but offers usually more speed and less overhead.
Dapper is a great Micro-ORM, however, writing SQL manually is often error-prone or tedious. Some purists love writing the SQL manually and be
sure which SQL they send off to the DB. That is much of the point of Dapper. However, lending a hand to developers in building their SQL should still be
allowed. The query compilation time added to such helper methods are miniscule anyways compared to the heavy overhead of an advanced ORM like EF.
Anyways, the code in this article shows some code I am working with for building inner joins between to tables. The relationship between the two tables are 1:1 in my test case
and the inner join does for now not support a where predicate filter, although adding such a filter should be easy.
The source code for DapperUtils of mine is available on GitHub:
https://github.com/toreaurstadboss/DapperUtils
First, we make use of SqlBuilder from DapperUtils addon lib for Dapper.
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>publicstaticIEnumerable<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;
}
privatestaticstring[] GetPublicPropertyNames<T>(string tableQualifierPrefix = null) {
returntypeof(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();
}
privatestaticboolIsNotMapped(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>privatestaticstringGetDbTableName<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;
}
returntypeof(TClass).Name;
}
privatestaticstringGetMemberName<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:
thrownew 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>privatestaticstringGetDbTableName<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;
}
returntypeof(TClass).Name;
}
publicstatic ExpandoObject ToExpandoObject(objectvalue)
{
IDictionary<string, object> dapperRowProperties = valueas 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:
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.
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.
