How to write query for fetching count of entity from Dataverse

Priya Jha 896

Hi All,

I have a linked service connecting to Dataverse and i am trying to fetch count of an entity/table via lookup activity.

What query should i write to fetch only table count of an entity?

1 answer

Chandra Boorla 7,960 Reputation points Microsoft Vendor

2025-02-06T02:20:58.6366667+00:00
Hi @Priya Jha

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

To fetch the count of records from a Dataverse entity using a Lookup activity in Azure Data Factory, you can utilize a FetchXML query with aggregation. Below is the FetchXML query and important considerations.

FetchXML Query Structure:

<fetch aggregate='true'> <entity name='<<tableName>>'> <attribute name='<<primaryKeyAttribute>>' alias='count' aggregate='countcolumn' /> </entity> </fetch>

Explanation:

<fetch aggregate='true'>: This indicates that the query is for aggregation.

<entity name='<<tableName>>'>: Replace <<tableName>> with the actual name of your Dataverse entity (table).

<attribute name='<<primaryKeyAttribute>>' alias='count' aggregate='countcolumn' />:

Replace <<primaryKeyAttribute>> with the name of the primary key attribute of your entity. This is crucial for counting.

alias='count' gives the resulting count column a name "count".

aggregate='countcolumn' specifies that we want to count the values in the specified attribute.

For additional information, please refer: Aggregate data using FetchXml

Example:

For instance, if you want to count the records in the "accounts" entity, where the primary key attribute is "accountid", your FetchXML query would look like this:

<fetch aggregate='true'> <entity name='accounts'> <attribute name='accountid' alias='count' aggregate='countcolumn' /> </entity> </fetch>

Output - The Lookup activity will return a single row with a column named "count", which contains the total number of records in the specified entity. You can reference this count in subsequent activities in your pipeline.

Important Considerations:

Primary Key Attribute - Ensure you are using the correct primary key attribute for the entity you are querying.

Entity Logical Name - Use the logical name of the entity (e.g., "accounts") rather than the display name.

Testing the Query - It’s a good practice to test the query using tools like the XrmToolBox FetchXML Builder to verify it returns the expected count.

Performance - Using FetchXML with aggregation is efficient as it retrieves only the count rather than all records.

By following these steps and considerations, you can effectively retrieve the count of records from a Dataverse entity using a Lookup activity in Azure Data Factory.

I hope this information helps. Please do let us know if you have any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.

1 person found this answer helpful.
Priya Jha 896 Reputation points

2025-02-06T11:38:44.6833333+00:00

Hi @Chandra Boorla

Thanks for providing your inputs.

How should i change the above query if for a particular entity i have multiple columns as Primary Key Attribute.

Should i mention them in comma separated list in above query?

Chandra Boorla 7,960 Reputation points Microsoft Vendor

2025-02-06T15:05:22.2466667+00:00

@Priya Jha

Thank you for the add-on question!

How should i change the above query if for a particular entity i have multiple columns as Primary Key Attribute.

In Dataverse, entities with composite primary keys (multiple columns) don't require you to mention all key columns when writing a FetchXML query for counting records. You only need to use one column (attribute) from the composite key to perform the count.

Should i mention them in comma separated list in above query?

There is no concept of listing multiple columns in a comma-separated list for this purpose in FetchXML. The aggregation works at the entity level, regardless of the composite key structure.

The aggregate='countcolumn' operation is not tied to the uniqueness of the primary key; it simply counts the total number of records in the entity. Whether the key is composite or not, the count query will provide the same result.

If You Want to Count Records Grouped by Primary Key Attributes:

If you need to group records by all attributes in the composite primary key (e.g., primary_key_attribute1 and primary_key_attribute2), you must specify a groupby for those attributes.

<fetch aggregate='true'> <entity name='your_entity_name'> <attribute name='primary_key_attribute1' alias='pk1' groupby='true' /> <attribute name='primary_key_attribute2' alias='pk2' groupby='true' /> <attribute name='some_non_nullable_attribute' alias='count' aggregate='count' /> </entity> </fetch>

primary_key_attribute1 and primary_key_attribute2 will act as grouping columns.

Replace some_non_nullable_attribute with an attribute that ensures no null values (e.g., a primary key or another field used for counting).

This query will return a count of records grouped by each unique combination of the composite primary key attributes.

FetchXML doesn’t allow specifying multiple columns in a single attribute tag. You need to define each column individually if you’re grouping records or retrieving multiple attributes.

Example - For an entity with a composite primary key of key1 and key2, you can:

Count all records:

<fetch aggregate='true'> <entity name='custom_entity'> <attribute name='key1' alias='count' aggregate='countcolumn' /> </entity> </fetch>

Count grouped by key1 and key2:

<fetch aggregate='true'> <entity name='custom_entity'> <attribute name='key1' alias='pk1' groupby='true' /> <attribute name='key2' alias='pk2' groupby='true' /> <attribute name='key1' alias='count' aggregate='count' /> </entity> </fetch>

I hope this information helps.

Priya Jha 896 Reputation points

2025-02-07T10:05:51.28+00:00

Hi @Chandra Boorla

Thanks for your reply.

I am also getting below error when trying to fetch the count via lookup activity-

Any guidance on what is this error about and is this happening because of some issue on Dataverse end or in lookup activity configurations.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to write query for fetching count of entity from Dataverse

1 answer

Your answer