Retrieving data using QueryExpression class

Goal: To retrieve a basic set of data using query expressions.
There’s a way to perform advanced calls to retrieve data from CRM with very little effort. One can use the GUI and create an advanced find followed by export of the fetch XML. However, there are certain limitations to that approach accompanied by the fact that it resembles 90’s style SQL programming with the commands executed towards the database begin provided as string. A constellation like String sql = “select * from Contacts”; raises a huge warning flag and in my book, that’s a huge no-no.
Luckily, there’s an other approach, offering full flexibility and adapting the C# coding style that most programmers appreciate. It’s provided in the namespace of Microsoft.Xrm.Sdk.Query and being based on the object oriented pattern, it follows very closely the modern way of creating and designing abstract data types.
Suppose that we’d like to retrieve a full set of all instances of an entity from a CRM server. An entity that surely exists is SystemUser, so let’s retrieve all instances of it. The assumption made here is that we’ve already connected to the server and successfully performed the authorization.
This is the smallest, valid set of parameters executable towards the server. One has always to specify which entity is being addressed (always using lower case) and which fields are to be regarded. In the first stage of development, it’s acceptable to set the parameter to true, requesting all available columns. However, it’s important to restrict that in the deliverable version in order to lower the data transfer load.
QueryExpression query = new QueryExpression
{
  EntityName = "systemuser",
  ColumnSet = new ColumnSet(true)
};
Assuming that we need to list the names of all users of the system (fullname field is the key, spelled using lower case)

, we should restrict the data transferred as follows.

QueryExpression query = new QueryExpression
{
  EntityName = "systemuser",
  ColumnSet = new ColumnSet("fullname")
};
Once the expression is designed, we need to execute it, retrieving the results. The actual response contains additional information besides the requested data set, which in some rare cases might be useful. In a most common scenario it’s not, which is the reason why I normally pick the entities contained in the response and work directly on those. That gives me also the advantage of being able to apply LINQ on the data set.
IEnumerable<Entity> entities = Service.RetrieveMultiple(query).Entities;
Suppose that we need to retrieve and present on the screen the list of all system users’ ids (a mandatory field), names (a system field of fullname) and some special codes (a custom field called new_security).
QueryExpression query = new QueryExpression
{
  EntityName = "systemuser",
  ColumnSet = new ColumnSet("fullname", "new_security")
};
IEnumerable<Entity> entities = Service.RetrieveMultiple(query).Entities;
foreach (Entity entity in entities)
  Console.WriteLine("User Id "
    + entity.Id + ": " 
    + entity["fullname"] + " (" 
    + entity["new_security"] + ")");
