Sign in
Microsoft.com
 
Microsoft
 
 
 

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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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?

    Thanks.


    Thursday, June 5, 2014 2:04 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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 ,
ERR.RelationshipRoleType,ER.SchemaName,RR.ReferencedEntityId,RR.ReferencingEntityId
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