none
CRM 2011 Query with LINQ NULL Values

    Question

  • I am querying the CRM 2011 Web Service using LINQs and I need to be able to query even if the field is null or empty. RIght now I get the error "The given key was not present in the dictionary."

     

    I am using:

        RadGrid1.DataSource = (from r in orgServiceContext.CreateQuery("opportunity")
                    where ((EntityReference)r["opportunityid"]).Id.Equals(new Guid("767dfb27-a5be-e011-8b2e-00505691002b"))
                    select new
                    {
                      CustomerId = ((EntityReference)r["customerid"]).Name,
                      Priority = ((OptionSetValue)r["opportunityratingcode"]).Value,
                      Evaluation = ((OptionSetValue)r["new_colderevaluation"]).Value,
                      ContactName = ((EntityReference)r["new_contact"]).Name,
                      Source = r["new_source"],
                      CreatedOn = r["createdon"]
                    });
        RadGrid1.DataBind();
    


    Any ideas or help would be awesome.

     

    Thanks!

    Wednesday, August 17, 2011 3:24 PM

Answers

  • Oops, I made another mistake in previous response.

    The following code should do the job properly.

    ContactName = !r.Contains("new_contact") ? null : ((EntityReference)r["new_contact"]).Name,
    
    

    or

    ContactName = !r.Contains("new_contact") ? string.Empty : ((EntityReference)r["new_contact"]).Name, 
    

    Daniel Cai | http://danielcai.blogspot.com
    • Marked as answer by CrazyeD1583 Thursday, August 18, 2011 8:03 PM
    Thursday, August 18, 2011 7:48 PM

All replies

  • The general practice is to first evaluate whether CRM has returned the field before accessing its value. An example would be:

    ContactName = r["new_contact"]) == null ? null : ((EntityReference)r["new_contact"]).Name,

    or

    ContactName = r["new_contact"]) == null ? string.Empty : ((EntityReference)r["new_contact"]).Name,

     

    Hope this helps.


    Daniel Cai | http://danielcai.blogspot.com



    Wednesday, August 17, 2011 3:52 PM
  • Thanks, I now seem to get this error "CS0828: Cannot assign <null> to anonymous type property"

     

    Any idea?

     

    Thanks!

    Wednesday, August 17, 2011 4:16 PM
  • I just had a similiar issue when making a collection of a Custom Advisor class.  Here is the code.
    I used to take care the issue.  I have not finalized the code with comments yet, so it is not
    cleaned up yet.  The function that solved my issue is entity.Contains(nameOfAttribute).

        public void GetAllAdvisor()
        {
          this.Clear();
    
          QueryExpression qe = new QueryExpression("new_advisor");
          string[] cols = {"new_firstname",
                   "new_lastname",
                   "new_middlename",
                   "emailaddress",
                   "new_advisorid",
                   "new_advisorfullname"};
    
          qe.ColumnSet.AddColumns(cols); // new ColumnSet(true); returns all fields, but is EXTREMELY SLOW      
          qe.Distinct = true;
    
          foreach (Entity advisor in this._CrmContext.RetrieveMultiple(qe).Entities)
          {
            Advisor a = new Advisor(this._CrmContext);
    
              a.FirstName = (string)this.GetCrmAttributeValue(advisor,"new_firstname");
              a.LastName = (string)this.GetCrmAttributeValue(advisor,"new_lastname");
              a.MiddleName = (string)this.GetCrmAttributeValue(advisor,"new_middlename");
              a.EmailAddress = (string)this.GetCrmAttributeValue(advisor, "emailaddress");
              a.FullName = (string)this.GetCrmAttributeValue(advisor,"new_advisorfullname");
              a.AdvisorIdentity = (Guid)this.GetCrmAttributeValue(advisor,"new_advisorid");
    
            this._Advisors.Add(a);
          }
        }
    
        // Checks made to ensure that entity contains the attributes requested
        private Object GetCrmAttributeValue(Entity entity, string nameOfAttribute)
        {
          Object obj = new Object();
    
          if (entity.Contains(nameOfAttribute))
          {
            obj = entity.Attributes[nameOfAttribute];
          }
          else
            obj = null;
    
          return obj;
        }

     

    Hope this helps!

    Paul Baratelli


    Paul Baratelli .NET Developer
    Wednesday, August 17, 2011 4:43 PM
  • Oops, I don't have an environment to verify the syntax. You need to ensure that values from both sides of ternary operator are in the same .NET type.

    You may try to cast them, such as (string) null. But again, I don't have an environment to verify the syntax, and you need to perform the cast case by case.


    Daniel Cai | http://danielcai.blogspot.com
    Wednesday, August 17, 2011 4:52 PM
  • Thanks.

     

    How would I use this with LINQ instead of QueryExpression?

     

    Thanks!

    Wednesday, August 17, 2011 6:18 PM
  • If I use

    ContactName = r["new_contact"]) == null ? null : ((EntityReference)r["new_contact"]).Name,
    or
    
    ContactName = r["new_contact"]) == null ? string.Empty : ((EntityReference)r["new_contact"]).Name,
    

    It doesn't seem to pick it up, it still gives me the "The given key was not present in the dictionary." error.

    Thursday, August 18, 2011 7:21 PM
  • Oops, I made another mistake in previous response.

    The following code should do the job properly.

    ContactName = !r.Contains("new_contact") ? null : ((EntityReference)r["new_contact"]).Name,
    
    

    or

    ContactName = !r.Contains("new_contact") ? string.Empty : ((EntityReference)r["new_contact"]).Name, 
    

    Daniel Cai | http://danielcai.blogspot.com
    • Marked as answer by CrazyeD1583 Thursday, August 18, 2011 8:03 PM
    Thursday, August 18, 2011 7:48 PM
  • Thanks! That worked Perfectly!!!
    Thursday, August 18, 2011 8:03 PM