Exception message: Table alias is not unique amongst all top-level table and join aliases CRM 2013

Recently I was trying to create a bunch of different views and I was using an existing view to "Save As" another view.
However, after publishing my new views, I would keep getting the error "Exception message: Table alias a_d8c7aa10e688e31180c200155d53e454 is not unique amongst all top-level table and join aliases" in the Event Viewer

When I looked into the FetchXml in SavedQueryBase table for the view that I just created, I would see that there were indeed two "linked entity" tags in the FetchXml.
Searching the internet, I found mostly 2 dirty work around's and 1 perfect work around (perfect for me) to resolve this issue.

Dirty Work Around

1. Export the Solution with the entity inside it. Open the customization.xml and remove the duplicate reference to the "linked entity" tag. Import the solution.
2. Get the FetchXml for the view from the SavedQueryBase table and remove the duplicate reference to the "linked entity" tag. Update the FetchXml back into the SavedQueryBase table for that view and then Publish the Entity from CRM.

Perfect Work Around (Perfect for my situation)

I just happened to accidentally read the comments left by one of the users in one of the blogs while researching a fix for this issue. I can't remember the blog or the commenter but all thanks to him for the issue resolution.

Root Cause
This is a bug in the product and apparently there was a fix released for this in CRM 2011 UR 12 but no fix has been released so far for CRM 2013 until UR 2.
The Root Cause is that if you already have columns from a "linked entity" in your "Entity Advanced Find View", any further views that you create that reference columns from a linked entity will throw an error.

Resolution
1. Go to the Entity Customization
2. Click on Views
3. Open the Entity Advanced Find View (Ex. Contact Advanced Find View)
4. Remove any references to columns that may have been added from another entity. i.e these fields do no exist inside the current entity.
5. Save and Publish the entity.

Thats it! Now, I was able to create all my views as usual without getting any errors. Hope this helps someone.

Retrieve CRM Form Metadata Xml

The below method helps us to retrieve the FormXml of a CRM Entity

        private string GetFormXml(string websiteName, string entityName, IOrganizationService service)
        {
            string formXml = "";

            try
            {
                RetrieveEntityRequest mdRequest = new RetrieveEntityRequest()
                {
                    EntityFilters = EntityFilters.Attributes,
                    LogicalName = entityName,
                    RetrieveAsIfPublished = true
                };
                // Execute the request
                RetrieveEntityResponse entityResponse = (RetrieveEntityResponse)service.Execute(mdRequest);
                //EntityMetadata entityData = entityResponse.EntityMetadata;y

                var entityTypeCode = entityResponse.EntityMetadata.ObjectTypeCode.Value;

                QueryExpression query = new QueryExpression("systemform");
                query.ColumnSet = new ColumnSet("formxml");
                query.Criteria.AddCondition(new ConditionExpression("name", ConditionOperator.Equal, websiteName));
                query.Criteria.AddCondition(new ConditionExpression("objecttypecode", ConditionOperator.Equal, entityTypeCode));

                RetrieveMultipleRequest retrieveRequest = new RetrieveMultipleRequest();
                retrieveRequest.Query = query;

                var results = (RetrieveMultipleResponse)service.Execute(retrieveRequest);
                formXml = results.EntityCollection.Entities[0].Attributes["formxml"].ToString();

                return formXml;

            }            
            catch (FaultException ex)
            {
                throw new FaultException("Error occured while instantiating the CRM Service " + ex.ToString());
            }
            
        }

Retrieving the optionset label

The below code helps us to retrieve the label of the option set. Please note that the "optionValue" is the integer value of the label that you want to retrieve.

        public static string GetOptionSetLabel(string entityName, string attributeName, int optionValue, IOrganizationService service)
        {
            try
            {
                RetrieveAttributeRequest retrieveAttributeRequest =
                            new RetrieveAttributeRequest
                            {
                                EntityLogicalName = entityName,
                                LogicalName = attributeName,
                                RetrieveAsIfPublished = true
                            };

                // Execute the request.
                RetrieveAttributeResponse retrieveAttributeResponse =
                    (RetrieveAttributeResponse)service.Execute(retrieveAttributeRequest);

                // Access the retrieved attribute.
                PicklistAttributeMetadata retrievedPicklistAttributeMetadata = (PicklistAttributeMetadata)retrieveAttributeResponse.AttributeMetadata;

                // Get the current options list for the retrieved attribute.
                OptionMetadata[] optionList = retrievedPicklistAttributeMetadata.OptionSet.Options.ToArray();

                foreach (OptionMetadata option in optionList)
                {
                    if (option.Value == optionValue)
                    {
                        return option.Label.UserLocalizedLabel.Label;
                    }
                }
            }
            catch(Exception ex)
            {
                WriteErrorLog(entityName, "Error while retrieving the optionset labels " + ex.ToString(), service);
            }

            return String.Empty;
        }

