Dataverse: How to use ExecuteByIdUserQueryRequest and ExecuteByIdSavedQueryRequest

In this blog post, we will learn how to use ExecuteByIdUserQueryRequest and ExecuteByIdSavedQueryRequest. Functionality-wise, these two functions have the same purpose: querying data from Dataverse and returning the result in string XML based on public/personal views that we define in the system.

For example, I created the below Personal View to get the Contact data:

Created personal view to get contact data

Then to get the ids of my personal view and the public view, I’m using this query (using SQL 4 CDS by Mark Carrington):

SELECT userqueryid, name, description, returnedtypecode FROM userquery

SELECT savedqueryid, name, description, returnedtypecode FROM savedquery
WHERE isdefault=1 and returnedtypecode='Contact'
ORDER BY returnedtypecode

The above query results in the below data:

UserQuery and SavedQuery results

Once we get the data, then we can try our demo code:

var userQueryRequest = new ExecuteByIdUserQueryRequest
{
    EntityId = new EntityReference("userquery", new Guid("478b56dc-2d0a-ed11-82e5-000d3a9a6d2a"))
};

var userQueryResponse = (ExecuteByIdUserQueryResponse)await service.ExecuteAsync(userQueryRequest);

If you execute the above code and debug the userQueryResponse.String, it will contain this sample data (in my environment):

The resulting tag represents one entity (in my case Contact)

If you check further the tag, those attributes inside the result tag are matched with all the attributes of the main entity (in my case Contact table). But the problem now, is how we can use this data if we want to use it in our code?

Real World Scenario?

The possible scenario that I think of is to process dynamics data (without changing the query and redeploying the solution) based on Environment Variable, Plugin Unsecure config, or other methods! But then the problem comes because we need to think about how to serialize the data from XML to Entity. For this purpose, I create the below sample code:

using System.ServiceModel;
using System.Xml.Linq;
using DataverseClient;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;

var builder = Helper.CreateHostBuilder().Build();
var serviceProvider = builder.Services;

var service = serviceProvider.GetRequiredService<IOrganizationServiceAsync2>();


var userQueryRequest = new ExecuteByIdUserQueryRequest
{
    EntityId = new EntityReference("userquery", new Guid("478b56dc-2d0a-ed11-82e5-000d3a9a6d2a"))
};

var userQueryResponse = (ExecuteByIdUserQueryResponse)await service.ExecuteAsync(userQueryRequest);

var entityMetadata = (RetrieveAllEntitiesResponse)await service.ExecuteAsync(
    new RetrieveAllEntitiesRequest
    {
        RetrieveAsIfPublished = true,
        EntityFilters = EntityFilters.Attributes
    });
var entities = CommonHelper.XmlToEntities(entityMetadata.EntityMetadata, "contact", userQueryResponse.String);
// process your retrieve entities data using Personal View query

var savedQueryRequest = new ExecuteByIdSavedQueryRequest
    { EntityId = new Guid("65d2cba8-eeda-4419-b03b-d9c2d8272e51") };

var savedQueryResponse = (ExecuteByIdSavedQueryResponse)await service.ExecuteAsync(savedQueryRequest);
var entities2 = CommonHelper.XmlToEntities(entityMetadata.EntityMetadata, "contact", userQueryResponse.String);
// process your retrieve entities data using Public View Query
Console.WriteLine("Run..");

public static class CommonHelper
{
    public static Entity[] XmlToEntities(EntityMetadata[] entityMetadatas, string primaryEntity, string xml)
    {
        var entityMetadataAttributes = entityMetadatas.Single(e => e.LogicalName == primaryEntity);

        var entities = new List<Entity>();
        var entityElements = XElement.Parse(xml).Elements("result");
        foreach (var entityElement in entityElements)
        {
            var entity = new Entity("contact");
            var attributes = entityElement.DescendantNodes().OfType<XElement>();
            foreach (var attribute in attributes)
            {
                var metadata = entityMetadataAttributes.Attributes.Single(e => e.LogicalName == attribute.Name);
                var objectValue = attribute.Value;
                var lookupLogicalName = !string.IsNullOrEmpty(attribute.Attribute("type")?.Value)
                    ? entityMetadatas
                        .Single(e => e.ObjectTypeCode == int.Parse(attribute.Attribute("type")!.Value))
                        .LogicalName
                    : "";
                object? value = !string.IsNullOrEmpty(objectValue)
                    ? metadata.AttributeType switch
                    {
                        AttributeTypeCode.String => objectValue,
                        AttributeTypeCode.Boolean => Convert.ToBoolean(int.Parse(objectValue)),
                        AttributeTypeCode.BigInt => long.Parse(objectValue),
                        AttributeTypeCode.Integer => int.Parse(objectValue),
                        AttributeTypeCode.Decimal => decimal.Parse(objectValue),
                        AttributeTypeCode.Money => new Money(decimal.Parse(objectValue)),
                        AttributeTypeCode.Lookup => new EntityReference(lookupLogicalName, new Guid(objectValue)),
                        AttributeTypeCode.Customer =>
                            new EntityReference(lookupLogicalName
                               , new Guid(objectValue)),
                        AttributeTypeCode.Owner => new EntityReference(lookupLogicalName, new Guid(objectValue)),
                        AttributeTypeCode.Uniqueidentifier => new Guid(objectValue),
                        AttributeTypeCode.DateTime => DateTime.Parse(objectValue),
                        AttributeTypeCode.Picklist => new OptionSetValue(int.Parse(objectValue)),
                        _ => throw new FaultException($"{metadata.LogicalName} not yet defined")
                    }
                    : null;
                if (value == null) continue;

                if (metadata.AttributeType == AttributeTypeCode.Uniqueidentifier)
                {
                    entity.Id = new Guid(objectValue);
                    continue;
                }
                entity[metadata.LogicalName] = value;
            }

            entities.Add(entity);
        }

        return entities.ToArray();
    }
}

Because we need to know how to map the attributes, we need to use RetrieveAllEntitiesRequest (I know this will be heavy, but we can make sure we only retrieve it once and put it in faster storage like cache). The reason we need to retrieve all the EntityMetadata is also to map the correct EntityLogicalName if the AttributeTypeCode is Lookup, Customer, or Owner.

Once you run that code, the below is the result where I can combine the entities of my personal view and public view:

Happy CRM-ing!

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.