There are two important remarks to be made at this point. There’s always some data retrieved, independent of the requested fields. The most important is the guid. In each of the above examples, the id of the retrieved entities has been included. This is very useful and can’t be turned off.
One needs to be aware that if a requested field is null in the database, it won’t be retrieved. That means, there’s no such field in the provided set of entities, despite the fact that one’s explicitly asked for it. One expects it to be included in the collection but set to null. It’s not! An attempt to read from that field will result in a run-time error saying that the collection doesn’t contain such a key. However, for some instances (the ones that do have that field set to anything but null, it’ll be included.

Authenticating using federations (ADFS, Federation, OnlineFederation etc.)

Goal: To make a connection to a server using authorization provider based on federations.
Logging in and authorizing using a federated security service is a complicated procedure. As the ADFS gains in popularity, certain aspects of the process could get nasty and overly complicated. However, I’m glad to say that so is not the case. Microsoft has released libraries and updates to support a smooth access to CRM protected by such security measures and they actually have done a great job integrating the calls so that the source code follows the pattern that AD already has.
Admittedly, there are configurations that will require additional steps. However, in my experience, most of the cases are easily managed the simple way. And if a server is setup in a way that requires more attention, the administrator should be aware of the additional costs of development coupled with overly ambitious protective walls.
private static void ShowSingleOrganizationContent(
  String url, String userName, String password)
{
  Authentication authentication = new Authentication
  {
    Base = new Uri(url),
    ClientCredential = new Credential
    {
      UserName = userName,
      Password = password,
      Type = Credential.CredentialType.Client
    }
  };

  using (OrganizationServiceProxy service = authentication.Service)
  {
    QueryExpression query = new QueryExpression
    {
      EntityName = "systemuser",
      ColumnSet = new ColumnSet(true)
    };

    IEnumerable<Entity> entities = service.RetrieveMultiple(query).Entities;
    foreach (Entity entity in entities)
      Console.WriteLine(entity.Id + ": " + entity["fullname"]);
  }

  Console.WriteLine("Done.");
  Console.ReadLine();
}
The code above relies on the service being set up using the Authentication class discussed in my previous posts. It delivers a list of all system users in the targeted organization and prints them to the screen. Having worked with web development in general and CRM in particular, I must say, it’s a refreshing experience to discover that things can get that smooth. I’m impressed and pleasantly surprised.

Authenticating using live id (part 3)

Goal: To design an effective pattern for management of credentials and security tokens using authorization provider based on live id.
In the last three postings, I discussed the approach on how to connect to a CRM server using the authorization provider based on live id. Furthermore, I presented some hints on how to trouble-shoot the issues that might (= surely will) occur. Besides that, there’s a need to mention a few additional points, most of which I’d describe as a requirement for a best practice pattern, to be taken into the consideration.
The most important amendment to the previous posting regards the registration of the current machine’s credentials with Microsoft. While the code presented works and does the job, it’s encumbered with a stark flaw of inefficiency. For each log in attempt, a new registration is performed, which leads to longer execution times due to latency, connection limitation and such. Instead, it’s highly recommended that once the machine is registered, the token provided by Microsoft’s service is stored and reused, hence eliminating the need for an additional round-trip.
As long we’re on the subject of storying, a savvy programmer will probably have noticed that the credentials (and especially the password) are stored in plain text. That’s, of course, not a recommended approach (unless a very serious need requires us to leave it that way) and I strongly urge everybody to encrypt the text, at least in a very basic way. For instance, a colleague of mine, who needs to be able to see the passwords during a testing phase, shifts the ASCII codes of it up and down according to a pattern known to him only, before writing the password to a file. So, a sequence of “KKKK” will become “LJLJ”. It’s no Enigma but it suffice.
Another suggestion I’d like to make is that, while the passwords should be randomly created, the machine name should be compiled based on the actual name of the currently used computer. That way, the code will emulate registration of the actual, physical device with Microsoft, which enhances the security.
Finally, as promised earlier, I’d like to present the wrapper for ClientCredentials class that I use everywhere. The reason for the its existence is that I really, really, really dislike the nomenclature of the properties in it. It’s confusing, poorly structured and looks just plain inappropriate. One needs to use System and System.ServiceModel.Description to compile the code.
public class Credential : ClientCredentials
{
  public enum CredentialType
  {
    Client,
    Device,
    Default
  }

  new public String UserName
  {
    get { return base.UserName.UserName; }
    set { base.UserName.UserName = value; }
  }

  public String Password
  {
    get { return base.UserName.Password; }
    set { base.UserName.Password = value; }
  }
  
  public CredentialType Type { get; set; }
}
There are, as expected, additional suggestions and points to be made. However, provided that Microsoft is phasing out this particular authorization method from CRM, substituting it for federated solutions, I’m not going to spend more time discussing the subject.

Authenticating using live id (part 2)

Goal: To trouble-shoot failed connection to a server using authorization provider based on live id.
When I wrote the article on how to connect to a CRM using authentication provider based on live id, I tried to simplify the code as much as I could. That way, I discovered a number of more or less confusing and cryptic error messages.
“An unsecured or incorrectly secured fault was received from the other party.”
This occurs due to negligence of conveying the serialized data containing randomized credentials to the service. The following line needs to be executed in order to actually perform the operation. In fact, I’d suggest fetching the result and analyzing its contents but that’s subject for a future discussion.
request.GetResponse();
“The username cannot be empty.”
This happens due to one of the following circumstances. The first is very obvious – the specified value of credential.UserName.UserName is an empty string (look for an assignment like userName = String.Empty) for client or device credentials. However, this can also happen in a not so straight-forward way. I got this error when I didn’t managed to serialize properly due to a misspelling. Make sure that the mapping is exactly right.
[XmlRoot("DeviceAddRequest")]
public sealed class Device
{
  [XmlElement("ClientInfo")]
  public DeviceRegistrationClientInfo DeviceInformation { get; set; }

  [XmlElement("Authentication")]
  public DeviceRegistrationAuthentication DeviceCredential { get; set; }

  ...
}
“The remote server returned an error: (400) Bad Request.”
This one is my personal favorite. It’s going to occur when one attempts to register the same device name too often (whatever that might be). This link explains what the code 400 is about. One can correct it by randomzing the user name for the credential and rerunning the program. If one doesn’t want to use the randomization algorithm provided by me, it’s fully sufficient to use the number of ticks since the digital date zero.
String uniqueName = String.Empty;
uniqueName = DateTime.Now.Ticks + "konrad_viltersten_is_godlike";
uniqueName = uniqueName.Substring(0, 24);
“Illegal characters in path.”
This didn’t happen to me but I’ve seen several other cries for help due to that error, so it seems appropriate to mention it as well. This error occurs when the programmer gets confused and mixes StringReader and StreamReader. Intellisense might be to blame here as the spelling is very similar and a true code warrior is lazy and doesn’t check the details. (Yes, I definitely am a true code warrior myself!) Possibly, there can be the the issue of confusing the URL that is targeting the service with the stream provided by it as seen in this discussion. The resolution for this problem goes through step-by-step analysis of the code to discover where exactly a stream or URL is provided instead of a string (or vice versa).

Authenticating using live id (part 1)

Goal: To make a connection to a server using authorization provider based on live id.
During the wave of the transitions to cloud based technologies such as Azure, Office365, SkyDrive etc., Microsoft introduced Dynamics CRM On-line. It brought a whole class of new capacity but also incurred a new model for authentication procedure. To make a very complicated story relatively short, one can say that in order to perform a successful login operation, the software needs to authorize both the client executing it and the machine that it’s run on.
In my humble opinion, this is the most tricky authentication to perform. There are examples at MSDN but they are heavy to understand and lack all explanation of the steps. I didn’t get much help out of them and it’s my understanding that I’m far from alone in this experience. Moreover, I’ve been told that the support for live id as authentication provider for CRM is being removed and will be gone within a year, which makes me very satisfied. At the same time, one has to realize that the most complicated approach is also the one that will get one very little reward in the professional conduct. Never the less, it’s important to document it for completeness’ sake.
Technically speaking, the program needs to provide two sets of credentials, the declaration of which is illustrated by the following code. In my opinion, the syntax for the creation of them is less than optimal. The nomenclature is deficient, the data structure is confusing and it lacks the modern ability to be created by initializators. To summarize, one has but two alternatives when working with the ClientCredentials class. Either accept it as it is, with all its flaws, or extend it to a wrapper that behaves properly and manages the quirks for one under the hood. I’ll elaborate on this subject and provide example source code in the future.
ClientCredentials
  clientCredentials = new ClientCredentials(),
  deviceCredentials = new ClientCredentials();

clientCredentials.UserName.UserName = "...";
clientCredentials.UserName.Password = "...";

deviceCredentials.UserName.UserName = "...";
deviceCredentials.UserName.Password = "...";
In order to authorize the machine (i.e. obtain the credentials for the device), one needs to contact Microsoft’s service for live id and provide an arbitrary set of user name and password. The service will then register the strings and reply with a puid (physical unit identifier), which is a token that’s supposed to be used at each login attempt. However, I discovered that it’s not needed at all. The only limitation that the registration actually possesses is that one can’t register the same user name multiple times within a short period of time. That can be circumvented by randomizing the string. To me it’s weird but that’s the way it is.
The first step is to create the randomized user name and password. Both are supposed to be 24 characters long. While the first may consist of alphanumerics, the latter may contain additional characters. Although the examples at MSDN will prefix the name by “11” (which probably refers to CRM 2011), it’s not needed and I’m a great believer in the KISS doctrine.
const String
  basicCharacterSet = "0123456789abcdefghijklmnopqrstuvqxyz",
  extendedCharacterSet = basicCharacterSet + "!@#$%^*()-_=+;,./?`~";
String
  generatedUserName = String.Empty,
  generatedPassword = String.Empty;
Random randomizer = new Random(DateTime.Now.Millisecond);
for (int i = 0; i < 24; i++)
{
  generatedUserName += basicCharacterSet[randomizer.Next(basicCharacterSet.Length)];
  generatedPassword += extendedCharacterSet[randomizer.Next(extendedCharacterSet.Length)];
}
When the user name and password are prepared, it’s time to contact Microsoft’s server and convey the happy news. I’ve done this using XML format posted as SOAP. It’s conceivable that there could be a different way but this is about the time when I got sick and tired and just went with a fairly simple syntax that I already had.
String registrationUrl = "https://login.live.com/ppsecure/DeviceAddCredential.srf";
WebRequest request = WebRequest.Create(registrationUrl);
request.ContentType = "application/soap+xml; charset=UTF-8";
request.Method = "POST";
request.Timeout = 60000;

using (Stream stream = request.GetRequestStream())
{
  Device deviceRegistrationRequest
    = new Device(Guid.NewGuid(), generatedUserName, generatedPassword);
  Serialize(stream, deviceRegistrationRequest);
}

request.GetResponse();
Furthermore, in order to be able to serialize the request, one needs to implement the definition of Device class properly. Then, one can apply the built in serializator that comes with the .NET framework.
[XmlRoot("DeviceAddRequest")]
public sealed class Device
{
  [XmlElement("ClientInfo")]
  public DeviceRegistrationClientInfo DeviceInformation { get; set; }

  [XmlElement("Authentication")]
  public DeviceRegistrationAuthentication DeviceCredential { get; set; }

  public Device() { }

  public Device(Guid guid, String userName, String password)
    : this()
  {
    DeviceInformation = new DeviceRegistrationClientInfo
    {
      ApplicationId = guid,
      Version = "1.0"
    };
    DeviceCredential = new DeviceRegistrationAuthentication()
    {
      MemberName = userName,
      Password = password
    };
  }
}
The strings in the attributes refer to the end points on Microsoft’s side. They can’t be altered but I strongly feel that the mapping should transform them to a more appropriate nomenclature. Hence, the discrepancy between the name in the attributes and the properties.
When the above operations are completed, one’s obtained the credentials for both the user himself and for the currently used device (registered at Microsoft’s service for live id authorization).
Uri organizationUrl = new Uri("http ... Organization.svc");
ClientCredentials
  clientCredentials = new ClientCredentials(),
  deviceCredentials = new ClientCredentials();
clientCredentials.UserName.UserName = "...";
clientCredentials.UserName.Password = "...";
deviceCredentials.UserName.UserName = GetRandomUserName();
deviceCredentials.UserName.Password = GetRandomPassword();
DoAuthorizeDeviceCredentials(deviceCredentials);

OrganizationServiceProxy organizationService = new OrganizationServiceProxy(
  organizationUrl, null, clientCredentials, deviceCredentials);
There are a number of additional issues that need to be taken into consideration, which I intend to discuss in the future together with some error messages, trouble-shooting, general suggestion on best practices and my suggested auxiliary definitions.

Authenticating using active directory

Goal: To make a connection to a server using authorization provider based on active directory.
The most commonly used authorization provider type, especially when working with a small or medium on-premise installation is through active directory. Irregardless of whether the client’s going to connect using Windows Login or VPN, the credentials will be provided prior to an attempted access to the CRM server.
The good part about it is that it’s very easy to authorize the user and, optimally (which, in fact, is quite likely to be the case), there’s no need to specify any credentials for the access, since they’ll be retrieved automagically from the cache. Hence, the only parameter that requires to be specified explicitly is the address of the organization.
Uri organizationUrl = new Uri("http ... Organization.svc");

OrganizationServiceProxy organizationService = new OrganizationServiceProxy(
  organizationUrl, null, null, null);
Of course, it is possible to specify customized credentials as well, should the need for such an approach arise. In the majority of cases, it won’t be necessary, though.
Uri organizationUrl = new Uri("http ... Organization.svc");
ClientCredentials clientCredentials = new ClientCredentials();
clientCredentials.UserName.UserName = "...";
clientCredentials.UserName.Password = "...";

OrganizationServiceProxy organizationService = new OrganizationServiceProxy(
  organizationUrl, null, clientCredentials, null);
It should be noted that while specifying the client credentials as null will work, it’s also possible to enter both the user name and the password as String.Empty, causing the authentication procedure to retract information from the cache. However, it’s not possible to only specify the user name, nor will it work if the specified credentials are incorrect.
There’s also another way to authenticate the client, which might be useful in the case when an instance of IServiceManagement has been created (when the program needs to determine the type of authorization, for instance).
Uri organizationUrl = new Uri("http ... Organization.svc");

IServiceManagement<IOrganizationService> serviceManagement =
  ServiceConfigurationFactory.CreateManagement<IOrganizationService>(organizationUrl);
OrganizationServiceProxy activeDirectoryService 
  = new OrganizationServiceProxy(serviceManagement, null as ClientCredentials);
And as previously, one can specify the client credentials explicitly, provided that they are correct.
Uri organizationUrl = new Uri("http ... Organization.svc");
ClientCredentials clientCredentials = new ClientCredentials();
clientCredentials.UserName.UserName = "...";
clientCredentials.UserName.Password = "...";

IServiceManagement<IOrganizationService> serviceManagement =
  ServiceConfigurationFactory.CreateManagement<IOrganizationService>(organizationUrl);
OrganizationServiceProxy activeDirectoryService 
  = new OrganizationServiceProxy(serviceManagement, clientCredentials);
There are two additional ways to authenticate to a server – using an instance of type IServiceConfiguration and applying a pre-existing token. The former is deprecated (or on a good way to be declared as such) and will be replaced by IServiceManagement interface. The latter is more advanced and its usage predicates that a connection already had been made, which places it outside the scope of this post.

Determining authentication provider type

Goal: To determine the type of authentication provider used by the targeted CRM installation based on the least amount of information.
Most of the cases, one knows already if the authorization provider is active directory, live id, federation etc. because the client’s told one that. However, if the software one’s developing is productifized and the authorization needs to be generally design, a question of learning what provider is being used arises. In the most basic case, all one knows is the URL of the services. Still, somehow, one needs to apply the correct authorization procedure.
The basic idea off a solution to this requirement is to connect to the server exposing the organization service and ask it what the applied authorization provider is. In the current version of SDK, there’s a factory class that can be used to produce a management object from which that information is readily available.
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
...
static public AuthenticationProviderType GetAuthenticationProviderType(Uri address)
{
  IServiceManagement<IOrganizationService> organizationServiceManagement
    = ServiceConfigurationFactory.CreateManagement<IOrganizationService>(address);
  return organizationServiceManagement.AuthenticationType;
}
While the class IOrganizationService resides directly in the assembly for SDK, the others (enumeration AuthenticationProviderType, interface IServiceManagement and factory class ServiceConfigurationFactory) require one to refer the client package as well.
Currently there are following values for the enumeration of AuthenticationProviderType.

  • ActiveDirectory
  • Federation
  • LiveId
  • None
  • OnlineFederation

Once one’s obtained the type, it’s pretty straight-forward to deploy the authorization procedure appropriate for it. In the upcoming blog posts I’ll go into details on implementing of the commonly used ones.

As a coding suggestion for the best practices, I’d recommend to place the authorization provider retrieval in an auxiliary static class so it can be reused in multiple projects. Also, it’s my experience that it’s a good idea to implement a polymorphic version of it, eliminating the need to explicitly create the Uri classed object. In most cases, the service information location will be provided as a String.
static public AuthenticationProviderType GetAuthenticationProviderType(String address)
{
  return GetAuthenticationProviderType(new Uri(address));
}