locked
Can't Get OptionSetValue using Linq C# RRS feed

  • Question

  • Ok. What I'm trying to do is simple. I want to display the contacts in a gridview. I've written code that gets me close... but all picklist fields appear in the gridview as "Microsoft.Xrm.Sdk.OptionSetValue". I need it to show the actual string. I've read that using "FormattedValues" will get me what I need, but it errors out. I get "The given key was not present in the dictionary."

    The following code works:

                var SoarContacts =
                    (
                        from c in context.ContactSet
                        where c.FullName.Contains(NameTextBox.Text)
                        where c.StatusCode.Value == 1
                        orderby c.FullName descending
                        select new
                        {
                            ContactID = c.ContactId,
                            FullName = c.FullName,
                            Gender = c.GenderCode,
                            DOB = c.BirthDate,
                            Status = c.StatusCode
                        }
                    );
                DataTable dt = new DataTable();
                dt.Columns.Add("ContactID", typeof(string));
                dt.Columns.Add("FullName", typeof(string));
                dt.Columns.Add("Gender", typeof(string));
                dt.Columns.Add("DOB", typeof(DateTime));
                dt.Columns.Add("Status", typeof(string));
    
                foreach (var c in SoarContacts)
                {
                    dt.Rows.Add(c.ContactID, c.FullName, c.Gender, c.DOB, c.Status);
                }
    
                RecordCount.Text = dt.Rows.Count.ToString() + " Contacts";
                ResultsGridView.DataSource = dt;
                ResultsGridView.DataBind();
    But I need to see the actual Gender string and status string. Changing "Gender = c.GenderCode" to "Gender = c.FormattedValues["gendercode"] does not work. How do I manipulate the code that I have to include the gender and status?

    Wednesday, August 9, 2017 1:09 PM

Answers

  • The following code should help you get what you need, however it will need to be fixed to work better and faster, this is just to give you an idea on how it works.

    I added 3 new functions, these need to use the CRM service so you would either need to make it global, static or send it into the classes. It is not pretty but it should work for you.


                foreach (var c in SoarContacts)
                {
                    dt.Rows.Add(c.GetAttributeValue<Guid>("contactid"),
                        c.GetAttributeValue<string>("fullname"),
                        GetOptionSetAttributeValue(c, "gendercode", _service),
                        DateTime.Now,
                        GetOptionSetAttributeValue(c, "statuscode", _service));
                }
            }
            private string GetOptionSetAttributeValue(Entity entity, string attributeName, OrganizationServiceProxy service)
            {
                if (entity.Attributes.ContainsKey(attributeName))
                {
                    return GetOptionSetAttributeText(entity.LogicalName, attributeName, entity.GetAttributeValue <OptionSetValue>(attributeName).Value, service);
                }
                else
                {
                    return string.Empty;
                }
            }
            private string GetOptionSetAttributeText(string entityName, string attributeName, int value, OrganizationServiceProxy service)
            {
                AttributeMetadata attributeMD = GetEntityMetadata(entityName, attributeName, service);
                if (attributeMD.GetType() == typeof(StatusAttributeMetadata))
                {
                    foreach (StatusOptionMetadata obj in ((StatusAttributeMetadata)attributeMD).OptionSet.Options)
                    {
                        if (obj.Value == value)
                        {
                            return obj.Label.UserLocalizedLabel.Label;
                        }
                    }
                }
                else if (attributeMD.GetType() == typeof(PicklistAttributeMetadata))
                {
                    foreach (OptionMetadata obj in ((PicklistAttributeMetadata)attributeMD).OptionSet.Options)
                    {
                        if (obj.Value == value)
                        {
                            return obj.Label.UserLocalizedLabel.Label;
                        }
                    }
                }
                return string.Empty;
            }
            /// <summary>
            /// Get metadata for entity
            /// </summary>
            /// <param name="entityName">Name of the entity to fetch metadata for.</param>
            /// <param name="entityFilter">Type of metadata to get.</param>
            /// <returns>EntityMetadata for sent in entity otherwise null if not found.</returns>
            public static AttributeMetadata GetEntityMetadata(string entityName, string attributeName, OrganizationServiceProxy service)
            {
                RetrieveAttributeRequest request = new RetrieveAttributeRequest() { EntityLogicalName = entityName, LogicalName = attributeName, RetrieveAsIfPublished = true };
                RetrieveAttributeResponse response = (RetrieveAttributeResponse)service.Execute(request);
                if (response != null && response.Results.Count > 0)
                {
                    return response.AttributeMetadata;
                }
                else
                {
                    return null;
                }
            }
        }
    }


    Halldór Jóhannsson

    • Marked as answer by BryanDunlap Thursday, August 10, 2017 12:42 PM
    Wednesday, August 9, 2017 10:40 PM

