SQL Queries from Transactional Plugin Pipeline

Sometimes the LINQ, Query Expressions or Fetch just doesn't give you the ability to quickly query your data in the way you want to. A good example of this is the lack of left outer join support if you want a where clause to filter results based on the joined entity. Sometime, you just need to query your database using good old T-SQL. In CRM 4 you could do this fairly easily by simply opening a connection directly and doing what you need to. Since Transactional Pipelines were introduced with CRM2011 , I've been dancing a jig every time I don't have write manual rollback compensation code – but – if you try a SQL query from a transactional pipeline that queries the same entity that you are updating/inserting, you'll get a blocking lock that will cause the operation to time out. To get around this, you have a number of options: 1) Call the SQL from outside a transaction in the PreValidation or an Async Pipeline 2) Use the following method to hook into the CRM Transaction and execute your query from within that. Note: I should say that this could be considered breaking the following rule in the SDK that defines what is supported or not: "The use of application programming interfaces (APIs) other than the documented APIs in the Web services DeploymentService, DiscoveryService, Organization Data Service, SOAP endpoint for Web Resources and OrganizationService." I'm assuming that you are familiar with the System.Data library, so I'm just posing how to get a SqlTransaction, and you can do the rest:   Microsoft.Xrm.Sdk.IPluginExecutionContext context = (Microsoft.Xrm.Sdk.IPluginExecutionContext) serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext)); object platformContext = context.GetType().InvokeMember("PlatformContext", System.Reflection.BindingFlags.GetProperty, null, context, null); SqlTransaction tx = (SqlTransaction)platformContext.GetType().InvokeMember("SqlTransaction", System.Reflection.BindingFlags.GetProperty, null, platformContext, null); DataSet result = SqlHelper.ExecuteDataset(tx, CommandType.Text, "SELECT ...");

  You can also call a stored procedure in a different database that points back to the MSCRM database if you have complex queries. You'll need to use 'SET TRUSTWORTHY ON' to ensure that the security context is passed between the two databases. My advice would be to only use this method only where using the SDK is just not possible or performs too slowly. Hope this helps. UPDATE: I've been doing some benchmarking recently on the FetchXml Reporting Services provider and using the Filtered Views. Under some circumstances where a user has only partial read access to records, using the FetchXml provider can perform considerably faster than the Filtered Views. Watch out for a blog post on this subject.

Embedding CRM reports in IFrames using the CRM2011 Report Viewer

It is quite common for users to request that reports be included in Iframes on forms, or in Dashboards. Although this can be done by pointing the Iframe directly at SQL Reporting services, this essentially bypasses the CRM Report connector and relies on the user authenticating directly with SQL Reporting Services and SQL Server. If you have SQL Server and Reporting Services on the same server or Kerberos is set up correctly, this isn't a problem. If however your users can only access the report through the CRM Front end, then you need to use the CRM Report review rather than the Reporting Services native one. 1) Create a html web resource named 'ReportViewer' or similar

