Asked by:
Dynamics CRM 2011 On-Premisses SQL Query of Entities Relationships

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?
Thanks.
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- Proposed as answer by Polat Aydın[MCP] Thursday, June 5, 2014 2:58 PM
- Edited by Polat Aydın[MCP] Thursday, June 5, 2014 3:02 PM
- Unproposed as answer by Polat Aydın[MCP] Thursday, June 5, 2014 6:27 PM
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 , 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- Edited by Polat Aydın[MCP] Thursday, June 5, 2014 9:31 PM
- Proposed as answer by Polat Aydın[MCP] Friday, June 6, 2014 6:27 AM
Thursday, June 5, 2014 9:17 PM