locked
N:N retrieve: "An unexpected error occurred" RRS feed

  • Question

  • I am using CRM 2011, on premises.

    We have an N:N (many to many) relationship between ContractDetail and a custom entity, called Dependent. I set up a Query to retrieve all the Dependents associated with a given ContractDetail, whose ID (contractdetailid=enrollmentRecord.Id) I know. I used the code at http://msdn.microsoft.com/en-us/library/microsoft.xrm.sdk.messages.retrievemultiplerequest.aspx

    When the RetrieveMultiple is executed it errors with "An unexpected error has occurred" (very helpful, right?). The inner exception is null. I am reviewed for spelling errors, etc. but I haven't pinpointed the problem.

    Here's my code - can someone point out the error of my ways?

    QueryExpression qeDep = new QueryExpression()
      {
         EntityName = new_dependent.EntityLogicalName,
         ColumnSet = new ColumnSet("new_dependentid", "new_lastname", "new_firstname", 
    "new_relationship"), LinkEntities = { new LinkEntity { LinkFromEntityName = new_dependent.EntityLogicalName, LinkFromAttributeName = "new_dependentid", LinkToEntityName = ContractDetail.EntityLogicalName, LinkCriteria = new FilterExpression { FilterOperator = LogicalOperator.And, Conditions = { new ConditionExpression { AttributeName = "contractdetailid", Operator = ConditionOperator.Equal, Values = {enrollmentRecord.Id} } } } } } }; try { EntityCollection dependents = _service.RetrieveMultiple(qeDep); } catch (Exception ex) { Console.WriteLine("dependent retrieve multiple failed: " + ex); }

    Friday, May 31, 2013 6:27 PM

Answers

  • Hi,

    Please try the below mentioned  code

    string linkFromEntityName = "entity1";
    QueryExpression query = new QueryExpression(linkFromEntityName);
    LinkEntity linkEntity1 = new LinkEntity(linkFromEntityName, relationShipName, entity1id, entity1id, JoinOperator.Inner);
    LinkEntity linkEntity2 = new LinkEntity(relationShipName, "entity2", entity2id, entity2id, JoinOperator.Inner);
    linkEntity1.LinkEntities.Add(linkEntity2);
    query.LinkEntities.Add(linkEntity1);
    linkEntity2.LinkCriteria = new FilterExpression();
    linkEntity2.LinkCriteria.AddCondition(new ConditionExpression( entity2id, ConditionOperator.Equal, new Guid("BF0191B6-CEBC-E011-B354-001C23FA0E1C")));
    EntityCollection coll = service.RetrieveMultiple(query);


    Thanks and Regards. Make sure to "Vote as Helpful" and "Mark As Answer",if you get answer of your question.

    My Blog

    • Marked as answer by MeProgrammer Friday, May 31, 2013 7:17 PM
    Friday, May 31, 2013 6:39 PM
  • You should define your ColumnSet on the QueryExpression, not the LinkEntity.

    query.ColumnSet = new ColumnSet("new_dependentid","new_lastname", "new_firstname","new_relationship");


    Steve Sämmang, Vienna, Austria
    Blog: xrm.io Website: simplic.at

    • Marked as answer by MeProgrammer Friday, May 31, 2013 8:19 PM
    Friday, May 31, 2013 8:14 PM

