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:
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:
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):
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!
Temmy Wahyu Raharjo 