public void Experiment_For_Filters_2_With_Linq_Conversion()
{
// var sql = string.Format("Select C.firstname, C.lastname From contact Where firstname Like '%ax%' ");
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
var serviceProvider = new CrmServiceProvider(new ExplicitConnectionStringProviderWithFallbackToConfig()
{
OrganisationServiceConnectionString = connectionString.ConnectionString
},
new CrmClientCredentialsProvider());
var orgService = serviceProvider.GetOrganisationService() as CrmOrganizationServiceContext;
using (orgService as IDisposable)
{
var contactsQuery = from c in orgService.CreateQuery("contact")
join a in orgService.CreateQuery("customeraddress") on(Guid) c["contactid"] equals
(Guid) a["parentid"]
where (((string)c["firstname"] == "Max" && (string)c["lastname"] == "Planck") ||
((string)c["firstname"] == "Albert" && (string)c["lastname"] == "Einstein")) ||
(string)a["line1"] == "Line2"
select c;
IQueryProvider queryProvider = contactsQuery.Provider;
MethodInfo translateMethodInfo = queryProvider.GetType().GetMethod("Translate");
QueryExpression query = (QueryExpression)translateMethodInfo.Invoke(queryProvider, new object[] { contactsQuery.Expression });
QueryExpressionToFetchXmlRequest reqConvertToFetchXml = new QueryExpressionToFetchXmlRequest {
Query = query
};
QueryExpressionToFetchXmlResponse respConvertToFetchXml = (QueryExpressionToFetchXmlResponse)orgService.Execute(reqConvertToFetchXml);
System.Diagnostics.Debug.Print(respConvertToFetchXml.FetchXml);
var results = contactsQuery.ToList();
int resultCount = 0;
foreach (var r in results)
{
resultCount++;
// Console.WriteLine(string.Format("{0} {1} {2}", (string)r["firstname"], (string)r["lastname"], (string)r["line1"]));
Console.WriteLine(string.Format("{0} {1}", (string)r["firstname"], (string)r["lastname"]));
}
Console.WriteLine("There were " + resultCount + " results..");
}
}
/// <summary>
/// Additional work for Initialization. Hook PreExecute event to display QueryExpression and FetchXML to SQL tab.
/// </summary>
/// <param name="cxInfo">IConnectionInfo</param>
/// <param name="context">XrmContext</param>
/// <param name="executionManager">QueryExecutionManager</param>
public override void InitializeContext(IConnectionInfo cxInfo, object context, QueryExecutionManager executionManager)
{
// Attach PreExecute event.
orgService.PreExecute += (s, e) =>
{
// Take QueryExpression and convert it to FetchXML.
QueryExpressionToFetchXmlRequest request = new QueryExpressionToFetchXmlRequest {
Query = e.query
};
string fetchXml = (orgService.Execute(request) as QueryExpressionToFetchXmlResponse).FetchXml;
// Instantiate DataContractSerializer to serialiez QueryExpression.
DataContractSerializer serializer = new DataContractSerializer(typeof(QueryExpression));
using (MemoryStream stream = new MemoryStream())
{
// Use XmlWriter with Indent.
using (XmlWriter writer = XmlWriter.Create(stream, new XmlWriterSettings {
Indent = true
}))
{
serializer.WriteObject(writer, e.query);
}
// Display result.
executionManager.SqlTranslationWriter.WriteLine("QueryExpression:");
executionManager.SqlTranslationWriter.WriteLine(Encoding.UTF8.GetString(stream.GetBuffer()));
executionManager.SqlTranslationWriter.WriteLine();
}
// Display FetchXml as well.
using (MemoryStream stream = new MemoryStream())
{
XDocument xdoc = XDocument.Parse(fetchXml);
using (XmlWriter writer = XmlWriter.Create(stream, new XmlWriterSettings {
Indent = true
}))
{
xdoc.WriteTo(writer);
}
executionManager.SqlTranslationWriter.WriteLine("FetchXml:");
executionManager.SqlTranslationWriter.WriteLine(Encoding.UTF8.GetString(stream.GetBuffer()).Replace("\"", "\'"));
}
};
base.InitializeContext(cxInfo, context, executionManager);
}
/// <summary>
/// Converts query expression to fetch xml
/// </summary>
/// <param name="service">Organization Service</param>
/// <returns>Fetch XML</returns>
public string ConvertQueryExpressionToFetchXml(IOrganizationService service)
{
if (service != null)
{
var conversionRequest = new QueryExpressionToFetchXmlRequest
{
Query = this.queryExpression
};
var conversionResponse =
(QueryExpressionToFetchXmlResponse)service.Execute(conversionRequest);
return(conversionResponse.FetchXml);
}
return(string.Empty);
}
/// <summary>
/// Converts from QueryBase to FetchXML, removing invalid attribute useraworderby to validate with fetch.xsd
/// Issue reported here: https://github.com/MicrosoftDocs/dynamics-365-customer-engagement/issues/233
/// </summary>
/// <param name="organizationService"></param>
/// <param name="query"></param>
/// <returns></returns>
public static string QueryExpressionToFetchXml(this IOrganizationService organizationService, QueryBase query)
{
QueryExpressionToFetchXmlRequest request = new QueryExpressionToFetchXmlRequest()
{
Query = query
};
QueryExpressionToFetchXmlResponse response = (QueryExpressionToFetchXmlResponse)organizationService.Execute(request);
var doc = new XmlDocument();
doc.LoadXml(response.FetchXml);
var fetchnode = doc.SelectSingleNode("fetch");
if (fetchnode != null && fetchnode.Attributes["useraworderby"] != null)
{
fetchnode.Attributes.RemoveNamedItem("useraworderby");
}
return(doc.OuterXml);
}
public void Experiment_For_Filters_2_With_Linq_Conversion()
{
// var sql = string.Format("Select C.firstname, C.lastname From contact Where firstname Like '%ax%' ");
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
var serviceProvider = new CrmServiceProvider(new ExplicitConnectionStringProviderWithFallbackToConfig() { OrganisationServiceConnectionString = connectionString.ConnectionString },
new CrmClientCredentialsProvider());
var orgService = serviceProvider.GetOrganisationService() as CrmOrganizationServiceContext;
using (orgService as IDisposable)
{
var contactsQuery = from c in orgService.CreateQuery("contact")
join a in orgService.CreateQuery("customeraddress") on (Guid)c["contactid"] equals
(Guid)a["parentid"]
where (((string)c["firstname"] == "Max" && (string)c["lastname"] == "Planck")
|| ((string)c["firstname"] == "Albert" && (string)c["lastname"] == "Einstein"))
|| (string)a["line1"] == "Line2"
select c;
IQueryProvider queryProvider = contactsQuery.Provider;
MethodInfo translateMethodInfo = queryProvider.GetType().GetMethod("Translate");
QueryExpression query = (QueryExpression)translateMethodInfo.Invoke(queryProvider, new object[] { contactsQuery.Expression });
QueryExpressionToFetchXmlRequest reqConvertToFetchXml = new QueryExpressionToFetchXmlRequest { Query = query };
QueryExpressionToFetchXmlResponse respConvertToFetchXml = (QueryExpressionToFetchXmlResponse)orgService.Execute(reqConvertToFetchXml);
System.Diagnostics.Debug.Print(respConvertToFetchXml.FetchXml);
var results = contactsQuery.ToList();
int resultCount = 0;
foreach (var r in results)
{
resultCount++;
// Console.WriteLine(string.Format("{0} {1} {2}", (string)r["firstname"], (string)r["lastname"], (string)r["line1"]));
Console.WriteLine(string.Format("{0} {1}", (string)r["firstname"], (string)r["lastname"]));
}
Console.WriteLine("There were " + resultCount + " results..");
}
}
public void Execute(IServiceProvider serviceProvider)
{
ITracingService tracingService =
(ITracingService)serviceProvider.GetService(typeof(ITracingService));
IPluginExecutionContext context = (IPluginExecutionContext)
serviceProvider.GetService(typeof(IPluginExecutionContext));
IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
if (context.Depth > 1)
{
return;
}
if (context.MessageName == "Update" || context.MessageName == "Create")
{
var target = (Entity)context.InputParameters["Target"];
if (target.Contains("ita_json"))
{
string json = (string)target["ita_json"];
try
{
var ser = new DataContractJsonSerializer(typeof(InspectionResults));
var stream1 = new MemoryStream();
StreamWriter sw = new StreamWriter(stream1);
sw.Write(json);
sw.Flush();
stream1.Position = 0;
InspectionResults ir = (InspectionResults)ser.ReadObject(stream1);
QueryExpression qe = new QueryExpression("ita_inspectionitem");
qe.Criteria.AddCondition(new ConditionExpression("ita_inspection", ConditionOperator.Equal, target.Id));
qe.ColumnSet = new ColumnSet("ita_inspectionitemid");
var results = service.RetrieveMultiple(qe).Entities;
foreach (var e in results)
{
service.Delete(e.LogicalName, e.Id);
}
CreateInspectionItem(service, "Length", ir.ProductLength, target);
CreateInspectionItem(service, "Width", ir.ProductWidth, target);
CreateInspectionItem(service, "Shape", ir.ProductShape, target);
}
catch (Exception ex)
{
target["ita_json"] = ex.Message;
service.Update(target);
}
}
}
else if (context.MessageName == "RetrieveMultiple")
{
if (context.Stage == 20)
{
if (context.InputParameters.Contains("Query"))
{
FetchExpression query = null;
if (context.InputParameters["Query"] is QueryExpression)
{
var qe = (QueryExpression)context.InputParameters["Query"];
var conversionRequest = new QueryExpressionToFetchXmlRequest
{
Query = qe
};
var conversionResponse =
(QueryExpressionToFetchXmlResponse)service.Execute(conversionRequest);
query = new FetchExpression(conversionResponse.FetchXml);
}
if (context.InputParameters["Query"] is FetchExpression)
{
query = (FetchExpression)context.InputParameters["Query"];
}
string pattern = @"condition attribute=""ita_inspectionforproduct"" operator=""eq"" value=""(.*?)""";
var matches = Regex.Matches(query.Query, pattern, RegexOptions.Multiline);
if (matches.Count > 0 && matches[0].Groups.Count > 0)
{
context.SharedVariables["productnumber"] = matches[0].Groups[1].Value;
}
}
}
else
{
EntityCollection col = (EntityCollection)context.OutputParameters["BusinessEntityCollection"];
if (context.SharedVariables.Contains("productnumber"))
{
string productNumber = (string)context.SharedVariables["productnumber"];
Entity entity = null;
if (productNumber == "333")
{
//Create a fake inspection record to pass the error back
entity = new Entity("ita_inspection");
entity.Id = Guid.NewGuid();
entity["ita_inspectionid"] = entity.Id;
entity["ita_inspectionforproduct"] = productNumber;
entity["ita_startinspectionerror"] = "Cannot inspect this product!";
}
else
{
//Get inspection record
//Will create here, but could also load an existing record
entity = new Entity("ita_inspection");
entity["ita_inspectionforproduct"] = productNumber;
entity["ita_name"] = productNumber;
entity.Id = service.Create(entity);
entity["ita_inspectionid"] = entity.Id;
//Prepare JSON
InspectionResults ir = new InspectionResults();
if (productNumber == "111")
{
ir.ProductLength = "Fail";
ir.ProductWidth = "Pass";
ir.ProductShape = "Fail";
}
else
{
ir.ProductLength = "N/A";
ir.ProductWidth = "N/A";
ir.ProductShape = "N/A";
}
var stream1 = new MemoryStream();
var ser = new DataContractJsonSerializer(typeof(InspectionResults));
ser.WriteObject(stream1, ir);
stream1.Position = 0;
var sr = new StreamReader(stream1);
string json = sr.ReadToEnd();
entity["ita_json"] = json;
}
col.Entities.Add(entity);
}
}
}
}
public string ConvertQueryExpressionToFetch(QueryExpression queryExpression)
{
var convertRequest = new QueryExpressionToFetchXmlRequest();
convertRequest.Query = queryExpression;
var convertResponse = (QueryExpressionToFetchXmlResponse) Execute(convertRequest);
var query = convertResponse.FetchXml;
return query;
}
private void DoQueryExpressionToFetchXmlConversion()
{
// Build a query expression that we will turn into FetchXML.
var queryExpression = new QueryExpression()
{
Distinct = false,
EntityName = Contact.EntityLogicalName,
ColumnSet = new ColumnSet("fullname", "address1_telephone1"),
LinkEntities =
{
new LinkEntity
{
JoinOperator = JoinOperator.LeftOuter,
LinkFromAttributeName = "parentcustomerid",
LinkFromEntityName = Contact.EntityLogicalName,
LinkToAttributeName = "accountid",
LinkToEntityName = Account.EntityLogicalName,
LinkCriteria =
{
Conditions =
{
new ConditionExpression("name", ConditionOperator.Equal, "Litware, Inc.")
}
}
}
},
Criteria =
{
Filters =
{
new FilterExpression
{
FilterOperator = LogicalOperator.And,
Conditions =
{
new ConditionExpression("address1_stateorprovince", ConditionOperator.Equal, "WA"),
new ConditionExpression("address1_city", ConditionOperator.In, new String[] { "Redmond","Bellevue", "Kirkland", "Seattle" }),
new ConditionExpression("createdon", ConditionOperator.LastXDays, 30),
new ConditionExpression("emailaddress1", ConditionOperator.NotNull)
},
},
new FilterExpression
{
FilterOperator = LogicalOperator.Or,
Conditions =
{
new ConditionExpression("address1_telephone1", ConditionOperator.Like, "(206)%"),
new ConditionExpression("address1_telephone1", ConditionOperator.Like, "(425)%")
}
}
}
}
};
// Run the query as a query expression.
EntityCollection queryExpressionResult =
_serviceProxy.RetrieveMultiple(queryExpression);
Console.WriteLine("Output for query as QueryExpression:");
DisplayContactQueryResults(queryExpressionResult);
// Convert the query expression to FetchXML.
var conversionRequest = new QueryExpressionToFetchXmlRequest
{
Query = queryExpression
};
var conversionResponse =
(QueryExpressionToFetchXmlResponse)_serviceProxy.Execute(conversionRequest);
// Use the converted query to make a retrieve multiple request to Microsoft Dynamics CRM.
String fetchXml = conversionResponse.FetchXml;
var fetchQuery = new FetchExpression(fetchXml);
EntityCollection result = _serviceProxy.RetrieveMultiple(fetchQuery);
// Display the results.
Console.WriteLine("\nOutput for query after conversion to FetchXML:");
DisplayContactQueryResults(result);
}
/// <summary>
/// Handles the entity retrieve multiple message.
/// </summary>
/// <param name="context">The context.</param>
public override void HandleRetrieveMultipleMessage(PluginExecutionContext context)
{
base.HandleRetrieveMultipleMessage(context);
var query = context.PluginContext.InputParameters["Query"];
if (query != null)
{
var mapper = new GenericMapper(context);
EntityCollection collection = new EntityCollection();
string fetchXml = string.Empty;
if (query is QueryExpression qe)
{
var convertRequest = new QueryExpressionToFetchXmlRequest();
convertRequest.Query = (QueryExpression)qe;
var response = (QueryExpressionToFetchXmlResponse)context.Service.Execute(convertRequest);
fetchXml = response.FetchXml;
}
else if (query is FetchExpression fe)
{
fetchXml = fe.Query;
}
if (!string.IsNullOrEmpty(fetchXml))
{
context.Trace($"Pre FetchXML: {fetchXml}");
var metadata = new AttributeMetadataCache(context.Service);
var fetch = Deserialize(fetchXml);
mapper.MapFetchXml(fetch);
//Store page info before converting
int page = -1;
int count = -1;
if (!string.IsNullOrEmpty(fetch.page))
{
page = Int32.Parse(fetch.page);
fetch.page = string.Empty;
}
if (!string.IsNullOrEmpty(fetch.count))
{
count = Int32.Parse(fetch.count);
fetch.count = string.Empty;
}
var sql = FetchXml2Sql.Convert(context.Service, metadata, fetch, new FetchXml2SqlOptions {
PreserveFetchXmlOperatorsAsFunctions = false
}, out _);
sql = mapper.MapVirtualEntityAttributes(sql);
context.Trace($"SQL: {sql}");
if (page != -1 && count != -1)
{
collection = this.GetEntitiesFromSql(context, mapper, sql, count, page);
}
else
{
collection = this.GetEntitiesFromSql(context, mapper, sql, -1, 1);
}
}
context.Trace($"Records Returned: {collection.Entities.Count}");
context.PluginContext.OutputParameters["BusinessEntityCollection"] = collection;
}
}
protected override void Execute(CodeActivityContext executionContext)
{
//*** Create the tracing service
ITracingService tracingService = executionContext.GetExtension <ITracingService>();
if (tracingService == null)
{
throw new InvalidPluginExecutionException("Failed to retrieve the tracing service.");
}
//*** Create the context
IWorkflowContext context = executionContext.GetExtension <IWorkflowContext>();
if (context == null)
{
throw new InvalidPluginExecutionException("Failed to retrieve the workflow context.");
}
tracingService.Trace("{0}.Execute(): ActivityInstanceId: {1}; WorkflowInstanceId: {2}; CorrelationId: {3}; InitiatingUserId: {4} -- Entering", CHILD_CLASS_NAME, executionContext.ActivityInstanceId, executionContext.WorkflowInstanceId, context.CorrelationId, context.InitiatingUserId);
IOrganizationServiceFactory serviceFactory = executionContext.GetExtension <IOrganizationServiceFactory>();
IOrganizationService serviceProxy = serviceFactory.CreateOrganizationService(context.UserId);
if (context.InputParameters.Contains(Common.Target) && context.InputParameters[Common.Target] is Entity)
{
try {
//*** Grab the Target Entity
var theEntity = (Entity)context.InputParameters[Common.Target];
tracingService.Trace("Active Stage Name: {0}", theEntity.EntityState);
//-------------------------------------------------------------------------------------------------------------
var processInstancesRequest = new RetrieveProcessInstancesRequest {
EntityId = theEntity.Id,
EntityLogicalName = theEntity.LogicalName
};
var processInstancesResponse = (RetrieveProcessInstancesResponse)serviceProxy.Execute(processInstancesRequest);
var processCount = processInstancesResponse.Processes.Entities.Count;
if (processCount > 0)
{
tracingService.Trace("{0}: Count of Process Instances concurrently associated with the Entity record: {1}", CHILD_CLASS_NAME, processCount);
tracingService.Trace("{0}: BPF Definition Name currently set for the Entity record: {1}, Id: {2}", CHILD_CLASS_NAME, processInstancesResponse.Processes.Entities[0].Attributes[CrmEarlyBound.Workflow.Fields.Name], processInstancesResponse.Processes.Entities[0].Id.ToString());
var bpfEntityRef = this.BpfEntityReference.Get <EntityReference>(executionContext);
var colSet = new ColumnSet();
colSet.AddColumn(CrmEarlyBound.Workflow.Fields.UniqueName);
var bpfEntity = serviceProxy.Retrieve(bpfEntityRef.LogicalName, bpfEntityRef.Id, colSet);
tracingService.Trace("{0}: Switching to BPF Unique Name: {1}, Id: {2}", CHILD_CLASS_NAME, bpfEntity.Attributes[CrmEarlyBound.Workflow.Fields.UniqueName].ToString(), bpfEntity.Id.ToString());
var bpfStageName = this.BpfStageName.Get <string>(executionContext).Trim();
var qe = new QueryExpression {
EntityName = CrmEarlyBound.Workflow.EntityLogicalName,
ColumnSet = new ColumnSet(new string[] { CrmEarlyBound.Workflow.Fields.Name }),
Criteria = new FilterExpression {
Conditions =
{
new ConditionExpression {
AttributeName = CrmEarlyBound.Workflow.Fields.UniqueName, Operator = ConditionOperator.Equal, Values ={ bpfEntity.Attributes[CrmEarlyBound.Workflow.Fields.UniqueName] } //new_bpf_472aceaabf7c4f1db4d13ac3c7076c65
}
}
},
NoLock = true,
Distinct = false
};
#region Convert Query Expression to FetchXML
var conversionRequest = new QueryExpressionToFetchXmlRequest {
Query = qe
};
var conversionResponse = (QueryExpressionToFetchXmlResponse)serviceProxy.Execute(conversionRequest);
var fetchXml = conversionResponse.FetchXml;
tracingService.Trace("{0}: [{1}], Message: {2}", CHILD_CLASS_NAME, fetchXml, context.MessageName);
#endregion Convert the query expression to FetchXML.
tracingService.Trace("{0}: Built BPF Query, Now Executing...", CHILD_CLASS_NAME);
var entColByQuery = serviceProxy.RetrieveMultiple(qe).Entities; //// Execute Query with Filter Expressions
//-------------------------------------------------------------------------------------------------------------
if (entColByQuery != null && entColByQuery.Count > 0) //// Search and handle related entities
{
tracingService.Trace("{0}: Found matching Business Process Flows...", CHILD_CLASS_NAME);
var bpfId = new Guid();
var bpfEntityName = String.Empty;
foreach (var entity in entColByQuery) //// Loop related entities and retrieve Workflow Names
{
bpfId = entity.Id;
bpfEntityName = entity.GetAttributeValue <string>(CrmEarlyBound.Workflow.Fields.Name);
break;
}
if (bpfId != Guid.Empty)
{
tracingService.Trace("{0}: Successfully retrieved the Business Process Flow that we'll be switching to: {1}, Id: {2}", CHILD_CLASS_NAME, bpfEntityName, bpfId.ToString());
System.Threading.Thread.Sleep(2000); // Wait for 2 seconds before switching the process
//*** Set to the new or same Business BpfEntityName Flow
var setProcReq = new SetProcessRequest {
Target = new EntityReference(theEntity.LogicalName, theEntity.Id),
NewProcess = new EntityReference(CrmEarlyBound.Workflow.EntityLogicalName, bpfId)
};
tracingService.Trace("{0}: ***Ready To Update - Business Process Flow", CHILD_CLASS_NAME);
var setProcResp = (SetProcessResponse)serviceProxy.Execute(setProcReq);
tracingService.Trace("{0}: ***Updated", CHILD_CLASS_NAME);
}
}
else
{
tracingService.Trace("{0}: No Business Process Flows were found with Unique Name: {1}", CHILD_CLASS_NAME, bpfEntity.Attributes[CrmEarlyBound.Workflow.Fields.UniqueName].ToString());
}
//-------------------------------------------------------------------------------------------------------------
//*** Verify if the Process Instance was switched successfully for the Entity record
processInstancesRequest = new RetrieveProcessInstancesRequest {
EntityId = theEntity.Id,
EntityLogicalName = theEntity.LogicalName
};
processInstancesResponse = (RetrieveProcessInstancesResponse)serviceProxy.Execute(processInstancesRequest);
processCount = processInstancesResponse.Processes.Entities.Count;
if (processCount > 0)
{
var activeProcessInstance = processInstancesResponse.Processes.Entities[0]; //*** First Entity record is the Active Process Instance
var activeProcessInstanceId = activeProcessInstance.Id; //*** Active Process Instance Id to be used later for retrieval of the active path of the process instance
tracingService.Trace("{0}: Successfully Switched to '{1}' BPF for the Entity Record.", CHILD_CLASS_NAME, activeProcessInstance.Attributes[CrmEarlyBound.Workflow.Fields.Name]);
tracingService.Trace("{0}: Count of process instances concurrently associated with the entity record: {1}.", CHILD_CLASS_NAME, processCount);
var message = "All process instances associated with the entity record:";
for (var i = 0; i < processCount; i++)
{
message = message + " " + processInstancesResponse.Processes.Entities[i].Attributes[CrmEarlyBound.Workflow.Fields.Name] + ",";
}
tracingService.Trace("{0}: {1}", CHILD_CLASS_NAME, message.TrimEnd(message[message.Length - 1]));
//*** Retrieve the Active Stage ID of the Active Process Instance
var activeStageId = new Guid(activeProcessInstance.Attributes[CrmEarlyBound.ProcessStage.Fields.ProcessStageId].ToString());
var activeStagePosition = 0;
var newStageId = new Guid();
var newStagePosition = 0;
//*** Retrieve the BPF Stages in the active path of the Active Process Instance
var activePathRequest = new RetrieveActivePathRequest {
ProcessInstanceId = activeProcessInstanceId
};
var activePathResponse = (RetrieveActivePathResponse)serviceProxy.Execute(activePathRequest);
tracingService.Trace("{0}: Retrieved the BPF Stages in the Active Path of the Process Instance:", CHILD_CLASS_NAME);
for (var i = 0; i < activePathResponse.ProcessStages.Entities.Count; i++)
{
var curStageName = activePathResponse.ProcessStages.Entities[i].Attributes[CrmEarlyBound.ProcessStage.Fields.StageName].ToString();
tracingService.Trace("{0}: Looping Through Stage #{1}: {2} (StageId: {3}, IndexId: {4})", CHILD_CLASS_NAME, i + 1, curStageName, activePathResponse.ProcessStages.Entities[i].Attributes[CrmEarlyBound.ProcessStage.Fields.ProcessStageId], i);
//*** Retrieve the Active Stage Name and Stage Position based on a successful match of the activeStageId
if (activePathResponse.ProcessStages.Entities[i].Attributes[CrmEarlyBound.ProcessStage.Fields.ProcessStageId].Equals(activeStageId))
{
activeStagePosition = i;
tracingService.Trace("{0}: Concerning the Process Instance -- Initial Active Stage Name: {1} (StageId: {2})", CHILD_CLASS_NAME, curStageName, activeStageId);
}
//*** Retrieve the New Stage Id, Stage Name, and Stage Position based on a successful match of the stagename
if (curStageName.Equals(bpfStageName, StringComparison.InvariantCultureIgnoreCase))
{
newStageId = new Guid(activePathResponse.ProcessStages.Entities[i].Attributes[CrmEarlyBound.ProcessStage.Fields.ProcessStageId].ToString());
newStagePosition = i;
tracingService.Trace("{0}: Concerning the Process Instance -- Desired New Stage Name: {1} (StageId: {2})", CHILD_CLASS_NAME, curStageName, newStageId);
}
}
//-------------------------------------------------------------------------------------------------------------
//***Update the Business Process Flow Instance record to the desired Active Stage
Entity retrievedProcessInstance;
ColumnSet columnSet;
var stageShift = newStagePosition - activeStagePosition;
if (stageShift > 0)
{
tracingService.Trace("{0}: Number of Stages Shifting Forward: {1}", CHILD_CLASS_NAME, stageShift);
//*** Stages only move in 1 direction --> Forward
for (var i = activeStagePosition; i <= newStagePosition; i++)
{
System.Threading.Thread.Sleep(1000);
//*** Retrieve the Stage Id of the next stage that you want to set as active
var newStageName = activePathResponse.ProcessStages.Entities[i].Attributes[CrmEarlyBound.ProcessStage.Fields.StageName].ToString();
newStageId = new Guid(activePathResponse.ProcessStages.Entities[i].Attributes[CrmEarlyBound.ProcessStage.Fields.ProcessStageId].ToString());
tracingService.Trace("{0}: Setting To Stage #{1}: {2} (StageId: {3}, IndexId: {4})", CHILD_CLASS_NAME, i + 1, newStageName, newStageId, i);
//*** Retrieve the BpfEntityName Instance record to update its Active Stage
columnSet = new ColumnSet();
columnSet.AddColumn(ACTIVE_STAGE_ID);
retrievedProcessInstance = serviceProxy.Retrieve(bpfEntity.Attributes[CrmEarlyBound.Workflow.Fields.UniqueName].ToString(), activeProcessInstanceId, columnSet);
//*** Set the next Stage as the Active Stage
retrievedProcessInstance[ACTIVE_STAGE_ID] = new EntityReference(CrmEarlyBound.ProcessStage.EntityLogicalName, newStageId); //(ProcessStage.EntityLogicalName, activeStageId);
try {
tracingService.Trace("{0}: ***Ready To Update -- BPF Stage", CHILD_CLASS_NAME);
serviceProxy.Update(retrievedProcessInstance);
tracingService.Trace("{0}: ***Updated", CHILD_CLASS_NAME);
} catch (FaultException <OrganizationServiceFault> ex) { //*** Determine BPF Stage Requirements
foreach (var stageAttribute in activePathResponse.ProcessStages.Entities[i].Attributes)
{
if (stageAttribute.Key.Equals("clientdata"))
{
tracingService.Trace("{0}: Attribute Key: {1}, Value: {2}", CHILD_CLASS_NAME, stageAttribute.Key, stageAttribute.Value.ToString());
break;
}
}
tracingService.Trace(FullStackTraceException.Create(ex).ToString());
throw;
}
}
}
else
{
tracingService.Trace("{0}: Number of Stages Shifting Backwards: {1}", CHILD_CLASS_NAME, stageShift);
}
//-------------------------------------------------------------------------------------------------------------
//***Retrieve the Business Process Flow Instance record again to verify its Active Stage information
columnSet = new ColumnSet();
columnSet.AddColumn(ACTIVE_STAGE_ID);
retrievedProcessInstance = serviceProxy.Retrieve(bpfEntity.Attributes[CrmEarlyBound.Workflow.Fields.UniqueName].ToString(), activeProcessInstanceId, columnSet);
var activeStageEntityRef = retrievedProcessInstance[ACTIVE_STAGE_ID] as EntityReference;
if (activeStageEntityRef != null)
{
if (activeStageEntityRef.Id.Equals(newStageId))
{
tracingService.Trace("{0}: Concerning the Process Instance -- Modified -- Active Stage Name: {1} (StageId: {2})", CHILD_CLASS_NAME, activeStageEntityRef.Name, activeStageEntityRef.Id);
}
}
}
else
{
tracingService.Trace("{0}:The RetrieveProcessInstancesRequest object returned 0", CHILD_CLASS_NAME);
}
}
} catch (FaultException <OrganizationServiceFault> ex) {
tracingService.Trace("{0}: Fault Exception: An Error Occurred During Workflow Activity Execution", CHILD_CLASS_NAME);
tracingService.Trace("{0}: Fault Timestamp: {1}", CHILD_CLASS_NAME, ex.Detail.Timestamp);
tracingService.Trace("{0}: Fault Code: {1}", CHILD_CLASS_NAME, ex.Detail.ErrorCode);
tracingService.Trace("{0}: Fault Message: {1}", CHILD_CLASS_NAME, ex.Detail.Message);
////localContext.Trace("{0}: Fault Trace: {1}", this.ChildClassName, ex.Detail.TraceText);
tracingService.Trace("{0}: Fault Inner Exception: {1}", CHILD_CLASS_NAME, null == ex.Detail.InnerFault ? "No Inner Fault" : "Has Inner Fault");
//*** Display the details of the inner exception.
if (ex.InnerException != null)
{
Exception innerEx = ex;
var i = 0;
while (innerEx.InnerException != null)
{
innerEx = innerEx.InnerException;
tracingService.Trace("{0}: Inner Exception: {1}, Message: {2};", CHILD_CLASS_NAME, i++, innerEx.Message);
}
}
throw new InvalidPluginExecutionException(OperationStatus.Failed, ex.Detail.ErrorCode, ex.Message);
} catch (Exception ex) {
tracingService.Trace("{0}: Exception: An Error Occurred During Workflow Activity Execution", CHILD_CLASS_NAME);
tracingService.Trace("{0}: Exception Message: {1}", CHILD_CLASS_NAME, ex.Message);
//*** Display the details of the inner exception.
if (ex.InnerException != null)
{
Exception innerEx = ex;
var i = 0;
while (innerEx.InnerException != null)
{
innerEx = innerEx.InnerException;
tracingService.Trace("{0}: Inner Exception: {1}, Message: {2};", CHILD_CLASS_NAME, i++, innerEx.Message);
}
}
throw new InvalidPluginExecutionException(OperationStatus.Failed, ex.HResult, ex.Message);
} finally {
tracingService.Trace("{0}.Execute(): ActivityInstanceId: {1}; WorkflowInstanceId: {2}; CorrelationId: {3} -- Exiting", CHILD_CLASS_NAME, executionContext.ActivityInstanceId, executionContext.WorkflowInstanceId, context.CorrelationId);
// Uncomment to force plugin failure for Debugging
//--> throw new InvalidPluginExecutionException(String.Format("{0}.Execute(): Plug-in Warning: Manually forcing exception for logging purposes.", CHILD_CLASS_NAME));
}
}
}
private QueryExpressionToFetchXmlResponse ExecuteInternal(QueryExpressionToFetchXmlRequest request)
{
var response = new QueryExpressionToFetchXmlResponse();
response["FetchXml"] = LocalCrmDatabase.ConvertQueryExpressionToFetchXml(request.Query as QueryExpression);
return response;
}
public void Execute(IServiceProvider serviceProvider)
{
try
{
IPluginExecutionContext executionContext = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService service = serviceFactory.CreateOrganizationService(executionContext.InitiatingUserId);
OrganizationServiceContext sContext = new OrganizationServiceContext(service);
ITracingService Trace = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
Trace.Trace("CommentsRetrievePlugin Execute Method - Start");
new InvalidPluginExecutionException("");
//if (executionContext.Stage == 20 && executionContext.MessageName.Equals("RetrieveMultiple"))
if (executionContext.MessageName.Equals("RetrieveMultiple"))
{
if (executionContext.InputParameters.Contains("Query") && executionContext.InputParameters["Query"] is QueryExpression)
{
// Verify User Roles
FilterExpression FilterConditions = SetCommentFilterConditions(executionContext, sContext, service);
// Get the QueryExpression from the property bag
if (FilterConditions.Filters.Count > 0)
{
QueryExpression objQueryExpression = (QueryExpression)executionContext.InputParameters["Query"];
objQueryExpression.Criteria.AddFilter(FilterConditions);
}
}
else if (executionContext.InputParameters.Contains("Query") && executionContext.InputParameters["Query"] is FetchExpression)
{
FetchExpression query = (FetchExpression)executionContext.InputParameters["Query"];
string fetchXml = (executionContext.InputParameters["Query"] as FetchExpression).Query;
var fetchXmlToQueryExpressionRequest = new FetchXmlToQueryExpressionRequest
{
FetchXml = fetchXml
};
var fetchXmlToQueryExpressionResponse = (FetchXmlToQueryExpressionResponse)service.Execute(fetchXmlToQueryExpressionRequest);
// Verify User Roles
FilterExpression FilterConditions = SetCommentFilterConditions(executionContext, sContext, service);
QueryExpression objQueryExpression = null;
// Get the QueryExpression from the property bag
if (FilterConditions.Filters.Count > 0)
{
objQueryExpression = fetchXmlToQueryExpressionResponse.Query;
objQueryExpression.Criteria.AddFilter(FilterConditions);
// Convert the query expression to FetchXML.
var queryExpressionToFetchXmlRequest = new QueryExpressionToFetchXmlRequest
{
Query = objQueryExpression
};
var queryExpressionToFetchXmlResponse =
(QueryExpressionToFetchXmlResponse)service.Execute(queryExpressionToFetchXmlRequest);
executionContext.InputParameters["Query"] = new FetchExpression(queryExpressionToFetchXmlResponse.FetchXml);
}
}
}
else if (executionContext.MessageName.Equals("Retrieve"))
{
if (executionContext.OutputParameters.Contains("BusinessEntity") && executionContext.OutputParameters["BusinessEntity"] is Entity &&
((Entity)executionContext.OutputParameters["BusinessEntity"]).LogicalName == "kli_comment")
{
Entity entComment = ((Entity)executionContext.OutputParameters["BusinessEntity"]);
if (entComment.Attributes.Contains("kli_commenttype"))
{ // Support Comment
// Verify User Roles
bool isValidUser = IsValidUser(((Microsoft.Xrm.Sdk.OptionSetValue)((entComment).Attributes["kli_commenttype"])).Value,
((Microsoft.Xrm.Sdk.OptionSetValue)((entComment).Attributes["kli_regardingobjecttype"])).Value,
executionContext, sContext, service);
if (!isValidUser)
{
throw new InvalidPluginExecutionException("You dont have required privileges to open the case comment.");
}
}
}
}
}
catch (Exception ex)
{
throw new InvalidPluginExecutionException(ex.Message);
}
}
public void Execute(IServiceProvider serviceProvider)
{
IPluginExecutionContext executionContext = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService service = serviceFactory.CreateOrganizationService(executionContext.InitiatingUserId);
OrganizationServiceContext sContext = new OrganizationServiceContext(service);
ITracingService Trace = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
try
{
string ReferencingEntity = executionContext.PrimaryEntityName;
string ReferencingAttribute = string.Empty;
string ReferencedEntity = string.Empty;
string ReferencedAttribute = string.Empty;
string ReferencedParentAttribute = string.Empty;
string HierarchyColumnNamespace = string.Empty;
bool TraceEnabled = false;
//<entity ReferencingEntity="incident" ReferencingAttribute="subjectid" ReferencedEntity="subject" ReferencedAttribute="subjectid" ReferencedParentAttribute="parentsubject" IsEnabled="false" />
if (executionContext.MessageName == "RetrieveMultiple")
{
foreach (var v in executionContext.InputParameters)
{
if (v.Key == "Query" && v.Value.GetType() == typeof(Microsoft.Xrm.Sdk.Query.QueryExpression))
{
XDocument xDoc = ReadGenericHierarchyRollupXml(service);
if (xDoc == null)
{
CreateTraceNote("GenericHierarchyPreRetrieveMultiple - xDoc", string.Format("No xDoc found for ReferencingEntity {0}", ReferencingEntity), service);
return;
}
HierarchyColumnNamespace = xDoc.Root.Attribute("HierarchyColumnNamespace").Value;
TraceEnabled = string.Compare(xDoc.Root.Attribute("EnableTrace").Value, "true", true) == 0 ? true : false;
var xmlConfig = xDoc.Root.Elements("entity").Where(a => a.Attribute("ReferencingEntity").Value == ReferencingEntity && string.Compare(a.Attribute("IsEnabled").Value, "true", true) == 0);
if (xmlConfig == null)
{
if (TraceEnabled)
{
CreateTraceNote("GenericHierarchyPreRetrieveMultiple - xmlConfig", string.Format("No xmlConfig found for ReferencingEntity {0}", ReferencingEntity), service);
}
return;
}
QueryExpression query = (QueryExpression)v.Value;
if (TraceEnabled)
{
QueryExpressionToFetchXmlRequest req001 = new QueryExpressionToFetchXmlRequest();
req001.Query = query;
QueryExpressionToFetchXmlResponse res001 = (QueryExpressionToFetchXmlResponse)service.Execute(req001);
CreateTraceNote("GenericHierarchyPreRetrieveMultiple-Original", res001.FetchXml, service);
}
if (query.ColumnSet.AllColumns == false && query.ColumnSet.Columns.Contains(HierarchyColumnNamespace + "_isretrievehierarchy"))
{
foreach (var xe in xmlConfig)
{
ReferencingAttribute = xe.Attribute("ReferencingAttribute").Value;
ReferencedEntity = xe.Attribute("ReferencedEntity").Value;
ReferencedAttribute = xe.Attribute("ReferencedAttribute").Value;
ReferencedParentAttribute = xe.Attribute("ReferencedParentAttribute").Value;
if (query.Criteria.Conditions.Where(a => a.AttributeName == ReferencingAttribute).Count() > 0)
{
#region if condition
var referenceEntityIdCondition = query.Criteria.Conditions.Where(a => a.AttributeName == ReferencingAttribute).FirstOrDefault();
FilterExpression hierarchyFilter = new FilterExpression(LogicalOperator.Or);
hierarchyFilter.AddCondition(new ConditionExpression(ReferencedAttribute, ConditionOperator.UnderOrEqual, (Guid)referenceEntityIdCondition.Values[0]));
LinkEntity accountLink = new LinkEntity()
{
LinkFromEntityName = executionContext.PrimaryEntityName,
LinkToEntityName = ReferencedEntity,
LinkFromAttributeName = ReferencingAttribute,
LinkToAttributeName = ReferencedAttribute,
LinkCriteria = hierarchyFilter
};
query.LinkEntities.Add(accountLink);
query.Criteria.Conditions.Remove(referenceEntityIdCondition);
if (TraceEnabled)
{
QueryExpressionToFetchXmlRequest req001 = new QueryExpressionToFetchXmlRequest();
req001.Query = query;
QueryExpressionToFetchXmlResponse res001 = (QueryExpressionToFetchXmlResponse)service.Execute(req001);
CreateTraceNote("GenericHierarchyPreRetrieveMultiple-Modified", res001.FetchXml, service);
}
#endregion
}
}
}
}
}
}
}
catch (Exception ex)
{
Trace.Trace(ex.Message, ex);
throw new InvalidPluginExecutionException("Exception on Generic.Hierarchy.Rollup.Plugins.GenericHierarchyPreRetrieveMultiple Plugin: " + ex.Message);
}
}
public void getAccountAnnotation()
{
IOrganizationService service = CRMHelper.ConnectToMSCRM();
Guid attachmentId = new Guid("1f3be793-1269-e611-80ea-5065f38af9f1");
var columnSets = new string[] { "filename", "documentbody" };
/* Retrive Multiple Query */
//QueryExpression RetrieveMultiple = new QueryExpression("annotation");
//RetrieveMultiple.ColumnSet = new ColumnSet(columnSets);
//LinkEntity linktoAccount = new LinkEntity("annotation", "account", "objectid", "accountid", JoinOperator.Inner);
//linktoAccount.LinkCriteria.AddCondition("name", ConditionOperator.Equal, "Choho Winery");
//RetrieveMultiple.LinkEntities.Add(linktoAccount);
QueryExpression RetrieveMultiple = new QueryExpression("annotation");
RetrieveMultiple.ColumnSet = new ColumnSet(columnSets);
RetrieveMultiple.Criteria.AddCondition("annotationid", ConditionOperator.Equal, attachmentId);
var conversionRequest = new QueryExpressionToFetchXmlRequest
{
Query = RetrieveMultiple
};
var response = (QueryExpressionToFetchXmlResponse)service.Execute(conversionRequest);
// Use the converted query to make a retrieve multiple request to Microsoft Dynamics CRM.
String RetrieveMultipleXML = response.FetchXml;
EntityCollection retrieveMulipleCollection = service.RetrieveMultiple(RetrieveMultiple);
Console.WriteLine("Retrieve Multiple Query Result with Length : " + readLength(retrieveMulipleCollection.Entities[0]));
/****** End Here *********/
/* x-Author Query */
QueryExpression xAthorQuery = new QueryExpression()
{
Distinct = true,
EntityName = "annotation",
ColumnSet = new ColumnSet(columnSets),
Criteria =
{
Filters =
{
new FilterExpression
{
FilterOperator = Microsoft.Xrm.Sdk.Query.LogicalOperator.And,
Conditions =
{
new ConditionExpression("annotationid", ConditionOperator.Equal, attachmentId)
}
}
}
}
};
conversionRequest = new QueryExpressionToFetchXmlRequest
{
Query = xAthorQuery
};
response = (QueryExpressionToFetchXmlResponse)service.Execute(conversionRequest);
// Use the converted query to make a retrieve multiple request to Microsoft Dynamics CRM.
RetrieveMultipleXML = response.FetchXml;
EntityCollection xAthorQueryCollection = service.RetrieveMultiple(xAthorQuery);
Console.WriteLine("Retrieve Multiple Query Result with Length : " + readLength(xAthorQueryCollection.Entities[0]));
/******* End Here *******/
/* Retrieve by Primary Key */
Entity SingleResponse = service.Retrieve("annotation", attachmentId, new ColumnSet(columnSets));
Console.WriteLine("Retrieve Multiple Query Result with Length : " + readLength(SingleResponse));
Console.ReadLine();
}
private QueryExpressionToFetchXmlResponse ExecuteInternal(QueryExpressionToFetchXmlRequest request)
{
return new QueryExpressionToFetchXmlResponse {["FetchXml"] = LocalCrmDatabase.ConvertQueryExpressionToFetchXml(request.Query as QueryExpression)};
}
public static void AppendValue(IOrganizationService service, object typeFullname, StringBuilder sb, object value)
{
switch (typeFullname)
{
case "System.Decimal":
case "System.Double":
case "System.Int32":
case "System.Boolean":
case "System.Single":
case "System.String":
case "System.Guid":
sb.Append('"');
sb.Append(value.ToString().Replace("\"", """));
sb.Append('"');
break;
case "System.DateTime":
sb.Append('"');
sb.Append(((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss \"GMT\"zzz"));
sb.Append('"');
break;
case "Microsoft.Xrm.Sdk.EntityReference":
var entityReference = (EntityReference)value;
sb.Append($"Id: \"{entityReference.Id}\", LogicalName: \"{entityReference.LogicalName}\"");
break;
case "Microsoft.Xrm.Sdk.Entity":
var entity = value as Entity;
if (entity == null)
{
sb.Append("NULL");
}
else
{
var tab = " ";
sb.Append("\r\n{\r\n");
sb.Append($"{tab}Id: {entity.Id},\r\n{tab}LogicalName: {entity.LogicalName}");
foreach (var attr in entity.Attributes)
{
var attrType = (object)attr.Value?.GetType().FullName;
sb.Append($",\r\n{tab}{attr.Key}: ");
AppendValue(service, attrType, sb, attr.Value);
}
sb.AppendLine("\r\n}");
}
break;
case "Microsoft.Xrm.Sdk.Query.ColumnSet":
sb.Append("[");
var colSet = value as ColumnSet;
if (colSet?.Columns.Count > 0)
{
foreach (var col in colSet.Columns)
{
sb.Append($"\"{col}\", ");
}
sb.Remove(sb.Length - 2, 2);
}
sb.Append(" ]");
break;
case "Microsoft.Xrm.Sdk.Query.QueryExpression":
QueryExpressionToFetchXmlRequest request = new QueryExpressionToFetchXmlRequest();
request.Query = (QueryExpression)value;
var queryExpression = (QueryExpression)value;
var top = queryExpression.TopCount.HasValue ? queryExpression.TopCount.ToString() : null;
sb.Append($"EntityName: \"{queryExpression.EntityName}\", Top: \"{top}\"");
sb.Append(", Columns:");
AppendValue(service, "Microsoft.Xrm.Sdk.Query.ColumnSet", sb, queryExpression.ColumnSet);
sb.AppendLine(", ");
var response = (QueryExpressionToFetchXmlResponse)service.Execute(request);
sb.Append("Query: \r\n");
sb.Append(FormatXml(response.FetchXml));
break;
case "Microsoft.Xrm.Sdk.Query.FetchExpression":
var fetchExpression = (FetchExpression)value;
sb.Append("Query: \r\n");
sb.Append(FormatXml(fetchExpression.Query));
break;
case "Microsoft.Xrm.Sdk.EntityCollection":
var entityCollection = (EntityCollection)value;
sb.Append("[");
foreach (var e in entityCollection.Entities)
{
sb.AppendLine(string.Empty);
AppendValue(service, e.GetType().FullName, sb, e);
sb.Append(", ");
}
if (entityCollection.Entities.Count > 0)
{
sb.Remove(sb.Length - 2, 2);
}
break;
case "Microsoft.Xrm.Sdk.OptionSetValue":
sb.Append('"');
sb.Append(((OptionSetValue)value).Value);
sb.Append('"');
break;
case "Microsoft.Xrm.Sdk.Money":
sb.Append('"');
sb.Append(((Money)value).Value.ToString(CultureInfo.CurrentCulture));
sb.Append('"');
break;
case "Microsoft.Xrm.Sdk.ConcurrencyBehavior":
sb.Append('"');
sb.Append(((ConcurrencyBehavior)value).ToString());
sb.Append('"');
break;
default:
sb.Append($"\"Undefined Type - {typeFullname}, Value: {value}\"");
break;
}
}
private void DoQueryExpressionToFetchXmlConversion()
{
//<snippetFetchXmlAndQueryExpressionQueryConversion2>
// Build a query expression that we will turn into FetchXML.
var queryExpression = new QueryExpression()
{
Distinct = false,
EntityName = Contact.EntityLogicalName,
ColumnSet = new ColumnSet("fullname", "address1_telephone1"),
LinkEntities =
{
new LinkEntity
{
JoinOperator = JoinOperator.LeftOuter,
LinkFromAttributeName = "parentcustomerid",
LinkFromEntityName = Contact.EntityLogicalName,
LinkToAttributeName = "accountid",
LinkToEntityName = Account.EntityLogicalName,
LinkCriteria =
{
Conditions =
{
new ConditionExpression("name", ConditionOperator.Equal, "Litware, Inc.")
}
}
}
},
Criteria =
{
Filters =
{
new FilterExpression
{
FilterOperator = LogicalOperator.And,
Conditions =
{
new ConditionExpression("address1_stateorprovince", ConditionOperator.Equal, "WA"),
new ConditionExpression("address1_city", ConditionOperator.In, new String[] {"Redmond", "Bellevue" , "Kirkland", "Seattle"}),
new ConditionExpression("createdon", ConditionOperator.LastXDays, 30),
new ConditionExpression("emailaddress1", ConditionOperator.NotNull)
},
},
new FilterExpression
{
FilterOperator = LogicalOperator.Or,
Conditions =
{
new ConditionExpression("address1_telephone1", ConditionOperator.Like, "(206)%"),
new ConditionExpression("address1_telephone1", ConditionOperator.Like, "(425)%")
}
}
}
}
};
// Run the query as a query expression.
EntityCollection queryExpressionResult =
_serviceProxy.RetrieveMultiple(queryExpression);
Console.WriteLine("Output for query as QueryExpression:");
DisplayContactQueryResults(queryExpressionResult);
// Convert the query expression to FetchXML.
var conversionRequest = new QueryExpressionToFetchXmlRequest
{
Query = queryExpression
};
var conversionResponse =
(QueryExpressionToFetchXmlResponse)_serviceProxy.Execute(conversionRequest);
// Use the converted query to make a retrieve multiple request to Microsoft Dynamics CRM.
String fetchXml = conversionResponse.FetchXml;
var fetchQuery = new FetchExpression(fetchXml);
EntityCollection result = _serviceProxy.RetrieveMultiple(fetchQuery);
// Display the results.
Console.WriteLine("\nOutput for query after conversion to FetchXML:");
DisplayContactQueryResults(result);
//</snippetFetchXmlAndQueryExpressionQueryConversion2>
}
private void SetRelationshipDetailsAttributes(pavelkh_advancedmultiselectitemsetconfiguration itemSetConfig)
{
var pluginContext = this.PluginContext;
pluginContext.Trace("Filling in Item Set Details...");
var relationshipName = itemSetConfig.pavelkh_RelationshipName;
var relationship = MetadataUtils.GetManyToManyRelationshipMetadata(
pluginContext,
relationshipName);
if (relationship == null)
{
throw new InvalidPluginExecutionException(
$"{this.GetGenericItemSetSavingErrorMessage(itemSetConfig)} The '{relationshipName}' relationship is not found.");
}
var target = pluginContext.InputTarget;
var currentEntityName = itemSetConfig.pavelkh_EntityName;
string intersectCurrentEntityRefAttributeName;
string intersectItemSetEntityRefAttributeName;
if (relationship.Entity1LogicalName == currentEntityName)
{
target.pavelkh_ItemSetEntityName = relationship.Entity2LogicalName;
target.pavelkh_EntityAttributeName = relationship.Entity1IntersectAttribute;
target.pavelkh_ItemSetEntityAttributeName = relationship.Entity2IntersectAttribute;
intersectCurrentEntityRefAttributeName = relationship.Entity1IntersectAttribute;
intersectItemSetEntityRefAttributeName = relationship.Entity2IntersectAttribute;
}
else
{
if (relationship.Entity2LogicalName != currentEntityName)
{
throw new InvalidPluginExecutionException(
$"{this.GetGenericItemSetSavingErrorMessage(itemSetConfig)} { currentEntityName } Entity is not associated with the { relationshipName } relationship.");
}
target.pavelkh_ItemSetEntityName = relationship.Entity1LogicalName;
target.pavelkh_EntityAttributeName = relationship.Entity2IntersectAttribute;
target.pavelkh_ItemSetEntityAttributeName = relationship.Entity1IntersectAttribute;
intersectCurrentEntityRefAttributeName = relationship.Entity2IntersectAttribute;
intersectItemSetEntityRefAttributeName = relationship.Entity1IntersectAttribute;
}
target.pavelkh_IsCustomRelationship = relationship.IsCustomRelationship;
target.pavelkh_IntersectEntityName = relationship.IntersectEntityName;
target.pavelkh_IntersectEntityRefAttributeName = $"{IntersectLinkedEntityAlias}.{intersectCurrentEntityRefAttributeName}";
itemSetConfig.pavelkh_ItemSetEntityName = target.pavelkh_ItemSetEntityName;
#region Figure Out ItemSet Entity PrimaryKey Attribute Name
var props = new MetadataPropertiesExpression {
AllProperties = false
};
props.PropertyNames.Add("PrimaryIdAttribute");
var filter = new MetadataFilterExpression();
filter.Conditions.Add(new MetadataConditionExpression(
"LogicalName",
MetadataConditionOperator.Equals,
target.pavelkh_ItemSetEntityName));
var query = new EntityQueryExpression {
Properties = props, Criteria = filter
};
var request = new RetrieveMetadataChangesRequest {
Query = query
};
var service = pluginContext.ServiceAsSystemUser;
var response = (RetrieveMetadataChangesResponse)service.Execute(request);
var entityMetadata = response.EntityMetadata;
// ReSharper disable PossibleNullReferenceException
var itemSetEntityPrimaryKeyAttributeName =
entityMetadata.FirstOrDefault().PrimaryIdAttribute;
// ReSharper restore PossibleNullReferenceException
#endregion
#region Build Additional Fetch Xml Prepared FetchXml Query Templates
var fetchXmlToQueryRequest = new FetchXmlToQueryExpressionRequest {
FetchXml = itemSetConfig.pavelkh_FetchXml
};
QueryExpression queryExpression;
try
{
queryExpression = ((FetchXmlToQueryExpressionResponse)service.Execute(fetchXmlToQueryRequest)).Query;
}
catch (Exception exc)
{
throw new InvalidPluginExecutionException(
$"Fetch Xml query is not valid. Details: {exc.Message}");
}
var itemSetLinkedEntity = queryExpression.AddLink(
relationship.IntersectEntityName, itemSetEntityPrimaryKeyAttributeName,
intersectItemSetEntityRefAttributeName, JoinOperator.Inner);
itemSetLinkedEntity.EntityAlias = IntersectLinkedEntityAlias;
itemSetLinkedEntity.LinkCriteria = new FilterExpression();
itemSetLinkedEntity.LinkCriteria.AddCondition(intersectCurrentEntityRefAttributeName, ConditionOperator.Equal, ItemSetBuilder.FetchXmlEntityIdPlaceHolder);
var baseColumnSet = new ColumnSet(queryExpression.ColumnSet.Columns.ToArray());
queryExpression.ColumnSet = new ColumnSet(false);
var queryToFetchXmlRequest = new QueryExpressionToFetchXmlRequest {
Query = queryExpression
};
var fetchXml = ((QueryExpressionToFetchXmlResponse)service.Execute(queryToFetchXmlRequest)).FetchXml;
target.pavelkh_FetchXmlForIntersect = fetchXml;
queryExpression.ColumnSet = baseColumnSet;
itemSetLinkedEntity.JoinOperator = JoinOperator.LeftOuter;
itemSetLinkedEntity.Columns.AddColumn(intersectCurrentEntityRefAttributeName);
if (itemSetConfig.pavelkh_AutoProcessItemStatus == true)
{
var statusAttrName = itemSetConfig.pavelkh_AutoprocessItemStatusAttributeName;
var columns = queryExpression.ColumnSet.Columns;
var statusAttrExists = columns.Contains(statusAttrName);
if (!statusAttrExists)
{
columns.Add(statusAttrName);
}
var mainFilter = new FilterExpression(LogicalOperator.And);
var leftOuterJoinFilter = new FilterExpression(LogicalOperator.Or);
leftOuterJoinFilter.AddCondition(statusAttrName, ConditionOperator.Equal, 0);
leftOuterJoinFilter.AddCondition(IntersectLinkedEntityAlias, intersectCurrentEntityRefAttributeName, ConditionOperator.NotNull);
mainFilter.AddFilter(leftOuterJoinFilter);
if (queryExpression.Criteria != null)
{
mainFilter.AddFilter(queryExpression.Criteria);
}
queryExpression.Criteria = mainFilter;
}
queryToFetchXmlRequest = new QueryExpressionToFetchXmlRequest {
Query = queryExpression
};
fetchXml = ((QueryExpressionToFetchXmlResponse)service.Execute(queryToFetchXmlRequest)).FetchXml;
target.pavelkh_FetchXmlForEditMode = fetchXml;
#endregion
}
