Scanning solutions for NUnit test adapter via Powershell

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.

The Powershell functions are these:

 
 
 
 Function Get-ProjectInSolution {
    [CmdletBinding()] param (
        [Parameter()][string]$Solution
    )
    $SolutionPath = $Solution
    $SolutionFile = Get-Item $SolutionPath
    $SolutionFolder = $SolutionFile.Directory.FullName

    Get-Content $Solution |
        Select-String 'Project\(' |
        ForEach-Object {
            $projectParts = $_ -Split '[,=]' | ForEach-Object { $_.Trim('[ "{}]') }
            [PSCustomObject]@{
                File = $projectParts[2]
                Guid = $projectParts[3]
                Name = $projectParts[1]
            }
        } |
        Where-Object File -match "csproj$" |
        ForEach-Object {
            Add-Member -InputObject $_ -NotePropertyName FullName -NotePropertyValue (Join-Path $SolutionFolder $_.File) -PassThru
        }
}

Function Get-TestProjectInSolution {
[CmdletBinding()] param (
[Parameter()][string]$Solution)
  
  $projects = & Get-ProjectInSolution $Solution
  $testProjects = $projects | Where-Object { $_.Name -like '*Test*' }
  return $testProjects
}


Function Get-PackagesInProject {
[CmdletBinding()] param (
[Parameter()][string]$ProjectFile)

Get-Content $ProjectFile | Write-Host 
}


# Get-ProjectInSolution "C:\dev\somesolution\someacme.sln" 

Function List-PackagesOfTestProjectInSolution {
[CmdletBinding()] param (
[Parameter()][string]$SolutionFile)

  & Get-TestProjectInSolution $SolutionFile | ForEach-Object {
   $filePath = $_.FullName 
   Write-Host $filePath
  (Get-Content $_.FullName | Find "<PackageReference Include")
}
}

 
    Function Get-PackagesOfTestProjectInSolution {
[CmdletBinding()] param (
[Parameter()][string]$SolutionFile)

$dict = @{}

  & Get-TestProjectInSolution $SolutionFile | ForEach-Object {
    $filePath = $_.FullName 
    # Write-Host $filePath
    if (-not $dict.ContainsKey($filePath)) {
        $dict[$filePath] = (Get-Content $_.FullName | Find "<PackageReference Include")
    }
    return $dict 
  }

}

Function Has-NunitTestAdapterPackageInTestProjectinSolution {
[CmdletBinding()] param (
[Parameter()][string]$SolutionFile) 
 $packagesDict = Get-PackagesOfTestProjectInSolution $SolutionFile
$allPackagesString = $packagesDict.Values
$isNunitTestAdapterFound = ($allPackagesString -like "*NUnit*TestAdapter*").Length -gt 0
return $isNunitTestAdapterFound
}


Get-PackagesOfTestProjectInSolution "C:\dev\someacme\someacme.sln" 

$isNunitTestAdapterPresent = Has-NunitTestAdapterPackageInTestProjectinSolution "C:\dev\someacme\somecme.sln" 

Write-Host "Is NUnit test adapter added?" $isNunitTestAdapterPresent

    
    
    
    

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":

Immutable lists in C# - Adding a wrapper class

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:

 
 void Main()
{
	var numbersInImmutableList = new ImmutableWrappedList<int>();
	numbersInImmutableList.AddRange(new[] { 3, 1, 4, 1, 5, 9, 2 }); 
	numbersInImmutableList.AddRange(new[]{ 2, 7, 1, 8, 2, 1, 8 });
	numbersInImmutableList.RemoveAt(2);
	numbersInImmutableList.Contents.Dump(); 	
}

public class ImmutableWrappedList<T>  {
	public ImmutableList<T> _internalList;
    	public ImmutableList<T> Contents => _internalList;
	
	public ImmutableWrappedList()
	{
		_internalList = ImmutableList.Create<T>(); 	
	}
	
	public void Clear() => _internalList.Clear(); 	
	public void AddRange(IEnumerable<T> itemsToAdd) => _internalList = _internalList.AddRange(itemsToAdd);
	public void Add(T itemToAdd) => _internalList = _internalList.Add(itemToAdd);
	public void Remove(T itemToAdd) => _internalList = _internalList.Remove(itemToAdd);
	public void RemoveAt(int index) => _internalList = _internalList.RemoveAt(index);
	public void Insert(T itemToAdd, int position) => _internalList = _internalList.Insert(position, itemToAdd);
}


 

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.

Dapper - Inner Joins between two tables - Helper methods

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 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.