All replies

  • Have you tried debugging through the code to see if the value is there somewhere?

    Might be as simple as changing the line to something like below, given the result you get into the grid you need to convert the object you insert there into an optionset object and get the data from there, something like ((OptionSetValue)c.GenderCode).Value, this will only likely give you the integer value, to get the rest there is a call to the metadata service, these calls are expensive timewise though and I would advice against using them, they are mostly useful when you get the data from another system than CRM and need to do a mapping, following code shows how you could manage that..

    however you should be able to query the CRM system with the text of the optionset included, as you mention yourself using the formattedvalues, for that you would need the entity object like so String text = entity.FormattedValues["yourattributename"].ToString();, we would probably need some more of the code to advice further (f.e. how do you create/fill the "context" object).

            /// <summary>
            /// Get metadata for entity
            /// </summary>
            /// <param name="entityName">Name of the entity to fetch metadata for.</param>
            /// <param name="entityFilter">Type of metadata to get.</param>
            /// <returns>EntityMetadata for sent in entity otherwise null if not found.</returns>
            public static AttributeMetadata GetEntityMetadata(string entityName, string attributeName)
            {

                RetrieveAttributeRequest request = new RetrieveAttributeRequest() { EntityLogicalName = entityName, LogicalName = attributeName, RetrieveAsIfPublished = true};

                RetrieveAttributeResponse response = (RetrieveAttributeResponse)CrmService.Client.OrganizationServiceProxy.Execute(request);

                if (response != null && response.Results.Count > 0)
                {
                    return response.AttributeMetadata;
                }
                else
                {
                    return null;
                }
            }


    Halldór Jóhannsson

    Wednesday, August 9, 2017 1:31 PM
  • Try to use LateBinding query for that purpose:

                var SoarContacts =
                    (
                        from c in context.CreateQuery("contact")
                        where c.GetAttribute<string>("fullname").Contains(NameTextBox.Text)
                        where c.GetAttribute<OptionSetValue>("statuscode").Value == 1
                        orderby c.GetAttribute<string>("fullname") descending
                        select new
                        {
                            ContactID = c.Id,
                            FullName = c.GetAttribute<string>("fullname"),
                            Gender = c.FormattedValues("gendercode"),
                            DOB = c.GetAttributeValue<DateTime>("birthdate"),
                            Status = c.FormattedValues("statuscode")
                        }
                    );


    Dynamics CRM/Business Solutions MVP
    Read My blog


    Wednesday, August 9, 2017 1:50 PM
    Moderator
  • Rewriting my code to use a latebinding query gives me the same error... The given key was not present in the dictionary. and it happens as soon as it enters into the foreach loop.
    Wednesday, August 9, 2017 3:01 PM
  • Here is the full code of the method I created:

            Uri organizationUriIFD = new Uri("https://MyURL/XRMServices/2011/Organization.svc");
    
            ClientCredentials credentials = new ClientCredentials();
            credentials.UserName.UserName = "MyUserName";
            credentials.UserName.Password = "MyPassword";
    
            IServiceConfiguration<IOrganizationService> config = ServiceConfigurationFactory.CreateConfiguration<IOrganizationService>(organizationUriIFD);
            using (Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy _serviceProxy = new OrganizationServiceProxy(config, credentials))
            {
                _serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());
                IOrganizationService _service = (IOrganizationService)_serviceProxy;
    
                Census context = new Census(_service);
                var SoarContacts =
                    (
                        from c in context.ContactSet
                        where c.FullName.Contains(NameTextBox.Text)
                        where c.StatusCode.Value == 1
                        orderby c.FullName descending
                        select new
                        {
                            ContactID = c.ContactId,
                            FullName = c.FullName,
                            Gender = c.GenderCode,
                            //Gender = c.FormattedValues["gendercode"],
                            DOB = c.BirthDate,
                            Status = c.StatusCode
                            //Status = c.FormattedValues["statuscode"]
                        }
                    );
                DataTable dt = new DataTable();
                dt.Columns.Add("ContactID", typeof(string));
                dt.Columns.Add("FullName", typeof(string));
                dt.Columns.Add("Gender", typeof(string));
                dt.Columns.Add("DOB", typeof(DateTime));
                dt.Columns.Add("Status", typeof(string));
    
                foreach (var c in SoarContacts)
                {
                    dt.Rows.Add(c.ContactID, c.FullName, c.Gender, c.DOB, c.Status);
                }
    
                RecordCount.Text = dt.Rows.Count.ToString() + " Contacts";
                ResultsGridView.DataSource = dt;
                ResultsGridView.DataBind();
            }

    Wednesday, August 9, 2017 3:07 PM
  • So... I figured out a few quirks that seem completely illogical. If I select more than one field, it fails with the "The given key was not present in the dictionary" error. But, if I downsize it to get only the status code, it works.  If I change it to gender code... it fails with the "Key" error. If I change it to look at one of our custom fields "Contact Type", it works. So... I tested a bit more, and it fails if the value is blank in the system (Status is never blank so it works. Many contacts have a blank gender, so it fails. Contact Type has a few blanks. If I get a contact that has a contact type, it works... no contact type then it fails. But no matter what, I can NEVER get more than one field at a time. as soon as I try to pull back FullName and Status... it fails with the "Key" error.

    Any ideas?

    Wednesday, August 9, 2017 7:09 PM
  • Hello,

    That's a possible reason. Try following:

                var SoarContacts =
                    (
                        from c in context.CreateQuery("contact")
                        where c.GetAttribute<string>("fullname").Contains(NameTextBox.Text)
                        where c.GetAttribute<OptionSetValue>("statuscode").Value == 1
                        orderby c.GetAttribute<string>("fullname") descending
                        select new
                        {
                            ContactID = c.Id,
                            FullName = c.GetAttribute<string>("fullname"),
                            Gender = c.Contains("gendercode") ? c.FormattedValues("gendercode") : null,
                            DOB = c.GetAttributeValue<DateTime>("birthdate"),
                            Status = c.FormattedValues("statuscode")
                        }
                    );


    Dynamics CRM/Business Solutions MVP
    Read My blog

    Wednesday, August 9, 2017 7:18 PM
    Moderator
  • Ahh so it seems the error lies in the field having a null value it seems..
    Have you tried returning the entity object out of the linq query instead of creating a new one that you return.
    I am not fluent in linq so it would be something like this...
    var SoarContacts = ( 
    c in context.ContactSet where c.FullName.Contains(NameTextBox.Text)  
    where c.StatusCode.Value == 1  
    orderby c.FullName descending  
    select c
    );

    Then in your foreach loop you can check if the object contains a value.
    foreach (var c in SoarContacts)
    {
     Guid contactId = new Guid();
     string fullname = string.Empty();
     string gender = string.Empty();
     if(c.Contains("contactid")) 
     {
        contactId = c.FormattedValues["contactid"]
     }

     if(c.Contains("fullname")) 
     {
        fullname = c.FormattedValues["fullname"]
     }

     if(c.Contains("gendercode")) 
     {
        gender = c.FormattedValues["gendercode"]
     }

     //ETC ETC

      dt.Rows.Add(contactId, fullname, gender, c.DOB, c.Status);  
    }

       

    You could also try (Probably not exact syntax)
    var SoarContacts =
     (
      from c in context.ContactSet
      where c.FullName.Contains(NameTextBox.Text)
      where c.StatusCode.Value == 1
      orderby c.FullName descending
      select new
      {
       ContactID = c.ContactId,
       FullName = c.FullName,
       Gender = c.Contains("gendercode") ? c.FormattedValues["gendercode"] : null;,
       DOB = c.BirthDate,
       Status = c.StatusCode
       //Status = c.FormattedValues["statuscode"]
      }
     );

    Halldór Jóhannsson


    Wednesday, August 9, 2017 7:27 PM
  • Sorry... Same error. Also... the code you provide shows FullName = c.GetAttribute. I've been changing that to c.GetAttributeValue like below. That's ok, right? Otherwise it doesn't compile.

                var SoarContacts =
                    (
                        from c in context.CreateQuery("contact")
                        where c.GetAttributeValue<string>("fullname").Contains(NameTextBox.Text)
                        where c.GetAttributeValue<OptionSetValue>("statuscode").Value == 1
                        orderby c.GetAttributeValue<string>("fullname") descending
                        select new
                        {
                            ContactID = c.Id,
                            FullName = c.GetAttributeValue<string>("fullname"),
                            Gender = c.Contains("gendercode") ? c.FormattedValues["gendercode"] : null,
                            DOB = c.GetAttributeValue<DateTime>("birthdate"),
                            Status = c.FormattedValues["statuscode"]
                        }
                    );


    Wednesday, August 9, 2017 7:44 PM
  • The following code should help you get what you need, however it will need to be fixed to work better and faster, this is just to give you an idea on how it works.

    I added 3 new functions, these need to use the CRM service so you would either need to make it global, static or send it into the classes. It is not pretty but it should work for you.


                foreach (var c in SoarContacts)
                {
                    dt.Rows.Add(c.GetAttributeValue<Guid>("contactid"),
                        c.GetAttributeValue<string>("fullname"),
                        GetOptionSetAttributeValue(c, "gendercode", _service),
                        DateTime.Now,
                        GetOptionSetAttributeValue(c, "statuscode", _service));
                }
            }
            private string GetOptionSetAttributeValue(Entity entity, string attributeName, OrganizationServiceProxy service)
            {
                if (entity.Attributes.ContainsKey(attributeName))
                {
                    return GetOptionSetAttributeText(entity.LogicalName, attributeName, entity.GetAttributeValue <OptionSetValue>(attributeName).Value, service);
                }
                else
                {
                    return string.Empty;
                }
            }
            private string GetOptionSetAttributeText(string entityName, string attributeName, int value, OrganizationServiceProxy service)
            {
                AttributeMetadata attributeMD = GetEntityMetadata(entityName, attributeName, service);
                if (attributeMD.GetType() == typeof(StatusAttributeMetadata))
                {
                    foreach (StatusOptionMetadata obj in ((StatusAttributeMetadata)attributeMD).OptionSet.Options)
                    {
                        if (obj.Value == value)
                        {
                            return obj.Label.UserLocalizedLabel.Label;
                        }
                    }
                }
                else if (attributeMD.GetType() == typeof(PicklistAttributeMetadata))
                {
                    foreach (OptionMetadata obj in ((PicklistAttributeMetadata)attributeMD).OptionSet.Options)
                    {
                        if (obj.Value == value)
                        {
                            return obj.Label.UserLocalizedLabel.Label;
                        }
                    }
                }
                return string.Empty;
            }
            /// <summary>
            /// Get metadata for entity
            /// </summary>
            /// <param name="entityName">Name of the entity to fetch metadata for.</param>
            /// <param name="entityFilter">Type of metadata to get.</param>
            /// <returns>EntityMetadata for sent in entity otherwise null if not found.</returns>
            public static AttributeMetadata GetEntityMetadata(string entityName, string attributeName, OrganizationServiceProxy service)
            {
                RetrieveAttributeRequest request = new RetrieveAttributeRequest() { EntityLogicalName = entityName, LogicalName = attributeName, RetrieveAsIfPublished = true };
                RetrieveAttributeResponse response = (RetrieveAttributeResponse)service.Execute(request);
                if (response != null && response.Results.Count > 0)
                {
                    return response.AttributeMetadata;
                }
                else
                {
                    return null;
                }
            }
        }
    }


    Halldór Jóhannsson

    • Marked as answer by BryanDunlap Thursday, August 10, 2017 12:42 PM
    Wednesday, August 9, 2017 10:40 PM
  • Halldor Bjorgvin Johannsson - I don't know how to thank you! This is great and it works perfectly! It's exactly what I needed. I've been a C# programmer for a long time, but I've never had to deal with doing anything like this with CRM. (I'm very new to CRM itself). Anyway, this is great. You really helped me through a tight spot.

    Thursday, August 10, 2017 12:46 PM
  • Good to know this worked, I would recommend calling the GetEntityMetadata function on load or in the function straight and store it as a variable so you only have to do it once for each optionset instead of how it is setup now where you do it for each and every entity that you get in your foreach loop.+

    I was in a hurry yesterday getting this to work so I did not do it properly, also not knowing more of the code makes it a little harder.

    This should work faster in most cases, you might have to fix some typos and more since I did this in a text editor.

      AttributeMetadata statusCodeMD = GetEntityMetadata("contact", "statuscode", _service);
      AttributeMetadata genderCodeMD = GetEntityMetadata("contact", "gendercode", _service);
     
      foreach (var c in SoarContacts)
                 {
                     dt.Rows.Add(c.GetAttributeValue<Guid>("contactid"),
                         c.GetAttributeValue<string>("fullname"),
                         GetOptionSetAttributeValue(c, "gendercode", genderCodeMD, _service),
                         DateTime.Now,
                         GetOptionSetAttributeValue(c, "statuscode", statusCodeMD, _service));
                 }

            }

            private string GetOptionSetAttributeValue(Entity entity, string attributeName, AttributeMetadata attributeMD, OrganizationServiceProxy service)
             {
                 if (entity.Attributes.ContainsKey(attributeName))
                 {
                     return GetOptionSetAttributeText(attributeMD, entity.GetAttributeValue <OptionSetValue>(attributeName).Value, service);
                 }
                 else
                 {
                     return string.Empty;
                 }
             }

            private string GetOptionSetAttributeText(AttributeMetadata attributeMD, int value, OrganizationServiceProxy service)
             {
                if (attributeMD.GetType() == typeof(StatusAttributeMetadata))
                 {
                     foreach (StatusOptionMetadata obj in ((StatusAttributeMetadata)attributeMD).OptionSet.Options)
                     {
                         if (obj.Value == value)
                         {
                             return obj.Label.UserLocalizedLabel.Label;
                         }
                     }
                 }
                 else if (attributeMD.GetType() == typeof(PicklistAttributeMetadata))
                 {
                     foreach (OptionMetadata obj in ((PicklistAttributeMetadata)attributeMD).OptionSet.Options)
                     {
                         if (obj.Value == value)
                         {
                             return obj.Label.UserLocalizedLabel.Label;
                         }
                     }
                 }

                return string.Empty;
             }

            /// <summary>
             /// Get metadata for entity
             /// </summary>
             /// <param name="entityName">Name of the entity to fetch metadata for.</param>
             /// <param name="entityFilter">Type of metadata to get.</param>
             /// <returns>EntityMetadata for sent in entity otherwise null if not found.</returns>
             public static AttributeMetadata GetEntityMetadata(string entityName, string attributeName, OrganizationServiceProxy service)
             {
                 RetrieveAttributeRequest request = new RetrieveAttributeRequest() { EntityLogicalName = entityName, LogicalName = attributeName, RetrieveAsIfPublished = true };
                 RetrieveAttributeResponse response = (RetrieveAttributeResponse)service.Execute(request);
                 if (response != null && response.Results.Count > 0)
                 {
                     return response.AttributeMetadata;
                 }
                 else
                 {
                     return null;
                 }
             }
         }
     }

    Best regards,
    Halldor


    Halldór Jóhannsson

    Thursday, August 10, 2017 1:35 PM