Thursday, 3 December 2015

Web API Preview - Unrecognized 'Edm.String' literal...

Today I was playing around with the new Web API Preview to try and check out how the new REST API was going to work. If you're interested take a look here: At a first glance this looks really good. It's much cleaner and does away with a long unnecessary url and the case sensitivity that always bugged me.

The first issue I hit was when trying to filter by a regarding object on an appointment. My first thought was is this not supported in the preview? I took a standard oData query from the 2011 endpoint and converted it so that this:
/xrmservices/2011/OrganizationData.svc/AppointmentSet?$select=Subject&$filter=RegardingObjectId/Id eq (guid'a199a199-a199-a199-a199-a199a199a199')

became this:
/api/data/appointments?$select=subject&$filter=regardingobjectid/id eq (guid'a199a199-a199-a199-a199-a199a199a199')

But I get presented with the error
Unrecognized 'Edm.String' literal 'guid'a199a199-a199-a199-a199-a199a199a199''

So what's going wrong? I tried many different variations, such as:

/api/data/appointments?$select=subject&$filter=regardingobjectid/id eq guid'a199a199-a199-a199-a199-a199a199a199'
/api/data/appointments?$select=subject&$filter=regardingobjectid eq (guid'a199a199-a199-a199-a199-a199a199a199')

etc... I always got 1 of 3 errors, the one previously mentioned, or
Can only bind segments that are Navigation, Structural, Complex, or Collections.

A binary operator with incompatible types was detected. Found operand types 'Edm.Guid' and 'Edm.String' for operator kind 'Equal'

The final error mentioned was by doing a direct comparison
/api/data/appointments?$select=subject&$filter=regardingobjectid eq 'a199a199-a199-a199-a199-a199a199a199'

And it got me thinking, maybe they've changed something. A quick look at oData 4.0 and I find that they've done away with both specifying the object type and the quotes for guids. The query is simply this:
/api/data/appointments?$select=subject&$filter=regardingobjectid eq a199a199-a199-a199-a199-a199a199a199

