locked
My second question in this forum about RetrieveMultiple method RRS feed

  • Question

  • Hi,

    I am able to create some accounts in CRM system which is good, but I am confused when try to retrieve them. According to some sample code in SDK, below are steps I did but with no luck.

    1. Create a new QueryExpression object and assignthe entity name then assign the columns.

    Question in step1: seems the columns are not exactly same like the object field, in Account object, the account name is called Name with capital N, but here is lower n. If I use Name then I will receive error "'Account' entity doesn't contain attribute with Name = 'Name'." So what's the mapping between object field name and attribute name?

    2. Try to create a Criteria something like 

    qe.Criteria = new FilterExpression
                {
                    Conditions = 
                    {
                        new ConditionExpression 
                        {
                            AttributeName = "name",
                            Operator = ConditionOperator.Contains,
                            Values = { "test" }
                        }
                    }
                };

     But the problem is when I run the method, I got error "Generic SQL error." What's wrong with this criteria?

    3. So finally I decide not to assign a criteria, then i am able to retrieve the EntityCollection which seems ok. Then i realized that if one entity doesn't have a Fax # then even i can't call Entity e["fax"] because there is no such entry in its results. Not like other system, at least it will return you empty string or DB null value.  Is it normal ? or I did something wrong?

    Thanks for any feedback, I need help!

    Lan

    Below is my code snip:

    QueryExpression qe = new QueryExpression();
                qe.EntityName = Account.EntityLogicalName;
                qe.ColumnSet = new ColumnSet();
                qe.ColumnSet.Columns.Add("name");
                //qe.ColumnSet.Columns.Add("fax");
                //qe.ColumnSet.Columns.Add("telephone1");
                //qe.ColumnSet.Columns.Add("telephone2");

                qe.Criteria = new FilterExpression
                {
                    Conditions = 
                    {
                        new ConditionExpression 
                        {
                            AttributeName = "name",
                            Operator = ConditionOperator.Contains,
                            Values = { "test" }
                        }
                    }
                };


                EntityCollection ec = OrgService.RetrieveMultiple(qe);
                foreach (Entity ee in ec.Entities)
                {
                    string name = (string)ee["name"];
                    //string fax = (string)ee["fax"];
                    //string telephone1 = (string)ee["telephone1"];
                    //string telephone2 = (string)ee["telephone2"];
                }

    Wednesday, June 3, 2015 11:34 PM

All replies

  • Hi,
    this is normally the code I use:

    // you can put the entity when you create the queryexpression
    // you are using Account.LogicalName, this requires an early bound generated file
    // if you don't want to use early bound just put "account" as parameter
    QueryExpression qe = new QueryExpression(Account.LogicalName);
    
    // you can define the columns directly as parameters
    qe.ColumnSet = new ColumnSet("name", "fax", "telephone1", "telephone2");
    
    // you can add the conditions with this syntax
    
    qe.Criteria.AddCondition("name", ConditionOperator.Contains, "test");
    
    EntityCollection ec = OrgService.RetrieveMultiple(qe);
    foreach (Entity ee in ec.Entities)
    {
    //use GetAttributeValue http://www.crmanswers.net/2015/04/getattributevalue-demystified.html
       string name = ee.GetAttributeValue<string>("name");
       string fax = ee.GetAttributeValue<string>("fax");
       string telephone1 = ee.GetAttributeValue<string>("telephone1");
       string telephone2 = ee.GetAttributeValue<string>("telephone2");
    }


    My blog: www.crmanswers.net - CRM Theme Generator

    Thursday, June 4, 2015 2:14 AM
  • Hi Guido,

    Thanks for your sample code, i tried your code but it still gives me exception which I mentioned in my original post saying Generic SQL error. 

    BTW, I am connecting to online Office 365 CRM site. Will that make things different?

    Thanks,

    Lan


    ZL @ Hypertec

    Thursday, June 4, 2015 4:13 AM
  • Change the Condition Operator Use Equal instead of Contains

    qe.Criteria.AddCondition("name", ConditionOperator.Equal, "test");

    Muhammad Sohail

    Thursday, June 4, 2015 7:22 AM
  • Hi Muhammed,

    Thanks for your suggestion, it works but it is not what I expect at beginning. Can't we use ConditionOperator.Contains operation in the criteria object?

    Lan


    ZL @ Hypertec

    Thursday, June 4, 2015 1:51 PM
  • Hi All,

    Finally I give up, I am able to use LINQ to do such kind of data query and with LINQ I can do inner join query which is very common. But just wondering since CRM doesn't provide direct SQL DB access then we can't benefit from lot if SQL server based feature like Store Procedure or some advanced SQL command.

    Any idea or thoughts?

    Lan


    ZL @ Hypertec

    Thursday, June 4, 2015 6:11 PM
  • Hi,

    Even though i am not CRM expert but in my opinion it is mainly cause of security and performance.

    Regards


    Ayaz

    Friday, June 5, 2015 2:19 PM
  • Can you try the following

    Criteria.AddCondition("name", ConditionOperator.Like, "%test%");

    Btw.

    , "%test%"); //Same as contains

    , "test%"); //Same as Begins with

    , "%test"); //Same as ends with.



    Halldór Jóhannsson



    Friday, June 5, 2015 3:05 PM
  • That's how CRM generally behaves.

    If you are using QueryExpression, you have to use names of attribute in lowercase. However, if you are using REST calls, then you have to stick with Schema Name, which can be upper, lower, normal or any case. You would need to open up customizations and check case of the attribute. I know, this is very inconsistent.

    Regarding no entity when "fax" attribute does not have value. CRM does not return attributes for an entity record if that attribute does not have value. In your case, check ee.Contains("fax") before retrieving its value. Also note, if fax was the only attribute that you were retrieving and none of the entity records contained value for fax, no records will be returned.

    Saturday, June 6, 2015 2:06 PM
  • Hu Ahmad,

    Thank you for your detail explanation. Seems I have to get used to CRM's behaves. I have to move on my next target which web resources.

    Thank you!

    Lan


    ZL @ Hypertec

    Monday, June 8, 2015 10:36 PM