Retrieve the image saved in an Entity Record

The below method can be used to retrieve the image that is stored in an entity record.

        public static byte[] GetEntityImage(string entityName, Guid entityId, IOrganizationService service)
        {
            try
            {
                QueryExpression query = new QueryExpression(entityName);
                query.ColumnSet = new ColumnSet("entityimage");
                query.Criteria = new FilterExpression(LogicalOperator.And);
                query.Criteria.AddCondition(entityName + "id", ConditionOperator.Equal, entityId);
                EntityCollection collection = service.RetrieveMultiple(query);
                if (collection.Entities[0].Contains("entityimage"))
                {
                    return collection.Entities[0]["entityimage"] as byte[];
                }

            }
            catch (Exception ex)
            {
                //WriteErrorLog(entityName, ex.ToString(), service);
            }
            return null;
        }

CRM 2011 - Auto assign a role to user when a New User is created/Updated

We had a recent requirement where we needed to assign a specific role to a user on user create using a plugin. The below is the complete source code of the plugin that had been written.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Messages; 


namespace AssignDefaultRole
{
    public class AssignRoleCS : IPlugin
    {        
        Guid systemUserGuid = Guid.Empty;
        EntityReference buLookup;
        Guid buid = Guid.Empty;
        EntityCollection userRolesColl = null;
        Entity entitySystemUser;
        string securityRole = string.Empty;

        public void Execute(IServiceProvider serviceProvider)
        {
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            IOrganizationService service = factory.CreateOrganizationService(context.UserId);

            
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {
                Entity primaryEntity = (Entity)context.InputParameters["Target"]; // systemuser
                if (primaryEntity.LogicalName != "systemuser" && context.MessageName.ToLower()!="update")
                {
                    return;
                }
                else if (context.PostEntityImages.Contains("PostUserImage") && context.PostEntityImages["PostUserImage"] is Entity)
                {
                    entitySystemUser = (Entity)context.PostEntityImages["PostUserImage"];
                    buLookup = (EntityReference)entitySystemUser.Attributes["businessunitid"];
                    buid = buLookup.Id;                   
                    systemUserGuid = new Guid(entitySystemUser.Attributes["systemuserid"].ToString());

                    //Check if the user already has the Default Role
                    bool userHasRole = CheckRoleExists(service);

                    if (!userHasRole)//user does not have the Default role
                    {
                        //Get the Default Role
                        Guid DefaultSecurityRoleId = GetSecurityRoleId(service, buid, securityRole);

                        //Assign the role to the user.
                        AssignSecurityRole(systemUserGuid, DefaultSecurityRoleId, service);
                    }
                }
            }          

          
        }

        /// 

        /// Check if the user already has the default role assigned.
        /// 
  
        private bool CheckRoleExists(IOrganizationService service)
        {
            try
            {
                //UserGuid = context.InitiatingUserId;
                securityRole = "Default Privileges Role";
                QueryExpression Query = new QueryExpression()
                {
                    LinkEntities = 
                    {
                        new LinkEntity
                        {
                            LinkFromEntityName = "role",
                            LinkFromAttributeName = "roleid",
                            LinkToEntityName = "systemuserroles",
                            LinkToAttributeName = "roleid",
                            LinkCriteria = new FilterExpression
                            {
                                FilterOperator = LogicalOperator.And,
                                Conditions = 
                                {
                                    new ConditionExpression
                                    {
                                        AttributeName = "systemuserid",
                                        Operator = ConditionOperator.Equal,
                                        Values = { systemUserGuid }
                                    }
                                }
                            }
                        }
                    }
                };

                Query.ColumnSet = new ColumnSet(true);
                Query.EntityName = "role";

                userRolesColl = service.RetrieveMultiple(Query);
               
                //If the role exists, return true
                if (userRolesColl.Entities.Count > 0 && userRolesColl.Entities[0].Attributes["name"].ToString() == securityRole)
                {
                    return true;
                }

                return false;

            }
            catch (System.Web.Services.Protocols.SoapException ex)
            {
                throw new InvalidPluginExecutionException(ex.Detail.InnerText);
            }
            catch (Exception ex)
            {
                throw new InvalidPluginExecutionException(ex.Message);
            }
        }


