Marcin Otorowski

[C#] Paging Dynamics CRM WebAPI with fetchXml

Recently I struggled a lot on how to correctly implement paging of queries against Dynamics CRM Web API. The API returns no more than 5000 items, and any subsequent items are only available after making another request with prepared parameters. What is usually pretty easy with Graph API (dedicated property with a link to the next page) is a bit clumsy with the older API.

In my scenario, I was trying to combine the paging with the usage of so-called fetchXml syntax. There is some minimal documentation available, but it does not explain all gotchas, and the samples require the usage of external pre-compiled libs (no .NET 5.0 available).

The basic usage of fetchXml is described in the following article: Use FetchXML to query data (Microsoft Dataverse) – Power Apps | Microsoft Docs. In this blog post I present how I did this with C# / NET 5.0, there is also a link to a more complex demo app on my GitHub:

https://github.com/marcinotorowski/dynamics-crm-webapi-demo

Let’s assume we have an endpoint and required OAuth2 token to authorize our request. The first thing to do is to start with the XML for fetch query. There are tons of documents and samples, a sample document may look like that:

<fetch mapping="logical" page="1">
    <entity name="audit">
        <attribute name="objectid" alias="objectid" />
        <attribute name="createdon" value="FormattedValue" />
        <link-entity name="systemuser" to="objectid" link-type="inner">
            <attribute name="fullname" />
        </link-entity>
        <filter type="and">
            <condition attribute="action" operator="eq" value="64" />
        </filter>
    </entity>
</fetch>

This essentially returns the list of login audits.

To make a query, strip the XML from spaces and white characters and URL-encode it. Then, assign the value to the URL parameter fetchXml and perform the following query:

https://<name>.crm4.dynamics.com/api/data/v9.1/audits/?fetchXml=%3Cfetch%20mapping%3D%22logical%22%20page%3D%221%22%3E%3Centity%20name%3D%22audit%22%3E%3Cattribute%20name%3D%22objectid%22%20alias%3D%22objectid%22%20%2F%3E%3Cattribute%20name%3D%22createdon%22%20value%3D%22FormattedValue%22%20%2F%3E%3Clink-entity%20name%3D%22systemuser%22%20to%3D%22objectid%22%20link-type%3D%22inner%22%3E%3Cattribute%20name%3D%22fullname%22%20%2F%3E%3C%2Flink-entity%3E%3Cfilter%20type%3D%22and%22%3E%3Ccondition%20attribute%3D%22action%22%20operator%3D%22eq%22%20value%3D%2264%22%20%2F%3E%3C%2Ffilter%3E%3C%2Fentity%3E%3C%2Ffetch%3E

This is pretty easy with a few functions from HttpUtility class and XDocument with C#. Then, a standard GET request is performed, (with bearer authorization header). The API replies with JSON that has two imporant properties:

  • value – contains a JSON array of returned elements
  • @Microsoft.Dynamics.CRM.fetchxmlpagingcookie – contains the pagination cookie if there is at least one page more to come, otherwise the property is not set.

Once the paging cookie is present, we need to take its value, unencode it (warning – it seems it has to be done twice, see source code in GitHub!) and then embed in all subsequent queries. The current page as indicated by fetchXml should be also increased by one.

The relevant code in GitHub repo:

/// <summary>
/// Returns the URL pointing to the next chunk of items.
/// </summary>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns>A hot running task that returns the URL to the next page.</returns>
/// <remarks>If this method returns null, then it means that there are no more pages to query.</remarks>
public async Task<Uri> GetNextPageUrlAsync(CancellationToken cancellationToken = default)
{
    if (this._parsedJsonResponse == null)
    {
        await this.ReadContentAsync(cancellationToken).ConfigureAwait(false);
    }
    
    var pagingCookie = this._parsedJsonResponse[FetchXmlPagingCookie]?.Value<string>();
    if (string.IsNullOrEmpty(pagingCookie))
    {
        return null;
    }

    cancellationToken.ThrowIfCancellationRequested();
    var pagingCookieResponseHtml = HttpUtility.HtmlDecode(pagingCookie);

    var html = XElement.Parse(pagingCookieResponseHtml);

    // The cookie is stored in the attribute "pagingcookie"
    pagingCookie = html.Attribute(PagingCookieHtmlResponse)?.Value;

    // The value must be decoded twice!
    pagingCookie = HttpUtility.UrlDecode(pagingCookie);
    pagingCookie = HttpUtility.UrlDecode(pagingCookie);

    var queryParse = HttpUtility.ParseQueryString(this._request.Query);

    // Let's take the current fetchXml from the URL and upgrade it with new cookie information
    var currentFetch = queryParse.Get(FetchXmlUrlParam);

    if (currentFetch == null)
    {
        throw new InvalidOperationException("fetchXml must be present in the query at this point.");
    }

    cancellationToken.ThrowIfCancellationRequested();
    var bodyAsXml = XElement.Parse(currentFetch);
    bodyAsXml.SetAttributeValue(PagingCookieHtmlRequest, pagingCookie);

    var page = int.Parse(bodyAsXml.Attribute(PageHtml)?.Value ?? "0");
    bodyAsXml.SetAttributeValue(PageHtml, page + 1);

    var newQuery = "?" + string.Join("&", queryParse.AllKeys.Select(paramName =>
    {
        string escapedName, escapedValue;

        if (paramName == FetchXmlUrlParam)
        {
            // if the parameter is fetchXml, we update it with a new value (encoded).
            escapedName = HttpUtility.UrlEncode(paramName);
            escapedValue = HttpUtility.UrlEncode(bodyAsXml.ToString(SaveOptions.DisableFormatting));
        }
        else
        {
            // for any other URL param just take it as-is.
            escapedName = HttpUtility.UrlEncode(paramName);
            escapedValue = HttpUtility.UrlEncode(queryParse[paramName]);
        }

        return $"{escapedName}={escapedValue}";
    }));
    
    cancellationToken.ThrowIfCancellationRequested();

    // Now build the new URI
    var uriBuilder = new UriBuilder(this._request)
    {
        Query = newQuery
    };

    return uriBuilder.Uri;
}

And the rest is just a matter of standard parsing of the response stream, in a loop for each page.

while (pageUri != null)
            {
                Console.WriteLine("{0} Getting page #{1} with no more than {2} entries...", stopWatch.Elapsed, page++, perPage);
                using var response = await request.RequestAsync(pageUri);
                try
                {
                    var elements = await response.GetElementsAsync<DynamicsAuditLog>();
                    Console.WriteLine("{0} Received {1} entries.", stopWatch.Elapsed, elements.Count);
                    allElements.AddRange(elements);
                    pageUri = await response.GetNextPageUrlAsync();
                }
                catch (Exception e)
                {
                    Console.WriteLine("{0} Error: {1}", stopWatch.Elapsed, e);
                    return 1;
                }
            }

Issues I had

  • Most of time spent on trying to understand why the authorization with bearer token does not work (HTTP 401). The problem was with the configuration – in my scenario, I had to make sure that there was a proper assignment of the registered app to an application user. This is done in directly in Dynamics CRM, not in Azure Portal.
    • The gotcha: Creation of application user was difficult due to some UI changes introduced in Dynamics CRM. The interface for settings is not obvious to find, and many internet sources claim it was somewhere else than where I actually found it. In my case, to find the UI I had to type the dynamics URL followed by /Main.aspx as the URL, and from the newly opened panel find my way though settings to set-up the user correctly.
  • Paging cookie has to be URL-decoded twice. No idea way.

Links

Posted by Marcin Otorowski in Programming, 0 comments

Fixing Gpg4win “secret key not available” error

After a fresh installation of Visual Studio with bundled Git for Windows + Gpg4Win, followed by setting up of a new GPG key for signing of commits, the following was reported when trying to sign a commit:

gpg: skipped "name m.otorowski@xxxxx.xyz": secret key not available
gpg: signing failed: secret key not available
error: gpg failed to sign the data
fatal: failed to write commit object

The message can be misleading, the keys are actually all correct. The problem can be solved by changing the path to GPG signer, which on my machine was (incorrectly) set to c:\Program Files\Git\usr\bin\gpg.exe. The file exists, but it is not the one that should be used here – the correct one is C:\Program Files (x86)\GnuPG\bin\gpg.exe.

Executing the following command which sets the correct path helps:

git config --global gpg.program "C:\Program Files (x86)\GnuPG\bin\gpg.exe"
Posted by Marcin Otorowski, 0 comments

Goodbye Ribbon!

MSIX Hero 2.0 (Preview) has been published. In this version, the old distinctive Ribbon UI has been dropped in favor of a simpler, Fluent Design inspired layout, which also shares a few characteristics and similarities with Azure / OneDrive UI.

Several problems with Ribbon:

  • It forced me to put all control in a central top place, while several of them would work better in the places where the user expected them.
  • It provided limited spacing for showing all the tools and wizard the tools had.
  • It was simply big, and taking precious screen space.
  • It felt so old-school on Windows 10 with its new design principles (the new compact ribbon would probably help with some of these, but would on the other hand introduce other issues on its own).
  • While it definitely felt “Windows-ish”, I also wanted to introduce some kind of entity, having something special and unique to the tool, while still not shocking the user with unknown concepts and patterns.

The new UI is not the only highlight of this preview. The full changelog and some highlights are documented on the following page:

MSIX Hero 2.0 (Preview) – Blog post

Note: this version installs side-by-side with the current 1.5 branch. You can distinguish both by their icon and display text in the Start Menu:

Posted by Marcin Otorowski in MSIX Hero, 0 comments

Publishing to winget with MSIX Hero

MSIX Hero is a freeware tool used by administrators and packagers for troubleshooting, analysis and debugging of MSIX packages. One of the latest updates introduced a new functionality – the ability to edit and create YAML manifests – a format accepted by winget package manager.

Winget is the newest approach from Microsoft, which aims to offer a centralized, format-agnostic package manager. While it differs substantially from what Linux/UNIX package managers and even Chocolatey, Scoop etc. offer, there seems to be rather a positive reception by the community. Once some missing features are there, together with constantly growing number of apps available, it may eventually become a really interesting part of the ecosystem, possibly one of the first thing the user would install to get his beloved apps and stuff on his newly staged computer.

In this blog post, I will show how to get started, create and validate an app definition and finally push it to the repository. Some basic knowledge of git would help you to get started (as the publishing process relies heavily on a proper git-based workflow), but this guide has been written for git beginners in mind. Just make sure you have a free GitHub account – register for a new one if you do not have any yet.

This tutorial is specifically addressed to those, who may not be quite proficient with git and related stuff, but want to still be able to publish submissions to winget. Users working with git on daily basis can certainly skip large parts of sections, describing how to fork and sync repositories.

Preparing

The app that I am going to publish will be the newest version 1.0.5 of MSIX Hero. The app is going to be an update of a previous version which is already in winget, but every submission is more or less following the same steps, regardless of being a completely new app, or just an updated version.

The first step is to prepare the sources. The app must be installable silently (with or without command line switches) and redistributable as a single file. The format itself is less important, most popular choices are:

  • MSI (Windows Installer) (note: because the file must be completely standalone, make sure that all your files and CAB archives are compressed inside the MSI container)
  • EXE (any format would work, some typical would be setups created by InnoSetup or NSIS).
  • MSIX/APPX (preferred choice for the modern deployment).

MSIX Hero is an MSIX app. The steps for other types are mostly the same.

Continue reading →
Posted by Marcin Otorowski, 0 comments

Creating VHD for MSIX app attach with MSIX Hero

In MSIX Hero 0.3.0.0 a new feature has been added. For any MSIX package, it is now possible to create a VHD image and scripts for staging, registering, unregistering and unstaging. This is a foundation for a new cool concept, named MSIX app attach (casing sic!).

MSIX is just a modern Windows app package format. MSIX app attach on the other hand takes the packages in this new format, and dynamically attaches them to Windows Virtual Desktop Session. Since the attached packages are expanded on read-only virtual disks, attaching and using an app is really fast and unnoticable to the user. And yet it still provides all benefits of maintaining minimal number of images, which are dynamically enhanced by the apps the user needs and can access. This all is possible without installing anything, with just only a minimal registration step that takes place. MSIX app attach is still in preview and will be available in upcoming version of Windows 10.

Now back to MSIX Hero. The new version has an ability to create attachable VHD disks + necessary scripts to test app attach out. This just eliminates a few manual steps that the user otherwise must do before getting any app attached. The automation built into the tool closely follows the steps from the following website:

https://docs.microsoft.com/en-us/azure/virtual-desktop/app-attach

Here is a UI for it. The dialog can be found in the GENERATE section > Generate VHD for app attach… accessible from the main ribbon.

Update: In version 0.3.4.0 the ribbon has been changed. The button is now in the EDIT > MSIX app attach VHD generator

The options are really minimalistic and self-explaning, but one or two things are interesting enough that they require a few extra words and comments.

Continue reading →
Posted by Marcin Otorowski in MSIX Hero, 0 comments

Convert HRESULT to a human-friendly error (PowerShell)

In several places in Windows (but especially in functions and calls resolving around COM) the errors/warnings returned by Windows APIs are returned as 32-bit numbers HRESULT. The binary value of HRESULT adds a special meaning for certain bits, like information about the type of the error, origin and much more.

Let’s analyze an example value of HRESULT 0x800706BA, which may be also represented in decimal form –2147023174. When seeing that number being returned by the API, it is usually not quite clear what the actual error really is. Wikipedia has a really simple explanation of how the number is structured:

https://en.wikipedia.org/wiki/HRESULT

Based on the specification and a header file to find out the mappings between facilities and their identifiers, I created a simple PowerShell function, which – given a number representing HRESULT – returns a human-friendly structure. This way you can convert this:

0x800706BA 

into this:

HResult : -2147023174
HResultHex : 0x800706BA
HResultBin : 10000000000001110000011010111010
Facility : FACILITY_WIN32
FacilityCode : 7
ErrorCode : 1722
IsFailure : True
Message : RPC server is unavailable
IsCustom : False
IsServerFailure : False 

Much better, isnt’t it? Turns out, this particular HRESULT represents an error, code 1722 (RPC Server is unavailable. Some other information, including representation in different formats are also there.

The usage is simple as that:

 Get-ActualError 0x800706BA 

and the full source code is available in the following gist:

https://gist.github.com/marcinotorowski/8c09fc556469b22a9df421be51e370b2

Posted by Marcin Otorowski in PowerShell, 0 comments

How to sign MSIX package (with MSIX Hero)

MSIX deployment stack requires that every package is digitally signed. Not doing it, or modifying an already signed package sooner or later ends with this:

The package is not signed, has a broken signature or the certificate is not trusted.

Package which is not trusted (as seen above) is not installable. The error is shown when trying to install an APPX or MSIX package which either :

  • Is not signed at all, or
  • Used to be signed, but due to unauthorized changes the signature is invalid, or
  • Is signed by an untrusted certificate.

Digital signing on Windows is not a rocket since, but still a bit tricky for APPX and MSIX packages. Aside of complex command-line of signtool and even a simple posessing of a valid certificate, the certificate subject must be kind of “imprinted” into the package manifest. Signing will fail if the publisher (from manifest) and the subject (from certificate) are not equal. And you will sign a lot, in fact after every single change in manifest or package files.

MSIX Hero makes signing and re-signing of packages piece of cake.

Continue reading →
Posted by Marcin Otorowski in MSIX Hero, 0 comments

Be more productive with MSIX with MSIX Hero

MSIX is a modern deployment and packaging format for Windows 10, with – although being relatively new in comparison to some other popular frameworks – plenty of tools and utilities out there. I personally find the ecosystem a bit scattered and a bit difficult for newcomers.

MSIX Hero is an answer to this challenge. This utility with simple GUI integrates several APIs, PowerShell commands and SDK tools in a single tool. Its primary use is management and troubleshooting. It does not try to be a complete authoring tool (for which commercial software like Advanced Installer, RayPack or InstallShield is a better choice) – instead it is rather a complimentary addition which can be used by anyone with any MSIX package, and should help users be more productive whichever of the above they use. It certainly has been driven by my needs, and being done in my spare time I actually managed to get rid of almost all scripts and snippets I was using for MSIX related tasks.

It can be downloaded from the following location: msixhero.net or by using a direct link to online installer. The best of it – it’s completelty free for personal and commercial use.

In the upcoming series of posts, I will show a few use cases and functions of MSIX Hero. In the current version the tool supports the following (the list is not comprehensive, it is just to name a few highlights):

  • Viewing installed packages (for current user or any local user)
  • Identification of packages:
    • Visual identification: translated user-friendly names, icons, colors
    • Identification of tool used to create the package
    • Showing applications and start-up information
    • Showing package dependencies, installed add-ons and users
  • Detection of PSF and visualization of file redirections
  • Adding and removing packages (for current user or all users)
  • Quick access to applets and screens
  • Ability to start installed apps and browse their manifest, folders etc.
  • Packing and unpacking of MSIX packages
  • Signing and changing signatures
  • Building modification packages stubs
  • Creating and editing .appinstaller files
  • Installing, extracting and creating of certificates

Posted by Marcin Otorowski in MSIX Hero, 0 comments

Executing custom commands in MSIX context

Sometimes, for troubleshooting it may be necessary to invoke commands in the context of MSIX, so that all virtualized file and registry resources are available. There are three main use cases for it:

  1. Testing out whether the files and registry entries are visible inside the virtual package in the correct place as defined in the AppxManifest.xml.
  2. Testing out whether Modification Package correctly merges its VFS and virtual registry with the base package.
  3. Executing your app from the MSIX package with custom executables and/or parameters which are not necessarily exposed as entry points.

There exists a really useful PowerShell cmdlet which does the heavy lifting, the only thing the user has to do is to supply the right parameters. Its signature (from MSDN):

Invoke-CommandInDesktopPackage
      [-PackageFamilyName] <String>
      [-AppId] <String>
      [-Command] <String>
      [[-Args] <String>]
      [-PreventBreakaway]

Both PackageFamilyName and AppId are required, as well as the command to be executed. You can get the two values from another cmdlet Get-AppxPackage.

The command that is started in the “bubble” can be pretty much anything (it is validated before running), but some typical examples are:

  • cmd.exe
  • powershell.exe
  • regedit.exe
  • Executables being part of the package (indirectly)

For example, let’s execute a PowerShell session in MSIX container to verify the virtualized file structure.

Continue reading →
Posted by Marcin Otorowski in MSIX, PowerShell, 0 comments

Change UI language in Visual Studio

The Visual Studio Installer for VS2019 automatically pulls the language package that matches the current Operating System. My Windows 10 Operating System language is currently German – there is a thing that always keeps me wondering:

Why is the Tools menu translated to Extras in German Visual Studio?

This and some more pecularities (especially important when working in pairs with non-German speakers) may be a good reasons to switch to old good English UI. Here is how to do it for an already installed Visual Studio 2019 instances (you can obviously apply this for any currently used language).

If target language pack has been already downloaded…

The only thing you have to do is to locate the correct settings. Go to Extras -> Optionen -> Umgebung -> Internationale Einstellungen and select the language from the list.

If target language pack has not been yet downloaded…

  1. Close all Visual Studio windows
  2. Run the Visual Studio installer. You can do this by going to Add/Remove Programs (Programme und Features) and selecting the right item. Press Ändern to bring the setup window.
  3. Go to the second tab and enable English language. You can disable German one if you do not plan to switch to it in future. Leaving just only new language enabled will automatically switch VS to that language. Press Ändern to apply the changes.
  4. In case you didn’t uninstall previous languages first, then after restarting you may need to go to Settings to change to the newly downloaded language pack. Simply go to Extras -> Optionen -> Umgebung -> Internationale Einstellungen and select the language from the list.

Installing language packs from command line

It is possible to automate installing language packs, by using the following command line (executed in directory where the installer resides, usually C:\Program Files (x86)\Microsoft Visual Studio\Installer):

vs_installer.exe modify --addProductLang En-us --path install="C:\Program Files (x86)\Microsoft Visual Studio\2019\Community" --quiet --wait 

Note: The full installation path is required for this to work. I am using VS 2019 community installed in the default location, but you may update the path accordingly.

Instead of En-us for American English, any valid identifier from this list works too.

Posted by Marcin Otorowski in Programming, 0 comments