/// <summary>
/// Retrieves all the data matched by the <see cref="FetchXml"/>
/// </summary>
/// <param name="org">The <see cref="IOrganizationService"/> to execute the query against</param>
/// <param name="metadata">The metadata cache to use when executing the query</param>
/// <param name="options">The options to apply to the query execution</param>
/// <returns>The records matched by the query</returns>
protected EntityCollection RetrieveAll(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options)
{
if (options.Cancelled)
{
return(null);
}
try
{
var res = new EntityCollection(RetrieveSequence(org, metadata, options).ToList());
res.EntityName = FetchXml.Items.OfType <FetchEntityType>().Single().name;
return(res);
}
catch (Exception ex)
{
// Attempt to handle aggregate queries that go over the standard FetchXML limit by rewriting them to retrieve the
// individual records and apply the aggregation in-memory
if (!ex.Message.Contains("AggregateQueryRecordLimit"))
{
throw;
}
if (AggregateAlternative == null)
{
throw;
}
return(AggregateAlternative.RetrieveAll(org, metadata, options));
}
}
public TableSizeCache(IOrganizationService org, IAttributeMetadataCache metadata)
{
_tableSize = new Dictionary <string, int>(StringComparer.OrdinalIgnoreCase);
_org = org;
_metadata = metadata;
_version = new Version(((RetrieveVersionResponse)_org.Execute(new RetrieveVersionRequest())).Version);
}
private void FindEntityNameGroupings(IAttributeMetadataCache metadata)
{
_entityNameGroupings = new HashSet <string>();
if (FetchXml.aggregateSpecified && FetchXml.aggregate)
{
FindEntityNameGroupings(metadata, Entity.name, Entity.Items);
}
}
/// <summary>
/// Converts a FetchXML query to SQL
/// </summary>
/// <param name="metadata">The metadata cache to use for the conversion</param>
/// <param name="fetch">The FetchXML string to convert</param>
/// <returns>The converted SQL query</returns>
public static string Convert(IAttributeMetadataCache metadata, string fetch)
{
using (var stream = new MemoryStream(Encoding.UTF8.GetBytes(fetch)))
{
var serializer = new XmlSerializer(typeof(FetchXml.FetchType));
var parsed = (FetchXml.FetchType)serializer.Deserialize(stream);
return(Convert(metadata, parsed));
}
}
/// <summary>
/// Retrieves all the data matched by the <see cref="FetchXml"/>
/// </summary>
/// <param name="org">The <see cref="IOrganizationService"/> to execute the query against</param>
/// <param name="metadata">The metadata cache to use when executing the query</param>
/// <param name="options">The options to apply to the query execution</param>
/// <returns>The records matched by the query, with any custom filters, calculated fields and sorted applied</returns>
protected IEnumerable <Entity> RetrieveSequence(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options)
{
var sequence = RetrieveSequenceInternal(org, metadata, options);
foreach (var extension in Extensions)
{
sequence = extension.ApplyTo(sequence, options);
}
return(sequence);
}
/// <summary>
/// Executes the query
/// </summary>
/// <param name="org">The <see cref="IOrganizationService"/> to execute the query against</param>
/// <param name="metadata">The metadata cache to use when executing the query</param>
/// <param name="options">The options to apply to the query execution</param>
/// <remarks>
/// After calling this method, the results can be retrieved from the <see cref="Result"/> property.
/// </remarks>
public void Execute(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options)
{
try
{
Result = ExecuteInternal(org, metadata, options);
}
catch (Exception ex)
{
Result = ex;
}
}
/// <summary>
/// Converts a FetchXML <filter> to a SQL condition
/// </summary>
/// <param name="metadata">The metadata cache to use for the conversion</param>
/// <param name="items">The items in the <entity> or <link-entity> to process the <filter> from</param>
/// <param name="prefix">The alias or name of the table that the <filter> applies to</param>
/// <param name="aliasToLogicalName">The mapping of table alias to logical name</param>
/// <returns>The SQL condition equivalent of the <filter> found in the <paramref name="items"/>, or <c>null</c> if no filter was found</returns>
private static BooleanExpression GetFilter(IAttributeMetadataCache metadata, object[] items, string prefix, IDictionary <string, string> aliasToLogicalName)
{
if (items == null)
{
return(null);
}
var filter = items.OfType <filter>().SingleOrDefault();
if (filter == null)
{
return(null);
}
return(GetFilter(metadata, filter, prefix, aliasToLogicalName));
}
protected override Entity[] GetValues(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options)
{
return(Values
.Select(dictionary =>
{
var entity = new Entity(LogicalName);
foreach (var attr in dictionary)
{
entity[attr.Key] = attr.Value;
}
return entity;
})
.ToArray());
}
/// <summary>
/// Converts a FetchXML <filter> to a SQL condition
/// </summary>
/// <param name="metadata">The metadata cache to use for the conversion</param>
/// <param name="filter">The FetchXML filter to convert</param>
/// <param name="prefix">The alias or name of the table that the <paramref name="filter"/> applies to</param>
/// <param name="aliasToLogicalName">The mapping of table alias to logical name</param>
/// <returns>The SQL condition equivalent of the <paramref name="filter"/></returns>
private static BooleanExpression GetFilter(IAttributeMetadataCache metadata, filter filter, string prefix, IDictionary <string, string> aliasToLogicalName)
{
BooleanExpression expression = null;
var type = filter.type == filterType.and ? BooleanBinaryExpressionType.And : BooleanBinaryExpressionType.Or;
// Convert each <condition> within the filter
foreach (var condition in filter.Items.OfType <condition>())
{
var newExpression = GetCondition(metadata, condition, prefix, aliasToLogicalName);
if (expression == null)
{
expression = newExpression;
}
else
{
expression = new BooleanBinaryExpression
{
FirstExpression = expression,
BinaryExpressionType = type,
SecondExpression = newExpression
}
};
}
// Recurse into sub-<filter>s
foreach (var subFilter in filter.Items.OfType <filter>())
{
var newExpression = GetFilter(metadata, subFilter, prefix, aliasToLogicalName);
if (expression == null)
{
expression = newExpression;
}
else
{
expression = new BooleanBinaryExpression
{
FirstExpression = expression,
BinaryExpressionType = type,
SecondExpression = newExpression
}
};
}
return(expression);
}
protected override Entity[] GetValues(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options)
{
Source.Execute(org, metadata, options);
if (Source.Result is Exception ex)
{
throw ex;
}
if (!(Source.Result is EntityCollection entities))
{
return(null);
}
var converted = new List <Entity>(entities.Entities.Count);
foreach (var entity in entities.Entities)
{
if (options.Cancelled)
{
break;
}
var newEntity = new Entity(LogicalName);
foreach (var attr in Mappings)
{
object value = null;
if (entity.Contains(attr.Key))
{
value = entity[attr.Key];
}
if (value is Guid g)
{
value = new EntityReference(entity.LogicalName, g);
}
newEntity[attr.Value] = value;
}
converted.Add(newEntity);
}
return(converted.ToArray());
}
/// <inheritdoc/>
protected override object ExecuteInternal(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options)
{
// Shortcut getting the total number of records in an entity where possible
if (RetrieveTotalRecordCount(org, metadata, out var result))
{
return(result);
}
// Run the raw SQL query against the T-SQL endpoint
if (ExecuteTSQL(org, options, out var dataTable))
{
return(dataTable);
}
// Execute the FetchXML
return(RetrieveAll(org, metadata, options));
}
private bool ContainsSortOnLookupAttribute(IAttributeMetadataCache metadata, string logicalName, object[] items, out FetchAttributeType lookupAttr)
{
if (items == null)
{
lookupAttr = null;
return(false);
}
foreach (var order in items.OfType <FetchOrderType>())
{
if (!String.IsNullOrEmpty(order.alias))
{
lookupAttr = items.OfType <FetchAttributeType>().FirstOrDefault(attr => attr.alias.Equals(order.alias, StringComparison.OrdinalIgnoreCase));
}
else
{
lookupAttr = items.OfType <FetchAttributeType>().FirstOrDefault(attr => attr.name.Equals(order.attribute, StringComparison.OrdinalIgnoreCase));
}
if (lookupAttr == null)
{
continue;
}
var meta = metadata[logicalName];
var attrName = lookupAttr.name;
var attrMetadata = meta.Attributes.SingleOrDefault(a => a.LogicalName.Equals(attrName, StringComparison.OrdinalIgnoreCase));
if (attrMetadata is LookupAttributeMetadata)
{
return(true);
}
}
foreach (var linkEntity in items.OfType <FetchLinkEntityType>())
{
if (ContainsSortOnLookupAttribute(metadata, linkEntity.name, linkEntity.Items, out lookupAttr))
{
return(true);
}
}
lookupAttr = null;
return(false);
}
public SqlQueryControl(ConnectionDetail con, IAttributeMetadataCache metadata, TelemetryClient ai, Action <WorkAsyncInfo> workAsync, Action <string> setWorkingMessage, Action <Action> executeMethod, Action <MessageBusEventArgs> outgoingMessageHandler, string sourcePlugin)
{
InitializeComponent();
_displayName = $"Query {++_queryCounter}";
_modified = true;
Service = con.ServiceClient;
Metadata = metadata;
WorkAsync = workAsync;
SetWorkingMessage = setWorkingMessage;
ExecuteMethod = executeMethod;
OutgoingMessageHandler = outgoingMessageHandler;
_editor = CreateSqlEditor();
_sourcePlugin = sourcePlugin;
_ai = ai;
_con = con;
SyncTitle();
splitContainer.Panel1.Controls.Add(_editor);
Icon = _sqlIcon;
}
private void FindEntityNameGroupings(IAttributeMetadataCache metadata, string logicalName, object[] items)
{
if (items == null)
{
return;
}
foreach (var attr in items.OfType <FetchAttributeType>().Where(a => a.groupbySpecified && a.groupby == FetchBoolType.@true))
{
var attributeMetadata = metadata[logicalName].Attributes.Single(a => a.LogicalName == attr.name);
if (attributeMetadata.AttributeType == AttributeTypeCode.EntityName)
{
_entityNameGroupings.Add(attr.alias);
}
}
foreach (var linkEntity in items.OfType <FetchLinkEntityType>())
{
FindEntityNameGroupings(metadata, linkEntity.name, linkEntity.Items);
}
}
/// <summary> /// Performs the actual query execution. Any exception thrown here will be captured in the <see cref="Result"/> property. /// </summary> /// <param name="org">The <see cref="IOrganizationService"/> to execute the query against</param> /// <param name="metadata">The metadata cache to use when executing the query</param> /// <param name="options">The options to apply to the query execution</param> protected abstract object ExecuteInternal(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options);
/// <inheritdoc/>
protected override object ExecuteInternal(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options)
{
// Check if the query is allowed
if (options.Cancelled)
{
return(null);
}
if (options.BlockDeleteWithoutWhere && !FetchXml.Items.OfType <FetchEntityType>().Single().Items.OfType <filter>().Any())
{
throw new InvalidOperationException("DELETE without WHERE is blocked by your settings");
}
var meta = metadata[EntityName];
// If we are using a bulk delete job, start the job
if (options.UseBulkDelete && Extensions.Count == 0 && meta.IsIntersect != true)
{
var query = ((FetchXmlToQueryExpressionResponse)org.Execute(new FetchXmlToQueryExpressionRequest {
FetchXml = Serialize(FetchXml)
})).Query;
var bulkDelete = new BulkDeleteRequest
{
JobName = $"SQL 4 CDS {meta.DisplayCollectionName.UserLocalizedLabel.Label} Bulk Delete Job",
QuerySet = new[] { query },
StartDateTime = DateTime.Now,
RunNow = true,
RecurrencePattern = String.Empty,
SendEmailNotification = false,
ToRecipients = new Guid[0],
CCRecipients = new Guid[0]
};
org.Execute(bulkDelete);
return("Bulk delete job started");
}
// Otherwise, get the records to delete
var count = 0;
var entities = RetrieveAll(org, metadata, options).Entities;
if (entities == null)
{
return(null);
}
// Check again if the query is allowed
if (!options.ConfirmDelete(entities.Count, meta))
{
throw new OperationCanceledException("DELETE cancelled by user");
}
ExecuteMultipleRequest multiple = null;
// Delete hte records in batches
foreach (var entity in entities)
{
if (options.Cancelled)
{
break;
}
if (options.BatchSize == 1)
{
options.Progress($"Deleting {meta.DisplayName.UserLocalizedLabel.Label} {count + 1:N0} of {entities.Count:N0}...");
org.Execute(CreateDeleteRequest(meta, entity));
count++;
}
else
{
if (multiple == null)
{
multiple = new ExecuteMultipleRequest
{
Requests = new OrganizationRequestCollection(),
Settings = new ExecuteMultipleSettings
{
ContinueOnError = false,
ReturnResponses = false
}
};
}
multiple.Requests.Add(CreateDeleteRequest(meta, entity));
if (multiple.Requests.Count == options.BatchSize)
{
options.Progress($"Deleting {meta.DisplayCollectionName.UserLocalizedLabel.Label} {count + 1:N0} - {count + multiple.Requests.Count:N0} of {entities.Count:N0}...");
var resp = (ExecuteMultipleResponse)org.Execute(multiple);
if (resp.IsFaulted)
{
throw new ApplicationException($"Error deleting {meta.DisplayCollectionName.UserLocalizedLabel.Label}");
}
count += multiple.Requests.Count;
multiple = null;
}
}
}
if (!options.Cancelled && multiple != null)
{
options.Progress($"Deleting {meta.DisplayCollectionName.UserLocalizedLabel.Label} {count + 1:N0} - {count + multiple.Requests.Count:N0}...");
var resp = (ExecuteMultipleResponse)org.Execute(multiple);
if (resp.IsFaulted)
{
throw new ApplicationException($"Error deleting {meta.DisplayCollectionName.UserLocalizedLabel.Label}");
}
count += multiple.Requests.Count;
}
return($"{count:N0} {meta.DisplayCollectionName.UserLocalizedLabel.Label} deleted");
}
/// <inheritdoc/>
protected override object ExecuteInternal(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options)
{
if (options.Cancelled)
{
return(null);
}
// Check if the update is allowed
if (options.BlockUpdateWithoutWhere && !FetchXml.Items.OfType <FetchEntityType>().Single().Items.OfType <filter>().Any())
{
throw new InvalidOperationException("UPDATE without WHERE is blocked by your settings");
}
// Get the records to update
var count = 0;
var entities = RetrieveAll(org, metadata, options).Entities;
if (entities == null)
{
return(null);
}
var meta = metadata[EntityName];
// Check again that the update is allowed
if (!options.ConfirmUpdate(entities.Count, meta))
{
throw new OperationCanceledException("UPDATE cancelled by user");
}
// Apply the update in batches
ExecuteMultipleRequest multiple = null;
foreach (var entity in entities)
{
if (options.Cancelled)
{
break;
}
var id = entity[IdColumn];
if (id is AliasedValue alias)
{
id = alias.Value;
}
var update = new Entity(EntityName);
update.Id = (Guid)id;
foreach (var attr in Updates)
{
update[attr.Key] = attr.Value(entity);
}
if (options.BatchSize == 1)
{
options.Progress($"Updating {meta.DisplayName?.UserLocalizedLabel?.Label} {count + 1:N0} of {entities.Count:N0}...");
org.Update(update);
count++;
}
else
{
if (multiple == null)
{
multiple = new ExecuteMultipleRequest
{
Requests = new OrganizationRequestCollection(),
Settings = new ExecuteMultipleSettings
{
ContinueOnError = false,
ReturnResponses = false
}
};
}
multiple.Requests.Add(new UpdateRequest {
Target = update
});
if (multiple.Requests.Count == options.BatchSize)
{
options.Progress($"Updating {meta.DisplayCollectionName?.UserLocalizedLabel?.Label} {count + 1:N0} - {count + multiple.Requests.Count:N0} of {entities.Count:N0}...");
var resp = (ExecuteMultipleResponse)org.Execute(multiple);
if (resp.IsFaulted)
{
throw new ApplicationException($"Error updating {meta.DisplayCollectionName?.UserLocalizedLabel?.Label}");
}
count += multiple.Requests.Count;
multiple = null;
}
}
}
if (!options.Cancelled && multiple != null)
{
options.Progress($"Updating {meta.DisplayCollectionName?.UserLocalizedLabel?.Label} {count + 1:N0} - {count + multiple.Requests.Count:N0} of {entities.Count:N0}...");
var resp = (ExecuteMultipleResponse)org.Execute(multiple);
if (resp.IsFaulted)
{
throw new ApplicationException($"Error updating {meta.DisplayCollectionName?.UserLocalizedLabel?.Label}");
}
count += multiple.Requests.Count;
}
return($"{count:N0} {meta.DisplayCollectionName?.UserLocalizedLabel?.Label} updated");
}
/// <summary>
/// Converts a FetchXML query to SQL
/// </summary>
/// <param name="metadata">The metadata cache to use for the conversion</param>
/// <param name="fetch">The query object to convert</param>
/// <returns>The converted SQL query</returns>
public static string Convert(IAttributeMetadataCache metadata, FetchXml.FetchType fetch)
{
var select = new SelectStatement();
var query = new QuerySpecification();
select.QueryExpression = query;
if (fetch.top != null)
{
query.TopRowFilter = new TopRowFilter {
Expression = new IntegerLiteral {
Value = fetch.top
}
}
}
;
if (fetch.distinct)
{
query.UniqueRowFilter = UniqueRowFilter.Distinct;
}
// SELECT (columns from first table)
var entity = fetch.Items.OfType <FetchEntityType>().SingleOrDefault();
AddSelectElements(query, entity.Items, entity?.name);
// FROM
var aliasToLogicalName = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
if (entity != null)
{
query.FromClause = new FromClause
{
TableReferences =
{
new NamedTableReference
{
SchemaObject = new SchemaObjectName
{
Identifiers =
{
new Identifier {
Value = entity.name
}
}
}
}
}
};
if (fetch.nolock)
{
((NamedTableReference)query.FromClause.TableReferences[0]).TableHints.Add(new TableHint {
HintKind = TableHintKind.NoLock
});
}
// Recurse into link-entities to build joins
query.FromClause.TableReferences[0] = BuildJoins(metadata, query.FromClause.TableReferences[0], (NamedTableReference)query.FromClause.TableReferences[0], entity.Items, query, aliasToLogicalName, fetch.nolock);
}
// OFFSET
if (!String.IsNullOrEmpty(fetch.page) && fetch.page != "1")
{
var page = Int32.Parse(fetch.page);
var pageSize = Int32.Parse(fetch.count);
query.OffsetClause = new OffsetClause
{
OffsetExpression = new IntegerLiteral {
Value = ((page - 1) * pageSize).ToString()
},
FetchExpression = new IntegerLiteral {
Value = fetch.count
}
};
}
// WHERE
var filter = GetFilter(metadata, entity.Items, entity.name, aliasToLogicalName);
if (filter != null)
{
query.WhereClause = new WhereClause
{
SearchCondition = filter
};
}
// ORDER BY
AddOrderBy(entity.name, entity.Items, query);
// For single-table queries, don't bother qualifying the column names to make the query easier to read
if (query.FromClause.TableReferences[0] is NamedTableReference)
{
select.Accept(new SimplifyMultiPartIdentifierVisitor(entity.name));
}
// Check whether each identifier needs to be quoted so we have minimal quoting to make the query easier to read
select.Accept(new QuoteIdentifiersVisitor());
new Sql150ScriptGenerator().GenerateScript(select, out var sql);
return(sql);
}
/// <summary>
/// Get the total number of records in an entity
/// </summary>
/// <param name="org">The <see cref="IOrganizationService"/> to execute the query against</param>
/// <param name="metadata">The metadata cache to use when executing the query</param>
/// <returns><c>true</c> if this method has retrieved the requested details, or <c>false</c> otherwise</returns>
private bool RetrieveTotalRecordCount(IOrganizationService org, IAttributeMetadataCache metadata, out EntityCollection result)
{
result = null;
if (FetchXml == null)
{
return(false);
}
if (Extensions.Count > 0)
{
return(false);
}
// Special case - SELECT count(primaryid) with no filter
if (!FetchXml.aggregate)
{
return(false);
}
var entity = FetchXml.Items.OfType <FetchEntityType>().Single();
var attributes = entity.Items.OfType <FetchAttributeType>().ToArray();
if (attributes.Length != 1 || attributes[0].aggregate != AggregateType.count)
{
return(false);
}
var filters = entity.Items.OfType <filter>().Count();
var links = entity.Items.OfType <FetchLinkEntityType>().Count();
if (filters != 0 || links != 0)
{
return(false);
}
if (attributes[0].name != metadata[entity.name].PrimaryIdAttribute)
{
return(false);
}
// RetrieveTotalRecordCountRequest is only supported in v9+
var version = (RetrieveVersionResponse)org.Execute(new RetrieveVersionRequest());
if (!Version.TryParse(version.Version, out var serverVersion) || serverVersion.Major < 9)
{
return(false);
}
var count = ((RetrieveTotalRecordCountResponse)org.Execute(new RetrieveTotalRecordCountRequest {
EntityNames = new[] { entity.name }
})).EntityRecordCountCollection[entity.name];
var resultEntity = new Entity(entity.name)
{
[attributes[0].alias] = new AliasedValue(entity.name, attributes[0].name, count)
};
result = new EntityCollection {
EntityName = entity.name, Entities = { resultEntity }
};
return(true);
}
/// <summary>
/// Recurse through link-entities to add joins to FROM clause and update SELECT clause
/// </summary>
/// <param name="metadata">The metadata cache to use for the conversion</param>
/// <param name="dataSource">The current data source of the SQL query</param>
/// <param name="parentTable">The details of the table that this new table is being linked to</param>
/// <param name="items">The FetchXML items in this entity</param>
/// <param name="query">The current state of the SQL query being built</param>
/// <param name="aliasToLogicalName">A mapping of table aliases to the logical name</param>
/// <param name="nolock">Indicates if the NOLOCK table hint should be applied</param>
/// <returns>The data source including any required joins</returns>
private static TableReference BuildJoins(IAttributeMetadataCache metadata, TableReference dataSource, NamedTableReference parentTable, object[] items, QuerySpecification query, IDictionary <string, string> aliasToLogicalName, bool nolock)
{
if (items == null)
{
return(dataSource);
}
// Find any <link-entity> elements to process
foreach (var link in items.OfType <FetchLinkEntityType>())
{
// Store the alias of this link
if (!String.IsNullOrEmpty(link.alias))
{
aliasToLogicalName[link.alias] = link.name;
}
// Create the new table reference
var table = new NamedTableReference
{
SchemaObject = new SchemaObjectName
{
Identifiers =
{
new Identifier
{
Value = link.name
}
}
},
Alias = String.IsNullOrEmpty(link.alias) ? null : new Identifier {
Value = link.alias
}
};
if (nolock)
{
table.TableHints.Add(new TableHint {
HintKind = TableHintKind.NoLock
});
}
// Add the join from the current data source to the new table
var join = new QualifiedJoin
{
FirstTableReference = dataSource,
SecondTableReference = table,
QualifiedJoinType = link.linktype == "outer" ? QualifiedJoinType.LeftOuter : QualifiedJoinType.Inner,
SearchCondition = new BooleanComparisonExpression
{
FirstExpression = new ColumnReferenceExpression
{
MultiPartIdentifier = new MultiPartIdentifier
{
Identifiers =
{
new Identifier {
Value = parentTable.Alias?.Value ?? parentTable.SchemaObject.Identifiers.Last().Value
},
new Identifier {
Value = link.to
}
}
}
},
ComparisonType = BooleanComparisonType.Equals,
SecondExpression = new ColumnReferenceExpression
{
MultiPartIdentifier = new MultiPartIdentifier
{
Identifiers =
{
new Identifier {
Value = link.alias ?? link.name
},
new Identifier {
Value = link.from
}
}
}
}
}
};
// Update the SELECT clause
AddSelectElements(query, link.Items, link.alias ?? link.name);
// Handle any filters within the <link-entity> as additional join criteria
var filter = GetFilter(metadata, link.Items, link.alias ?? link.name, aliasToLogicalName);
if (filter != null)
{
var finalFilter = new BooleanBinaryExpression
{
FirstExpression = join.SearchCondition,
BinaryExpressionType = BooleanBinaryExpressionType.And,
SecondExpression = filter
};
join.SearchCondition = finalFilter;
}
// Recurse into any other links
dataSource = BuildJoins(metadata, join, (NamedTableReference)join.SecondTableReference, link.Items, query, aliasToLogicalName, nolock);
}
return(dataSource);
}
/// <summary>
/// Converts a FetchXML <condition> to a SQL condition
/// </summary>
/// <param name="metadata">The metadata cache to use for the conversion</param>
/// <param name="condition">The FetchXML condition to convert</param>
/// <param name="prefix">The alias or name of the table that the <paramref name="condition"/> applies to</param>
/// <param name="aliasToLogicalName">The mapping of table alias to logical name</param>
/// <returns>The SQL condition equivalent of the <paramref name="condition"/></returns>
private static BooleanExpression GetCondition(IAttributeMetadataCache metadata, condition condition, string prefix, IDictionary <string, string> aliasToLogicalName)
{
// Start with the field reference
var field = new ColumnReferenceExpression
{
MultiPartIdentifier = new MultiPartIdentifier
{
Identifiers =
{
new Identifier {
Value = condition.entityname ?? prefix
},
new Identifier {
Value = condition.attribute
}
}
}
};
// Get the metadata for the attribute
BooleanComparisonType type;
ScalarExpression value;
if (!aliasToLogicalName.TryGetValue(condition.entityname ?? prefix, out var logicalName))
{
logicalName = condition.entityname ?? prefix;
}
var meta = metadata[logicalName];
var attr = meta.Attributes.SingleOrDefault(a => a.LogicalName == condition.attribute);
// Get the literal value to compare to
if (attr == null)
{
value = new StringLiteral {
Value = condition.value
}
}
;
else if (attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.BigInt ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Integer ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Picklist ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.State ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Status)
{
value = new IntegerLiteral {
Value = condition.value
}
}
;
else if (attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Boolean)
{
value = new BinaryLiteral {
Value = condition.value
}
}
;
else if (attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Decimal ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Double)
{
value = new NumericLiteral {
Value = condition.value
}
}
;
else if (attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Money)
{
value = new MoneyLiteral {
Value = condition.value
}
}
;
else if (attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Lookup ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Owner ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Customer)
{
value = new IdentifierLiteral {
Value = condition.value
}
}
;
else
{
value = new StringLiteral {
Value = condition.value
}
};
// Apply the appropriate conversion for the type of operator
switch (condition.@operator)
{
case @operator.above:
case @operator.containvalues:
case @operator.eqbusinessid:
case @operator.eqorabove:
case @operator.eqorunder:
case @operator.equserid:
case @operator.equserlanguage:
case @operator.equseroruserhierarchy:
case @operator.equseroruserhierarchyandteams:
case @operator.equseroruserteams:
case @operator.equserteams:
case @operator.infiscalperiod:
case @operator.infiscalperiodandyear:
case @operator.infiscalyear:
case @operator.inorafterfiscalperiodandyear:
case @operator.inorbeforefiscalperiodandyear:
case @operator.lastfiscalperiod:
case @operator.lastfiscalyear:
case @operator.lastmonth:
case @operator.lastsevendays:
case @operator.lastweek:
case @operator.lastxdays:
case @operator.lastxfiscalperiods:
case @operator.lastxfiscalyears:
case @operator.lastxhours:
case @operator.lastxmonths:
case @operator.lastxweeks:
case @operator.lastxyears:
case @operator.lastyear:
case @operator.nebusinessid:
case @operator.neuserid:
case @operator.nextfiscalperiod:
case @operator.nextfiscalyear:
case @operator.nextmonth:
case @operator.nextsevendays:
case @operator.nextweek:
case @operator.nextxdays:
case @operator.nextxfiscalperiods:
case @operator.nextxfiscalyears:
case @operator.nextxhours:
case @operator.nextxmonths:
case @operator.nextxweeks:
case @operator.nextxyears:
case @operator.nextyear:
case @operator.notcontainvalues:
case @operator.notunder:
case @operator.olderthanxdays:
case @operator.olderthanxhours:
case @operator.olderthanxminutes:
case @operator.olderthanxmonths:
case @operator.olderthanxweeks:
case @operator.olderthanxyears:
case @operator.on:
case @operator.onorafter:
case @operator.onorbefore:
case @operator.thisfiscalperiod:
case @operator.thisfiscalyear:
case @operator.thismonth:
case @operator.thisweek:
case @operator.thisyear:
case @operator.today:
case @operator.tomorrow:
case @operator.under:
case @operator.yesterday:
// These FetchXML operators don't have a direct SQL equivalent, so convert to the format
// field = function(arg)
// so <condition attribute="createdon" operator="lastxdays" value="2" /> will be converted to
// createdon = lastxdays(2)
type = BooleanComparisonType.Equals;
value = new FunctionCall {
FunctionName = new Identifier {
Value = [email protected]()
}
};
if (condition.value != null)
{
((FunctionCall)value).Parameters.Add(new StringLiteral {
Value = condition.value
});
}
break;
case @operator.beginswith:
case @operator.notbeginwith:
return(new LikePredicate {
FirstExpression = field, SecondExpression = new StringLiteral {
Value = condition.value + "%"
}, NotDefined = condition.@operator == @operator.notbeginwith
});
case @operator.between:
case @operator.notbetween:
return(new BooleanTernaryExpression {
FirstExpression = field, TernaryExpressionType = condition.@operator == @operator.between ? BooleanTernaryExpressionType.Between : BooleanTernaryExpressionType.NotBetween, SecondExpression = new StringLiteral {
Value = condition.Items[0].Value
}, ThirdExpression = new StringLiteral {
Value = condition.Items[1].Value
}
});
case @operator.endswith:
case @operator.notendwith:
return(new LikePredicate {
FirstExpression = field, SecondExpression = new StringLiteral {
Value = "%" + condition.value
}, NotDefined = condition.@operator == @operator.notendwith
});
case @operator.eq:
type = BooleanComparisonType.Equals;
break;
case @operator.ge:
type = BooleanComparisonType.GreaterThanOrEqualTo;
break;
case @operator.gt:
type = BooleanComparisonType.GreaterThan;
break;
case @operator.@in:
case @operator.notin:
var @in = new InPredicate {
Expression = field, NotDefined = condition.@operator == @operator.notin
};
foreach (var val in condition.Items)
{
@in.Values.Add(new StringLiteral {
Value = val.Value
});
}
return(@in);
case @operator.le:
type = BooleanComparisonType.LessThanOrEqualTo;
break;
case @operator.like:
case @operator.notlike:
return(new LikePredicate {
FirstExpression = field, SecondExpression = new StringLiteral {
Value = condition.value
}, NotDefined = condition.@operator == @operator.notlike
});
case @operator.lt:
type = BooleanComparisonType.LessThan;
break;
case @operator.ne:
case @operator.neq:
type = BooleanComparisonType.NotEqualToBrackets;
break;
case @operator.@null:
case @operator.notnull:
return(new BooleanIsNullExpression {
Expression = field, IsNot = condition.@operator == @operator.notnull
});
default:
throw new NotImplementedException();
}
var expression = new BooleanComparisonExpression
{
FirstExpression = field,
ComparisonType = type,
SecondExpression = value
};
return(expression);
}
/// <summary>
/// Creates a new <see cref="MetaMetadataCache"/>
/// </summary>
/// <param name="inner">The <see cref="IAttributeMetadataCache"/> that provides the metadata for the standard data entities</param>
public MetaMetadataCache(IAttributeMetadataCache inner)
{
_inner = inner;
}
/// <summary>
/// Creates a new <see cref="Autocomplete"/>
/// </summary>
/// <param name="entities">The list of entities available to use in the query</param>
/// <param name="metadata">The cache of metadata about each entity</param>
public Autocomplete(EntityMetadata[] entities, IAttributeMetadataCache metadata)
{
_entities = entities;
_metadata = metadata;
}
internal FetchAttributeType AddAttribute(string colName, Func <FetchAttributeType, bool> predicate, IAttributeMetadataCache metadata, out bool added, out FetchLinkEntityType linkEntity)
{
var parts = colName.Split('.');
if (parts.Length == 1)
{
added = false;
return(Entity.FindAliasedAttribute(colName, predicate, out linkEntity));
}
var entityName = parts[0];
var attr = new FetchAttributeType {
name = parts[1].ToLowerInvariant()
};
if (Alias == entityName)
{
linkEntity = null;
var meta = metadata[Entity.name].Attributes.SingleOrDefault(a => a.LogicalName == attr.name && a.AttributeOf == null);
if (meta == null && (attr.name.EndsWith("name") || attr.name.EndsWith("type")))
{
var logicalName = attr.name.Substring(0, attr.name.Length - 4);
meta = metadata[Entity.name].Attributes.SingleOrDefault(a => a.LogicalName == logicalName && a.AttributeOf == null);
if (meta != null)
{
attr.name = logicalName;
}
}
if (Entity.Items != null)
{
var existing = Entity.Items.OfType <FetchAttributeType>().FirstOrDefault(a => a.name == attr.name || a.alias == attr.name);
if (existing != null && (predicate == null || predicate(existing)))
{
added = false;
return(existing);
}
}
Entity.AddItem(attr);
}
else
{
linkEntity = Entity.FindLinkEntity(entityName);
var meta = metadata[linkEntity.name].Attributes.SingleOrDefault(a => a.LogicalName == attr.name && a.AttributeOf == null);
if (meta == null && (attr.name.EndsWith("name") || attr.name.EndsWith("type")))
{
var logicalName = attr.name.Substring(0, attr.name.Length - 4);
meta = metadata[linkEntity.name].Attributes.SingleOrDefault(a => a.LogicalName == logicalName && a.AttributeOf == null);
if (meta != null)
{
attr.name = logicalName;
}
}
if (linkEntity.Items != null)
{
var existing = linkEntity.Items.OfType <FetchAttributeType>().FirstOrDefault(a => a.name == attr.name || a.alias == attr.name);
if (existing != null && (predicate == null || predicate(existing)))
{
added = false;
return(existing);
}
}
linkEntity.AddItem(attr);
}
added = true;
return(attr);
}
/// <summary> /// Returns a sequence of the entities to insert /// </summary> /// <returns></returns> protected abstract Entity[] GetValues(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options);
protected override object ExecuteInternal(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options)
{
var meta = metadata[LogicalName];
// Add each record in turn
var count = 0;
var entities = GetValues(org, metadata, options);
if (entities != null)
{
foreach (var entity in entities)
{
if (options.Cancelled)
{
break;
}
// Special cases for intersect entities
if (LogicalName == "listmember")
{
var listId = entity.GetAttributeValue <EntityReference>("listid");
var entityId = entity.GetAttributeValue <EntityReference>("entityid");
if (listId == null)
{
throw new ApplicationException("listid is required");
}
if (entityId == null)
{
throw new ApplicationException("entityid is required");
}
org.Execute(new AddMemberListRequest
{
ListId = listId.Id,
EntityId = entityId.Id
});
}
else if (meta.IsIntersect == true)
{
// For generic intersect entities we expect a single many-to-many relationship in the metadata which describes
// the relationship that this is the intersect entity for
var relationship = meta.ManyToManyRelationships.Single();
var entity1 = entity.GetAttributeValue <EntityReference>(relationship.Entity1IntersectAttribute);
var entity2 = entity.GetAttributeValue <EntityReference>(relationship.Entity2IntersectAttribute);
if (entity1 == null)
{
throw new ApplicationException($"{relationship.Entity1IntersectAttribute} is required");
}
if (entity2 == null)
{
throw new ApplicationException($"{relationship.Entity2IntersectAttribute} is required");
}
org.Execute(new AssociateRequest
{
Target = entity1,
Relationship = new Relationship(relationship.SchemaName)
{
PrimaryEntityRole = EntityRole.Referencing
},
RelatedEntities = new EntityReferenceCollection(new[] { entity2 })
});
}
else
{
org.Create(entity);
}
count++;
options.Progress($"Inserted {count:N0} of {entities.Length:N0} {meta.DisplayCollectionName.UserLocalizedLabel.Label} ({(float)count / entities.Length:P0})");
}
}
return($"{entities.Length:N0} {meta.DisplayCollectionName.UserLocalizedLabel.Label} inserted");
}
/// <summary>
/// Retrieves all the data matched by the <see cref="FetchXml"/>
/// </summary>
/// <param name="org">The <see cref="IOrganizationService"/> to execute the query against</param>
/// <param name="metadata">The metadata cache to use when executing the query</param>
/// <param name="options">The options to apply to the query execution</param>
/// <returns>The records matched by the query, with any custom filters and calculated fields applied</returns>
private IEnumerable <Entity> RetrieveSequenceInternal(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options)
{
if (options.Cancelled)
{
yield break;
}
var mainEntity = FetchXml.Items.OfType <FetchEntityType>().Single();
var name = mainEntity.name;
var meta = metadata[name];
options.Progress($"Retrieving {meta.DisplayCollectionName?.UserLocalizedLabel?.Label}...");
// Get the first page of results
var res = org.RetrieveMultiple(new FetchExpression(Serialize(FetchXml)));
foreach (var entity in res.Entities)
{
yield return(entity);
}
var count = res.Entities.Count;
// Aggregate queries return up to 5000 records and don't provide a method to move on to the next page
// Throw an exception to indicate the error to the caller
if (AllPages && FetchXml.aggregateSpecified && FetchXml.aggregate && count == 5000 && FetchXml.top != "5000" && !res.MoreRecords)
{
throw new ApplicationException("AggregateQueryRecordLimit");
}
// Move on to subsequent pages
while (AllPages && res.MoreRecords && !options.Cancelled && options.ContinueRetrieve(count))
{
options.Progress($"Retrieved {count:N0} {meta.DisplayCollectionName?.UserLocalizedLabel?.Label}...");
if (FetchXml.page == null)
{
FetchXml.page = "2";
}
else
{
FetchXml.page = (Int32.Parse(FetchXml.page) + 1).ToString();
}
FetchXml.pagingcookie = res.PagingCookie;
var nextPage = org.RetrieveMultiple(new FetchExpression(Serialize(FetchXml)));
foreach (var entity in nextPage.Entities)
{
yield return(entity);
}
count += nextPage.Entities.Count;
res = nextPage;
}
}
private void AddSchemaAttributes(NodeSchema schema, IAttributeMetadataCache metadata, string entityName, string alias, object[] items)
{
if (items == null && !ReturnFullSchema)
{
return;
}
var meta = metadata[entityName];
if (ReturnFullSchema)
{
foreach (var attrMetadata in meta.Attributes)
{
if (attrMetadata.IsValidForRead == false)
{
continue;
}
if (attrMetadata.AttributeOf != null)
{
continue;
}
var fullName = $"{alias}.{attrMetadata.LogicalName}";
var attrType = attrMetadata.GetAttributeSqlType();
AddSchemaAttribute(schema, fullName, attrMetadata.LogicalName, attrType, attrMetadata);
}
}
if (items != null)
{
foreach (var attribute in items.OfType <FetchAttributeType>())
{
var attrMetadata = meta.Attributes.Single(a => a.LogicalName == attribute.name);
var attrType = attrMetadata.GetAttributeSqlType();
if (attribute.aggregateSpecified && (attribute.aggregate == Engine.FetchXml.AggregateType.count || attribute.aggregate == Engine.FetchXml.AggregateType.countcolumn) ||
attribute.dategroupingSpecified)
{
attrType = typeof(SqlInt32);
}
string fullName;
string attrAlias;
if (!String.IsNullOrEmpty(attribute.alias))
{
if (!FetchXml.aggregate || attribute.groupbySpecified && attribute.groupby == FetchBoolType.@true)
{
fullName = $"{alias}.{attribute.alias}";
attrAlias = attribute.alias;
}
else
{
fullName = attribute.alias;
attrAlias = null;
}
}
else
{
fullName = $"{alias}.{attribute.name}";
attrAlias = attribute.name;
}
AddSchemaAttribute(schema, fullName, attrAlias, attrType, attrMetadata);
}
if (items.OfType <allattributes>().Any())
{
foreach (var attrMetadata in meta.Attributes)
{
if (attrMetadata.IsValidForRead == false)
{
continue;
}
if (attrMetadata.AttributeOf != null)
{
continue;
}
var attrType = attrMetadata.GetAttributeSqlType();
var attrName = attrMetadata.LogicalName;
var fullName = $"{alias}.{attrName}";
AddSchemaAttribute(schema, fullName, attrName, attrType, attrMetadata);
}
}
foreach (var sort in items.OfType <FetchOrderType>())
{
string fullName;
string attributeName;
if (!String.IsNullOrEmpty(sort.alias))
{
var attribute = items.OfType <FetchAttributeType>().SingleOrDefault(a => a.alias.Equals(sort.alias, StringComparison.OrdinalIgnoreCase));
if (!FetchXml.aggregate || attribute != null && attribute.groupbySpecified && attribute.groupby == FetchBoolType.@true)
{
fullName = $"{alias}.{attribute.alias}";
}
else
{
fullName = attribute.alias;
}
attributeName = attribute.name;
}
else
{
fullName = $"{alias}.{sort.attribute}";
attributeName = sort.attribute;
}
// Sorts applied to lookup or enum fields are actually performed on the associated ___name virtual attribute
var attrMeta = meta.Attributes.SingleOrDefault(a => a.LogicalName.Equals(attributeName, StringComparison.OrdinalIgnoreCase));
if (attrMeta is LookupAttributeMetadata || attrMeta is EnumAttributeMetadata || attrMeta is BooleanAttributeMetadata)
{
fullName += "name";
}
schema.SortOrder.Add(fullName);
}
foreach (var linkEntity in items.OfType <FetchLinkEntityType>())
{
if (linkEntity.SemiJoin)
{
continue;
}
if (schema.PrimaryKey != null)
{
var childMeta = metadata[linkEntity.name];
if (linkEntity.from != childMeta.PrimaryIdAttribute)
{
if (linkEntity.linktype == "inner")
{
schema.PrimaryKey = $"{linkEntity.alias}.{childMeta.PrimaryIdAttribute}";
}
else
{
schema.PrimaryKey = null;
}
}
}
AddSchemaAttributes(schema, metadata, linkEntity.name, linkEntity.alias, linkEntity.Items);
}
}
}
private void OnRetrievedEntity(Entity entity, INodeSchema schema, IQueryExecutionOptions options, IAttributeMetadataCache metadata)
{
// Expose any formatted values for OptionSetValue and EntityReference values
foreach (var formatted in entity.FormattedValues)
{
if (!entity.Contains(formatted.Key + "name"))
{
entity[formatted.Key + "name"] = formatted.Value;
}
}
if (options.UseLocalTimeZone)
{
// For any datetime values, check the metadata to see if they are affected by timezones and convert them
foreach (var attribute in entity.Attributes.ToList())
{
var entityName = entity.LogicalName;
var attributeName = attribute.Key;
var value = attribute.Value;
if (value is AliasedValue alias)
{
entityName = alias.EntityLogicalName;
attributeName = alias.AttributeLogicalName;
value = alias.Value;
}
if (value is DateTime dt)
{
var meta = metadata[entityName];
var attrMeta = (DateTimeAttributeMetadata)meta.Attributes.Single(a => a.LogicalName == attributeName);
if (attrMeta.DateTimeBehavior == DateTimeBehavior.UserLocal)
{
dt = dt.ToLocalTime();
entity[attribute.Key] = dt;
}
}
}
}
// Prefix all attributes of the main entity with the expected alias
foreach (var attribute in entity.Attributes.Where(attr => !attr.Key.Contains('.') && !(attr.Value is AliasedValue)).ToList())
{
entity[$"{Alias}.{attribute.Key}"] = attribute.Value;
}
// Only prefix aliased values if they're not aggregates
PrefixAliasedScalarAttributes(entity, Entity.Items, Alias);
// Convert aliased values to the underlying value
foreach (var attribute in entity.Attributes.Where(attr => attr.Value is AliasedValue).ToList())
{
var aliasedValue = (AliasedValue)attribute.Value;
// When grouping by EntityName attributes the value is converted from the normal string value to an OptionSetValue
// Convert it back now for consistency
if (_entityNameGroupings.Contains(attribute.Key))
{
int otc;
if (aliasedValue.Value is OptionSetValue osv)
{
otc = osv.Value;
}
else if (aliasedValue.Value is int i)
{
otc = i;
}
else
{
throw new QueryExecutionException($"Expected ObjectTypeCode value, got {aliasedValue.Value} ({aliasedValue.Value?.GetType()})");
}
var meta = metadata[otc];
entity[attribute.Key] = meta.LogicalName;
}
else
{
entity[attribute.Key] = aliasedValue.Value;
}
}
// Copy any grouped values to their full names
if (FetchXml.aggregateSpecified && FetchXml.aggregate)
{
if (Entity.Items != null)
{
foreach (var attr in Entity.Items.OfType <FetchAttributeType>().Where(a => a.groupbySpecified && a.groupby == FetchBoolType.@true))
{
if (entity.Attributes.TryGetValue(attr.alias, out var value))
{
entity[$"{Alias}.{attr.alias}"] = value;
}
}
}
foreach (var linkEntity in Entity.GetLinkEntities().Where(le => le.Items != null))
{
foreach (var attr in linkEntity.Items.OfType <FetchAttributeType>().Where(a => a.groupbySpecified && a.groupby == FetchBoolType.@true))
{
if (entity.Attributes.TryGetValue(attr.alias, out var value))
{
entity[$"{linkEntity.alias}.{attr.alias}"] = value;
}
}
}
}
// Expose the type of lookup values
foreach (var attribute in entity.Attributes.Where(attr => attr.Value is EntityReference).ToList())
{
if (!entity.Contains(attribute.Key + "type"))
{
entity[attribute.Key + "type"] = ((EntityReference)attribute.Value).LogicalName;
}
//entity[attribute.Key] = ((EntityReference)attribute.Value).Id;
}
// Convert values to SQL types
foreach (var col in schema.Schema)
{
object sqlValue;
if (entity.Attributes.TryGetValue(col.Key, out var value) && value != null)
{
sqlValue = SqlTypeConverter.NetToSqlType(DataSource, value);
}
else
{
sqlValue = SqlTypeConverter.GetNullValue(col.Value);
}
if (_primaryKeyColumns.TryGetValue(col.Key, out var logicalName) && sqlValue is SqlGuid guid)
{
sqlValue = new SqlEntityReference(DataSource, logicalName, guid);
}
entity[col.Key] = sqlValue;
}
}
