/// <summary>
/// Sends a single Get Request using $batch
/// </summary>
/// <param name="svc">The configured service</param>
/// <param name="path">The relative URL of the GET request</param>
/// <param name="headers">Any headers to be applied to the request.</param>
/// <returns></returns>
private static JToken SendGetAsBatch(CDSWebApiService svc, string path, Dictionary <string, string> headers = null)
{
var batchGet = new BatchGetRequest()
{
Path = path,
Headers = headers
};
var batchResponses = svc.PostBatch(new List <BatchItem> {
batchGet
});
HttpResponseMessage batchResponse = batchResponses[0];
if (batchResponse.IsSuccessStatusCode)
{
var responseContent = JToken.Parse(batchResponse.Content.ReadAsStringAsync().Result);
return(responseContent);
}
else
{
throw new Exception("Error sending GET request in Batch.");
}
}
private static void DeleteRequiredRecords(CDSWebApiService svc, bool deleteCreatedRecords)
{
if (!deleteCreatedRecords)
{
Console.Write("\nDo you want these sample entity records deleted? (y/n) [y]: ");
string answer = Console.ReadLine();
answer = answer.Trim();
if (!(answer.StartsWith("y") || answer.StartsWith("Y") || answer == string.Empty))
{
return;
}
}
Console.WriteLine("\nDeleting data created for this sample:");
entityUris.ForEach(x =>
{
// Console.WriteLine($"\tDeleting {svc.BaseAddress.MakeRelativeUri(x)}"); //remove later
Console.Write(".");
svc.Delete(x);
//Thread.Sleep(100);
});
Console.WriteLine("\nData created for this sample deleted.");
}
private static void DeleteRequiredRecords(CDSWebApiService svc)
{
entityUris.ForEach(x =>
{
svc.Delete(x);
});
}
private static void UnInstallFunctionsAndActionsSolution(CDSWebApiService svc)
{
if (IsSolutionInstalled(svc))
{
svc.Delete(new Uri($"{svc.BaseAddress}solutions({customSolutionID})"));
Console.WriteLine("Solution containing custom actions was uninstalled.");
}
}
private static void Main()
{
try
{
using (CDSWebApiService svc = new CDSWebApiService(config))
{
BasicOperations.Run(svc, true);
// ConditionalOperations.Run(svc);
// FunctionsAndActions.Run(svc);
// QueryData.Run(svc, true);
// BatchOperations.Run(svc, true);
// QueryExpressionQuery.Run(svc,true);
// ServiceProtectionLimitTest.Run(svc);
//EntityMetadataQuery.Run(svc);
}
}
catch (CDSWebApiException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine($"Unexpected Error:\t{ex.Message}\n" +
$"\tStatusCode: {ex.StatusCode}\n" +
$"\tReasonPhrase: {ex.ReasonPhrase}\n" +
$"\tErrorCode: {ex.ErrorCode}");
Console.ResetColor();
}
catch (AggregateException aex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.BackgroundColor = ConsoleColor.White;
Console.WriteLine("Unexpected Errors:\t{0}", aex.Message);
foreach (Exception ex in aex.InnerExceptions)
{
Console.WriteLine($"\t{ex.GetType().Name}: {ex.Message}");
if (ex.InnerException != null)
{
Console.WriteLine($"\t\t{ex.InnerException.GetType().Name}: {ex.InnerException.Message}");
}
}
Console.ResetColor();
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.BackgroundColor = ConsoleColor.White;
Console.WriteLine("Unexpected Error:\t{0}", ex.Message);
Console.ResetColor();
}
finally
{
Console.WriteLine("Press any key to exit");
Console.ReadLine();
}
}
public static void Run(CDSWebApiService svc)
{
var entityFilter = new MetadataFilterExpression
{
FilterOperator = LogicalOperator.And,
Conditions = new List <MetadataConditionExpression>
{
new MetadataConditionExpression()
{
ConditionOperator = MetadataConditionOperator.Equals,
PropertyName = "SchemaName",
Value = new Microsoft.Cds.Metadata.Query.Object()
{
Type = "string", Value = "Account"
}
}
}
};
var entityProperties = new MetadataPropertiesExpression()
{
AllProperties = true
};
var query = new EntityKeyQueryExpression()
{
Criteria = entityFilter,
Properties = entityProperties
};
var jsonQuery = JsonConvert.SerializeObject(query);
var jsonDeletedMetadataFilters = JsonConvert.SerializeObject(DeletedMetadataFilters.Default);
//BUG: 1563435 This returns an HTML Bad Request error
//svc.Get($"RetrieveMetadataChanges(Query={jsonQuery},DeletedMetadataFilters={jsonDeletedMetadataFilters})");
//This returns all metadata without any filter
var response = svc.Get("RetrieveMetadataChanges");
var results = (RetrieveMetadataChangesResponse)JsonConvert.DeserializeObject(response.ToString(), typeof(RetrieveMetadataChangesResponse));
Console.WriteLine($"ServerVersionStamp: {results.ServerVersionStamp}\n");
Console.WriteLine($"Entities returned: {results.EntityMetadata.Count}\n");
var accountMetadata = results.EntityMetadata.Find(x => x.SchemaName.Equals("Account"));
accountMetadata.Attributes.Sort((x, y) => x.SchemaName.CompareTo(y.SchemaName));
accountMetadata.Attributes.ForEach(x => {
Console.WriteLine($"{x.SchemaName} {x.AttributeTypeName.Value}");
});
Console.WriteLine();
}
private static bool IsSolutionInstalled(CDSWebApiService svc)
{
//Check whether solution is already installed
JObject solutionsResult = svc.Get("solutions?" +
"$select=solutionid&" +
$"$filter=uniquename eq '{customSolutionName}'") as JObject;
var IsInstalled = solutionsResult["value"].Any();
if (IsInstalled)
{
customSolutionID = solutionsResult["value"][0]["solutionid"].ToString();
}
return(IsInstalled);
}
private static bool InstallFunctionsAndActionsSolution(CDSWebApiService svc)
{
if (!IsSolutionInstalled(svc))
{
//Install the solution
//Locate the custom solution zip file, which should have been copied over to the build
//output directory.
string solutionPath = Directory.GetCurrentDirectory() + "\\SolutionFiles\\" + customSolutionFilename;
if (!File.Exists(solutionPath))
{
Console.WriteLine($"Solution file not found at {solutionPath}.");
return(false);
}
//Read the solution package into memory
byte[] packageBytes = File.ReadAllBytes(solutionPath);
Guid ImportJobId = Guid.NewGuid();
//Import the solution package.
JObject importParams = new JObject
{
["CustomizationFile"] = packageBytes,
["OverwriteUnmanagedCustomizations"] = false,
["PublishWorkflows"] = false,
["ImportJobId"] = ImportJobId
};
svc.Post("ImportSolution", importParams);
if (IsSolutionInstalled(svc))
{
Console.WriteLine("Solution containing custom actions was installed.");
}
}
else
{
Console.WriteLine("Solution containing custom actions was already installed.");
}
return(true);
}
/// <summary>
/// Performs 3000 CRUD operations on multiple threads which should be enough to trigger Service Protection limits.
/// </summary>
/// <param name="svc"></param>
public static void Run(CDSWebApiService svc)
{
ParallelLoopResult result = Parallel.For(0, 1000, ctr => {
var contact = new JObject
{
["firstname"] = $"test",
["lastname"] = $"contact {ctr}"
};
string name = $"{contact["lastname"]}";
var contactUri = svc.PostCreate("contacts", contact);
Console.WriteLine($"\t {name} Created");
svc.Get($"{contactUri}?$select=lastname");
Console.WriteLine($"\t {name} Retrieved");
svc.Delete(contactUri);
Console.WriteLine($"\t {name} Deleted");
});
Console.WriteLine("Result: {0}", result.IsCompleted ?
"Completed Normally" :
$"Completed to {result.LowestBreakIteration}");
}
public static void Run(CDSWebApiService svc)
{
Console.WriteLine("\n--Starting Functions And Actions--");
//Create records required for this sample
CreateRequiredRecords(svc);
#region Call an unbound function with no parameters.
//Retrieve the current user's full name from the WhoAmI function:
// https://docs.microsoft.com/dynamics365/customer-engagement/web-api/whoami
// Which returns a WhoAmIResponse ComplexType
// https://docs.microsoft.com/dynamics365/customer-engagement/web-api/whoamiresponse
// Which contains a UserId property that can be used to retrieve information about the current user
Console.WriteLine("Unbound function: WhoAmI");
var whoAmIresp = svc.Get("WhoAmI");
//Then retrieve the full name for that unique ID.
var myUserId = whoAmIresp["UserId"];
var user = svc.Get($"systemusers({myUserId})?$select=fullname");
Console.WriteLine($"\tCurrent user has full name: '{user["fullname"]}'.");
#endregion Call an unbound function with no parameters.
#region Call an unbound function that requires parameters.
//Retrieve the time zone code for the specified time zone, using the GetTimeZoneCodeByLocalizedName
//function: https://docs.microsoft.com/dynamics365/customer-engagement/web-api/gettimezonecodebylocalizedname,
// which returns a GetTimeZoneCodeByLocalizedNameResponse ComplexType
// https://docs.microsoft.com/dynamics365/customer-engagement/web-api/gettimezonecodebylocalizednameresponse
string timeZoneName = "Pacific Standard Time";
int localeID = 1033;
Console.WriteLine("Unbound function with parameters: GetTimeZoneCodeByLocalizedName");
var LocalizedNameResponse = svc.Get($"GetTimeZoneCodeByLocalizedName" +
$"(LocalizedStandardName=@p1,LocaleId=@p2)" +
$"?@p1='{timeZoneName}'&@p2={localeID}");
Console.WriteLine($"\tThe time zone '{timeZoneName}' has the code '{LocalizedNameResponse["TimeZoneCode"]}'.");
#endregion Call an unbound function that requires parameters.
#region Call a bound function.
//Retrieve the total time, in minutes, spent on all tasks associated with an incident.
//Uses the CalculateTotalTimeIncident function:
//https://docs.microsoft.com/dynamics365/customer-engagement/web-api/calculatetotaltimeincident,
//which returns a CalculateTotalTimeIncidentResponse complex type:
//https://docs.microsoft.com/en-us/dynamics365/customer-engagement/web-api/calculatetotaltimeincidentresponse.
Console.WriteLine("Bound function: CalculateTotalTimeIncident");
var cttir = svc.Get($"{incident1Uri}/Microsoft.Dynamics.CRM.CalculateTotalTimeIncident()");
Console.WriteLine($"\tThe total duration of tasks associated with the " +
$"incident is {cttir["TotalTime"]} minutes.");
#endregion Call a bound function.
#region Call an unbound action that requires parameters.
//Close an opportunity and mark it as won. Uses the WinOpportunity action:
//https://docs.microsoft.com/en-us/dynamics365/customer-engagement/web-api/winopportunity,
//which takes a int32 status code and an opportunityclose entity type:
//https://docs.microsoft.com/en-us/dynamics365/customer-engagement/web-api/opportunityclose.
Console.WriteLine("UnBound function: WinOpportunity");
var winOpportParams = new JObject(
new JProperty("Status", 3),
new JProperty("OpportunityClose", new JObject(
new JProperty("subject", "Won Opportunity"),
new JProperty("*****@*****.**", opportunity1Uri)
)));
svc.Post("WinOpportunity", winOpportParams);
Console.WriteLine("\tOpportunity won.");
#endregion Call an unbound action that requires parameters.
#region Call a bound action that requires parameters.
//Add a new letter tracking activity to the current user's queue. Uses the AddToQueue
//action: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/web-api/addtoqueue, which is bound to the queue
//entity type: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/reference/entities/queue, and returns a
//AddToQueueResponse complex type: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/web-api/addtoqueueresponse.
JObject letter = new JObject
{
["description"] = "Example letter"
};
var letterUri = svc.PostCreate("letters", letter);
var letterActivityId = (svc.Get($"{letterUri}/activityid"))["value"];
var queueRef = svc.Get($"systemusers({myUserId})/queueid/$ref");
var myQueueUri = new Uri(queueRef["@odata.id"].ToString());
JObject addToQueueParams = new JObject(
new JProperty("Target",
new JObject(
new JProperty("activityid", letterActivityId),
new JProperty("@odata.type", "Microsoft.Dynamics.CRM.letter")
)));
Console.WriteLine("Bound action: AddToQueue");
var queueResponse = svc.Post($"{myQueueUri}/Microsoft.Dynamics.CRM.AddToQueue", addToQueueParams);
var queueItemId = queueResponse["QueueItemId"].ToString();
Console.WriteLine($"\tQueueItemId returned from AddToQueue action: {queueItemId}");
#endregion Call a bound action that requires parameters.
if (InstallFunctionsAndActionsSolution(svc))
{
#region Call a bound custom action that requires parameters.
//Add a note to a specified contact. Uses the custom action sample_AddNoteToContact, which
//is bound to the contact to annotate, and takes a single param, the note to add. It also
//returns the URI to the new annotation.
Console.WriteLine("Custom action: sample_AddNoteToContact");
var note1 = new JObject
{
["NoteTitle"] = "Note Title",
["NoteText"] = "The text content of the note."
};
var noteRef = svc.Post($"{contact1Uri}/Microsoft.Dynamics.CRM.sample_AddNoteToContact", note1);
//Retrieve the created note and the related contact full name;
var noteAndContact = svc.Get($"{svc.BaseAddress}annotations({noteRef["annotationid"]})" +
$"?$select=subject,notetext&$expand=objectid_contact($select=fullname)");
Console.WriteLine($"\tA note with the subject '{noteAndContact["subject"]}' \n" +
$"\tand the text '{noteAndContact["notetext"]}' was created and associated with \n" +
$"\tthe contact '{noteAndContact["objectid_contact"]["fullname"]}'");
#endregion Call a bound custom action that requires parameters.
#region Call an unbound custom action that requires parameters.
//Create a customer of the specified type, using the custom action sample_CreateCustomer,
//which takes two parameters: the type of customer ('account' or 'contact') and the name of
//the new customer.
var customerParam = new JObject
{
["CustomerType"] = "account",
["AccountName"] = "New account customer (sample)"
};
Console.WriteLine("Custom action: sample_CreateCustomer");
svc.Post("sample_CreateCustomer", customerParam);
Console.WriteLine($"\tThe account '{customerParam["AccountName"]}' was created.");
//Try to call the same custom action with invalid parameters, here the same name is
//not valid for a contact. (ContactFirstname and ContactLastName parameters are
//required when CustomerType is contact.)
var invalidCustomerParam = new JObject
{
["CustomerType"] = "contact",
["AccountName"] = "New account customer (sample)"
};
try
{
Console.WriteLine("Custom action: sample_CreateCustomer with invalid parameters:");
svc.Post("sample_CreateCustomer", invalidCustomerParam);
}
catch (CDSWebApiException ex)
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"Expected Error:\t{ex.Message}\n" +
$"\tStatusCode: {ex.StatusCode}\n" +
$"\tReasonPhrase: {ex.ReasonPhrase}\n" +
$"\tErrorCode: {ex.ErrorCode}");
Console.ResetColor();
}
#endregion Call an unbound custom action that requires parameters.
Console.WriteLine("Uninstalling solution containing custom actions.");
UnInstallFunctionsAndActionsSolution(svc);
}
//Delete records used by this sample
Console.WriteLine("Deleting records created for this sample.");
DeleteRequiredRecords(svc);
Console.WriteLine("\n--Functions And Actions Completed--");
}
private static void CreateRequiredRecords(CDSWebApiService svc)
{
var account1 = new JObject {
{ "name", "Contoso, Ltd. (sample)" },
{ "Account_Tasks", new JArray {
new JObject {
{ "subject", "Task 1 for Contoso, Ltd." },
{ "description", "Task 1 for Contoso, Ltd. description" }
},
new JObject {
{ "subject", "Task 2 for Contoso, Ltd." },
{ "description", "Task 2 for Contoso, Ltd. description" }
},
new JObject {
{ "subject", "Task 3 for Contoso, Ltd." },
{ "description", "Task 3 for Contoso, Ltd. description" }
},
} },
{ "primarycontactid", new JObject {
{ "firstname", "Yvonne" },
{ "lastname", "McKay (sample)" },
{ "jobtitle", "Coffee Master" },
{ "annualincome", 45000 },
{ "Contact_Tasks", new JArray {
new JObject {
{ "subject", "Task 1 for Yvonne McKay" },
{ "description", "Task 1 for Yvonne McKay description" }
},
new JObject {
{ "subject", "Task 2 for Yvonne McKay" },
{ "description", "Task 2 for Yvonne McKay description" }
},
new JObject {
{ "subject", "Task 3 for Yvonne McKay" },
{ "description", "Task 3 for Yvonne McKay description" }
},
} }
} },
{ "contact_customer_accounts", new JArray {
new JObject {
{ "firstname", "Susanna" },
{ "lastname", "Stubberod (sample)" },
{ "jobtitle", "Senior Purchaser" },
{ "annualincome", 52000 },
{ "Contact_Tasks", new JArray {
new JObject {
{ "subject", "Task 1 for Susanna Stubberod" },
{ "description", "Task 1 for Susanna Stubberod description" }
},
new JObject {
{ "subject", "Task 2 for Susanna Stubberod" },
{ "description", "Task 2 for Susanna Stubberod description" }
},
new JObject {
{ "subject", "Task 3 for Susanna Stubberod" },
{ "description", "Task 3 for Susanna Stubberod description" }
},
} }
},
new JObject {
{ "firstname", "Nancy" },
{ "lastname", "Anderson (sample)" },
{ "jobtitle", "Activities Manager" },
{ "annualincome", 55500 },
{ "Contact_Tasks", new JArray {
new JObject {
{ "subject", "Task 1 for Nancy Anderson" },
{ "description", "Task 1 for Nancy Anderson description" }
},
new JObject {
{ "subject", "Task 2 for Nancy Anderson" },
{ "description", "Task 2 for Nancy Anderson description" }
},
new JObject {
{ "subject", "Task 3 for Nancy Anderson" },
{ "description", "Task 3 for Nancy Anderson description" }
},
} }
},
new JObject {
{ "firstname", "Maria" },
{ "lastname", "Cambell (sample)" },
{ "jobtitle", "Accounts Manager" },
{ "annualincome", 31000 },
{ "Contact_Tasks", new JArray {
new JObject {
{ "subject", "Task 1 for Maria Cambell" },
{ "description", "Task 1 for Maria Cambell description" }
},
new JObject {
{ "subject", "Task 2 for Maria Cambell" },
{ "description", "Task 2 for Maria Cambell description" }
},
new JObject {
{ "subject", "Task 3 for Maria Cambell" },
{ "description", "Task 3 for Maria Cambell description" }
},
} }
},
new JObject {
{ "firstname", "Scott" },
{ "lastname", "Konersmann (sample)" },
{ "jobtitle", "Accounts Manager" },
{ "annualincome", 38000 },
{ "Contact_Tasks", new JArray {
new JObject {
{ "subject", "Task 1 for Scott Konersmann" },
{ "description", "Task 1 for Scott Konersmann description" }
},
new JObject {
{ "subject", "Task 2 for Scott Konersmann" },
{ "description", "Task 2 for Scott Konersmann description" }
},
new JObject {
{ "subject", "Task 3 for Scott Konersmann" },
{ "description", "Task 3 for Scott Konersmann description" }
},
} }
},
new JObject {
{ "firstname", "Robert" },
{ "lastname", "Lyon (sample)" },
{ "jobtitle", "Senior Technician" },
{ "annualincome", 78000 },
{ "Contact_Tasks", new JArray {
new JObject {
{ "subject", "Task 1 for Robert Lyon" },
{ "description", "Task 1 for Robert Lyon description" }
},
new JObject {
{ "subject", "Task 2 for Robert Lyon" },
{ "description", "Task 2 for Robert Lyon description" }
},
new JObject {
{ "subject", "Task 3 for Robert Lyon" },
{ "description", "Task 3 for Robert Lyon description" }
},
} }
},
new JObject {
{ "firstname", "Paul" },
{ "lastname", "Cannon (sample)" },
{ "jobtitle", "Ski Instructor" },
{ "annualincome", 68500 },
{ "Contact_Tasks", new JArray {
new JObject {
{ "subject", "Task 1 for Paul Cannon" },
{ "description", "Task 1 for Paul Cannon description" }
},
new JObject {
{ "subject", "Task 2 for Paul Cannon" },
{ "description", "Task 2 for Paul Cannon description" }
},
new JObject {
{ "subject", "Task 3 for Paul Cannon" },
{ "description", "Task 3 for Paul Cannon description" }
},
} }
},
new JObject {
{ "firstname", "Rene" },
{ "lastname", "Valdes (sample)" },
{ "jobtitle", "Data Analyst III" },
{ "annualincome", 86000 },
{ "Contact_Tasks", new JArray {
new JObject {
{ "subject", "Task 1 for Rene Valdes" },
{ "description", "Task 1 for Rene Valdes description" }
},
new JObject {
{ "subject", "Task 2 for Rene Valdes" },
{ "description", "Task 2 for Rene Valdes description" }
},
new JObject {
{ "subject", "Task 3 for Rene Valdes" },
{ "description", "Task 3 for Rene Valdes description" }
},
} }
},
new JObject {
{ "firstname", "Jim" },
{ "lastname", "Glynn (sample)" },
{ "jobtitle", "Senior International Sales Manager" },
{ "annualincome", 81400 },
{ "Contact_Tasks", new JArray {
new JObject {
{ "subject", "Task 1 for Jim Glynn" },
{ "description", "Task 1 for Jim Glynn description" }
},
new JObject {
{ "subject", "Task 2 for Jim Glynn" },
{ "description", "Task 2 for Jim Glynn description" }
},
new JObject {
{ "subject", "Task 3 for Jim Glynn" },
{ "description", "Task 3 for Jim Glynn description" }
},
} }
}
} }
};
account1Uri = svc.PostCreate("accounts", account1);
entityUris.Add(account1Uri);
contact1Uri = new Uri((svc.Get($"{account1Uri}/primarycontactid/$ref"))["@odata.id"].ToString());
entityUris.Add(contact1Uri);
var contact_customer_accounts = svc.Get($"{account1Uri}/contact_customer_accounts/$ref");
foreach (JObject contactRef in contact_customer_accounts["value"])
{
//Add to the top of the list so these are deleted first
entityUris.Insert(0, new Uri(contactRef["@odata.id"].ToString()));
}
//The related tasks will be deleted automatically when the owning record is deleted.
Console.WriteLine("Account 'Contoso, Ltd. (sample)' created with 1 primary " +
"contact and 8 associated contacts.");
}
/// <summary>
/// Runs the sample
/// </summary>
/// <param name="svc">Service containing methods to use the Web API.</param>
/// <param name="deleteCreatedRecords">Whether to delete entities create by this sample.</param>
public static void Run(CDSWebApiService svc, bool deleteCreatedRecords)
{
Console.WriteLine("\n--Starting Query Data --");
//Create the records that this sample will query.
CreateRequiredRecords(svc);
//Get the id and name of the account created to use as a filter.
var contoso = svc.Get($"{account1Uri}?$select=accountid,name");
var contosoId = Guid.Parse(contoso["accountid"].ToString());
string contosoName = (string)contoso["name"];
#region Selecting specific properties
// Basic query: Query using $select against a contact entity to get the properties you want.
// For performance best practice, always use $select, otherwise all properties are returned
Console.WriteLine("-- Basic Query --");
//Header required to include formatted values
var formattedValueHeaders = new Dictionary <string, List <string> > {
{ "Prefer", new List <string>
{
"odata.include-annotations=\"OData.Community.Display.V1.FormattedValue\""
} }
};
var contact1 = svc.Get(
$"{contact1Uri}?$select=fullname,jobtitle,annualincome",
formattedValueHeaders);
Console.WriteLine($"Contact basic info:\n" +
$"\tFullname: {contact1["fullname"]}\n" +
$"\tJobtitle: {contact1["jobtitle"]}\n" +
$"\tAnnualincome (unformatted): {contact1["annualincome"]} \n" +
$"\tAnnualincome (formatted): {contact1["*****@*****.**"]} \n");
#endregion Selecting specific properties
#region Using query functions
// Filter criteria:
// Applying filters to get targeted data.
// 1) Using standard query functions (e.g.: contains, endswith, startswith)
// 2) Using CDS query functions (e.g.: LastXhours, Last7Days, Today, Between, In, ...)
// 3) Using filter operators and logical operators (e.g.: eq, ne, gt, and, or, etc…)
// 4) Set precedence using parenthesis (e.g.: ((criteria1) and (criteria2)) or (criteria3)
// For more info, see:
//https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/query-data-web-api#filter-results
Console.WriteLine("-- Filter Criteria --");
//Filter 1: Using standard query functions to filter results. In this operation, we
//will query for all contacts with fullname containing the string "(sample)".
var containsSampleinFullNameCollection = svc.Get("contacts?" +
"$select=fullname,jobtitle,annualincome&" +
"$filter=contains(fullname,'(sample)') and " +
$"_parentcustomerid_value eq {contosoId.ToString()}",
formattedValueHeaders);
WriteContactResultsTable(
"Contacts filtered by fullname containing '(sample)':",
containsSampleinFullNameCollection["value"]);
//Filter 2: Using CDS query functions to filter results. In this operation, we will query
//for all contacts that were created in the last hour. For complete list of CDS query
//functions, see: https://docs.microsoft.com/dynamics365/customer-engagement/web-api/queryfunctions
var createdInLastHourCollection = svc.Get("contacts?" +
"$select=fullname,jobtitle,annualincome&" +
"$filter=Microsoft.Dynamics.CRM.LastXHours(PropertyName='createdon',PropertyValue='1') and " +
$"_parentcustomerid_value eq {contosoId.ToString()}",
formattedValueHeaders);
WriteContactResultsTable(
"Contacts that were created within the last 1hr:",
createdInLastHourCollection["value"]);
//Filter 3: Using operators. Building on the previous operation, we further limit
//the results by the contact's income. For more info on standard filter operators,
//https://docs.microsoft.com/powerapps/developer/common-data-service/webapi/query-data-web-api#filter-results
var highIncomeContacts = svc.Get("contacts?" +
"$select=fullname,jobtitle,annualincome&" +
"$filter=contains(fullname,'(sample)') and " +
"annualincome gt 55000 and " +
$"_parentcustomerid_value eq {contosoId.ToString()}",
formattedValueHeaders);
WriteContactResultsTable(
"Contacts with '(sample)' in name and income above $55,000:",
highIncomeContacts["value"]);
//Filter 4: Set precedence using parentheses. Continue building on the previous
//operation, we further limit results by job title. Parentheses and the order of
//filter statements can impact results returned.
var seniorOrSpecialistsCollection = svc.Get("contacts?" +
"$select=fullname,jobtitle,annualincome&" +
"$filter=contains(fullname,'(sample)') and " +
"(contains(jobtitle, 'senior') or " +
"contains(jobtitle,'manager')) and " +
"annualincome gt 55000 and " +
$"_parentcustomerid_value eq {contosoId.ToString()}",
formattedValueHeaders);
WriteContactResultsTable(
"Contacts with '(sample)' in name senior jobtitle or high income:",
seniorOrSpecialistsCollection["value"]);
#endregion Using query functions
#region Ordering and aliases
//Results can be ordered in descending or ascending order.
Console.WriteLine("\n-- Order Results --");
var orderedResults = svc.Get("contacts?" +
"$select=fullname,jobtitle,annualincome&" +
"$filter=contains(fullname,'(sample)')and " +
$"_parentcustomerid_value eq {contosoId.ToString()}&" +
"$orderby=jobtitle asc, annualincome desc",
formattedValueHeaders);
WriteContactResultsTable(
"Contacts ordered by jobtitle (Ascending) and annualincome (descending)",
orderedResults["value"]);
//Parameterized aliases can be used as parameters in a query. These parameters can be used
//in $filter and $orderby options. Using the previous operation as basis, parameterizing the
//query will give us the same results. For more info, see:
//https://docs.microsoft.com/powerapps/developer/common-data-service/webapi/use-web-api-functions#passing-parameters-to-a-function
Console.WriteLine("\n-- Parameterized Aliases --");
var orderedResultsWithParams = svc.Get("contacts?" +
"$select=fullname,jobtitle,annualincome&" +
"$filter=contains(@p1,'(sample)') and " +
"@p2 eq @p3&" +
"$orderby=@p4 asc, @p5 desc&" +
"@p1=fullname&" +
"@p2=_parentcustomerid_value&" +
$"@p3={contosoId.ToString()}&" +
"@p4=jobtitle&" +
"@p5=annualincome",
formattedValueHeaders);
WriteContactResultsTable(
"Contacts ordered by jobtitle (Ascending) and annualincome (descending)",
orderedResultsWithParams["value"]);
#endregion Ordering and aliases
#region Limit results
//To limit records returned, use the $top query option. Specifying a limit number for $top
//returns at most that number of results per request. Extra results are ignored.
//For more information, see:
// https://docs.microsoft.com/powerapps/developer/common-data-service/webapi/query-data-web-api#use-top-query-option
Console.WriteLine("\n-- Top Results --");
var topFive = svc.Get("contacts?" +
"$select=fullname,jobtitle,annualincome&" +
"$filter=contains(fullname,'(sample)') and " +
$"_parentcustomerid_value eq {contosoId.ToString()}&" +
"$top=5",
formattedValueHeaders);
WriteContactResultsTable("Contacts top 5 results:", topFive["value"]);
//Result count - count the number of results matching the filter criteria.
//Tip: Use count together with the "odata.maxpagesize" to calculate the number of pages in
//the query. Note: CDS has a max record limit of 5000 records per response.
Console.WriteLine("\n-- Result Count --");
//1) Get a count of a collection without the data.
var count = svc.Get($"contacts/$count");
Console.WriteLine($"\nThe contacts collection has {count} contacts.");
// 2) Get a count along with the data.
var countWithData = svc.Get("contacts?" +
"$select=fullname,jobtitle,annualincome&" +
"$filter=(contains(jobtitle,'senior') or contains(jobtitle, 'manager')) and " +
$"_parentcustomerid_value eq {contosoId.ToString()}" +
"&$count=true",
formattedValueHeaders);
WriteContactResultsTable($"{countWithData["@odata.count"]} " +
$"Contacts with 'senior' or 'manager' in job title:",
countWithData["value"]);
#endregion Limit results
#region Expanding results
//The expand option retrieves related information.
//To retrieve information on associated entities in the same request, use the $expand
//query option on navigation properties.
// 1) Expand using single-valued navigation properties (e.g.: via the 'primarycontactid')
// 2) Expand using partner property (e.g.: from contact to account via the 'account_primary_contact')
// 3) Expand using collection-valued navigation properties (e.g.: via the 'contact_customer_accounts')
// 4) Expand using multiple navigation property types in a single request.
// Note: Expansions can only go 1 level deep.
// Tip: For performance best practice, always use $select statement in an expand option.
Console.WriteLine("\n-- Expanding Results --");
//1) Expand using the 'primarycontactid' single-valued navigation property of account1.
var account1 = svc.Get($"{account1Uri}?" +
"$select=name&" +
"$expand=primarycontactid($select=fullname,jobtitle,annualincome)");
Console.WriteLine($"\nAccount {account1["name"]} has the following primary contact person:\n" +
$"\tFullname: {account1["primarycontactid"]["fullname"]} \n" +
$"\tJobtitle: {account1["primarycontactid"]["jobtitle"]} \n" +
$"\tAnnualincome: { account1["primarycontactid"]["annualincome"]}");
//2) Expand using the 'account_primary_contact' partner property.
var contact2 = svc.Get($"{contact1Uri}?$select=fullname,jobtitle,annualincome&" +
"$expand=account_primary_contact($select=name)");
Console.WriteLine($"\nContact '{contact2["fullname"]}' is the primary contact for the following accounts:");
foreach (JObject account in contact2["account_primary_contact"])
{
Console.WriteLine($"\t{account["name"]}");
}
//3) Expand using the collection-valued 'contact_customer_accounts' navigation property.
var account2 = svc.Get($"{account1Uri}?" +
"$select=name&" +
"$expand=contact_customer_accounts($select=fullname,jobtitle,annualincome)",
formattedValueHeaders);
WriteContactResultsTable(
$"Account '{account2["name"]}' has the following contact customers:",
account2["contact_customer_accounts"]);
//4) Expand using multiple navigation property types in a single request, specifically:
// primarycontactid, contact_customer_accounts, and Account_Tasks.
Console.WriteLine("\n-- Expanding multiple property types in one request -- ");
var account3 = svc.Get($"{account1Uri}?$select=name&" +
"$expand=primarycontactid($select=fullname,jobtitle,annualincome)," +
"contact_customer_accounts($select=fullname,jobtitle,annualincome)," +
"Account_Tasks($select=subject,description)",
formattedValueHeaders);
Console.WriteLine($"\nAccount {account3["name"]} has the following primary contact person:\n" +
$"\tFullname: {account3["primarycontactid"]["fullname"]} \n" +
$"\tJobtitle: {account3["primarycontactid"]["jobtitle"]} \n" +
$"\tAnnualincome: {account3["primarycontactid"]["annualincome"]}");
WriteContactResultsTable(
$"Account '{account3["name"]}' has the following contact customers:",
account3["contact_customer_accounts"]);
Console.WriteLine($"\nAccount '{account3["name"] }' has the following tasks:");
foreach (JObject task in account3["Account_Tasks"])
{
Console.WriteLine($"\t{task["subject"]}");
}
#endregion Expanding results
#region FetchXML queries
//Use FetchXML to query for all contacts whose fullname contains '(sample)'.
//Note: XML string must be URI encoded. For more information, see:
//https://docs.microsoft.com/powerapps/developer/common-data-service/webapi/retrieve-and-execute-predefined-queries#use-custom-fetchxml
Console.WriteLine("\n-- FetchXML -- ");
string fetchXmlQuery =
"<fetch mapping='logical' output-format='xml-platform' version='1.0' distinct='false'>" +
"<entity name ='contact'>" +
"<attribute name ='fullname' />" +
"<attribute name ='jobtitle' />" +
"<attribute name ='annualincome' />" +
"<order descending ='true' attribute='fullname' />" +
"<filter type ='and'>" +
"<condition value ='%(sample)%' attribute='fullname' operator='like' />" +
$"<condition value ='{contosoId.ToString()}' attribute='parentcustomerid' operator='eq' />" +
"</filter>" +
"</entity>" +
"</fetch>";
var contacts = svc.Get(
$"contacts?fetchXml={WebUtility.UrlEncode(fetchXmlQuery)}",
formattedValueHeaders);
WriteContactResultsTable($"Contacts Fetched by fullname containing '(sample)':", contacts["value"]);
#region Sending FetchXml as $batch
// Because FetchXml is passed in the Uri and the total length
// may exceed the max length for Uri. $batch allows the request to be included
// in the body rather than in the Uri.
var contactsFromBatch = SendGetAsBatch(
svc,
$"contacts?fetchXml={WebUtility.UrlEncode(fetchXmlQuery)}",
new Dictionary <string, string> {
{ "Prefer", "odata.include-annotations=\"OData.Community.Display.V1.FormattedValue\"" }
}
);
WriteContactResultsTable($"Contacts Fetched by fullname containing '(sample) in $batch':", contactsFromBatch["value"]);
#endregion Sending FetchXml as $batch
#endregion FetchXML queries
#region Using predefined queries
//Use predefined queries of the following two types:
// 1) Saved query (system view)
// 2) User query (saved view)
//For more info, see:
//https://docs.microsoft.com/powerapps/developer/common-data-service/webapi/retrieve-and-execute-predefined-queries#predefined-queries
//1) Saved Query - retrieve "Active Accounts", run it, then display the results.
Console.WriteLine("\n-- Saved Query -- ");
var savedqueryid = svc.Get("savedqueries?" +
"$select=name,savedqueryid&" +
"$filter=name eq 'Active Accounts'")["value"][0]["savedqueryid"];
var activeAccounts = svc.Get(
$"accounts?savedQuery={savedqueryid}",
formattedValueHeaders)["value"] as JArray;
DisplayFormattedEntities("\nActive Accounts", activeAccounts, new string[] { "name" });
//2) Create a user query, then retrieve and execute it to display its results.
//For more info, see:
//https://docs.microsoft.com/powerapps/developer/common-data-service/saved-queries
Console.WriteLine("\n-- User Query -- ");
var userQuery = new JObject
{
["name"] = "My User Query",
["description"] = "User query to display contact info.",
["querytype"] = 0,
["returnedtypecode"] = "contact",
["fetchxml"] = @"<fetch mapping='logical' output-format='xml-platform' version='1.0' distinct='false'>
<entity name ='contact'>
<attribute name ='fullname' />
<attribute name ='contactid' />
<attribute name ='jobtitle' />
<attribute name ='annualincome' />
<order descending ='false' attribute='fullname' />
<filter type ='and'>
<condition value ='%(sample)%' attribute='fullname' operator='like' />
<condition value ='%Manager%' attribute='jobtitle' operator='like' />
<condition value ='55000' attribute='annualincome' operator='gt' />
</filter>
</entity>
</fetch>"
};
//Create the saved query
var myUserQueryUri = svc.PostCreate("userqueries", userQuery);
entityUris.Add(myUserQueryUri); //To delete later
//Retrieve the userqueryid
var myUserQueryId = svc.Get($"{myUserQueryUri}/userqueryid")["value"];
//Use the query to return results:
var myUserQueryResults = svc.Get($"contacts?userQuery={myUserQueryId}", formattedValueHeaders)["value"];
WriteContactResultsTable($"Contacts Fetched by My User Query:", myUserQueryResults);
#endregion Using predefined queries
DeleteRequiredRecords(svc, deleteCreatedRecords);
Console.WriteLine("\n--Query Data Completed--");
}
public static void Run(CDSWebApiService svc, bool deleteCreatedRecords)
{
Console.WriteLine("--Starting BatchOperations sample--");
//Create an account outside of the batch to show linking.
var parentAccount = new JObject
{
{ "name", "Parent Account" }
};
Uri accountUri = svc.PostCreate("accounts", parentAccount);
// Not technically necessary to convert to a relative Uri, but
// it does make the JSON passed more readable in Fiddler
var relativeAccountUri = svc.BaseAddress.MakeRelativeUri(accountUri);
entityUris.Add(relativeAccountUri);
Console.WriteLine("\nCreated an account record before the batch operation.");
//Create a BatchChangeSet to include operations within the batch
var changeset = new BatchChangeSet();
//Compose the body for the first contact.
var contact1 = new JObject
{
{ "firstname", "Joe" },
{ "lastname", "Smith" },
{ "accountrolecode", 1 }, //Decision Maker
{ "jobtitle", "Manager" },
//Related to the account
{ "*****@*****.**", relativeAccountUri.ToString() }
};
//Add a request to the changeset.
changeset.Requests.Add(
new HttpRequestMessage(HttpMethod.Post, "contacts")
{
Content = new StringContent(contact1.ToString())
}
);
//Compose the body for the second contact.
var contact2 = new JObject
{
{ "firstname", "Jack" },
{ "lastname", "Jones" },
{ "accountrolecode", 2 }, //Employee
{ "jobtitle", "Assistant" },
//Related to first contact in batch
{ "*****@*****.**", "$1" },
//Related to the account
{ "*****@*****.**", relativeAccountUri.ToString() }
};
//Add another request to the changeset.
changeset.Requests.Add(
new HttpRequestMessage(HttpMethod.Post, "contacts")
{
Content = new StringContent(contact2.ToString())
}
);
//Add the changeset to the list of BatchItems
var items = new List <BatchItem>()
{
changeset
};
var includeAnnotationsHeader = new Dictionary <string, string>();
//Adding annotations to show formatted values
includeAnnotationsHeader.Add("Prefer", "odata.include-annotations=\"*\"");
var query = "?$select=name&$expand=contact_customer_accounts($select=fullname,accountrolecode)";
var getRequest = new BatchGetRequest()
{
Path = $"{relativeAccountUri.ToString()}{query}",
Headers = includeAnnotationsHeader
};
//Add the GET request to the list of BatchItems
items.Add(getRequest);
//Send the batch request.
var responses = svc.PostBatch(items);
Console.WriteLine("\nCreated these contact records in the batch:");
responses.ForEach(x => {
if (x.Headers.Contains("OData-EntityId"))
{
var contactRelativeUri = svc.BaseAddress.MakeRelativeUri(new Uri(x.Headers.GetValues("OData-EntityId").FirstOrDefault()));
Console.WriteLine($"\tContact: {contactRelativeUri.ToString()}");
}
});
Console.WriteLine("\nInformation about the Account retrieved in the batch:");
Console.WriteLine(JObject.Parse(responses[2].Content.ReadAsStringAsync().Result));
if (deleteCreatedRecords)
{
svc.Delete(relativeAccountUri);
Console.WriteLine("\nThe account created for this sample was deleted and the related contacts with it.");
}
Console.WriteLine("--BatchOperations sample complete--");
}
public static void Run(CDSWebApiService svc)
{
Console.WriteLine("\t--Starting Conditional Operations--");
/// <summary> Creates the CRM entity instance used by this sample. </summary>
#region Create sample Account record
// Create a CRM account record.
Console.WriteLine("\nCreate sample data");
var account = new JObject
{
{ "name", "Contoso Ltd" },
{ "telephone1", "555-0000" }, //Phone number value will increment with each update attempt
{ "revenue", 5000000 },
{ "description", "Parent company of Contoso Pharmaceuticals, etc." }
};
var accountUri = svc.PostCreate("accounts", account);
Console.WriteLine("Account entity created:");
//Retrieve the account record you created to access the ETag value
account = svc.Get($"{accountUri}?$select=name,revenue,telephone1,description") as JObject;
var initialAcctETagVal = account["@odata.etag"].ToString();
var updatedAcctETagVal = string.Empty;
Console.WriteLine($"ETag value: {initialAcctETagVal}");
#endregion Create sample Account record
#region Conditional GET
Console.WriteLine("\n--Conditional GET section started--");
var IfNoneMatchHeader = new Dictionary <string, List <string> >
{
{ "If-None-Match", new List <string> {
initialAcctETagVal
} }
};
// Retrieve only if it doesn't match previously retrieved version.
var result = svc.Get($"{accountUri}?$select=name,revenue,telephone1,description", IfNoneMatchHeader);
if (result == null)
{
Console.WriteLine("Expected outcome: Entity was not modified so nothing was returned.");
}
else
{
Console.WriteLine("Unexpected outcome: Entity was not modified so nothing should be returned.");
}
// Modify the account instance by updating telephone1
svc.Put(accountUri, "telephone1", "555-0001");
Console.WriteLine("\nAccount telephone number updated.");
// Re-Attempt conditional GET with original initialAcctETagVal ETag value
result = svc.Get($"{accountUri}?$select=name", IfNoneMatchHeader);
if (result == null)
{
Console.WriteLine("Unexpected outcome: Entity was modified so something should be returned.");
}
else
{
Console.WriteLine("Expected outcome: Entity was modified so something was returned.");
}
#endregion Conditional GET
#region Optimistic concurrency on delete and update
Console.WriteLine("\n--Optimistic concurrency section started--");
// Attempt to delete original account (if matches original initialAcctETagVal ETag value).
var IfMatchHeader = new Dictionary <string, List <string> >
{
{ "If-Match", new List <string> {
initialAcctETagVal
} }
};
try
{
svc.Delete(accountUri, IfMatchHeader);
}
catch (CDSWebApiException ex)
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"Expected Error: {ex.Message}\n" +
$"\tAccount not deleted using the initial ETag value: {initialAcctETagVal}\n" +
$"\tStatusCode: {ex.StatusCode}\n" +
$"\tReasonPhrase: {ex.ReasonPhrase}");
Console.ResetColor();
}
//Attempt to update account (if matches original ETag value).
JObject accountUpdate = new JObject
{
{ "telephone1", "555-0002" },
{ "revenue", 6000000 }
};
try
{
svc.Patch(accountUri, accountUpdate, IfMatchHeader);
}
catch (CDSWebApiException ex)
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"Expected Error: {ex.Message}\n" +
$"\tAccount not updated using the initial ETag value: {initialAcctETagVal}\n" +
$"\tStatusCode: {ex.StatusCode}\n" +
$"\tReasonPhrase: {ex.ReasonPhrase}");
Console.ResetColor();
}
//Get current ETag value:
updatedAcctETagVal = svc.Get($"{accountUri}?$select=accountid")["@odata.etag"].ToString();
// Reattempt update if matches current ETag value.
var NewIfMatchHeader = new Dictionary <string, List <string> >
{
{ "If-Match", new List <string> {
updatedAcctETagVal
} }
};
svc.Patch(accountUri, accountUpdate, NewIfMatchHeader);
Console.WriteLine($"\nAccount successfully updated using ETag: {updatedAcctETagVal}");
// Retrieve and output current account state.
account = svc.Get($"{accountUri}?$select=name,revenue,telephone1,description") as JObject;
Console.WriteLine(account.ToString(Formatting.Indented));
// Delete the account record
svc.Delete(accountUri);
Console.WriteLine("Account Deleted.");
//Verify that it now longer exists
try
{
svc.Get($"{accountUri}?$select=name,revenue,telephone1,description");
}
catch (CDSWebApiException ex)
{
if (ex.StatusCode.Equals(404))
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"Expected Error: {ex.Message}\n" +
$"\tVerified that Account does not exist.\n" +
$"\tStatusCode: {ex.StatusCode}\n" +
$"\tReasonPhrase: {ex.ReasonPhrase}");
Console.ResetColor();
}
else
{
throw ex;
}
}
#endregion Optimistic concurrency on delete and update
#region Controlling upsert operations
Console.WriteLine("\n--Controlling upsert operations section started--");
// Attempt to update it only if it exists
accountUpdate["telephone1"] = "555-0006";
var IfMatchAnyHeader = new Dictionary <string, List <string> >
{
{ "If-Match", new List <string> {
"*"
} }
};
try
{
svc.Patch(accountUri, accountUpdate, IfMatchAnyHeader);
}
catch (CDSWebApiException ex)
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"Expected Error: {ex.Message}\n" +
$"\tAccount not updated because it does not exist.\n" +
$"\tStatusCode: {ex.StatusCode}\n" +
$"\tReasonPhrase: {ex.ReasonPhrase}");
Console.ResetColor();
}
//Attempt to upsert to re-create the record that was deleted
// as long as there are no existing account records with the same id.
var IfNoneMatchAnyHeader = new Dictionary <string, List <string> >
{
{ "If-None-Match", new List <string> {
"*"
} }
};
//Remove any lookup properties since they cannot be set.
account.Remove("_transactioncurrencyid_value");
svc.Patch(accountUri, account, IfNoneMatchAnyHeader);
Console.WriteLine("Account upserted.");
//Verify that it now exists again with same id
try
{
svc.Get($"{accountUri}?$select=name");
Console.WriteLine($"Verified that account with accountid '{account["accountid"]}' exists.");
}
catch (CDSWebApiException ex)
{
if (ex.StatusCode.Equals(404))
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine($"Unexpected Error: {ex.Message}\n" +
$"\tThe account does not exist.\n" +
$"\tStatusCode: {ex.StatusCode}\n" +
$"\tReasonPhrase: {ex.ReasonPhrase}");
Console.ResetColor();
}
else
{
throw ex;
}
}
#endregion Controlling upsert operations
#region Clean-up
//Delete the account record for good
svc.Delete(accountUri);
#endregion Clean-up
Console.WriteLine("\t--Conditional Operations Completed--");
}
private static void CreateRequiredRecords(CDSWebApiService svc)
{
//Create a parent account, an associated incident with three associated tasks
//(required for CalculateTotalTimeIncident).
Console.WriteLine("----Creating Required Records---- -");
JObject account1 = new JObject(new JProperty("name", "Fourth Coffee"));
var account1Uri = svc.PostCreate("accounts", account1);
entityUris.Add(account1Uri);
JObject incident1 = new JObject
(
new JProperty("title", "Sample Case"),
new JProperty("*****@*****.**", account1Uri),
new JProperty("Incident_Tasks",
new JArray(
new JObject(
new JProperty("subject", "Task 1"),
new JProperty("actualdurationminutes", 30)
),
new JObject(
new JProperty("subject", "Task 2"),
new JProperty("actualdurationminutes", 30)
),
new JObject(
new JProperty("subject", "Task 3"),
new JProperty("actualdurationminutes", 30)
))));
incident1Uri = svc.PostCreate("incidents", incident1);
Console.WriteLine("Incident and related tasks are Created");
//Close the associated tasks (so that they represent completed work).
JObject completeCode = new JObject(
new JProperty("statecode", 1),
new JProperty("statuscode", 5));
var incidentTaskRefs = svc.Get($"{incident1Uri}/Incident_Tasks/$ref");
foreach (JObject obj in incidentTaskRefs["value"])
{
svc.Patch(new Uri(obj["@odata.id"].ToString()), completeCode);
}
Console.WriteLine("Tasks are closed.");
//Create another account and associated opportunity (required for CloseOpportunityAsWon).
JObject account2 = new JObject(
new JProperty("name", "Coho Winery"),
new JProperty("opportunity_customer_accounts",
new JArray(
new JObject(
new JProperty("name", "Opportunity to win")
))));
var account2Uri = svc.PostCreate("accounts", account2);
entityUris.Add(account2Uri);
Console.WriteLine("Another Account is created with associated Opportunity");
//Retrieve the URI to the opportunity.
var custOpporRefs = svc.Get($"{account2Uri}/opportunity_customer_accounts/$ref");
opportunity1Uri = new Uri(custOpporRefs["value"][0]["@odata.id"].ToString());
//Create a contact to use with custom action sample_AddNoteToContact
var contact1 = new JObject(
new JProperty("firstname", "Jon"),
new JProperty("lastname", "Fogg"));
contact1Uri = svc.PostCreate("contacts", contact1);
Console.WriteLine("Contact record is created");
entityUris.Add(contact1Uri);
}
public static void Run(CDSWebApiService svc, bool deleteCreatedRecords)
{
/*
* Sample Goals
* - Use ColumnSet to specify attributes
* - Specify whether formatted values should be returned?
* - Get a count of how many records are returned
*
* - Apply Conditions in a query https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/org-service/use-conditionexpression-class
* - Apply multiple conditions in a Filter Expression
* - Page large result sets
* - Page results with a cookie?
* - Use a left outer join in QueryExpression to query for records "not in" https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/org-service/use-left-outer-join-queryexpression-query-records-not-in
* - Show how to use aggregates and grouping
* - Send as $batch
*/
Console.WriteLine("\n--Starting Query Data with Query Expression--");
CreateRequiredRecords(svc);
//Get the id and name of the account created to use as a filter.
var account1 = svc.Get($"{account1Uri}?$select=accountid,name");
var account1Id = Guid.Parse(account1["accountid"].ToString());
string account1Name = (string)account1["name"];
//Header required to include formatted values
var formattedValueHeaders = new Dictionary <string, List <string> > {
{ "Prefer", new List <string>
{
"odata.include-annotations=\"OData.Community.Display.V1.FormattedValue\""
} }
};
#region Use ColumnSet to specify attributes
var query1 = new QueryExpression("contact")
{
ColumnSet = new ColumnSet("fullname", "jobtitle", "annualincome")
};
query1.Criteria.AddCondition("parentcustomerid", ConditionOperator.Equal, account1Id);
var query1Results = svc.Get(
$"contacts?queryExpression={query1.ToJSON()}",
formattedValueHeaders);
//Output results in formatted table
WriteContactResultsTable(
$"{((JContainer)query1Results["value"]).Count} Contacts related to the account '{account1Name}'",
query1Results["value"]);
#endregion Use ColumnSet to specify attributes
#region Use PagingInfo
//Re-use the previously defined query1
query1.PageInfo = new PagingInfo()
{
PageNumber = 1,
Count = 4,
ReturnTotalRecordCount = true
//Bug 1686048: Unable to return count when using QueryExpression with Web API
};
var top4contactsRelatedToAccount1 = svc.Get(
$"contacts?queryExpression={query1.ToJSON()}",
formattedValueHeaders);
//Output results in formatted table
WriteContactResultsTable(
$"Top 4 Contacts related to the account '{account1Name}'",
top4contactsRelatedToAccount1["value"]);
//Get next 4 contacts
// You can't use the @odata.nextLink property
// Bug 1686046: Error: 'The query parameter queryExpression is not supported' when using @odata.nextlink returned from Web API QueryExpression response
//var next4contactsRelatedToAccount1 = svc.Get(
// top4contactsRelatedToAccount1["@odata.nextLink"].ToString(),
// formattedValueHeaders);
//Update the PageNumber to get the next set of result
query1.PageInfo.PageNumber = 2;
var next4contactsRelatedToAccount1 = svc.Get(
$"contacts?queryExpression={query1.ToJSON()}",
formattedValueHeaders);
//Output results in formatted table
WriteContactResultsTable(
$"Next 4 Contacts related to the account '{account1Name}'",
next4contactsRelatedToAccount1["value"]);
#endregion Use PagingInfo
#region Apply multiple conditions in a Filter Expression
var query2 = new QueryExpression("contact")
{
ColumnSet = new ColumnSet("fullname", "jobtitle", "annualincome")
};
//By default all criteria are evaluated using LogicalOperator.And
query2.Criteria.AddCondition("parentcustomerid", ConditionOperator.Equal, account1Id);
query2.Criteria.AddCondition("createdon", ConditionOperator.LastXHours, "1");
var contactsRelatedToAccount1CreatedInLastHour = svc.Get(
$"contacts?queryExpression={query2.ToJSON()}",
formattedValueHeaders);
//Output results in formatted table
WriteContactResultsTable(
$"Contacts created in the past hour related to '{account1Name}'",
contactsRelatedToAccount1CreatedInLastHour["value"]);
var query3 = new QueryExpression("contact")
{
ColumnSet = new ColumnSet("fullname", "jobtitle", "annualincome")
};
query3.Criteria.AddCondition("parentcustomerid", ConditionOperator.Equal, account1Id);
query3.Criteria.AddCondition("annualincome", ConditionOperator.GreaterThan, "55000");
FilterExpression childFilter = query3.Criteria.AddFilter(LogicalOperator.Or);
childFilter.AddCondition("jobtitle", ConditionOperator.Like, "%senior%");
childFilter.AddCondition("jobtitle", ConditionOperator.Like, "%manager%");
var highValueContacts = svc.Get(
$"contacts?queryExpression={query3.ToJSON()}",
formattedValueHeaders);
//Output results in formatted table
WriteContactResultsTable(
$"High salary contacts with 'senior' or 'manager' in job title:",
highValueContacts["value"]);
#endregion Apply multiple conditions in a Filter Expression
#region Link Entities
var query4 = new QueryExpression("task")
{
ColumnSet = new ColumnSet("subject", "description"),
LinkEntities =
{
new LinkEntity()
{
Columns = new ColumnSet("fullname"),
EntityAlias = "c",
LinkFromAttributeName = "regardingobjectid",
LinkFromEntityName = "task",
LinkToAttributeName = "contactid",
LinkToEntityName = "contact",
LinkCriteria = new FilterExpression()
{
Conditions =
{
new ConditionExpression("parentcustomerid", ConditionOperator.Equal, account1Id)
}
}
}
}
};
var tasksLinkedToContact = svc.Get(
$"tasks?queryExpression={query4.ToJSON()}",
formattedValueHeaders);
//Output results in formatted table
//Bug 1686153: Web API QueryExpression Link Entity response alias separator is '_x002e_' rather than '.'
WriteTaskResultsTable("Tasks with linked Contacts", tasksLinkedToContact["value"], "c_x002e_fullname", "subject");
var query4UsingFetchXml = $@"<fetch>
<entity name='task' >
<attribute name='description' />
<attribute name='subject' />
<link-entity name='contact' from='contactid' to='regardingobjectid' alias='c' >
<attribute name='fullname' />
<filter>
<condition attribute='parentcustomerid' operator='eq' value='{account1Id}' />
</filter>
</link-entity>
</entity>
</fetch>";
var tasksLinkedToContactFetch = svc.Get(
$"tasks?fetchXml={WebUtility.UrlEncode(query4UsingFetchXml)}",
formattedValueHeaders);
//Output results in formatted table
WriteTaskResultsTable(
"Tasks with linked Contacts using FetchXMl",
tasksLinkedToContactFetch["value"], "c.fullname", "subject");
#endregion Link Entities
#region Apply Aggregation and grouping
#region Aggregate income data
string avg_income_alias = "avg_income";
string sum_income_alias = "sum_income";
string max_income_alias = "max_income";
string min_income_alias = "min_income";
var query5 = new QueryExpression("contact")
{
ColumnSet = new ColumnSet("annualincome")
{
AttributeExpressions =
{
new XrmAttributeExpression("annualincome", XrmAggregateType.Avg, avg_income_alias),
new XrmAttributeExpression("annualincome", XrmAggregateType.Sum, sum_income_alias),
new XrmAttributeExpression("annualincome", XrmAggregateType.Max, max_income_alias),
new XrmAttributeExpression("annualincome", XrmAggregateType.Min, min_income_alias)
}
}
};
query5.Criteria.AddCondition("parentcustomerid", ConditionOperator.Equal, account1Id);
var avgIncome = svc.Get(
$"contacts?queryExpression={query5.ToJSON()}",
formattedValueHeaders);
var data = avgIncome["value"][0];
string formattedSumIncome = (string)data
[$"{sum_income_alias}@OData.Community.Display.V1.FormattedValue"];
string formattedAvgIncome = (string)data
[$"{avg_income_alias}@OData.Community.Display.V1.FormattedValue"];
string formattedMaxIncome = (string)data
[$"{max_income_alias}@OData.Community.Display.V1.FormattedValue"];
string formattedMinIncome = (string)data
[$"{min_income_alias}@OData.Community.Display.V1.FormattedValue"];
Console.WriteLine($"\nIncome information for employees at'{account1Name}':");
Console.WriteLine($"\tSum:\t{formattedSumIncome} ");
Console.WriteLine($"\tAverage:{formattedAvgIncome} ");
Console.WriteLine($"\tMax:\t{formattedMaxIncome} ");
Console.WriteLine($"\tMin:\t{formattedMinIncome} ");
#endregion Aggregate income data
#endregion Apply Aggregation and grouping
DeleteRequiredRecords(svc, deleteCreatedRecords);
Console.WriteLine("\n--Query Data with Query Expression Completed--");
}
public static void Run(CDSWebApiService svc, bool deleteCreatedRecords)
{
Console.WriteLine("--Starting Basic Operations--");
#region Section 1: Basic Create and Update operations
Console.WriteLine("--Section 1 started--");
//Create a contact
var contact1 = new JObject
{
{ "firstname", "Rafel" },
{ "lastname", "Shillo" }
};
Uri contact1Uri = svc.PostCreate("contacts", contact1);
Console.WriteLine($"Contact '{contact1["firstname"]} " +
$"{contact1["lastname"]}' created.");
entityUris.Add(contact1Uri); //To delete later
Console.WriteLine($"Contact URI: {contact1Uri}");
//Update a contact
JObject contact1Add = new JObject
{
{ "annualincome", 80000 },
{ "jobtitle", "Junior Developer" }
};
svc.Patch(contact1Uri, contact1Add);
Console.WriteLine(
$"Contact '{contact1["firstname"]} {contact1["lastname"]}' " +
$"updated with jobtitle and annual income");
//Retrieve a contact
var retrievedcontact1 = svc.Get(contact1Uri.ToString() +
"?$select=fullname,annualincome,jobtitle,description");
Console.WriteLine($"Contact '{retrievedcontact1["fullname"]}' retrieved: \n" +
$"\tAnnual income: {retrievedcontact1["annualincome"]}\n" +
$"\tJob title: {retrievedcontact1["jobtitle"]} \n" +
//description is initialized empty.
$"\tDescription: {retrievedcontact1["description"]}.");
//Modify specific properties and then update entity instance.
JObject contact1Update = new JObject
{
{ "jobtitle", "Senior Developer" },
{ "annualincome", 95000 },
{ "description", "Assignment to-be-determined" }
};
svc.Patch(contact1Uri, contact1Update);
Console.WriteLine($"Contact '{retrievedcontact1["fullname"]}' updated:\n" +
$"\tJob title: {contact1Update["jobtitle"]}\n" +
$"\tAnnual income: {contact1Update["annualincome"]}\n" +
$"\tDescription: {contact1Update["description"]}\n");
// Change just one property
string telephone1 = "555-0105";
svc.Put(contact1Uri, "telephone1", telephone1);
Console.WriteLine($"Contact '{retrievedcontact1["fullname"]}' " +
$"phone number updated.");
//Now retrieve just the single property.
var telephone1Value = svc.Get($"{contact1Uri}/telephone1");
Console.WriteLine($"Contact's telephone # is: {telephone1Value["value"]}.");
#endregion Section 1: Basic Create and Update operations
#region Section 2: Create record associated to another
/// <summary>
/// Demonstrates creation of entity instance and simultaneous association to another,
/// existing entity.
/// </summary>
///
Console.WriteLine("\n--Section 2 started--");
//Create a new account and associate with existing contact in one operation.
var account1 = new JObject
{
{ "name", "Contoso Ltd" },
{ "telephone1", "555-5555" },
{ "*****@*****.**", contact1Uri }
};
var account1Uri = svc.PostCreate("accounts", account1);
entityUris.Add(account1Uri); //To delete later
Console.WriteLine($"Account '{account1["name"]}' created.");
Console.WriteLine($"Account URI: {account1Uri}");
//Retrieve account name and primary contact info
JObject retrievedAccount1 = svc.Get($"{account1Uri}?$select=name," +
$"&$expand=primarycontactid($select=fullname,jobtitle,annualincome)") as JObject;
Console.WriteLine($"Account '{retrievedAccount1["name"]}' has primary contact " +
$"'{retrievedAccount1["primarycontactid"]["fullname"]}':");
Console.WriteLine($"\tJob title: {retrievedAccount1["primarycontactid"]["jobtitle"]} \n" +
$"\tAnnual income: {retrievedAccount1["primarycontactid"]["annualincome"]}");
#endregion Section 2: Create record associated to another
#region Section 3: Create related entities
/// <summary>
/// Demonstrates creation of entity instance and related entities in a single operation.
/// </summary>
///
Console.WriteLine("\n--Section 3 started--");
//Create the following entries in one operation: an account, its
// associated primary contact, and open tasks for that contact. These
// entity types have the following relationships:
// Accounts
// |---[Primary] Contact (N-to-1)
// |---Tasks (1-to-N)
//Build the Account object inside-out, starting with most nested type(s)
JArray tasks = new JArray();
JObject task1 = new JObject
{
{ "subject", "Sign invoice" },
{ "description", "Invoice #12321" },
{ "scheduledend", DateTimeOffset.Parse("4/19/2019") }
};
tasks.Add(task1);
JObject task2 = new JObject
{
{ "subject", "Setup new display" },
{ "description", "Theme is - Spring is in the air" },
{ "scheduledstart", DateTimeOffset.Parse("4/20/2019") }
};
tasks.Add(task2);
JObject task3 = new JObject
{
{ "subject", "Conduct training" },
{ "description", "Train team on making our new blended coffee" },
{ "scheduledstart", DateTimeOffset.Parse("6/1/2019") }
};
tasks.Add(task3);
JObject contact2 = new JObject
{
{ "firstname", "Susie" },
{ "lastname", "Curtis" },
{ "jobtitle", "Coffee Master" },
{ "annualincome", 48000 },
//Add related tasks using corresponding navigation property
{ "Contact_Tasks", tasks }
};
JObject account2 = new JObject
{
{ "name", "Fourth Coffee" },
//Add related contacts using corresponding navigation property
{ "primarycontactid", contact2 }
};
//Create the account and related records
Uri account2Uri = svc.PostCreate("accounts", account2);
Console.WriteLine($"Account '{account2["name"]} created.");
entityUris.Add(account2Uri); //To delete later
Console.WriteLine($"Contact URI: {account2Uri}");
//Retrieve account, primary contact info, and assigned tasks for contact.
//CDS only supports querying-by-expansion one level deep, so first query
// account-primary contact.
var retrievedAccount2 = svc.Get($"{account2Uri}?$select=name," +
$"&$expand=primarycontactid($select=fullname,jobtitle,annualincome)");
Console.WriteLine($"Account '{retrievedAccount2["name"]}' " +
$"has primary contact '{retrievedAccount2["primarycontactid"]["fullname"]}':");
Console.WriteLine($"\tJob title: {retrievedAccount2["primarycontactid"]["jobtitle"]} \n" +
$"\tAnnual income: {retrievedAccount2["primarycontactid"]["annualincome"]}");
//Next retrieve same contact and its assigned tasks.
//Don't have a saved URI for contact 'Susie Curtis', so create one
// from base address and entity ID.
Uri contact2Uri = new Uri($"{svc.BaseAddress}contacts({retrievedAccount2["primarycontactid"]["contactid"]})");
//Retrieve the contact
var retrievedcontact2 = svc.Get($"{contact2Uri}?$select=fullname," +
$"&$expand=Contact_Tasks($select=subject,description,scheduledstart,scheduledend)");
Console.WriteLine($"Contact '{retrievedcontact2["fullname"]}' has the following assigned tasks:");
foreach (JToken tk in retrievedcontact2["Contact_Tasks"])
{
Console.WriteLine(
$"Subject: {tk["subject"]}, \n" +
$"\tDescription: {tk["description"]}\n" +
$"\tStart: {tk["scheduledstart"].Value<DateTime>().ToString("d")}\n" +
$"\tEnd: {tk["scheduledend"].Value<DateTime>().ToString("d")}\n");
}
#endregion Section 3: Create related entities
#region Section 4: Associate and Disassociate entities
/// <summary>
/// Demonstrates associating and disassociating of existing entity instances.
/// </summary>
Console.WriteLine("\n--Section 4 started--");
//Add 'Rafel Shillo' to the contact list of 'Fourth Coffee',
// a 1-to-N relationship.
JObject rel1 = new JObject
{
{ "@odata.id", contact1Uri }
}; //relationship object for msg content
Uri navUri1 = new Uri($"{account2Uri}/contact_customer_accounts/$ref");
//Create relationship
svc.Post(navUri1.ToString(), rel1);
Console.WriteLine($"Contact '{retrievedcontact1["fullname"]}' " +
$"associated to account '{account2["name"]}'.");
//Retrieve and output all contacts for account 'Fourth Coffee'.
var retrievedContactList1 = svc.Get($"{account2Uri}/contact_customer_accounts?" +
$"$select=fullname,jobtitle");
Console.WriteLine($"Contact list for account '{retrievedAccount2["name"]}':");
foreach (JToken ct in retrievedContactList1["value"])
{
Console.WriteLine($"\tName: {ct["fullname"]}, Job title: {ct["jobtitle"]}");
}
//Dissociate the contact from the account. For a collection-valued
// navigation property, must append URI of referenced entity.
Uri dis1Uri = new Uri($"{navUri1}?$id={contact1Uri}");
//Equivalently, could have dissociated from the other end of the
// relationship, using the single-valued navigation ref, located in
// the contact 'Peter Cambel'. This dissociation URI has a simpler form:
// [Org URI]/api/data/v9.1/contacts([contactid#])/parentcustomerid_account/$ref
svc.Delete(dis1Uri);
//'Rafel Shillo' was removed from the the contact list of 'Fourth Coffee'
//Associate an opportunity to a competitor, an N-to-N relationship.
//First, create the required entity instances.
JObject comp1 = new JObject
{
{ "name", "Adventure Works" },
{
"strengths",
"Strong promoter of private tours for multi-day outdoor adventures"
}
};
Uri comp1Uri = svc.PostCreate("competitors", comp1);
entityUris.Add(comp1Uri); //To delete later
JObject oppor1 = new JObject
{
["name"] = "River rafting adventure",
["description"] = "Sales team on a river-rafting offsite and team building"
};
Uri oppor1Uri = svc.PostCreate("opportunities", oppor1);
entityUris.Add(oppor1Uri); //To delete later
//Associate opportunity to competitor via opportunitycompetitors_association.
// navigation property.
JObject rel2 = new JObject
{
{ "@odata.id", comp1Uri }
};
Uri navUri2 = new Uri($"{oppor1Uri}/opportunitycompetitors_association/$ref");
svc.Post(navUri2.ToString(), rel2);
Console.WriteLine($"Opportunity '{oppor1["name"]}' associated with competitor '{comp1["name"]}'.");
//Retrieve all opportunities for competitor 'Adventure Works'.
var retrievedOpporList1 = svc.Get($"{comp1Uri}?$select=name,&$expand=opportunitycompetitors_association($select=name,description)");
Console.WriteLine($"Competitor '{retrievedOpporList1["name"]}' has the following opportunities:");
foreach (JToken op in
retrievedOpporList1["opportunitycompetitors_association"])
{
Console.WriteLine($"\tName: {op["name"]}, \n" +
$"\tDescription: {op["description"]}");
}
//Dissociate opportunity from competitor.
svc.Delete(new Uri($"{navUri2}?$id={comp1Uri}"));
// 'River rafting adventure' opportunity disassociated with 'Adventure Works' competitor
#endregion Section 4: Associate and Disassociate entities
#region Section 5: Delete sample entities
Console.WriteLine("\n--Section 5 started--");
//Delete all the created sample entities. Note that explicit deletion is not required
// for contact tasks because these are automatically cascade-deleted with owner.
if (!deleteCreatedRecords)
{
Console.Write("\nDo you want these entity records deleted? (y/n) [y]: ");
String answer = Console.ReadLine();
answer = answer.Trim();
if (!(answer.StartsWith("y") || answer.StartsWith("Y") || answer == string.Empty))
{
entityUris.Clear();
}
else
{
Console.WriteLine("\nDeleting created records.");
}
}
else
{
Console.WriteLine("\nDeleting created records.");
}
foreach (Uri entityUrl in entityUris)
{
svc.Delete(entityUrl);
}
#endregion Section 5: Delete sample entities
Console.WriteLine("--Basic Operations Completed--");
}