A sample on this query would have been nice (maybe there is one, I couldn't find it!)... But I'm liking these changes a lot!

Tuesday, 14 July 2015

Dynamics CRM - Developing faster code

We're deep in the days of large corporate multinational CRM systems, integrating with other systems, being interrogated daily by websites and portals, and generally being poked and prodded by many different systems. Because of this we regularly hit performance issues in our systems that in a worst case scenario can grind CRM or the related access points to a halt. Outside of throwing servers at the problem what as a developer can you do? Here are my top 5 tips.

1. Always specify a column set

This is a no-brainer, or at least it should be, always specify a column set when performing a query otherwise you're pulling back every column in the table every time. The amount of times I see the following in code is not funny:
var cols = new ColumnSet(true);

I've even been a perpetrator myself! We really need to get better at specifying our columnsets. Breaking it down to basics, specifying a column set can mean the difference between this:
select * from contact

and this
select contactid, fullname, birthdate from contact

I have seen companies hit the max columns on either contact or account, so that might just give you an idea of what sort of impact specifying all columns will have.

2. Nolock = true

Unfortunately, this is only available on the QueryExpression or via FetchXml in a FetchExpression object. Be aware that this tip isn't necessarily as black and white as "always do this", as there are situations where it will not be feasible.

The upside of setting NoLock = true is that it won't request a record lock during the SQL read and therefore should neither block nor get blocked from making the call. This not only makes the call faster, but makes load balanced servers more efficient as they can read the same records without waiting on the data to be freed from a lock.

There is a downside to setting this which is the possibility of reading uncommitted data or even duplicate data. Depending on what you are reading you may or may not care about this, so you'll need to make a judgement accordingly.

For example, imagine I was reading a settings table to find out if I should display a field in red or blue. I happen to read some uncommitted data that changed the colour and was subsequently rolled back. This usually wouldn't be considered detrimental. In fact, this is probably a good example of when you should consider using NoLock.

On the other hand what if you were reading something more crucial? For example you read an uncommitted price of a stock on the market that in turn caused you to price an option incorrectly due to uncommitted data being rolled back. Probably an unrealistic scenario, but definitely something you would consider as a bigger issue!

The question you need to answer before applying this is - which is worse...
  • A deadlock
  • Inaccurate data
If inaccurate data isn't a huge problem then NoLock is going to offer a favourable solution.

3. OrganizationService.Retrieve locks the record!

This leads on from the previous point, if you perform an OrganizationService.Retrieve you'll end up locking the record on the database. If the retrieve doesn't need to lock then use a QueryExpression (or FetchExpression) to perform a NoLock and retrieve the record that way instead. This will return an entity collection which you'll just need the first record from, but if performance is king this shouldn't be a problem.

4. LINQ locks too!

Again, as with Retrieve, LINQ doesn't allow you to specify a NoLock. Which means you need to use a Query Expression (or FetchExpression) instead. This is particularly annoying as LINQ is such a beautiful way to access CRM, but such a pity you can't gain access to the QueryExpression it creates under the hood. (Well... you can... but not without performing some reflection to invoke a private method... let me know if you'd like to know how to do this)

5. Indexing

Finally, slightly beyond the realm of coding, but still something you could do - take a look at indexing in the database or lack thereof. If you have a DBA team these guys will be able to generate reports on missing indexes for you. On the other hand, if you know of fields that you are constantly searching / filtering then consider adding them to the quickfind views which will in turn create indexes in the background. Getting a DBA to add coverage is ultimately better, but quickfind is a quick (be it slightly dirty!) way to get a field indexed.

Take care though, indexes can deplete performance on other operations, such as inserts. So don't start adding indexes willy nilly all over the database! Something to be aware of.

Wednesday, 27 May 2015

The new LocalPluginContext (and new BasePlugin class)

As a .net developer who often embarks on CRM plugins there's 1 thing I have always took for granted and that is the code provided for plugins when you use the CRM Developer Toolkit for Visual Studio. I have often reviewed the code, performed some minor refactoring on it, and pretty much just overlooked the general logic of it. It's boiler plate stuff that you just learn to accept. But today things were different. Today I decided to read it properly. And let's all admit it, LocalPluginContext and Plugins from the CRM Developer Toolkit need a serious refactor.

Don't get me wrong, I have used and loved this class for years. It wraps up all the objects I need very nicely and provides them in properties for me. But you know what else it does? It makes your plugins un-unit-testable, if I am allowed to create such a word. This is of course if you don't change / rewrite the class yourself.

There are a few mistake that are made by the developer toolkit:

Firstly, it embeds the LocalPluginContext class as a protected class and makes all the important properties internal. Worse again, it privatizes all the property setters on those most used interfaces. Now I cannot mock them with most mocking libraries!

Secondly, it creates a list of registered events to check if the plugin should run for the given message (the infamous tuple that dirties your constructors and has driven me daft for years). This is like a double plugin registration and really isn't required. Think about it, you register your plugin to fire in the Plugin Registration Tool, but if you forget to register it a second time in the constructor it will refuse to execute!

Finally, it needs a serious code review with resharper installed. There are a few redundant statements and lines that could be simplified or even removed.

I think it's time for new Plugin and LocalPluginContext classes and here is my suggestion:

Rework the base plugin class

I don't like the idea of inheriting from a Plugin class that implements IPlugin. It feels wrong to me that we have a class we can register as a plugin, but it's not really a plugin! So I want to rework this as a proper Base Plugin class. And this is my version of it:

    public abstract class BasePlugin : IPlugin
        private readonly string _className;

        protected BasePlugin()
            _className = GetType().Name;

        public void Execute(IServiceProvider serviceProvider)
            // Construct the Local plug-in context.
            var localContext = new LocalPluginContext(serviceProvider);

            localContext.Trace("Entered {0}.Execute()", _className);

            catch (FaultException<OrganizationServiceFault> e)
                // Trace the exception before bubbling so that we ensure everything we need hits the log

                // Bubble the exception
                localContext.Trace("Exiting {0}.Execute()", _className);

        public abstract void Execute(ILocalPluginContext localContext);

So we still have the Execute method as before, but instead of an over complicated execution process (using the dreaded tuple) I just expect you to implement an Execute method accepting an ILocalPluginContext when inheriting this class.. So it will work almost exactly as before. Also, although our BasePlugin implements IPlugin it is abstract, so it's not going to appear in the list of available plugins when you load the DLL.

You also may have noticed I have introduced an ILocalPluginContext. This brings me on to my next suggestion.

Rework LocalPluginContext

Another area that always concerned me was how this internal class was written. I'm going to simplify it a little. Rather than pulling everything out of the necessary areas in a constructor let's just create lazy loaded properties. At least then we're only pulling out an IOrganizationService if it's used, a tracer if it's used, etc. I generally expect that all plugins will use a tracer, but quite often you won't need an IOrganizationService, e.g. validation plugins.

I also want to add an interface to this to allow easier mocking while unit testing.

So here's my idea for the LocalPluginContext class:

    public interface ILocalPluginContext
        IOrganizationService OrganizationService { get; }
        IPluginExecutionContext PluginExecutionContext { get; }
        ITracingService TracingService { get; }
        void Trace(string message, params object[] o);
        void Trace(FaultException<OrganizationServiceFault> exception);

    public class LocalPluginContext : ILocalPluginContext
        private readonly IServiceProvider _serviceProvider;
        private IPluginExecutionContext _pluginExecutionContext;
        private ITracingService _tracingService;
        private IOrganizationServiceFactory _organizationServiceFactory;
        private IOrganizationService _organizationService;

        public IOrganizationService OrganizationService
                return _organizationService ?? (_organizationService = OrganizationServiceFactory.CreateOrganizationService(PluginExecutionContext.UserId));

        public IPluginExecutionContext PluginExecutionContext
                return _pluginExecutionContext ??
                       (_pluginExecutionContext = (IPluginExecutionContext)_serviceProvider.GetService(typeof(IPluginExecutionContext)));

        public ITracingService TracingService
                return _tracingService ?? (_tracingService = (ITracingService)_serviceProvider.GetService(typeof(ITracingService)));

        private IOrganizationServiceFactory OrganizationServiceFactory
            get { return _organizationServiceFactory ?? (_organizationServiceFactory = (IOrganizationServiceFactory)_serviceProvider.GetService(typeof(IOrganizationServiceFactory))); }

        public LocalPluginContext(IServiceProvider serviceProvider)
            if (serviceProvider == null)
                throw new ArgumentNullException("serviceProvider");

            _serviceProvider = serviceProvider;

        public void Trace(string message, params object []o)
            if (PluginExecutionContext == null)
                SafeTrace(message, o);
                    "{0}, Correlation Id: {1}, Initiating User: {2}",
                    string.Format(message, o),

        public void Trace(FaultException<OrganizationServiceFault> exception)
            // Trace the first message using the embedded Trace to get the Correlation Id and User Id out.
            Trace("Exception: {0}", exception.Message);

            // From here on use the tracing service trace

            if (exception.Detail != null)
                SafeTrace("Error Code: {0}", exception.Detail.ErrorCode);
                SafeTrace("Detail Message: {0}", exception.Detail.Message);
                if (!string.IsNullOrEmpty(exception.Detail.TraceText))
                    SafeTrace("Trace: ");

                foreach (var item in exception.Detail.ErrorDetails)
                    SafeTrace("Error Details: ");

                if (exception.Detail.InnerFault != null)
                    Trace(new FaultException<OrganizationServiceFault>(exception.Detail.InnerFault));

        private void SafeTrace(string message, params object[] o)
            if (string.IsNullOrWhiteSpace(message) || TracingService == null)
            TracingService.Trace(message, o);

One extra feature I have added is how it traces. The new LocalPluginContext contains a function that performs better tracing of FaultExceptions (ToString does not cut it) and I've matched the standard trace to how the tracing service works and included a param of objects.

What the new plugins look like!

A basic plugin looks like this which in my opinion is a lot cleaner:

    public class MyPlugin : BasePlugin
        override public void Execute(ILocalPluginContext localContext)
            // do what needs to be done!

One big upside to doing it this way is we have a much more testable framework where we simply pass in a mocked up ILocalPluginContext.

Friday, 23 January 2015

CRM 2011/2013 - SQLAgent service is not running

Quick one from me today. Quite often I come across this issue where it tells me the SQLAgent service is not running on the server when trying to import an organization or install a CRM server.

9 times out of 10 this comes down to user rights. I believe the right it is being denied is interrogating the the windows server to get a list of services running so that it can check for SQL Agent. Which means adding the user as a sys admin to SQL often won't help.

Try adding the user to the Local Admins group on the SQL box as well and this error should go away.