locked
How Build a Query with a N to N Entity? RRS feed

  • Question

  • Hi EveryBody:

     

     

    The N to N entity, i have problem s with it in CRM 4.0 I don't Know How Work en It.

     

     

    Anybody have a Idea, can help me with this, I review the SDK but is not totally cleary with this.

     

     

    Thank You.

     

     

    José

    Tuesday, July 8, 2008 3:35 PM

Answers

  • You can't use RetrieveMultiple on the intersection entities like TeamMembership, or any other for a N-N relationship. You have to use Fetch instead, or, in the case of TeamMembership, you can use either the RetrieveMembersTeam or RetrieveTeamsSystemUser message
    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    Friday, June 5, 2009 8:33 AM
    Moderator
  • You need to use QueryExpression to retireve N to N entity:

     

    Assuming you are using the SDK Helper:

     

     

    QueryExpressionHelper query = new QueryExpressionHelper(<ParentEntity>);

    query.Columns.AddColumn(<ParentEntity> + "id");

     

    // Setup the inner join between the parent entity and the N to N entity (reletionship entity name found in the customization window)

    LinkEntityHelper le1 = new LinkEntityHelper(<ParentEntity>, <Parententity>+ "id", <N to N Entity>, <ParentEntity> + "id", JoinOperator.Inner);

     

    // Create Filter and Condition expression

    FilterExpressionHelper filter = new FilterExpressionHelper();

    filter.Conditions.AddCondition(<N to N child Entity> + "id", ConditionOperator.Equal, PluginHelpers.GetEntityId(context));

     

    // link the filter object to the link entity

    le1.Criteria.Filters.AddFilter(filter);

     

    // link the link entity to the query.

    query.LinkEntities.AddLink(le1);

    RetrieveMultipleRequest multiRequest = new RetrieveMultipleRequest();

    multiRequest.Query = query.Query;

    multiRequest.ReturnDynamicEntities = true;

    RetrieveMultipleResponse multiResponse = (RetrieveMultipleResponse)crmService.Execute(multiRequest);

    BusinessEntityCollection bec = multiResponse.BusinessEntityCollection;

     

    For this example, I have retrieve all the ParentID in the Parent Entity where the Child Entity is equals to the context id in a many to many relationship.  Let me know if this is clear for you.

     

    Jude

     

    Tuesday, July 8, 2008 6:13 PM
  • Sorry Jose, that was the code that I used in my plug-ins.  The PluginHelper and the context are only for plug-ins. So, don't worry about it.

    Wednesday, July 9, 2008 12:04 AM
  • No problem José .  We are here to answer your questions. 

     

    As soon as you are querying the FILTERED views (e.g. Filteredaccount) against the CRM database, you are guarnteed that your users are getting the records based on their security context and Microsoft guarantees the upgrade. 

     

    Although SDK is recommanded but there are times that you would query against the database.  For example, MS CRM doesn't support rollups of contacts to the Master Accounts (assuming you have more than one sub-accounts).  If you do this through SDK, it may end up with quite a number of Web Service call while you can achieve the same goal with one simple SQL recurisve query.

     

     

     

    Wednesday, July 9, 2008 1:04 PM

