LINQ examples for Dynamics 365

4 min read

Summary

Below are some sample LINQ code snippets to help with querying Microsoft Dynamics 365 using Azure serverless computing.

I created a commandline application and stored this as an Azure Webjob.

Microsoft has thorough documentation here: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/org-service/linq-query-examples

Simple ‘where’ clause

// Create a gateway to the Organization Service Context
OrganizationServiceContext orgContext = new OrganizationServiceContext(service);

// Create a new query to query the 'account' entity
var myLinqQuery = from accountEntity in orgContext.CreateQuery("account")

    // Add a 'where' condition: Last name equals 'Aurelio'
where accountEntity["new_lastname"].Equals("Aurelio")

    // Create the new Select statement
select new {

    // GetAttributeValue is used in this instance where the data may not be populated
    // Note: GetAttributeValue returns null

        firstName = accountEntity.GetAttributeValue < string > ("new_firstname"),
        lastName = accountEntity.GetAttributeValue < string > ("new_lastname"),
};

// Iterate through the existing collection
// 'Dump' whatever is in query

foreach(var results in myLinqQuery) {
          entity["new_message"] = "First Name: " + results.firstName + " Last Name: " + results.lastName;
    }

// RESULT: First Name:  Last Name: Aurelio

Simple ‘where’ clause for a lookup

// Example demonstrates how to query a lookup

// Create a gateway to the Organization Service Context
 var myLinqQuery = from accountEntity in orgServiceContext.CreateQuery("account")

                        where ((EntityReference)accountEntity["accountid"]).Id.Equals(new Guid("25892e17-80f6-415f-9c65-7395632f0233"))

                        select new
                        {
                            AccountName = accountEntity["name"],
                            AccountNumber = accountEntity["accountnumber"],
                           
                        };
// Iterate through the existing collection
// 'Dump' whatever is in query

foreach(var results in myLinqQuery) {
          entity["new_message"] = "Account Name: " + results.AccountName + " Account Number: " + results.AccountNumber;
    }

// RESULT: Account Name:  ACME Account Number: 7637499

Simple ‘where’ clause with three conditions

// Create a gateway to the Organization Service Context
OrganizationServiceContext orgContext = new OrganizationServiceContext(service);

// Create a new query to query the 'account' entity
var myLinqQuery = from accountEntity in orgContext.CreateQuery("account")

    // Add a 'where' condition: Last name equals 'Aurelio'
    where   accountEntity["new_lastname"].Equals("Aurelio") && 
            accountEntity["fax"].Equals("01121234") && 
            accountEntity["address1_city"].Equals("London")

    // Create the new Select statement
    select new {

    // GetAttributeValue is used in this instance where the data may not be populated
    // Note: GetAttributeValue returns null
        firstName = accountEntity.GetAttributeValue < string > ("new_firstname"),
        lastName = accountEntity.GetAttributeValue < string > ("new_lastname"),
};

// Iterate through the existing collection
// 'Dump' whatever is in query

foreach(var results in myLinqQuery) {
    entity["new_message"] = "First Name: " + results.firstName + " Last Name: " + results.lastName;
}

Simple join and ‘where’ clause

 // Create a gateway to the Organization Service Context
 OrganizationServiceContext orgContext = new OrganizationServiceContext(service);

 // Create a new query to query the primary entity: 'account' entity
 var myLinqQuery = from accountEntity in orgContext.CreateQuery("account")

     // Join a second Entity
    join contactEntity in orgContext.CreateQuery("contact")

     // Select the attribute that links the accountEntity to the contactEntity
    on accountEntity["accountid"] equals contactEntity["parentcustomerid"]

     // Apply first condition: Company name in the accountEntity should contain the word 'ACME'
    where((string) accountEntity["name"]).Contains("ACME")

     // Apply second condition: Last name in the contactEntity should contain the word 'Mathews'
    where((string) contactEntity["lastname"]).Contains("Mathews")

     // Create the new Select statement
    select new {

     // GetAttributeValue is used in this instance where the data may not be populated
     // Note: GetAttributeValue returns null values
     name = accountEntity.GetAttributeValue < string > ("name"),
         email = contactEntity.GetAttributeValue < string > ("emailaddress1"),
    };

 // Iterate through the existing collection
 // 'Dump' whatever is in query
 foreach(var results in myLinqQuery) {
     entity["new_message"] = "Company Name: " + results.name + "\nLast Name: " + results.email;
 }

Retrieve a record id and set a lookup field

 try {
 // Get the BusinessUnitId using LINQ

 // Create a gateway to the Organization Service Context
 OrganizationServiceContext orgContext = new OrganizationServiceContext(service);

 // Create a new query to query the 'businessunit' entity
 var myLinqQuery = from targetEntity in orgContext.CreateQuery("businessunit")

 // Add a 'where' condition: Last name equals 'Accounting'
 where targetEntity["name"].Equals("Accounting")

 // Create the new Select statement
 select new {

 // GetAttributeValue is used in this instance where the data may not be populated
 // Note: GetAttributeValue returns null
 // Note: We are retrieving a GUID
 businessId = targetEntity.GetAttributeValue < Guid > ("businessunitid"),
 };

 // Iterate through the existing collection
 // 'Dump' whatever is in query

 foreach(var results in myLinqQuery) {

 // Set the lookup field
 entity["new_businessunit"] = new EntityReference("businessunit", results.businessId);

 // Set the text field
 entity["new_message"] = results.businessId.ToString();
 }

 if (context.Depth <= 1) {
 service.Update(entity);
 }

 } catch (FaultException ex) {
 throw new InvalidPluginExecutionException("An error occurred in the plug-in", ex);
 }

Inner Join example

       private static void RetrieveRecordTest(IOrganizationService service, string entity, string identity, string aName, string id)
        {
            OrganizationServiceContext orgContext = new OrganizationServiceContext(service);

            string rs = null;

            var q = from e in orgContext.CreateQuery(entity)
                    join c in orgContext.CreateQuery("businessunit")
                    on (Guid)e["owningbusinessunit"] equals (Guid)c["businessunitid"]
                    where (Guid)c["businessunitid"] != new Guid(id)

                    select e;

            foreach (var results in q)
            {
                rs = results.Id.ToString();
            }
        }

You must be logged in to post a comment.