/// <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;
}
}
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");
}
/// <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");
}
protected override IEnumerable <Entity> ExecuteInternal(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IDictionary <string, object> parameterValues)
{
PagesRetrieved = 0;
if (!dataSources.TryGetValue(DataSource, out var dataSource))
{
throw new NotSupportedQueryFragmentException("Missing datasource " + DataSource);
}
ReturnFullSchema = false;
var schema = GetSchema(dataSources, parameterTypes);
// Apply any variable conditions
if (parameterValues != null)
{
if (_parameterizedConditions == null)
{
FindParameterizedConditions();
}
foreach (var param in parameterValues)
{
if (_parameterizedConditions.TryGetValue(param.Key, out var condition))
{
condition.SetValue(param.Value, options);
}
}
}
FindEntityNameGroupings(dataSource.Metadata);
var mainEntity = FetchXml.Items.OfType <FetchEntityType>().Single();
var name = mainEntity.name;
var meta = dataSource.Metadata[name];
if (!(Parent is PartitionedAggregateNode))
{
options.Progress(0, $"Retrieving {GetDisplayName(0, meta)}...");
}
// Get the first page of results
options.RetrievingNextPage();
// Ensure we reset the page number & cookie for subsequent executions
if (_resetPage)
{
FetchXml.page = _startingPage;
FetchXml.pagingcookie = null;
}
else
{
_startingPage = FetchXml.page;
_resetPage = true;
}
var res = dataSource.Connection.RetrieveMultiple(new FetchExpression(Serialize(FetchXml)));
PagesRetrieved++;
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 FaultException <OrganizationServiceFault>(new OrganizationServiceFault {
ErrorCode = -2147164125, Message = "AggregateQueryRecordLimitExceeded"
});
}
// Aggregate queries with grouping on lookup columns don't provide reliable paging as the sorting is done by the name of the related
// record, not the guid. Non-aggregate queries can also be sorted on the primary key as a tie-breaker.
if (res.MoreRecords && FetchXml.aggregateSpecified && FetchXml.aggregate && ContainsSortOnLookupAttribute(dataSource.Metadata, Entity.name, Entity.Items, out var lookupAttr))
{
throw new InvalidPagingException($"{lookupAttr.name} is a lookup attribute - paging with a sort order on this attribute is not reliable.");
}
foreach (var entity in res.Entities)
{
OnRetrievedEntity(entity, schema, options, dataSource.Metadata);
yield return(entity);
}
// Move on to subsequent pages
while (AllPages && res.MoreRecords && options.ContinueRetrieve(count))
{
if (!(Parent is PartitionedAggregateNode))
{
options.Progress(0, $"Retrieved {count:N0} {GetDisplayName(count, meta)}...");
}
if (FetchXml.page == null)
{
FetchXml.page = "2";
}
else
{
FetchXml.page = (Int32.Parse(FetchXml.page, CultureInfo.InvariantCulture) + 1).ToString();
}
FetchXml.pagingcookie = res.PagingCookie;
options.RetrievingNextPage();
var nextPage = dataSource.Connection.RetrieveMultiple(new FetchExpression(Serialize(FetchXml)));
PagesRetrieved++;
foreach (var entity in nextPage.Entities)
{
OnRetrievedEntity(entity, schema, options, dataSource.Metadata);
yield return(entity);
}
count += nextPage.Entities.Count;
res = nextPage;
}
}
public void Progress(double?progress, string message)
{
_options.Progress(progress, message);
}
protected override IEnumerable<Entity> ExecuteInternal(IDictionary<string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary<string, Type> parameterTypes, IDictionary<string, object> parameterValues)
{
var schema = Source.GetSchema(dataSources, parameterTypes);
var groupByCols = GetGroupingColumns(schema);
var groups = new ConcurrentDictionary<Entity, Dictionary<string, AggregateFunctionState>>(new DistinctEqualityComparer(groupByCols));
InitializePartitionedAggregates(schema, parameterTypes);
var aggregates = CreateAggregateFunctions(parameterValues, options, true);
var fetchXmlNode = (FetchXmlScan)Source;
var name = fetchXmlNode.Entity.name;
var meta = dataSources[fetchXmlNode.DataSource].Metadata[name];
options.Progress(0, $"Partitioning {GetDisplayName(0, meta)}...");
// Get the minimum and maximum primary keys from the source
var minKey = GetMinMaxKey(fetchXmlNode, dataSources, options, parameterTypes, parameterValues, false);
var maxKey = GetMinMaxKey(fetchXmlNode, dataSources, options, parameterTypes, parameterValues, true);
if (minKey.IsNull || maxKey.IsNull || minKey == maxKey)
throw new QueryExecutionException("Cannot partition query");
// Add the filter to the FetchXML to partition the results
fetchXmlNode.Entity.AddItem(new filter
{
Items = new object[]
{
new condition { attribute = "createdon", @operator = @operator.gt, value = "@PartitionStart" },
new condition { attribute = "createdon", @operator = @operator.le, value = "@PartitionEnd" }
}
});
var partitionParameterTypes = new Dictionary<string, Type>
{
["@PartitionStart"] = typeof(SqlDateTime),
["@PartitionEnd"] = typeof(SqlDateTime)
};
if (parameterTypes != null)
{
foreach (var kvp in parameterTypes)
partitionParameterTypes[kvp.Key] = kvp.Value;
}
if (minKey > maxKey)
throw new QueryExecutionException("Cannot partition query");
// Split recursively, add up values below & above split value if query returns successfully, or re-split on error
// Range is > MinValue AND <= MaxValue, so start from just before first record to ensure the first record is counted
var fullRange = new Partition
{
MinValue = minKey.Value.AddSeconds(-1),
MaxValue = maxKey,
Percentage = 1
};
_queue = new BlockingCollection<Partition>();
_pendingPartitions = 1;
SplitPartition(fullRange);
// Multi-thread where possible
var org = dataSources[fetchXmlNode.DataSource].Connection;
var maxDop = options.MaxDegreeOfParallelism;
_lock = new object();
#if NETCOREAPP
var svc = org as ServiceClient;
if (maxDop <= 1 || svc == null || svc.ActiveAuthenticationType != Microsoft.PowerPlatform.Dataverse.Client.AuthenticationType.OAuth)
{
maxDop = 1;
svc = null;
}
#else
var svc = org as CrmServiceClient;
if (maxDop <= 1 || svc == null || svc.ActiveAuthenticationType != Microsoft.Xrm.Tooling.Connector.AuthenticationType.OAuth)
{
maxDop = 1;
svc = null;
}
#endif
try
{
Parallel.For(0, maxDop, index =>
{
var ds = new Dictionary<string, DataSource>
{
[fetchXmlNode.DataSource] = new DataSource
{
Connection = svc?.Clone() ?? org,
Metadata = dataSources[fetchXmlNode.DataSource].Metadata,
Name = fetchXmlNode.DataSource,
TableSizeCache = dataSources[fetchXmlNode.DataSource].TableSizeCache
}
};
var fetch = new FetchXmlScan
{
Alias = fetchXmlNode.Alias,
DataSource = fetchXmlNode.DataSource,
FetchXml = CloneFetchXml(fetchXmlNode.FetchXml),
Parent = this
};
var partitionParameterValues = new Dictionary<string, object>
{
["@PartitionStart"] = minKey,
["@PartitionEnd"] = maxKey
};
if (parameterValues != null)
{
foreach (var kvp in parameterValues)
partitionParameterValues[kvp.Key] = kvp.Value;
}
foreach (var partition in _queue.GetConsumingEnumerable())
{
try
{
// Execute the query for this partition
ExecuteAggregate(ds, options, partitionParameterTypes, partitionParameterValues, aggregates, groups, fetch, partition.MinValue, partition.MaxValue);
lock (_lock)
{
_progress += partition.Percentage;
options.Progress(0, $"Partitioning {GetDisplayName(0, meta)} ({_progress:P0})...");
}
if (Interlocked.Decrement(ref _pendingPartitions) == 0)
_queue.CompleteAdding();
}
catch (Exception ex)
{
lock (_queue)
{
if (!GetOrganizationServiceFault(ex, out var fault))
{
_queue.CompleteAdding();
throw;
}
if (!IsAggregateQueryLimitExceeded(fault))
{
_queue.CompleteAdding();
throw;
}
SplitPartition(partition);
}
}
}
// Merge the stats from this clone of the FetchXML node so we can still see total number of executions etc.
// in the main query plan.
lock (fetchXmlNode)
{
fetchXmlNode.MergeStatsFrom(fetch);
}
});
}
catch (AggregateException aggEx)
{
throw aggEx.InnerExceptions[0];
}
foreach (var group in groups)
{
var result = new Entity();
for (var i = 0; i < groupByCols.Count; i++)
result[groupByCols[i]] = group.Key[groupByCols[i]];
foreach (var aggregate in GetValues(group.Value))
result[aggregate.Key] = aggregate.Value;
yield return result;
}
}