All replies

  • the N:N relationship is stored in its own filtered view.  You can find the name of this relationship table by looking at the relationship record.  For example, when you go to settings/customization/customize entities and pull up accounts, and click on the N:N tab, you will see the many to many relationship between accounts and marketinglists. When you double click on that relationship, note that the relationship entity name is listmember..

     

    That is the N:N relationship entity.  If you query this entity you will see it has   the ID of the account, the ID of the Marketing list, and the ID of the relationship.

     

    So say you wanted to report on the marketing lists associated with the account, you would join filteredaccount to filteredlistmember and filteredlistmember to filteredlist.

     

    It's the same for any N:N relationship.

    Tuesday, July 8, 2008 6:04 PM
    Moderator
  • You need to use QueryExpression to retireve N to N entity:

     

    Assuming you are using the SDK Helper:

     

     

    QueryExpressionHelper query = new QueryExpressionHelper(<ParentEntity>);

    query.Columns.AddColumn(<ParentEntity> + "id");

     

    // Setup the inner join between the parent entity and the N to N entity (reletionship entity name found in the customization window)

    LinkEntityHelper le1 = new LinkEntityHelper(<ParentEntity>, <Parententity>+ "id", <N to N Entity>, <ParentEntity> + "id", JoinOperator.Inner);

     

    // Create Filter and Condition expression

    FilterExpressionHelper filter = new FilterExpressionHelper();

    filter.Conditions.AddCondition(<N to N child Entity> + "id", ConditionOperator.Equal, PluginHelpers.GetEntityId(context));

     

    // link the filter object to the link entity

    le1.Criteria.Filters.AddFilter(filter);

     

    // link the link entity to the query.

    query.LinkEntities.AddLink(le1);

    RetrieveMultipleRequest multiRequest = new RetrieveMultipleRequest();

    multiRequest.Query = query.Query;

    multiRequest.ReturnDynamicEntities = true;

    RetrieveMultipleResponse multiResponse = (RetrieveMultipleResponse)crmService.Execute(multiRequest);

    BusinessEntityCollection bec = multiResponse.BusinessEntityCollection;

     

    For this example, I have retrieve all the ParentID in the Parent Entity where the Child Entity is equals to the context id in a many to many relationship.  Let me know if this is clear for you.

     

    Jude

     

    Tuesday, July 8, 2008 6:13 PM
  •  Jude Lee wrote:

    You need to use QueryExpression to retireve N to N entity:

     

    Assuming you are using the SDK Helper:

     

     

    QueryExpressionHelper query = new QueryExpressionHelper(<ParentEntity>);

    query.Columns.AddColumn(<ParentEntity> + "id");

     

    // Setup the inner join between the parent entity and the N to N entity (reletionship entity name found in the customization window)

    LinkEntityHelper le1 = new LinkEntityHelper(<ParentEntity>, <Parententity>+ "id", <N to N Entity>, <ParentEntity> + "id", JoinOperator.Inner);

     

    // Create Filter and Condition expression

    FilterExpressionHelper filter = new FilterExpressionHelper();

    filter.Conditions.AddCondition(<N to N child Entity> + "id", ConditionOperator.Equal, PluginHelpers.GetEntityId(context));

     

    // link the filter object to the link entity

    le1.Criteria.Filters.AddFilter(filter);

     

    // link the link entity to the query.

    query.LinkEntities.AddLink(le1);

    RetrieveMultipleRequest multiRequest = new RetrieveMultipleRequest();

    multiRequest.Query = query.Query;

    multiRequest.ReturnDynamicEntities = true;

    RetrieveMultipleResponse multiResponse = (RetrieveMultipleResponse)crmService.Execute(multiRequest);

    BusinessEntityCollection bec = multiResponse.BusinessEntityCollection;

     

    For this example, I have retrieve all the ParentID in the Parent Entity where the Child Entity is equals to the context id in a many to many relationship.  Let me know if this is clear for you.

     

    Jude

     

     

     

    Perfect Jude Thank You, I will Test This example. But:

     

     

     

    // Create Filter and Condition expression

    FilterExpressionHelper filter = new FilterExpressionHelper();

    filter.Conditions.AddCondition(<N to N child Entity> + "id", ConditionOperator.Equal, PluginHelpers.GetEntityId(context));

     

     

    The context???  what context that you mean????

     

    That's only doubt i have.

     

     

    Very ThankFully Jude That's Nice.

     

     

    Jose!

    Tuesday, July 8, 2008 10:53 PM
  • and this: PluginHelpers

     

    I don't Know..

     

     

    Thanks Again Jude.

     

     

     

    Tuesday, July 8, 2008 11:08 PM
  • Sorry Jose, that was the code that I used in my plug-ins.  The PluginHelper and the context are only for plug-ins. So, don't worry about it.

    Wednesday, July 9, 2008 12:04 AM
  • Hi Jude:

     

     

    Sorry if i bored  i have one Qs More:

     

     

    This example, can i one way to do this through VIEWS of CRM in sql???? or it isn't  recommended , isnt it? if it ok microsoft, nor guarantee me the change of some thing through version to version???

     

    I imagine that the right way an correct is teh SDK, but i like to know your opinion,

     

     

    Thanks Jude.

     

     

    José

    Wednesday, July 9, 2008 12:22 AM
  • No problem José .  We are here to answer your questions. 

     

    As soon as you are querying the FILTERED views (e.g. Filteredaccount) against the CRM database, you are guarnteed that your users are getting the records based on their security context and Microsoft guarantees the upgrade. 

     

    Although SDK is recommanded but there are times that you would query against the database.  For example, MS CRM doesn't support rollups of contacts to the Master Accounts (assuming you have more than one sub-accounts).  If you do this through SDK, it may end up with quite a number of Web Service call while you can achieve the same goal with one simple SQL recurisve query.

     

     

     

    Wednesday, July 9, 2008 1:04 PM
  • Hi Jude:

     

     

        The query of the code you gave me, it's trow this exception, do you know what happen here??  this message detail in the inner exception say that no support the N to N entity especifically my NtoN entity called: 'new_plandesuscripcion_new_disciplina'

     

     

    The detail error:

     

    <error>
      <code>0x80040800</code>
      <description>The 'RetrieveMultiple' method does not support entities of type 'new_plandesuscripcion_new_disciplina'.</description>
      <type>Platform</type>
    </error>

     

    Aprecciatte any help jude or anybody.

     

    Jose

     

     

    Thursday, July 31, 2008 8:39 PM
  • Hi Jose,

     

    Is the name 'new_plandesuscripcion_new_disciplina' the relationship name or the relationship entity name?  you have to use the relationship entity name in the query expression

    Thursday, July 31, 2008 8:44 PM
  • Yes, It is, look the example:

     

     

     

     I have an entity called New_plandesuscripcion and  other entity called new_disciplina.

     

     

    The entity of relationship on over those is:  new_plandesuscripcion_new_disciplina

     

     

                                                                             N to N

    New_plandesuscripcion  -----------<<new_plandesuscripcion_new_disciplina>>------------- new_disciplina

    Thursday, July 31, 2008 8:59 PM
  • Jude:

     

     

    Tha's my code it's run but trown the exception that you know:

     

     

     

     

     

    Helpers.QueryExpressionHelper query = new Helpers.QueryExpressionHelper("new_plandesuscripcion_new_disciplina");

    query.Columns.AddColumn("new_disciplinaid");

    // Setup the inner join between the parent entity and the N to N entity (relationship entity name found in the customization window)

    Helpers.LinkEntityHelper le1 = new Helpers.LinkEntityHelper("new_plandesuscripcion", "new_plandesuscripcionid", "new_plandesuscripcion_new_disciplina", "new_plandesuscripcionid", CRM.JoinOperator.Inner);

    // Create Filter and Condition expression

    Helpers.FilterExpressionHelper filter = new Helpers.FilterExpressionHelper();

    filter.Conditions.AddCondition("new_plandesuscripcionid", CRM.ConditionOperator.Equal, _temp[0].ToString());

    // link the filter object to the link entity

    le1.Criteria.Filters.AddFilter(filter);

    // link the link entity to the query.

    query.LinkEntities.AddLink(le1.FromAttributeName.ToString(), le1.ToEntityName.ToString(), le1.ToAttributeName.ToString());

    CRM.RetrieveMultipleRequest multiRequest = new CRM.RetrieveMultipleRequest();

    multiRequest.Query = query.Query;

    multiRequest.ReturnDynamicEntities = true;

     

    CRM.RetrieveMultipleResponse multiResponse = (CRM.RetrieveMultipleResponse)GetCrmService().Execute(multiRequest);

    CRM.BusinessEntityCollection bec = multiResponse.BusinessEntityCollection;

     

     

     

     

    Thnanks   Jude

     

     

     

    Jose

    Thursday, July 31, 2008 9:26 PM
  • Hello everybody,

    I have the same problem like a____4 an get the same Exception.

    I tried it with my own dynamic entities and with the standard teammembership entity, which is the relationship entity between team and systemuser.

    I get the same InnerXml Message: The 'RetrieveMultiple' method does not support entities of type 'TeamMembership'

    Anybody has an Idea?

    Thanks
    Friday, June 5, 2009 6:42 AM
  • You can't use RetrieveMultiple on the intersection entities like TeamMembership, or any other for a N-N relationship. You have to use Fetch instead, or, in the case of TeamMembership, you can use either the RetrieveMembersTeam or RetrieveTeamsSystemUser message
    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    Friday, June 5, 2009 8:33 AM
    Moderator
  • Thanks for your answer, that helps me :)


    Friday, June 5, 2009 9:45 AM