fredag den 30. maj 2014

System Center Service Manager Automation

I’ve never worked with SCSM before, but a few weeks back I was asked to implement some automation processes using Microsoft Orchestrator (also a new product for me).

One of the jobs needed to add a user to a group, and you would expect something so simple would be easy. But after reading this guide, I ended up with this


Are you freaking kidding me ? My life is just to short for that, so after a while I ended up with this instead


The main problem lies in the fact that you need to fetch a lot of related objects. You start by sending the GUID of the Automation job inside SCSM. You then get the related objects, and finally you fetch the real AD objects. The job will of course fail if the user is already member of the group, so feel free to add 5-10 more steps for check group members ships too. Gaaaahhh

So lets look at the PowerShell script instead. First a little back ground. To run PowerShell scripts you need to install a Management pack. I choose Orchestrator Integration Pack for PowerShell Script Execution . Next we need a way to “talk” with SCSM from PowerShell, and I choose SMLETS for that. You need to be aware you can run into some issues with “double hob” when running the PowerShell scripts, so I choose to configure an service account and configure the PowerShell scripts to run on the SCSM server, and the use CredSSP as authentication scheme.


And we of course need to pass the GUID of the automation job in SCSM to the PowerShell script


And now to the good part. I’ve attached 2 scripts like the “normal” examples you find while goggling. “Add self to group” and “add user to group”. The first assumes an AD Group object has been associated with the Service Request. The latter, assumes an AD User Object and AD Group Object has been associated with the Service Request. You could associate the objects with either the Automation Request or Service Request, doesn’t really matter, the script is easy to modify for either.

As an added bonus you will also find some scripts for handling office 365 subscripts, users, licenses and management of SharePoint Online Service users in a federated environment. And notes about how to call Orchestrator run books from PowerShell completely dynamically with parameters.
(download link)

