/// <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; } }