Dynamics CRM 2011 On-Premisses SQL Query of Entities Relationships RRS feed

  • Question

  • Hello All!

    I've been trying to build a query to list all entities with its relationships (1:N, N:1 and N:N) but so far what I've got is:

    Main Entity Name

    Main Entity Logical Name

    Main Entity Relationship Type

    Main Entity Relationship Schema Name

    I still need the related entity Name and Logical Name.

    See the query:

    select l.Label as Label, pe.LogicalName as LogicalName, err.RelationshipRoleType as RelationshipRoleType, er.SchemaName as SchemaName from EntityRelationshipRoleView err
    join EntityView pe on err.EntityId = pe.EntityId
    join LocalizedLabelView l on err.EntityId = l.ObjectId and l.ObjectColumnName = 'LocalizedName'
    join EntityRelationshipView er on err.EntityRelationshipId = er.EntityRelationshipId
    where pe.LogicalName = 'account'
    order by err.RelationshipRoleType

    Any Ideas?


    Thursday, June 5, 2014 2:04 PM

All replies

  • Hi Egabrioli

    Here the query that you want. You can change Objecttypecode as you want the entity.RelationType 0 equals n to n realtionship , 1 equals 1-->N , N-->1

    select  MyQuery.[ Entity Name] , MyQuery.[Entity Logical Name],MyQuery.SchemaName,EE.Name as RelatedEntityName,MyQuery.RelationshipType from ( 
    select E.Name as [ Entity Name],E.LogicalName [Entity Logical Name],er.SchemaName, r.RelationshipType, r.ReferencingEntityId as ReferencingEntityId  from MetadataSchema.EntityRelationshipRelationships err
    inner join EntityRelationship  er on err.EntityRelationshipId = er.EntityRelationshipId
    inner join Relationship r on r.RelationshipId = err.RelationshipId
    inner join Entity E on E.EntityId = r.ReferencedEntityId
    where  E.ObjectTypeCode  = 1 ) as MyQuery
    inner join Entity EE on EE.EntityId = MyQuery.ReferencingEntityId

    Thursday, June 5, 2014 2:55 PM
  • Hi Polat, first, thanks for your help. When I choose ObjectTypeCode = 3 (opportunity), it retrieves 114 records. So I opened CRM and I saw that in fact there are 83 relationships (1:N = 46, N:1 = 31, N:N = 6).
    Thursday, June 5, 2014 4:40 PM
  • Elvis ,

    Sorry for the previous post. I made a quick research. And ı build the query. Also ı tried on my test system and it gives the correct result. Pls try the below query.

    select x.EntityId as MainEntityId,x.Name as MainEntityName,x.LogicalName as MainEntityLogicalName ,
    case when x.RelationshipRoleType = 0 then 'one to many'
    when x.RelationshipRoleType = 1 then 'many to one'
    when x.RelationshipRoleType = 2 then 'many to many' end as RelationName,EREFERNCED.Name as MainEntity,EREFERNCED1.Name as RelatedEntity,x.SchemaName   from (
    select E.EntityId
    ,E.Name ,E.LogicalName ,
    from EntityRelationshipRoleAsIfPublishedView ERR
    join EntityRelationshipAsIfPublishedView ER on ERR.EntityRelationshipId = ER.EntityRelationshipId
    join EntityAsIfPublishedView E on E.EntityId = ERR.EntityId
    join EntityRelationshipRelationshipsAsIfPublishedView ERRS on ERRS.EntityRelationshipId = ER.EntityRelationshipId 
    inner join RelationshipAsIfPublishedView RR on ERRs.RelationshipId = RR.RelationshipId and ( rr.ReferencingEntityId = E.EntityId or rr.ReferencedEntityId = E.EntityId)
    where E.LogicalName = 'account'  ) as x
    join EntityAsIfPublishedView EREFERNCED on EREFERNCED.EntityId = x.ReferencedEntityId
    join EntityAsIfPublishedView EREFERNCED1 on EREFERNCED1.EntityId = x.ReferencingEntityId

    Also in addition to this , you can reach the relations from c#

      RetrieveEntityRequest r = new RetrieveEntityRequest()
                    EntityFilters = EntityFilters.Relationships,
                    RetrieveAsIfPublished = true,
                    LogicalName = "account"
                RetrieveEntityResponse response = (RetrieveEntityResponse)mySrv.Service.Execute(r);
                OneToManyRelationshipMetadata[]  onetomany = response.EntityMetadata.OneToManyRelationships;
                OneToManyRelationshipMetadata[] manytoone = response.EntityMetadata.ManyToOneRelationships;
                ManyToManyRelationshipMetadata[] manytomany = response.EntityMetadata.ManyToManyRelationships;

    Pls Marked Answered if it is  helpful for you

    Thursday, June 5, 2014 9:17 PM