/// <summary>
/// Convert a QueryExpression to FetchXml using QueryExpressionToFetchXmlRequest
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private static string GetFetchXml(QueryExpression query)
{
var xmlWriterSettings = new XmlWriterSettings {
Indent = true
};
QueryExpressionToFetchXmlRequest request = new QueryExpressionToFetchXmlRequest();
request.Query = query;
QueryExpressionToFetchXmlResponse response = (QueryExpressionToFetchXmlResponse)_serviceProxy.Execute(request);
Console.ForegroundColor = ConsoleColor.Blue;
// Format fetchxml
XmlDocument fetchDoc = new XmlDocument();
fetchDoc.LoadXml(response.FetchXml);
StringWriter fetchXmlOutBuffer = new StringWriter();
using (var writer = XmlTextWriter.Create(fetchXmlOutBuffer, xmlWriterSettings))
{
fetchDoc.WriteTo(writer);
}
return(fetchXmlOutBuffer.ToString());
}
private QueryExpressionToFetchXmlResponse ExecuteInternal(QueryExpressionToFetchXmlRequest request)
{
var response = new QueryExpressionToFetchXmlResponse();
response["FetchXml"] = LocalCrmDatabase.ConvertQueryExpressionToFetchXml(request.Query as QueryExpression);
return(response);
}
internal static string queryToFetch(CrmServiceClient service, QueryBase query)
{
QueryExpressionToFetchXmlRequest request = new QueryExpressionToFetchXmlRequest
{
Query = query
};
QueryExpressionToFetchXmlResponse responce = (QueryExpressionToFetchXmlResponse)service.Execute(request);
return(responce.FetchXml);
}
public static string ConvertQueryToFetchXml(QueryExpression query, IOrganizationService crmService)
{
QueryExpressionToFetchXmlRequest request = new QueryExpressionToFetchXmlRequest();
request.Query = query;
QueryExpressionToFetchXmlResponse response
= (QueryExpressionToFetchXmlResponse)crmService.Execute(request);
return(response.FetchXml);
}
public string ConvertQueryToFetch(QueryExpression queryExpression)
{
QueryExpressionToFetchXmlRequest req = new QueryExpressionToFetchXmlRequest
{
Query = queryExpression
};
QueryExpressionToFetchXmlResponse resp = (QueryExpressionToFetchXmlResponse)ServiceProxy.Execute(req);
return(resp.FetchXml);
}
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>
/// 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);
}
private QueryExpressionToFetchXmlResponse ExecuteInternal(QueryExpressionToFetchXmlRequest request)
{
var response = new QueryExpressionToFetchXmlResponse();
response["FetchXml"] = LocalCrmDatabase.ConvertQueryExpressionToFetchXml(request.Query as QueryExpression);
return response;
}
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);
}
}
