We will learn and understand the different methods for querying the data in Power Apps using the SDK for .NET. Before we start, make sure to subscribe to CRM Crate so that you can stay up to date in the field of Power Platform.

In Power Apps, you can create a Model-Driven App that efficiently accesses the information stored in Micrsoft Dataverse in form of tables & attributes. Tables serve as a means to structure and manage the business data. In the process of app development, one has the option to employ either standard tables, custom tables, or a combination of both. Microsoft Dataverse comes equipped with default standard tables. The attributes are the elements which stores different format of data such as text, integer, date & time, decimal, lookup etc.

Methods for querying data in Power Apps using SDK for .NET

The .NET SDK offers various methods for data querying, each presenting distinct advantages.

QueryExpression

You possess a robust, strongly typed object model for constructing complex data queries. It supports all FetchXML features except for aggregates and grouping, but it enables the inclusion of data from linked table rows (entity records).
With QueryExpression you can define table name, define columnset, add query criteria, add data from linked entities and order the result.

The following example demonstrates a simple query to return up to 500 matching CRM Crate Account rows where the ‘usertype’ value equals 1.

var query = new QueryExpression("crmcrateaccount")
{
  ColumnSet = new ColumnSet("name"),
  Criteria = new FilterExpression(LogicalOperator.And),
  TopCount = 500
};
query.Criteria.AddCondition("usertype", ConditionOperator.Equal, 1);
query.AddOrder("name", OrderType.Ascending);

EntityCollection results = svc.RetrieveMultiple(query);

results.Entities.ToList().ForEach(x =>
{
  Console.WriteLine(x.Attributes["name"]);
});

Important Note: When fetching rows, it is recommended to specify the exact column values needed by configuring the attributes through the ColumnSet class constructor. While the ColumnSet class constructor offers an overload with a boolean parameter ‘allColumns’ (Setting the ColumnSet as true), it is recommended not to employ this in production code as it impact application performance.

QueryByAttribute

The QueryByAttribute class offers a set of strongly typed objects designed for streamlined and common queries on table rows. In contrast to FetchXML and QueryExpression, QueryByAttribute is limited to retrieving data from a single table. It lacks the capability to fetch data from related table rows or support complex query criteria.

The following example demonstrates a simple query to return up to 500 matching CRMCrateAccount rows where the “usertype” value equals 1, ordered by name.

var query = new QueryByAttribute("crmcrateaccount")
{
  TopCount = 500,
  ColumnSet = new ColumnSet("name")
};
query.AddAttributeValue("usertype", 1);
query.AddOrder("name", OrderType.Ascending);

EntityCollection results = svc.RetrieveMultiple(query);

results.Entities.ToList().ForEach(x =>
{
  Console.WriteLine(x.Attributes["name"]);
});

FetchExpression

FetchXml is an XML-based query language proprietary to the system, applicable for SDK Assembly queries through FetchExpression and for the Web API using the fetchXml query string. You have the option to construct a FetchXML query tailored to a particular table. Subsequently, encode the XML into a URL-friendly format and employ the fetchXml query string parameter to transmit it to the entity set.

The following example shows a simple query to return up to 500 matching account rows where the “usertype” value equals Redmond, ordered by name.

string fetchXml = @"
<fetch top='500' >
  <entity name='crmcrateaccount' >
    <attribute name='name' />
    <filter>
      <condition 
        attribute='usertype' 
        operator='eq' 
        value=1/>
    </filter>
    <order attribute='name' />
  </entity>
</fetch>";

var query = new FetchExpression(fetchXml);

EntityCollection results = svc.RetrieveMultiple(query);

results.Entities.ToList().ForEach(x => {
  Console.WriteLine(x.Attributes["name"]);
});

LINQ (Language Integrated Query)

Language-Integrated Query (LINQ) refers to a collection of technologies that incorporate query capabilities directly into the C# language. In traditional approaches, data queries are typically expressed as plain strings, lacking compile-time type checking and IntelliSense support. Moreover, different query languages need to be learned for distinct data sources such as SQL databases, XML documents, and various web services. In contrast, LINQ elevates queries to the status of a first-class language construct, akin to classes, methods, and events.
The most apparent “language-integrated” aspect of LINQ is the query expression when crafting queries. These expressions utilize a declarative query syntax. Through this syntax, you can conduct filtering, ordering, and grouping operations on data sources with minimal code. The consistent query expression patterns enable querying and transforming data from various types of data sources.

The provided example demonstrates a comprehensive query operation, encompassing the creation of a data source, formulation of the query expression, and execution of the query within a foreach statement.

////CRM Crate Sample Code For LINQ
// Specify the data source.
int[] values= [95, 90, 83, 65];

// Define the query expression.
IEnumerable<int> crmcrateQuery=
    from value in values
    where value > 90
    select value ;

// Execute the query.
foreach (var i in crmcrateQuery)
{
    Console.Write(i + " ");
}

Important Note: You might need to add a using directive, using System.Linq;, for the preceding example to compile. 

• LinkedIn
• Facebook
• Telegram