param (

$logscope = "AddUserToGroup"
$logname = "log"
. 'C:\Runbook\ServiceManager\functions\Initialization.ps1'
WriteHost "ActivityGuid: $ActivityGuid" -class $logscope

# Get current RunBook Activity in Service Manager
$runbook = Get-SCSMObject $ActivityGuid

# Dynamicly find related object (should only be one, of type Service Request )
$serviceRequest = (Get-SCSMRelationshipObject -ByTarget $runbook).SourceObject

$relationClass = Get-SCSMRelationshipClass System.WorkItemRelatesToConfigItem$
$scsmobjects = Get-SCSMRelatedObject -SMObject $ServiceRequest -Relationship $relationClass
$aduser = $null;$adgroup = $null;
foreach($scsmobject in $scsmobjects)
$adobject = Get-ADObject $scsmobject.DistinguishedName
if($adobject.ObjectClass -eq 'user')
$aduser = Get-ADUser $scsmobject.DistinguishedName
WriteHost "Found user in ad as $($aduser.Name) $($aduser.UserPrincipalName)" -class $logscope
} elseif($adobject.ObjectClass -eq 'group') {
$adgroup = Get-ADGroup $scsmobject.DistinguishedName
WriteHost "Found group in ad as $($adgroup.Name) $($adgroup.UserPrincipalName)" -class $logscope

if($aduser -and $adgroup)
WriteHost "Ensuring $($aduser.UserPrincipalName) is a member of '$($adgroup.Name)'" -class $logscope
# and now. The moment we have all been wating for .. Tadaaaaa, drum roll, blow the trompets, scream hale-juja, and add the user to the group!
Add-ADGroupMember -Identity $adgroup -Members $aduser
} else {
WriteHost "Failed locating user or group in ad!" -class $logscope
Throw "Failed locating user or group in ad!"

WriteHost "--Completed--" -class $logscope

XenApp hotfix management

One thing I never understood is how to figure out what patches are needed on a XenApp server. And it has always annoyed me with the whole process of needing to download everything and never knowing what fixes need a reboot.

So a few days ago I started searching for something smarter. Here someone made a script to get the patches but it only works with newer versions of PowerShell, and here someone made a complete solution. but, I like the ability to get status back and it doesn’t seem to check if a patch is actually needed. So I combined the two, into one PowerShell script that also works in PowerShell 2.0.
( download link )

Set-Location 'c:\'
$snapin = Get-PSSnapin | where {$ -eq 'Citrix.Common.Commands'}
if($snapin -eq $null){ Add-PSSnapin Citrix.Common.Commands }
$snapin = Get-PSSnapin | where {$ -eq 'Citrix.XenApp.Commands'}
if($snapin -eq $null){ Add-PSSnapin Citrix.XenApp.Commands }

function installXenappHotfix([string]$HotfixName, [string]$hotfixpath ){
$exitcode = (Start-Process -FilePath "msiexec.exe" -ArgumentList "/qb /passive /norestart /p `"$hotfixpath`"" -Wait -Passthru).ExitCode
if($exitcode -eq 3010){
$exitcode = 1604
#Write-Host 'Restart needed, so restarting. doh!'
#restart-computer -force -throttlelimit 10; exit $exitcode
#exit $exitcode
return $exitcode
if($exitcode -ne 0){
Write-Host ('Unknown exitcode: ' + $exitcode)
#exit $exitcode
return $exitcode
return 0

function installXenappHotfixWithIsNeededCheck([string]$HotfixName, [string]$hotfixpath ){
# Get current computername and XenServer object
$computername = $env:computername

$isInstalled = $false;
try {
foreach($hotfix in (Get-XAServerHotFix -ServerName $computername)){
if($hotfix.HotfixName -eq $HotfixName){ $isInstalled = $true; }
# IMA service proberly not running.
} catch {
if($_.invocationinfo) {
status ($_.Exception.Message + "`n `n" + $_.InvocationInfo.PositionMessage)
} else {
status $_.ToString()
return 1604
if($isInstalled -eq $false){
Write-Host ('Missing ' + $HotfixName)
$exitcode = (Start-Process -FilePath "msiexec.exe" -ArgumentList "/qb /passive /norestart /p `"$hotfixpath`"" -Wait -Passthru).ExitCode
if($exitcode -eq 3010){
$exitcode = 1604
Write-Host 'Restart needed, so restarting. doh!'
#restart-computer -force -throttlelimit 10; exit $exitcode
#exit $exitcode
return $exitcode
if($exitcode -ne 0){
Write-Host ('Unknown exitcode: ' + $exitcode)
#exit $exitcode
return $exitcode
} else {
# Write-Host ($HotfixName + ' ok')
return 0

function Get-URLContent ($url) {
$client = new-object System.Net.WebClient
#$client.DownloadFile( $url, [IO.Path]::GetTempFileName() )
return $client.downloadString($url)
function Get-URLFile ($url, $saveas) {
$client = new-object System.Net.WebClient
#$client.DownloadFile( $url, [IO.Path]::GetTempFileName() )
$client.DownloadFile( $url, $saveas )

function CheckXenappHotfix() {
# XenApp 6.5 : $url = ""
# XenApp 6.0 : $url = ""
# XenApp 5.0 : $url = ""

# PVS 6.1 : $url = ""

$hotfixurl = ''
$source = Join-Path (Get-Location).Path 'xa_hotfix'
if(! (Test-Path $source)) { New-Item -ItemType directory -Path $source | Out-Null }

$service = Get-Service imaservice
if($service.Status.ToString() -eq 'Stopped') {
Get-Service imaservice | Start-Service
try {
$computername = $env:computername
$installed = (Get-XAServerHotFix -ServerName $computername)
} catch {
if($_.invocationinfo) {
write-error ($_.Exception.Message + "`n `n" + $_.InvocationInfo.PositionMessage)
} else {
write-error $_.ToString()

write-progress -id 1 -Activity "Xenapp Hotfix Script" -Status "Fetch lastest xenapp hotfixes"
write-progress -id 2 -ParentId 1 -Activity "Xenapp Hotfix Script" -Status "Fetch $hotfixurl"

$hotfixes = @()
$xml = [xml](Get-URLContent $hotfixurl)
foreach($fix in $xml.rdf.item) {
write-progress -id 2 -ParentId 1 -Activity "Xenapp Hotfix Script" -Status "Fetch $($"

$html = Get-URLContent $
$pattern = '(?<=.)/servlet/KbServlet/download/.+?(?=")'
$results = ($html | Select-String -Pattern $Pattern -AllMatches).Matches | select value
if($results) {
if($results -is [system.array]){ $results = $results[0] }
$url = ('' + $results.Value)
$filename = [string][io.path]::GetFileName($url)
if($filename.Contains('.msp')) {
$HotfixName = [string][io.path]::GetFileNameWithoutExtension($filename)
if(! ($installed | ?{$_.HotfixName -eq $HotfixName})) {
$saveas = (Join-Path $source $filename)
if(! (Test-Path $saveas)) {
write-progress -id 2 -ParentId 1 -Activity "Xenapp Hotfix Script" -Status "Downloading $filename"
write-verbose "Downloading $filename"
Get-URLFile $url $saveas
} else {
write-progress -id 2 -ParentId 1 -Activity "Xenapp Hotfix Script" -Status "$filename has allready been downloaded"
write-verbose "$filename has allready been downloaded"
$hotfixes += $saveas
} else {
write-progress -id 2 -ParentId 1 -Activity "Xenapp Hotfix Script" -Status "$filename has allready been installed"
write-verbose "$filename has allready been installed"
} else {
write-progress -id 2 -ParentId 1 -Activity "Xenapp Hotfix Script" -Status "skipping $filename"
write-verbose "skipping $filename"

$needreboot = $false
$updateSystemInfo = new-object -com Microsoft.Update.SystemInfo
if($updateSystemInfo.rebootRequired){ $needreboot = $true }

write-progress -id 1 -Activity "Xenapp Hotfix Script" -Status "Installing missing xenapp hotfixes"
foreach($filename in $hotfixes) {
$HotfixName = [string][io.path]::GetFileNameWithoutExtension($filename)
write-progress -id 2 -ParentId 1 -Activity "Xenapp Hotfix Script" -Status "Installing $HotfixName"

$exitcode = installXenappHotfix $HotfixName $filename
if($exitcode -eq 3010){ $needreboot = $true; }
if($exitcode -eq 1642){
# some hotfixes will shut down the IMA service
# If you then start the service without rebooting it will not report the patch installed
# if that is NOT the case, go do some troubleshootnig to figure out what is wrong

Write-Warning "$HotfixName has allready been installed (ERROR_PATCH_TARGET_NOT_FOUND)"
$needreboot = $true

write-progress -id 2 -ParentId 1 -Activity "Xenapp Hotfix Script" -Status "n/a" -Completed
if($needreboot) {
write-progress -id 2 -ParentId 1 -Activity "Updating" -Status "Rebooting system"
Write-Warning "Reboot required"
Restart-Computer -Force

Windows Update with PowerShell

I’ve had a PowerShell script (rewritten from VBS) for a long time. It works and all, but once in a while when your testing stuff on fresh machines it would drive me nuts I couldn’t get a “status” like when your doing it though GUI.

Doing a progress bar while downloading is/was easy enough, but we all know it’s the installation that takes time, and that gave me a few issues. I finally figured out, I couldn’t re-use the installer, and sometimes it fails if you assign each update without first adding it to an “UpdateColl”. So, here it is (download link)

Begin {
$Install = $true;
$EulaAccept = $true;

write-progress -id 1 -Activity "Windows Update" -Status "Initialising"
$UpdateSession = New-Object -ComObject 'Microsoft.Update.Session'
$UpdateSession.ClientApplicationID = 'Wingu Update Client'
Process {
write-progress -id 1 -Activity "Windows Update" -Status "Checking for available updates"
$UpdateSearcher = $UpdateSession.CreateUpdateSearcher()
$SearchResult = $UpdateSearcher.Search("IsInstalled=0 and Type='Software'") # and IsHidden=0
$UpdatesToDownload = New-Object -com Microsoft.Update.UpdateColl
foreach ($Update in $SearchResult.Updates)
[bool]$addThisUpdate = $false
if ($Update.InstallationBehavior.CanRequestUserInput)
#Write-Verbose "> Skipping: $($Update.Title) because it requires user input"
[bool]$addThisUpdate = $true
} else {
if (!($Update.EulaAccepted)) {
Write-Verbose "> Note: $($Update.Title) has a license agreement that must be accepted:"
$Update.AcceptEula() | Out-Null
[bool]$addThisUpdate = $true
} else {
[bool]$addThisUpdate = $true
if ([bool]$addThisUpdate) {
Write-Verbose "Adding: $($Update.Title)"
$UpdatesToDownload.Add($Update) | Out-Null
if ($UpdatesToDownload.Count -eq 0) {
Write-Verbose 'All applicable updates were skipped.'
return $false
$downloader = $updateSession.CreateUpdateDownloader()
foreach($update in $UpdatesToDownload) {
$counter += 1
write-progress -id 1 -Activity "Updating" -Status "Downloading $counter/$($UpdatesToDownload.count) updates"
if(!$update.IsDownloaded) {
write-progress -id 2 -ParentId 1 -Activity "Updating" -Status "Downloading $($update.Title) $([int]($update.MaxDownloadSize / 1MB))MB"
$updateCollection=New-Object -com Microsoft.Update.UpdateColl
$updateCollection.Add($Update) | Out-Null
$downloader.Updates = $updateCollection
$Result = $downloader.Download()
} else {
write-progress -id 2 -ParentId 1 -Activity "Updating" -Status "isready $($update.Title)"

$needreboot = $false
$updateSystemInfo = new-object -com Microsoft.Update.SystemInfo
if($updateSystemInfo.rebootRequired){ $needreboot = $true }
$counter = 0
foreach($update in $UpdatesToDownload) {
$counter += 1
write-progress -id 1 -Activity "Updating" -Status "Installing $counter/$($UpdatesToDownload.count) updates"
write-progress -id 2 -ParentId 1 -Activity "Updating" -Status "Installing $($update.Title)"
$updatesToInstall = New-object -com "Microsoft.Update.UpdateColl"
$updatesToInstall.Add($update) | out-null
$installer = $updateSession.CreateUpdateInstaller()
$updateCollection=New-Object -com Microsoft.Update.UpdateColl
$updateCollection.Add($Update) | Out-Null
if($installer.ForceQuiet -eq $false) { $installer.ForceQuiet=$true }
$installer.Updates = $updateCollection
$installationResult = $installer.Install()
if($installationResult.rebootRequired){ $needreboot = $true }
if($installationResult.HResult -eq 4) {
#$resultcode= @{0="Not Started"; 1="In Progress"; 2="Succeeded"; 3="Succeeded With Errors"; 4="Failed" ; 5="Aborted" }
Write-Warning "Failed installting $($update.Title) ResultCode $($installationResult.ResultCode) HResult $($installationResult.HResult)"
if($installationResult.HResult -eq 5) {
#$resultcode= @{0="Not Started"; 1="In Progress"; 2="Succeeded"; 3="Succeeded With Errors"; 4="Failed" ; 5="Aborted" }
Write-Warning "Aborted $($update.Title) ResultCode $($installationResult.ResultCode) HResult $($installationResult.HResult)"

if(!$needreboot) {
$arguments = @('/c wuauclt /reportnow')
Start-Process -FilePath "cmd.exe" -ArgumentList $arguments -Wait

if(!$skipReboot -and $needreboot){
write-progress -id 2 -ParentId 1 -Activity "Updating" -Status "Rebooting system"
Write-Warning "Reboot required"
Restart-Computer -Force
return $needreboot
End {
write-progress -id 1 -Activity "Updating" -Status "Windows Update Check Complete" -Completed

onsdag den 15. januar 2014

Managing CRM 2011 and 2013

I fixed a series of bugs and tested my CRM powershell module against both 2011 and 2013. Enjoy.


tirsdag den 14. januar 2014

Microsoft Dynamics Nav 2013 goodies

One of the cool things about Nav 2013 is the ClickOnce deployment. Running Nav in a multi Tenant depoyment with many customers can make this a bit of a headache. First of all you need several deployments, one for each customer (seems stupid when all you need is a different ClientUserSettings.config ( maybe we could create a iis module or something to handle this ? ) anyway, for now lets keep it like that. Next if you read this page they promise us gold in the form of having several installations at the same computer. That will not work following microsofts guide thou, you need to have different assemblyIdentity names for that to work. Thank god we have powershell …


$baseclientpath = 'C:\NAV2013R2\Install\RoleTailoredClient\program files\Microsoft Dynamics NAV\71\RoleTailored Client\'
$baseclientcommonpath = 'C:\NAV2013R2\Install\RoleTailoredClient\CommonAppData\Microsoft\Microsoft Dynamics NAV\71\'
$templatepath = 'C:\NAV2013R2\Install\ClickOnceInstallerTools\Program Files\Microsoft Dynamics NAV\71\ClickOnce Installer Tools\TemplateFiles\'
$clickoneroot = "C:\inetpub\wwwroot\ClickOnce\$tenantid\"
$clickoneDeployment = "$($clickoneroot)Deployment\"
$clickoneApp = "$($clickoneroot)Deployment\ApplicationFiles\"

#if ( (Test-Path $clickoneroot) -ne $true) {
Copy-Item $templatepath $clickoneroot -recurse -force

if ( (Test-Path "$($clickoneroot)default.html") -ne $true) {
Copy-Item "$($clickoneroot)NAVClientInstallation.html" "$($clickoneroot)default.html"

Write-Host "Remove Nav ClickOnce Client Application"
if ( (Test-Path $clickoneApp) -eq $true) { Remove-Item $clickoneApp -force -recurse }
Write-Host "Copy fresh Nav ClickOnce Client Application"
Copy-Item $baseclientpath $clickoneApp -recurse -force

Write-Host "Copy fresh templates"
Copy-Item "$($templatepath)Deployment\ApplicationFiles\Microsoft.Dynamics.Nav.Client.exe.manifest" $clickoneApp
Copy-Item "$($templatepath)Deployment\ApplicationFiles\Icon.ico" $clickoneApp
Copy-Item "$($templatepath)Deployment\Microsoft.Dynamics.Nav.Client.application" $clickoneDeployment
Copy-Item "$($baseclientcommonpath)ClientUserSettings.config" $clickoneApp

Write-Host "Remove OpenXml and ReportLayout"
Remove-Item "$($clickoneApp)Add-ins\OpenXml" -recurse -force
Remove-Item "$($clickoneApp)ReportLayout" -recurse -force
Remove-Item "$($clickoneApp)Finhlink.exe" -force
Remove-Item "$($clickoneApp)finsql.exe" -force
Remove-Item "$($clickoneApp)finsql.exe.config" -force

Write-Host "Update ClientUserSettings"
$xml = [xml](Get-Content "$($clickoneApp)ClientUserSettings.config")
($xml.configuration.appSettings.add | ?{$_.key -eq 'Server'}).value = $alternateId
($xml.configuration.appSettings.add | ?{$_.key -eq 'ClientServicesPort'}).value = $ClientServicesPort.ToString()
($xml.configuration.appSettings.add | ?{$_.key -eq 'ServerInstance'}).value = $navinstancename
($xml.configuration.appSettings.add | ?{$_.key -eq 'TenantId'}).value = $tenantid
($xml.configuration.appSettings.add | ?{$_.key -eq 'ClientServicesCredentialType'}).value = 'UserName'
($xml.configuration.appSettings.add | ?{$_.key -eq 'DnsIdentity'}).value = $navcertificatesubject
($xml.configuration.appSettings.add | ?{$_.key -eq 'HelpServer'}).value = $helpserver
($xml.configuration.appSettings.add | ?{$_.key -eq 'HelpServerPort'}).value = $helpserverport
($xml.configuration.appSettings.add | ?{$_.key -eq 'ProductName'}).value = $tenantproductname

$manifestfile = "$($clickoneApp)Microsoft.Dynamics.Nav.Client.exe.manifest"
$applicationfile = "$($clickoneDeployment)Microsoft.Dynamics.Nav.Client.application"
$baseclickoneurl = "https://$alternateId/ClickOnce/$tenantid"
$manifesturl = "$($baseclickoneurl)/Deployment/ApplicationFiles/Microsoft.Dynamics.Nav.Client.exe.manifest"
$codebaseurl = "$($baseclickoneurl)/Deployment/Microsoft.Dynamics.Nav.Client.application"

Write-Host "Update manifest file"
$xml = [xml](Get-Content $manifestfile)
$ = "Microsoft.Dynamics.Nav.$tenantid" # Microsoft.Dynamics.Nav.ClickOnceClient
$xml.assembly.assemblyIdentity.version = $clientversion

Write-Host "Update application file"
$xml = [xml](Get-Content $applicationfile)
$xml.assembly.description.publisher = 'Microsoft Corporation and Proinfo A/S'
$xml.assembly.description.product = $tenantproductname
$ = $tenantproductname
$xml.assembly.deployment.deploymentProvider.codebase = $codebaseurl
$xml.assembly.assemblyIdentity.version = $clientversion
$ = "Microsoft.Dynamics.Nav.$tenantid"

& C:\NAV2013R2\mage\mage.exe -Update $manifestfile -FromDirectory $clickoneApp

& C:\NAV2013R2\mage\mage.exe -update $applicationfile -appmanifest $manifestfile -appcodebase $manifesturl
Write-Host "Microsoft Dynamics Nav 2013 for $tenantid published at https://$alternateId/ClickOnce/$tenantid"

tirsdag den 16. juli 2013

MongoDB,follow up

While venturing into the world of MongoDB I had a lot of “aha” experiences. I can’t even remember all of them and write about them, but a few stands out.

One common way to start on a new .NET/MS SQL project is to start at the database layer. weather your sitting in Enterprise manager creating tables and relations, or coding up your classes in a POCO model, your tend to think more about relations of your data, and ways your application would need to “fetch” data, and worry about performance later.And, god knows why, for some reason in my mind set, the less data you would need to get per dataset, the faster it had to be, right ?

But for me, working with MongoDB had me thinking more about performance first, ways to insert/update, and THEN how the application would access this. A lot of the performance gains can be archived though using $INC instead of updating complete documents. That can be quite hard to implement if you got a documents that is not just a few strings and integers.

So to challenge my self I set my self the goal to create a browser based RPG game. My goal was no page would take more than 20 milliseconds to generate. As I progressed and the game (and database) got more complex I found my self doing more and more “database” stuff directly in the webpages than in a “prober” DAL layer, to get that “extras” performance. While load testing I would also start getting issues with concurrency ( 2 webpages updating the same document )

So after a few weeks I deleted everything and started over, but this time, wiser from experience, I started with another mind set, that would include measuring each document and field in regard to concurrency. ( Do a Google search on Atomic Updates and concurrency in mongo dB if your interested in this. ) To avoid having tons of small “update” statements placed all over my code, I started thinking in two ways to handle updates. One would be the traditionally “entity framework” thinking where all my classes would have some kind of “tracking” ability to detect updated. I still believe this is the best way, but it is a LOT of work to implement. I also started wondering if you couldn’t just automated all this, and while goggling that, I came across UpdateDocumentBuilder .

This class is really cool, but there are 2 big issues with this class. The first issue comes from Mongo dB. You cannot pop/push multiple classes from an array in one update statement. Sure you could extend this class to support splitting up updates into many updates, but I will leave that up to someone else. The other issue, is the fact it uses $SET on all updates, and not embracing the whole “we don’t care in what order updates gets done” thinking. So I created a new version of this class, that uses $INC on all number values. To ensure support for atomic updates I add an lastupdatedon and use that in my query incase if needed.

Odata. There are so many skilled people out there that would be much better at creating this than me, but until that happens I had to work with my own implementation. I found a ton of issues in my last post, so I’ve uploaded a new version that has less errors and better support for Microsoft OData client ( adding a Service reference to your odata feed ). Beware with that. It doesn’t support properties starting with underscoore  _. Also if you want to expose _t use a different name, add bsonignore to avoid double updates, and then use my filterinformation attribute to support querys on it ( see jira 742 )
Basicly just use the new BSONFilter2 from your web api controller, You can test different querys by running then TestBSONFilter project.

You can see the updated filter, and my updated UpdateDocumentBuilder in this test project.

torsdag den 25. april 2013

MongoDB and OData

updated: 01-05-2013: small bug fixes in source to JSONFilter class.
updated: 01-05-2013: add support for resolving bson attributes.
updated: 16-07-2013: This is outdated, see new post here

I decided to try and spend some time playing with MongoDB. It didn’t take more than a few hours before I feel completely in love with MongoDB, so to put it to good use I decided to wrap OData on top of it. One of the big problems using OData and entity framework is the fact it will try and save as much as possible in memory. That is fine if you got a small database of a few MB/GB but if you got millions and millions of rows, that starts to get troublesome. So it felt important to find a solution that would send all database query's to the database
At first I tried MongOData. At first it looked really promising but what makes OData strong is the ability to filter data using QueryInterceptor and ChangeInterceptor and that doesn't seem to work with that solution ( The developers added this feature within a few days, after asking about this feature. ). Next I tried playing around with Web Api OData. The information out on the “Google web” is really “conflicting”, since 80% of the code samples you will find, doesn’t work but once you get something working it looks really promising. Until you decide to something simple like . Simple comparison works but searching arrays, or doing text search just doesn’t work. Than I came across LinqToQuerystring . That looked really good, they even have a special test project for testing against MongoDB, but after realizing you cannot query on arrays and collections I quickly ruled that out again. ( Apparently Linq To Query now supports any query's, but at the time of writing this, it didn't. Thank you for that information Pete Smith :-) )
After a few days of testing different things I decided to throw my love at Web Api OData. ( I went back to MongOData again, but more about that later )

So, for each step, try out the following queries. Replace /odata/ with /api/ in the first sample, since the odata route haven't been added yet.

So lets first try and have quick look at how to expose a Collection (table in MongoDB ) as OData using Web Api OData.

http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=name eq 'Cruz Roudabush'
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=(substringof('Vaill', name) eq true)
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=skills/any(x: x eq 'VB' )
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=skills/any(x: (x eq 'VB') or (x eq 'XML'))
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=skills/any(x: (x eq 'VB') and (x eq 'MongoDB'))
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=skills/any(x: (substringof('L', x) eq true))

http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=addresses/any(x: x/city eq 'Anchorage' )
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=addresses/any(x: x/city eq 'New York' )
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=addresses/any(x: x/city eq 'New York' and x/zip eq '10018' )
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=addresses/any(x: x/city eq 'New York' or x/zip eq '99515' )
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=addresses/any(x: (substringof('York', x/city) eq true) )

Create an empty MVC 4 project, open package manager and install MongoDB C# Driver and WebApi.OData … just to be safe, update all NuGet packages by issuing Update-Package in the Package Manager Console.

The easy and simple way, would be to use the old syntax.
Add an empty API controller, add a GET function and decorate it with Queryable, and return an IQueryable(of person)


Imports MongoDB.Driver.Linq

Public Class PersonsController
Inherits ApiController

Public Function [get]() As IQueryable(Of person)
Return repository.Persons.AsQueryable
End Function

End Class

But, to embrace MVC a bit more, and open up for more advanced queries. First we add an OData route in WebApiConfig.vb , then we change the controller to an EntitySetController. You can either use Queryable decoration on all functions you want to allow it for, or just generally open up for this on all functions by adding EnableQuerySupport here

Public Class WebApiConfig
Public Shared Sub Register(ByVal config As HttpConfiguration)
Dim model = repository.getModel()
config.Routes.MapODataRoute(routeName:="odata", routePrefix:="odata", model:=model)

Public Class PersonsController
Inherits OData.EntitySetController(Of person, String)

Protected Overrides Function GetEntityByKey(key As String) As person
Return repository.Persons.AsQueryable.Where(Function(x) = key).FirstOrDefault
End Function

Public Overrides Function [get]() As IQueryable(Of person)
Return repository.Persons.AsQueryable
End Function

End Class

But there is a slight problem with the 2 above solutions.
The Mongo DB C# driver is very limited in what queries it understands/supports. Simple stuff like “name eq” and “age gt 10” works.  But if you want to search a string, or search lists or sub documents you will get all kinds of errors.
We can, however, try and process the odata query our self. One “quick” way of doing that would be to look at LinqToQuerystring but that doesn’t support any queries, so that wouldn’t fit my needs. So I started looking at what it would take to “parse” the query manually and translate it to something the Mongo DB c# driver would understand.

If we look at QueryOptions ( a property you get access too when inheriting EntitySetController or add as parameter when inheriting from ODataController, we get easy access to the “query tree”, I think we can call it and so something like this

Public Class PersonsController
Inherits OData.EntitySetController(Of person, String)

Protected Overrides Function GetEntityByKey(key As String) As person
Return repository.Persons.AsQueryable.Where(Function(x) = key).FirstOrDefault
End Function

Public Overrides Function [get]() As IQueryable(Of person)
Dim cursor = JSONFilter.QueryOptionsToCursor(Of person)(repository.Persons, QueryOptions, 5)
Return cursor.AsQueryable
End Function

End Class

We have 3 options, when we want to parse the OData query.

1) We can do like Microsoft and turn it into LINQ but “turn down” some of their aggressive “isnullable” expressions to make expressions more eatable by the Mongo DB Driver. that works some of the way, but once you start trying to do Any queries (Searching lists and collections) you get into all kinds of problems, most of them with the Mongo DB driver.

2) we can parse the queries one by one, and try and express the as an IMongoQuery . That is actually pretty easy with all the simple stuff, but as soon as you get to functions, any/all queries and such, it just gets very complicated, and hard to rewrite.

3) we can parse the queries to the JSON queries language mongo dB speaks, and then load that as an Query Document and execute that.

I began writing code for all 3 solutions and got furthest using option 3.

So in the end we end up with a controller that looks like this

Public Class PersonsController
Inherits OData.ODataController

Function [Get](key As String) As person
Return repository.Persons.FindOne(MongoDB.Driver.Builders.Query.EQ("id", New MongoDB.Bson.BsonString(key)))
End Function

Public Function [Get](QueryOptions As Web.Http.OData.Query.ODataQueryOptions) As OData.PageResult(Of person)
Dim inlinecount As Integer = -1
If QueryOptions.InlineCount IsNot Nothing Then
If QueryOptions.InlineCount.Value = Web.Http.OData.Query.InlineCountValue.AllPages Then
' we cannot use cursor twice, so need to open a new one, and do the count on that
Dim countcursor = JSONFilter.QueryOptionsToCursor(Of person)(repository.Persons, QueryOptions)
inlinecount = countcursor.Count
End If
End If

Dim cursor = JSONFilter.QueryOptionsToCursor(Of person)(repository.Persons, QueryOptions, 2)
' yeah, yeah ... sue me, for being creative here
Dim NextPageLink As String = Request.RequestUri.AbsolutePath
If QueryOptions.Skip Is Nothing Then
NextPageLink = Request.RequestUri.AbsoluteUri & "&$skip=" & cursor.Limit
NextPageLink = Request.RequestUri.AbsoluteUri
NextPageLink = NextPageLink.Replace("$skip=" & cursor.Skip, "$skip=" & cursor.Skip + cursor.Limit)
End If

If inlinecount > -1 Then
Return New OData.PageResult(Of person)(cursor, New Uri(NextPageLink), inlinecount)
Return New OData.PageResult(Of person)(cursor, New Uri(NextPageLink), Nothing)
End If
End Function

End Class

And JSONFilter

' filters explained

' inspiration

Imports MongoDB.Driver
Imports MongoDB.Bson.BsonExtensionMethods

Imports System.Web.Http.OData.Query
Imports Microsoft.Data.Edm
Imports Microsoft.Data.OData.Query.SemanticAst
Imports Microsoft.Data.OData.Query
Imports MongoDB

Imports System.Reflection

Public Class JSONFilter
Private _model As IEdmModel
Protected Sub New(model As IEdmModel)
_model = model
End Sub

Public Shared Function QueryOptionsToCursor(Of T)(collection As MongoCollection, options As ODataQueryOptions,
Optional defaultPagesize As Integer = 2) As MongoDB.Driver.MongoCursor(Of T)
Dim _t As Type = GetType(T)
Dim q = Builders.Query.EQ("_t", New Bson.BsonString(_t.Name))
Return QueryOptionsToCursor(Of T)(collection, options, q, defaultPagesize)
End Function
Public Shared Function QueryOptionsToCursor(Of T)(collection As MongoCollection, options As ODataQueryOptions,
basequery As IMongoQuery, Optional defaultPagesize As Integer = 2) As MongoDB.Driver.MongoCursor(Of T)
Dim query As MongoDB.Driver.IMongoQuery = Nothing
Dim cursor As MongoDB.Driver.MongoCursor(Of T)
Dim querylimit As Integer = defaultPagesize
Dim queryskip As Integer = 0
If (options.Top IsNot Nothing) Then querylimit = options.Top.Value
If (options.Skip IsNot Nothing) Then queryskip = options.Skip.Value

If options.Filter IsNot Nothing Then
Dim jsonQuery As String = JSONFilter.BindFilterQueryOption(options.Filter)
Debug.WriteLine("db." & collection.Name & ".find( " & jsonQuery & ");")

If basequery IsNot Nothing Then
jsonQuery = "{$and : [" & jsonQuery & "," & basequery.ToJson & "]}"
End If
Dim doc As Bson.BsonDocument = MongoDB.Bson.Serialization.BsonSerializer.Deserialize(Of Bson.BsonDocument)(jsonQuery)
Dim queryDoc = Bson.Serialization.BsonSerializer.Deserialize(Of Bson.BsonDocument)(jsonQuery)
cursor = collection.FindAs(Of T)(New QueryDocument(queryDoc))
If basequery IsNot Nothing Then
cursor = collection.FindAs(Of T)(basequery)
cursor = collection.FindAllAs(Of T)()
End If
End If
If queryskip > 0 Then cursor.SetSkip(queryskip)

If options.OrderBy IsNot Nothing Then
For Each orderby As System.Web.Http.OData.Query.OrderByPropertyNode In options.OrderBy.OrderByNodes
If orderby.Direction = Microsoft.Data.OData.Query.OrderByDirection.Ascending Then
End If
End If

Return cursor
End Function

Public Shared Function BindFilterQueryOption(filterQuery As FilterQueryOption) As String
If filterQuery IsNot Nothing Then
Dim binder As New JSONFilter(filterQuery.Context.Model)
Return "{" & binder.Bind(filterQuery.FilterClause.Expression) & "}"
End If
Return ""
End Function

Protected Function Bind(node As QueryNode) As String
Dim collectionNode As CollectionNode = TryCast(node, CollectionNode)
Dim singleValueNode As SingleValueNode = TryCast(node, SingleValueNode)

If collectionNode IsNot Nothing Then
Select Case node.Kind
Case QueryNodeKind.CollectionNavigationNode
Dim navigationNode As CollectionNavigationNode = TryCast(node, CollectionNavigationNode)
Return BindNavigationPropertyNode(navigationNode.Source, navigationNode.NavigationProperty)

Case QueryNodeKind.CollectionPropertyAccess
Return BindCollectionPropertyAccessNode(TryCast(node, CollectionPropertyAccessNode))
End Select
ElseIf singleValueNode IsNot Nothing Then
Select Case node.Kind
Case QueryNodeKind.BinaryOperator
Return BindBinaryOperatorNode(TryCast(node, BinaryOperatorNode))

Case QueryNodeKind.Constant
Return BindConstantNode(TryCast(node, ConstantNode))

Case QueryNodeKind.Convert
Return BindConvertNode(TryCast(node, ConvertNode))

Case QueryNodeKind.EntityRangeVariableReference
Return BindRangeVariable(TryCast(node, EntityRangeVariableReferenceNode).RangeVariable)

Case QueryNodeKind.NonentityRangeVariableReference
Return BindRangeVariable(TryCast(node, NonentityRangeVariableReferenceNode).RangeVariable)

Case QueryNodeKind.SingleValuePropertyAccess
Return BindPropertyAccessQueryNode(TryCast(node, SingleValuePropertyAccessNode))

Case QueryNodeKind.UnaryOperator
Return BindUnaryOperatorNode(TryCast(node, UnaryOperatorNode))

Case QueryNodeKind.SingleValueFunctionCall
Return BindSingleValueFunctionCallNode(TryCast(node, SingleValueFunctionCallNode))

Case QueryNodeKind.SingleNavigationNode
Dim navigationNode As SingleNavigationNode = TryCast(node, SingleNavigationNode)
Return BindNavigationPropertyNode(navigationNode.Source, navigationNode.NavigationProperty)

Case QueryNodeKind.Any
Return BindAnyNode(TryCast(node, AnyNode))

Case QueryNodeKind.All
Return BindAllNode(TryCast(node, AllNode))
End Select
End If

Throw New NotSupportedException([String].Format("Nodes of type {0} are not supported", node.Kind))
End Function

Private Function findType(name As String, fullname As String) As Type
Dim res = From assembly In AppDomain.CurrentDomain.GetAssemblies()
From type In assembly.GetTypes()
Where type.Name = name
For Each t In res
If t.type.FullName = fullname Then Return t.type
Return Nothing
End Function

Private Function BSONPropertyNode(sp As Library.EdmStructuralProperty) As String
Dim PropertyName As String = sp.Name
'Dim et As Microsoft.Data.Edm.Library.EdmEntityType = sp.DeclaringType
'Dim et As Microsoft.Data.Edm.Library.EdmStructuredType = sp.DeclaringType

Dim ClassName As String
Dim PropertyClassType As Type = Nothing
If TypeOf sp.DeclaringType Is Microsoft.Data.Edm.Library.EdmEntityType Then
Dim et As Microsoft.Data.Edm.Library.EdmEntityType = sp.DeclaringType
ClassName = et.Namespace & "." & et.Name
PropertyClassType = findType(et.Name, ClassName)

ElseIf TypeOf sp.DeclaringType Is Microsoft.Data.Edm.Library.EdmComplexType Then
Dim et As Microsoft.Data.Edm.Library.EdmComplexType = sp.DeclaringType
ClassName = et.Namespace & "." & et.Name
PropertyClassType = findType(et.Name, ClassName)
End If
If PropertyClassType IsNot Nothing Then
Dim pdc As ComponentModel.PropertyDescriptorCollection = ComponentModel.TypeDescriptor.GetProperties(PropertyClassType)
Dim prop As ComponentModel.PropertyDescriptor = pdc.Find(PropertyName, False)

Dim BsonElement As MongoDB.Bson.Serialization.Attributes.BsonElementAttribute = prop.Attributes.Item(GetType(MongoDB.Bson.Serialization.Attributes.BsonElementAttribute))
Dim BsonId As MongoDB.Bson.Serialization.Attributes.BsonIdAttribute = prop.Attributes.Item(GetType(MongoDB.Bson.Serialization.Attributes.BsonIdAttribute))
If BsonElement IsNot Nothing Then
If Not String.IsNullOrEmpty(BsonElement.ElementName) Then
Return Convert.ToString(BsonElement.ElementName)
End If
ElseIf Not BsonId Is Nothing Then
Return Convert.ToString("_id")
End If
End If
Return PropertyName
End Function

Private Function BindCollectionPropertyAccessNode(node As CollectionPropertyAccessNode) As String
Return BSONPropertyNode(node.Property)
'Return Convert.ToString(node.[Property].Name)
'Return Bind(collectionPropertyAccessNode.Source) & "." & Convert.ToString(collectionPropertyAccessNode.[Property].Name)
End Function

Private Function BindNavigationPropertyNode(singleValueNode As SingleValueNode, edmNavigationProperty As IEdmNavigationProperty) As String
Return Convert.ToString(edmNavigationProperty.Name)
'Return Bind(singleValueNode) & "." & Convert.ToString(edmNavigationProperty.Name)
End Function

Private Function BindAllNode(allNode As AllNode) As String
Dim isInList As String = "[]"
Dim anylist As String = Bind(allNode.Body)
anylist = anylist.Replace("""" & allNode.RangeVariables.First().Name & """ : ", "")
Dim result As String = Bind(allNode.Source) & " : {$all : [" & anylist & "]}"
Return result
End Function

Private Function BindAnyNode(Node As AnyNode) As String
Dim source = Bind(Node.Source)
Dim anylist As String = Bind(Node.Body)
Dim var = Node.RangeVariables.First.Name

If TypeOf Node.Source.ItemType Is Library.EdmComplexTypeReference Then

Dim result As String = Bind(Node.Source) & " : {$elemMatch: {" & anylist & "} }"
' list of class ?
'Throw New Exception("any on complex types not supported")
Return result
' list of values
'Dim isInList As String = "[]"
'anylist = anylist.Replace("""" & Node.RangeVariables.First().Name & """ : ", "")
'Dim result As String = Bind(Node.Source) & " : {$in : [" & anylist & "]}"
anylist = anylist.Replace("""" & Node.RangeVariables.First().Name & """", """" & Bind(Node.Source) & """")
Dim result As String = anylist
Return result

End If
End Function

Private Function BindNavigationPropertyNode(singleEntityNode As SingleEntityNode, edmNavigationProperty As IEdmNavigationProperty) As String
Return Convert.ToString(edmNavigationProperty.Name)
'Return Bind(singleEntityNode) & "." & Convert.ToString(edmNavigationProperty.Name)
End Function

Private Function BindSingleValueFunctionCallNode(singleValueFunctionCallNode As SingleValueFunctionCallNode) As String
Dim arguments = singleValueFunctionCallNode.Arguments.ToList()
Select Case singleValueFunctionCallNode.Name
'Case "concat"
' Return Convert.ToString(singleValueFunctionCallNode.Name) & "(" & Bind(arguments(0)) & "," & Bind(arguments(1)) & ")"
'Case "length", "trim", "year", "years", "month", "months", _
' "day", "days", "hour", "hours", "minute", "minutes", _
' "second", "seconds", "round", "floor", "ceiling"
' Return Convert.ToString(singleValueFunctionCallNode.Name) & "(" & Bind(arguments(0)) & ")"

Case "substringof"
Dim val As String = Bind(arguments(0))
If val.StartsWith("'") And val.EndsWith("'") Then
val = val.Substring(1, val.Length - 2)
End If
Return """" & Bind(arguments(1)) & """ : /" & val & "/i"
Case Else
Throw New NotImplementedException()
End Select
End Function

Private Function BindUnaryOperatorNode(unaryOperatorNode As UnaryOperatorNode) As String
Return ToString(unaryOperatorNode.OperatorKind) & "(" & Bind(unaryOperatorNode.Operand) & ")"
End Function

Private Function BindPropertyAccessQueryNode(node As SingleValuePropertyAccessNode) As String
Dim source = Bind(node.Source)
If source = "$it" Then
Return BSONPropertyNode(node.Property)
'Return Convert.ToString(node.[Property].Name)
Return source & "." & BSONPropertyNode(node.Property)
'Return Convert.ToString(source & "." & node.[Property].Name)
End If
'Return Bind(singleValuePropertyAccessNode.Source) & "." & Convert.ToString(singleValuePropertyAccessNode.[Property].Name)
End Function

Private Function BindRangeVariable(nonentityRangeVariable As NonentityRangeVariable) As String
Return nonentityRangeVariable.Name.ToString()
End Function

Private Function BindRangeVariable(entityRangeVariable As EntityRangeVariable) As String
Return entityRangeVariable.Name.ToString()
End Function

Private Function BindConvertNode(convertNode As ConvertNode) As String
Return Bind(convertNode.Source)
End Function

Private Function BindConstantNode(constantNode As ConstantNode) As String
If TypeOf constantNode.Value Is String Then
Return [String].Format("'{0}'", constantNode.Value)
ElseIf TypeOf constantNode.Value Is DateTime Then
Dim t1 = New MongoDB.Bson.BsonDateTime(DirectCast(constantNode.Value, DateTime))
Dim t2 = "ISODate(""" & t1.AsBsonValue.ToString & """)"
Return t2
ElseIf TypeOf constantNode.Value Is Integer Then
Return constantNode.Value.ToString
End If
Return constantNode.Value.ToString()
End Function

Private Function BindBinaryOperatorNode(binaryOperatorNode As BinaryOperatorNode) As String
Dim left = Bind(binaryOperatorNode.Left)
Dim right = Bind(binaryOperatorNode.Right)
If binaryOperatorNode.Left.Kind = QueryNodeKind.SingleValueFunctionCall Then
Return left
ElseIf binaryOperatorNode.OperatorKind = BinaryOperatorKind.Equal Then
Return """" & left & """ : " & right
ElseIf binaryOperatorNode.OperatorKind = BinaryOperatorKind.Or Then
Return "$or : [ {" & left & "}, {" & right & "} ]"
ElseIf binaryOperatorNode.OperatorKind = BinaryOperatorKind.And Then
Return "$and : [ {" & left & "}, {" & right & "} ]"
Return """" & left & """ : {" & ToString(binaryOperatorNode.OperatorKind) & " : " & right & "}"
End If

End Function

Private Overloads Function ToString(binaryOpertor As BinaryOperatorKind) As String
Select Case binaryOpertor
Case BinaryOperatorKind.Add
Return "$inc"
Case BinaryOperatorKind.[And]
Return "$and"
'Case BinaryOperatorKind.Divide
' Return "/"
Case BinaryOperatorKind.Equal
Return "$eq"
Case BinaryOperatorKind.GreaterThan
Return "$gt"
Case BinaryOperatorKind.GreaterThanOrEqual
Return "$gte"
Case BinaryOperatorKind.LessThan
Return "$lt"
Case BinaryOperatorKind.LessThanOrEqual
Return "$lte"
Case BinaryOperatorKind.Modulo
Return "$mod"
'Case BinaryOperatorKind.Multiply
' Return "*"
Case BinaryOperatorKind.NotEqual
Return "$ne"
Case BinaryOperatorKind.[Or]
Return "$or"
'Case BinaryOperatorKind.Subtract
'Return "-"
Case Else
Throw New NotSupportedException([String].Format("Opdaterator of type '{0}' are not supported", binaryOpertor))
'Return Nothing
End Select
End Function

Private Overloads Function ToString(unaryOperator As UnaryOperatorKind) As String
Select Case unaryOperator
Case UnaryOperatorKind.Negate
Return "!"
Case UnaryOperatorKind.[Not]
Return "NOT"
Case Else
Return Nothing
End Select
End Function

End Class

JSONFilter being one of 3 classes that can parse the ODataQueryOptions into something mongo db will understand, you can download a test project here.