All replies

  • Hi,

    Please try the below mentioned  code

    string linkFromEntityName = "entity1";
    QueryExpression query = new QueryExpression(linkFromEntityName);
    LinkEntity linkEntity1 = new LinkEntity(linkFromEntityName, relationShipName, entity1id, entity1id, JoinOperator.Inner);
    LinkEntity linkEntity2 = new LinkEntity(relationShipName, "entity2", entity2id, entity2id, JoinOperator.Inner);
    linkEntity1.LinkEntities.Add(linkEntity2);
    query.LinkEntities.Add(linkEntity1);
    linkEntity2.LinkCriteria = new FilterExpression();
    linkEntity2.LinkCriteria.AddCondition(new ConditionExpression( entity2id, ConditionOperator.Equal, new Guid("BF0191B6-CEBC-E011-B354-001C23FA0E1C")));
    EntityCollection coll = service.RetrieveMultiple(query);


    Thanks and Regards. Make sure to "Vote as Helpful" and "Mark As Answer",if you get answer of your question.

    My Blog

    • Marked as answer by MeProgrammer Friday, May 31, 2013 7:17 PM
    Friday, May 31, 2013 6:39 PM
  • Thanks you for responding. What's "relationShipName"?
    Friday, May 31, 2013 6:56 PM
  • Hi,

    the relationship name is the name of the N:N entity, something like "new_contract_new_dependent". You define it when you create the N:N relationship in your solution.

    You can also try to use the following code. If you want to use Linq. I find it a little bit easier to read an maintain.

    try {
            using(OrganizationServiceContext osc = new OrganizationServiceContext(_service)) {
              var dependents = from   c in osc.CreateQuery("new_contract_new_dependent") //Name of NN Scheme
                               join   d in osc.CreateQuery("new_dependent")
                               on     c.GetAttributeValue<Guid>("new_dependentid") equals d.GetAttributeValue<Guid>("new_dependentid")
                               where  c.GetAttributeValue<Guid>("contractdetailid") == enrollmentRecord.Id
                               select new {
                                 DepententID = d.GetAttributeValue<Guid>("new_dependentid"),
                                 LastName = d.GetAttributeValue<string>("new_lastname"),
                                 FirstName = d.GetAttributeValue<string>("new_firstname"),
                                 RelationShip = d.GetAttributeValue<object>("new_relationship") // 
                               };
              
              foreach(var dependent in dependents) {
                Console.WriteLine(dependent.DepententID.ToString());
              }
            }
          }
          catch(Exception ex) {
             Console.WriteLine("dependent retrieve multiple failed: " + ex);
          }

    But you have to check 2 things: 

    1. "new_contract_new_dependent" is the correct scheme name (relationship name)

    2.  I don't know the type of new_relationship

    Regards,

    Steve


    Steve Sämmang
    , Vienna, Austria
    Blog: xrm.io Website: simplic.at

    Friday, May 31, 2013 7:05 PM
  • The name of the n:n relationship.

    Goto customization >> customse the system >> entities >> find ContractDetail entity expand it >> click on n:n relationship >> find the relation with custom entity that you currently working with then double click on it. You will see something like below.


    Thanks and Regards. Make sure to "Vote as Helpful" and "Mark As Answer",if you get answer of your question.

    My Blog

    Friday, May 31, 2013 7:06 PM
  • Abhi, thank you - that did the trick. But can you explain to me what I did wrong in my original code?

    And how can I retrieve more fields than just the id?

    Edit:

    Ahh. Now I've gone through it and find that that code returned the intersect entity - a step half-way to what I need. What's the best way to actually retrieve the new_dependent records?
    Friday, May 31, 2013 7:18 PM
  • Thank you for replying, Steve, and thanks for the definition. For the moment I am sticking with Query Expression, although I used LINQ elsewhere in my program.
    Friday, May 31, 2013 7:19 PM
  • Please post the exact code what you are using now.

    Thanks and Regards. Make sure to "Vote as Helpful" and "Mark As Answer",if you get answer of your question.

    My Blog

    Friday, May 31, 2013 7:21 PM
  • String linkFromEntityName = "new_dependent";
    QueryExpression query = new QueryExpression(linkFromEntityName);
    LinkEntity linkEntity1 = new LinkEntity(linkFromEntityName, "new_contractdetail_new_dependent", "new_dependentid", "new_dependentid", JoinOperator.Inner);
    LinkEntity linkEntity2 = new LinkEntity("new_contractdetail_new_dependent", ContractDetail.EntityLogicalName, "contractdetailid", "contractdetailid", JoinOperator.Inner);
    linkEntity1.LinkEntities.Add(linkEntity2);
    query.LinkEntities.Add(linkEntity1);
    linkEntity2.LinkCriteria = new FilterExpression();
    linkEntity2.LinkCriteria.AddCondition(new ConditionExpression("contractdetailid", ConditionOperator.Equal, new Guid(enrollmentRecord.Id.ToString())));

    Friday, May 31, 2013 7:32 PM
  • If I am not wrong then the only difference I make out is the LinkFromEntityName = "new_dependent" and earlier you are using the new_dependent.EntityLogicalName.

    Please try this out to confirm.


    Thanks and Regards. Make sure to "Vote as Helpful" and "Mark As Answer",if you get answer of your question.

    My Blog

    Friday, May 31, 2013 7:42 PM
  • Yes, I changed the first line to
    StringlinkFromEntityName = new_dependent.EntityLogicalName;

    and 2 dependent records were returned. So, much progress! Thank you. But all I see is the ids for each.

    If I want to return more than just the Id, can I code:
    linkEntity1.Columns.AddColumns(...) ? Or am I adding the columns to the wrong level?

    Friday, May 31, 2013 7:53 PM
  • I did

    linkEntity1.Columns.AddColumns("new_lastname");

    and the error is:

    'ce_contractdetail_ce_dependent' entity doesn't contain attribute with Name = 'new_lastname'.




    Friday, May 31, 2013 8:02 PM
  • You should define your ColumnSet on the QueryExpression, not the LinkEntity.

    query.ColumnSet = new ColumnSet("new_dependentid","new_lastname", "new_firstname","new_relationship");


    Steve Sämmang, Vienna, Austria
    Blog: xrm.io Website: simplic.at

    • Marked as answer by MeProgrammer Friday, May 31, 2013 8:19 PM
    Friday, May 31, 2013 8:14 PM
  • please use

    query.ColumnSet.AllColumns = true;

    or

    query.ColumnSet.AddColumns("new_lastname");


    Thanks and Regards. Make sure to "Vote as Helpful" and "Mark As Answer",if you get answer of your question.

    My Blog


    • Edited by Crm.Abhi Friday, May 31, 2013 8:21 PM
    Friday, May 31, 2013 8:18 PM
  • Thanks, Steve!  That worked perfectly. I appreciate the time and assistance you offered.
    Friday, May 31, 2013 8:20 PM
  • No problem. You're welcome.

    Steve Sämmang, Vienna, Austria
    Blog: xrm.io Website: simplic.at

    Friday, May 31, 2013 8:26 PM