<html> <head> <title></title> <script src="ClientGlobalContext.js.aspx" ></script> <script type="text/javascript"> function submitForm() { var form = document.forms[0]; var context = GetGlobalContext(); form.action = context.getServerUrl() + '/CRMReports/rsviewer/reportviewer.aspx'; form.uniquename.value = context.getOrgUniqueName();

    var query = window.location.search.substring(1);
    var vars = query.split("&");
    var idSet = false;

    for (var i = 0; i &lt; vars.length; i++) {
        var pair = vars[i].split("=");
        switch (pair[0]) {
            case 'data':
                var params = unescape(pair[1]).split("&");
                for (var j = 0; j &lt; params.length; j++) {
                    var param = params[j].split("=");
                    switch (param[0]) {
                        case 'id':
                            form.id.value = param[1];
                            idSet = true;
                        case 'hideparams':
                            form.PromptAreaCollapsed.value = param[1];
                        case 'iscustomreport':
                            form.iscustomreport.value = param[1];
                            // Add any other values as report parameters
                            var paramInput = document.createElement('input');
                            paramInput.setAttribute('type', 'hidden');
                            paramInput.setAttribute('name', 'p:' + param[0]);
                            paramInput.setAttribute('value', param[1]);

    if (idSet) {
    else {
        // Show message
        var dvMessage = document.createElement("div");
        dvMessage.innerHTML ="Report Id is not set";


</script> </head> <body onload="submitForm()"> <form action ="" method="post"> <input type="hidden" name="id" value="{xxx}" /> <input type="hidden" name="uniquename" value="" /> <input type="hidden" name="iscustomreport" value="true" /> <input type="hidden" name="reportName" value="Report Name" /> <input type="hidden" name="isScheduledReport" value="false" /> <input type="hidden" name="PromptAreaCollapsed" value="false" /> </form> </body> </html>

2) Include in a Dashboard or Webresource on a form using the following Web Resource Properties (The Customer Parameter Data field): Id={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx} (make sure you add the curly braces) You can also pass any report parameters you have by adding on : param1=value1&param2=value2 If you want to have the report parameters hidden by default use: &hideparams=true

 So the whole data field would be something like: Id={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}&hideparams=true&param1=value1&param2=value2 I don't want to spoil all your fun – so I'll leave you to add the query to lookup the id by passing in the report name rather than the ID! Have fun!

CRM Developer ‘Must Know’ #2: Web Resource Caching

With the introduction of Web Resources in CRM 2011 the task of adding custom user interface functionality (beyond simple JavaScript) has become a whole lot easier to build and deploy. The fact that web resource are part of the solution means that there is no need to have custom deployment routines to create sites in the ISV folder. You always want your users to have the faster experience possible, so it is important in each of these situations to ensure that your web resources are being cached by the Client browser to avoid downloading a copy with each request. How Web Resources are cached CRM 2011 uses an very simple but effective means of ensuring that not only Web Resources are cached by the browser, but when you update any of them, the cache is invalidated and the new version is downloaded. When a Web Resource is referenced, although the Url shown on the Web Resource form is something like: http://crm/Contoso/WebResources/new/custompage.htm If you request this url in a browser, and use Internet Explorer's F12 Developer Tab Network monitor, you'll see that the response has the following Headers: Cache-Contro:    private Expires:     <Today's Date Time>

This instructs the browser/proxy server that is should never cache this file, and every time the browser asks for it, get the latest version from the server. This may not seem so bad for the odd file, but if you add all the files the browser needs for every page request and then multiply by the number of users you have, it introduces a considerable network load and download time. So how are they cached? When the Web Resource is referenced by a CRM 2011 page, the following format is used: http://crm/Contoso/%7B634411504110000000%7D /WebResources/new/custompage.htm If you look at the response headers you'll now see: Cache-Control:    public Expires:     <Today's Date Time Plus One Year>

So the browser/proxy server will keep a copy of this web resource for a year and use that before it goes to get the latest version. So how is the client cache cleared when I publish a new version of the web resource? The additional %7B634411504110000000%7D is the 'cache directory' and every time the customisations are re-published is updated to a new number. Since the browser/proxy cache is linked to the url of the file, if the url changes, the cache is no longer valid, and it is considered a new file altogether to be downloaded. So how do we ensure that this cache strategy is always used? Luckily, in most cases CRM 2011 handles this for us providing we play by its rules: Among the options for showing a Web Resource 1) Embedding in an Entity form2) Showing from a site map link3) Ribbon button image4) A Popup dialogue from a Ribbon button via a javascript function. Here are the ways to ensure caching in each of these scenarios: 1) Embedding in an Entity form If you embed a web resource in an Entity form, the cache directory is automatically used for you. However, if that web resource is an html page, ensure that you use relative paths in any javascript/css/image links so that you always stay in the cache directory. E.g. If you have the following webresources:

new/Account/AssignAccount.htm new/scripts/Common.js

In your AssignAccount.htm page, reference relatively using: <script src="../scripts/Common.js" type="text/javascript"></script>

Do not use absolute: <script src="/Webresources/scripts/Common.js" type="text/javascript"></script>

2) Showing from a site map link When showing an html page from a site map link, you can ensure that CRM uses the cache directory by avoiding absolute paths and use the $webresource token in the SubArea definition: <SubArea Id="navassignaccount" PassParams="0" Client="All" Url="$webresource:new/Account/AssignAccount.htm" Icon="$webresource:new_/icons/assign16.gif">