        /// 

        /// Get the Role ID of the Default CRM 2011 role
        /// 
     
        private Guid GetSecurityRoleId(IOrganizationService service,Guid businessUnitID,string roleName)
        {
            Guid SecurityRoleId = Guid.Empty;
            EntityCollection ec = new EntityCollection();

            try
            {
                QueryExpression qe = new QueryExpression("role");
                ColumnSet set = new ColumnSet(new string[] { "roleid" });

                qe.ColumnSet = set;

                ConditionExpression cond = new ConditionExpression("name", ConditionOperator.Equal, roleName);
                ConditionExpression cond2 = new ConditionExpression("businessunitid", ConditionOperator.Equal, businessUnitID);
                FilterExpression fl = new FilterExpression(LogicalOperator.And);
                fl.Conditions.Add(cond);
                fl.Conditions.Add(cond2);

                qe.Criteria.AddFilter(fl);

                ec = service.RetrieveMultiple(qe);

                if (ec.Entities.Count > 0)
                {
                    SecurityRoleId = new Guid(ec.Entities[0].Attributes["roleid"].ToString());
                }
            }

            catch (System.Web.Services.Protocols.SoapException soapex)
            {
                throw new InvalidPluginExecutionException(soapex.Detail.InnerText);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return SecurityRoleId;
        }


        /// 

        /// Assign the Default CRM 2011 Role to the user
        /// 
        public void AssignSecurityRole(Guid userID, Guid securityRoleID, IOrganizationService service)
        {
            try
            {
                service.Associate(
                               "role",
                               securityRoleID,
                               new Relationship("systemuserroles_association"),
                               new EntityReferenceCollection() { new EntityReference("systemuser", userID) });
            }
            catch (System.Web.Services.Protocols.SoapException soapex)
            {
                throw new InvalidPluginExecutionException(soapex.Detail.InnerText);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        
    }
}

The above code first checks if the user has any role assigned. If not, it goes ahead and assigns the Default Security Role to the user.

After building the plugin, I had it registered in the following manner:

Notice that for both "Create" and "Update" events, I have registered just the one step displayed above

I am not exactly sure why, but I noticed that even if I register my plugin for the Create event, the plugin also automatically fires another update event. So, I have just registered my plugin for the update event and this works for both the create and the update.

Another issue I was facing was when I tried to register this plugin in a synchronous manner. I would keep getting the error message "Error: usersettings With Id = 92bd1403-6ace-e211-a1c4-0800279f0ce3 Does Not Exist" in the trace logs.

In order to rid myself of the error and in order to ensure that the plugin only fires once per transaction, I have registered it ONLY for the "Update" message and in "Asynchronous" execution mode.

Cheers!

CRM 2011 - Instantiating the CRM Service and Impersonating it

 The below code can be used to create the CRM service in CRM 2011 and also to impersonate the CRM service

public static IOrganizationService Service() 
{
    ClientCredentials Credentials = new ClientCredentials(); 
    Credentials.Windows.ClientCredential.UserName ="Impersonating User ID Name"; 
    Credentials.Windows.ClientCredential.Password ="Impersonating User ID password"; 

    //Update the below URL as per your environment
    Uri OrganizationUri = new Uri("http://serverName/OrganizationName/XRMServices/2011/Organization.svc"); 
    Uri HomeRealmUri = null; 

    //Get the CRM Service from the CRM Service Proxy
    using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, Credentials, null)) 
    {
        IOrganizationService service = (IOrganizationService)serviceProxy; 
        return service; 
    }
}

Learning ODATA Queries

Hi,
The below link is one of the best blogs I have come across for learning ODATA queries

http://crmscape.blogspot.com/2011/03/crm-2011-odata-json-and-crm-forms.html

Please check it out.