locked
Linq & N:1 Relationship RRS feed

  • Question

  • I have entity1 and entity2 that have an N:1 relationship. So I have the following schema names:

    entity1->N:1 Relationship->new_new_entity2_new_entity1, referential
    entity2->1:N Relationship->new_new_entity2_new_entity1, referential

    I have a plugin that triggers when entity1 is being modified. In this plugin I need a copy of the entity2 with which entity1 has a relationship. I can't quite figure out how to do this. I tried something like this:

    IQueryable<new_entity2> entity2Set = from e2 in xrm.new_entity2Set
       join e1 in xrm_new_entity1 on e2.new_new_entity2_new_entity1 equals e1.new_new_entity2_new_entity1
       select e2;

    However, it seems I cannot join on the field new_new_entity2_new_entity1

    Tuesday, May 29, 2012 12:57 AM

Answers

  • With my updated understanding, your query should be something like this: 

    IQueryable<new_entity2> entity2Set = from e2 in xrm.new_entity2Set
       join e1 in xrm.new_entity1Set on e2.new_entity2Id equals e1.new_entity2Id.Id 
       select e2;

    Daniel Cai | http://danielcai.blogspot.com | @danielcai | Data Integration made easy with SSIS Integration Toolkit

    • Marked as answer by hfaun Tuesday, May 29, 2012 5:03 AM
    Tuesday, May 29, 2012 4:34 AM

All replies

  • CRM LINQ provider has some constraints, so it is often the case that you can do something in native SQL script, but you cannot do the same using LINQ query. This is due to the fact that CRM LINQ provider is based on FetchXML and query expression.

    In your case, you would need to first join the intermediate entity, then join entity1, if I understand your requirement correctly. 


    Daniel Cai | http://danielcai.blogspot.com | @danielcai | Data Integration made easy with SSIS Integration Toolkit

    Tuesday, May 29, 2012 3:52 AM
  • Thanks Daniel. I did try to join the two with my join statement and using the new_new_entity2_new_entity1 as the field that must match. This seems the only field that the two entities have in common, i.e. there is no common ID, or did I miss something?

    Btw, the compile error I get is "They type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'". I believe this is because referential types (like in a N:1 relationship) cannot be compared.

    Tuesday, May 29, 2012 4:03 AM
  • I might have read wrong with my previous response. I thought you have an intermediate entity between entity1 and entity2. 

    If I read correctly, new_new_entity2_new_entity1 is the relationship name, it cannot be used for join. 


    Daniel Cai | http://danielcai.blogspot.com | @danielcai | Data Integration made easy with SSIS Integration Toolkit

    Tuesday, May 29, 2012 4:20 AM
  • Daniel, so if I have the entity on the 'N side' (entity1) how do I get the '1 side' (entity2) in the N:1 relationship? If there is an ID then I can use that but all I have is that field of type 'referential'.
    Tuesday, May 29, 2012 4:30 AM
  • With my updated understanding, your query should be something like this: 

    IQueryable<new_entity2> entity2Set = from e2 in xrm.new_entity2Set
       join e1 in xrm.new_entity1Set on e2.new_entity2Id equals e1.new_entity2Id.Id 
       select e2;

    Daniel Cai | http://danielcai.blogspot.com | @danielcai | Data Integration made easy with SSIS Integration Toolkit

    • Marked as answer by hfaun Tuesday, May 29, 2012 5:03 AM
    Tuesday, May 29, 2012 4:34 AM
  • Daniel, THANK YOU!!! That worked.
    Tuesday, May 29, 2012 5:03 AM