3) Ribbon button image When referencing images from site maps/ribbons, use the $webresource token as above rather than using the absolute path of the web resource. 4) - A Popup dialogue from a Ribbon button via a JavaScript function UR8 or later  UR8 introduced a new utility function called 'openWebResource' - this will ensure that if you need to show a popup window with a webresource, caching is used rather than having to provide an absolute path: Xrm.Utility.openWebResource("newpage.htm"); This results in a url being used along the lines of: http://server/Org/%7B634940089750000000%7D/WebResources/newpage.htm If you do need to manually construct a Web Resource Url, you can use the contstant 'WEBRESOURCEORGVERSIONNUMBER' but baring in mind that this is not a supported/documented SDK constant. Before UR8 Prior to UR8, this final one poses a bit more of a challenge. If you want to show a popup window that references a web resource page, before UR8 there was no way in the customisations to achieve this – it has to be JavaScript which means you need to construct the cache directory web resource location yourself. This is biggest area where I see developers writing code resulting in the browser always downloading the file with every request. To avoid this, you can find the current cache directory from another web resource that is currently loaded using something similar to: function GetCacheDirectory(webresourceUrl) { var scripts = document.getElementsByTagName("script"); for (var i = 0; i < scripts.length; i++) { var url = scripts[i].src; var p1 = url.search("/%7B") if (p1 > 0) { var p2 = url.search("%7D/", p1) + 4; var resourceCache = url.substr(p1, (p2 - p1)); break; } } var url = "/" + Xrm.Page.context.getOrgUniqueName() + resourceCache + webresourceUrl; return url; }

And calling it using: // Get cache directory resource url var url = GetCacheDirectory('/WebResources_/new/Account/AssignAccount.htm');

// Open Window window.open(url);

Of course - I would recommend you upgade to the latest rollup if you don't have UR8 installed yet! If you follow these steps, you will ensure that your user's browser only download files when they need to resulting in less network load and faster load time. Happy caching!

Early bound Entity Types in Silverlight

One of the drawbacks of using Silverlight clients for CRM2011, is that there is no built in support for early bound entity types since you cannot use the Microsoft SDK assemblies. Really, there isn't anything that special going on with early bound support in the SDK assemblies – a behaviour is added to the WCF client that intercepts the entities after serialization and converts them to the early bound types. Ironically, one of the things about Silverlight is that it needs early bound properties for binding purposes – below is the code you can use to easily simulate this early bound support in Silverlight. 1. You need to create the attributes to use to decorate your early bound entities.

