Distinct results sets with ICriteria in NHibernate

If you’re using ICriteria to write queries in NHibernate and you’re having citeria on items in a one-to-many relation (e.g. retrieve all departments with at least one employee named “John”) then you’re likely to have experienced that too many objects are returned (at least more than expected). In the example, all departments with multiple “Johns” will be returned once per matching employee. This is probably not what’s expected, so here’s how to fix it:

// The 'criteria' variable contains criteria on Department

// Create alias to use for employee conditions
ICriteria aliasCriteria = criteria.CreateAlias("Employees", "pr");

// Add criteria on the alias...

// We only want distinct root objects returned
criteria.SetResultTransformer(Transformers.DistinctRootEntity);

The trick is to use Transformers.DistinctRootEntity (at least in NHibernate 2.1). In earlier versions you may want to try something like this:

criteria.SetResultTransformer(new DistinctRootEntityResultTransformer());

Related to this is also how to write criteria that counts the results without actually retrieving them, which can be very useful for paging the result set (i.e. only read a subset of the results at a time). Normally you would write something like this:

criteria.SetProjection(Projections.Count("Id"));
int numberOfItems = (int)criteria.UniqueResult();

However, this suffers from the same problem as described above. To get the correct result, do this:

criteria.SetProjection(Projections.CountDistinct("Id"));
int numberOfItems = (int)criteria.UniqueResult();

Problem solved!

Credits to my colleague Jon for helping out with these rather obscure problem solutions!

/Emil

Using PowerShell for login scripts (HTTP POST)

Here’s a small example of how to use PowerShell to pretend it’s a web browser to login to a service. In my case it’s used to let my computer get access to a customer’s network, but it could also be used to login to other services, such as public WiFi services, etc. It also serves as an example of how to do a HTTP POST operation in PowerShell, something that can be very useful in many situations.

param($username=($env:username), $password=$(throw "The 'password' parameter is required!"))

$nvc = new-object System.Collections.Specialized.NameValueCollection
$nvc.Add('username',$username)
$nvc.Add('password',$password)
$wc = new-object net.webclient
[void] $wc.UploadValues('http://123.123.123.123/loginuser', $nvc)

The code is very simple:

  • The script takes two parameters, $username and $password. Note the use of default values to get the logged in user’s username into $username (if it’s not given by the caller) and mark $password as required (an exception is thrown if the parameter is not given). The last in particular is not very obvious if you ask me.
  • We then initialize a normal .Net NameValueCollection with the names and values of the fields to post (i.e. names of the input elements in the HTTP form we’re imitating)
  • The actual HTTP-posting is done using the UploadValues method of System.Net.WebClient object.

I think simple examples like this really shows the potential of PowerShell and that making .Net types so easily accessible in the language was a really smart design desicion by the language designers.

/Emil

Calling a PowerShell script from a Windows shortcut

Executing a PowerShell script from outside PowerShell (for example via a Windows shortcut) can be a little complicated, especially if the path to the script contains spaces and it has parameters, so here’s how to do it:

powershell.exe -command "& 'C:\A path with spaces\login.ps1' -password XXX"

Note that I’m using the PowerShell & operator to execute the script. This is because I have to surround the path with quotes so that it’s interpreted by PowerShell as a single literal even though it contains spaces. If I would leave out the “&” then the quoted string would simply be interpreted as a string literal to output, so the script would not be executed.

The parameters to the script should not be included within the single quotes since they are not part of the first literal, which should only contain the path to the script to execute.

/Emil