[AttributeUsage(AttributeTargets.Property)] public sealed class AttributeLogicalNameAttribute : Attribute { public AttributeLogicalNameAttribute(string logicalName) { if (string.IsNullOrWhiteSpace(logicalName)) { throw new ArgumentNullException("logicalName"); } this.LogicalName = logicalName; }

public string LogicalName
{ get; set; }


 2. Create an early bound entity with the code to get/set the attribute values from the attribute collection – this is essentially the same as the SDK generated early bound types. For example, I'm using contact here:

public class Contact : Entity { [AttributeLogicalName("contactid")] public Guid ContactId { get { return this.GetAttributeValue<Guid>("contactid"); } set {

            this.SetAttributeValue("contactid", value);


    public string FullName
            return this.GetAttributeValue&lt;string&gt;("fullname");

            this.SetAttributeValue("fullname", value);


    public string FirstName
            return this.GetAttributeValue&lt;string&gt;("firstname");

            this.SetAttributeValue("firstname", value);

    public string LastName
            return this.GetAttributeValue&lt;string&gt;("lastname");

            this.SetAttributeValue("lastname", value);

  1. Add extension methods to allow converting to the early bound types

public static class ProxyTypeSupport { public static void SetAttributeValue(this Entity thisEntity, string attributeLogicalName, object value) { if (string.IsNullOrWhiteSpace(attributeLogicalName)) { throw new ArgumentNullException("attributeLogicalName"); } thisEntity[attributeLogicalName] = value; }

    public static T ToEntity&lt;T&gt;(this Entity thisEntity) where T : Entity
        if (typeof(T) == typeof(Entity))
            Entity entity = new Entity();
            return (entity as T);

        T target = (T)Activator.CreateInstance(typeof(T));
        return target;

    public static void ShallowCopyTo(this Entity thisEntity, Entity target)
        if ((target != null) && (target != thisEntity))
            target.Id = thisEntity.Id;
            target.LogicalName = thisEntity.LogicalName;
            target.EntityState = thisEntity.EntityState;
            target.RelatedEntities = thisEntity.RelatedEntities;
            target.Attributes = thisEntity.Attributes;
            target.FormattedValues = thisEntity.FormattedValues;



 4. Add code to retrieve, and convert the returned Entities into the early bound Contact Types.

service.BeginRetrieveMultiple(query, (IAsyncResult asyncResult) => { // Convert results to early bound Entity EntityCollection results = service.EndRetrieveMultiple(asyncResult); ObservableCollection<Contact> contacts = new ObservableCollection<Contact>();

foreach (Entity lateBoundEntity in results.Entities) { contacts.Add(lateBoundEntity.ToEntity<Contact>()); }

this.Dispatcher.BeginInvoke(() => { this.dataGrid1.ItemsSource = contacts;


}, null);

 This would also work in reverse to allow you to update values. Note: The example is not using MVVM pattern so as to make is simpler for readers.


The use of type 'Microsoft.Xrm.Sdk.Query.FilterExpression' as a get-only collection is not supported with NetDataContractSerializer.

A while back I had a problem with calling the CRM2011 WCF endpoint where the Microsoft SDK Proxy assembly was not available (e.g. Silverlight). When calling RetrieveMultiple I was getting the following exception:

"The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://schemas.microsoft.com/xrm/2011/Contracts/Services:request. The InnerException message was 'The use of type 'Microsoft.Xrm.Sdk.Query.FilterExpression' as a get-only collection is not supported with NetDataContractSerializer.Consider marking the type with the CollectionDataContractAttribute attribute or the SerializableAttribute attribute or adding a setter to the property." The strange thing was that this appeared even when not using a FilterExpression in the query. After a bit of digging it turned out to be to do with the serialization of the ColumnSet object when AllColumns was set to true, but the Columns collection was empty. The solution was to use partial classes to simulate the Microsoft SDK Proxy classes, and then set the Columns collection to a 'dummy' value when AllColumns was set to true.

partial class ColumnSet { public ColumnSet() { this.ColumnsField = new ObservableCollection(); } public ColumnSet(params string[] columns) { this.Columns = new ObservableCollection(columns); } public ColumnSet(bool allColumns) { this.AllColumnsField = allColumns; this.ColumnsField = new string[] { allColumns.ToString() }; } }

Then you can use:

query.ColumnSet = new ColumnSet("col1","col2","col3");


query.ColumnSet = new ColumnSet(true);

If you are not using the ObservableCollection in your proxy classes, use this version instead:

partial class ColumnSet { public ColumnSet() { this.ColumnsField = new ObservableCollection(); } public ColumnSet(params string[] columns) { this.Columns = columns; } public ColumnSet(bool allColumns) { this.AllColumnsField = allColumns; this.ColumnsField = new string[] { allColumns.ToString() }; } }

Hope this helps!

Silverlight Theme for CRM2011

Develop1 have a Silverlight 4 CRM 2011 theme available for the following user interface components: Label Style Text Box Style Date Picker Style Radio Button Style Data Grid Style Lookup Style (Coming soon) Please contact us for more information.

“Value cannot be null.Parameter name: attributeName” when running Linq query with join

If you run the following query using the CRM 2011 Linq provider (I'm using Linq pad here):

from u in SystemUserSet where u.Id == new Guid("f19f4c09-965b-e011-ab82-000c2957f385") select u

All is well. However, if you add a join:

from u in SystemUserSet join t in TerritorySet on u.TerritoryId.Id equals t.TerritoryId.Value where u.Id == new Guid("f19f4c09-965b-e011-ab82-000c2957f385") select u

You will get the following exception: System.ArgumentNullException: Value cannot be null.Parameter name: attributeName Looking at the Query Expression created, you will see:

<ConditionExpression> <AttributeName i:nil="true" /> <Operator>Equal</Operator> <Values xmlns:d5p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> <d5p1:anyType xmlns:d6p1="http://schemas.microsoft.com/2003/10/Serialization/" i:type="d6p1:guid">f19f4c09-965b-e011-ab82-000c2957f385</d5p1:anyType> </Values> </ConditionExpression>

The problem seems to do with the way that the Linq provider resolves attribute names on the Id field. You need to change the query to be (specifying the Id parameter explicitly):

from u in SystemUserSet join t in TerritorySet on u.TerritoryId.Id equals t.TerritoryId.Value where u.SystemUserId == new Guid("f19f4c09-965b-e011-ab82-000c2957f385") select u

How to change the default Lookup type on ‘Customer’ fields

In CRM 4, this used to be a very popular request: "Please change the default lookup type on the customer field to contact" Also… "Please limit the lookup type on the customer field to only allow selection of contacts" In CRM 2011, there still doesn't seem to be an easy way of doing this, so we have to revert to 'unsupported' means:

document.getElementById("customerid").setAttribute("lookuptypes", "2"); document.getElementById("customerid").setAttribute("defaulttype", "2");

If I find a more supported way, I'll update this post.

How to clear options without leaving an empty entry

Using the clearOptions method on PickLists, can leave an empty entry because Drop Down lists in HTML must have at least one entry. To work around use the following code that removes all options other than the current value to avoid the empty entry.

var attribute = Xrm.Page.getAttribute(“attributename”); // NOTE: We can't use clearOptions since this adds an empty option // attribute.controls.forEach(function (control) { control.clearOptions(); })

for (var j = 0; j

If this doesn't work for you, the empty entry can be manually removed using:

attribute.controls.forEach(function (control) { control.removeOption('') });

Microsoft deliver last milestone of Dynamics CRM 2011 release schedule

Following Januaries release of the Online Version, the last milestone in the Dynamics CRM 2011 project has been reached with the release of the On-Premises and Partner-Hosted Deployments. The new version can be downloaded at the Microsoft Download Centre. 90 Day Trial Product Keys: Microsoft Dynamics CRM Workgroup Server 2011: H84KC-JH8DF-7PDYK-TXBXR-2RMMT Microsoft Dynamics CRM Server 2011: MQM2H-JYYRB-RRD6J-8WBBC-CVBD3 "Microsoft announced the new release scaled to 150,000 concurrent users in a single instance while delivering sub-second response times, once again raising the bar for performance and scalability in the CRM industry. The new benchmark results will be detailed in a Microsoft Dynamics CRM performance and scalability white paper that will be delivered in the coming weeks." Read more about the release on the Microsoft News Centre We are really excited about the huge investment Microsoft has made into improving Dynamics CRM from both the end user and developer perspective.