public string Execute(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IDictionary <string, object> parameterValues) { _executionCount++; try { using (_timer.Run()) { if (!dataSources.TryGetValue(DataSource, out var dataSource)) { throw new QueryExecutionException("Missing datasource " + DataSource); } #if NETCOREAPP if (dataSource.Connection is ServiceClient svc) { svc.CallerId = Guid.Empty; } #else if (dataSource.Connection is Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy svcProxy) { svcProxy.CallerId = Guid.Empty; } else if (dataSource.Connection is Microsoft.Xrm.Sdk.WebServiceClient.OrganizationWebProxyClient webProxy) { webProxy.CallerId = Guid.Empty; } else if (dataSource.Connection is CrmServiceClient svc) { svc.CallerId = Guid.Empty; } #endif else { throw new QueryExecutionException("Unexpected organization service type") { Node = this } }; return("Reverted impersonation"); } } catch (QueryExecutionException ex) { if (ex.Node == null) { ex.Node = this; } throw; } catch (Exception ex) { throw new QueryExecutionException(ex.Message, ex) { Node = this }; } }
public override string Execute(IDictionary<string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary<string, Type> parameterTypes, IDictionary<string, object> parameterValues) { _executionCount++; try { if (!dataSources.TryGetValue(DataSource, out var dataSource)) throw new QueryExecutionException("Missing datasource " + DataSource); List<Entity> entities; EntityMetadata meta; Dictionary<string, AttributeMetadata> attributes; Dictionary<string, Func<Entity, object>> attributeAccessors; Func<Entity, object> primaryIdAccessor; using (_timer.Run()) { entities = GetDmlSourceEntities(dataSources, options, parameterTypes, parameterValues, out var schema); // Precompile mappings with type conversions meta = dataSource.Metadata[LogicalName]; attributes = meta.Attributes.ToDictionary(a => a.LogicalName); var dateTimeKind = options.UseLocalTimeZone ? DateTimeKind.Local : DateTimeKind.Utc; attributeAccessors = CompileColumnMappings(meta, ColumnMappings, schema, attributes, dateTimeKind); attributeAccessors.TryGetValue(meta.PrimaryIdAttribute, out primaryIdAccessor); } // Check again that the update is allowed. Don't count any UI interaction in the execution time if (options.Cancelled || !options.ConfirmInsert(entities.Count, meta)) throw new OperationCanceledException("INSERT cancelled by user"); using (_timer.Run()) { return ExecuteDmlOperation( dataSource.Connection, options, entities, meta, entity => CreateInsertRequest(meta, entity, attributeAccessors, primaryIdAccessor, attributes), new OperationNames { InProgressUppercase = "Inserting", InProgressLowercase = "inserting", CompletedLowercase = "inserted" }); } } catch (QueryExecutionException ex) { if (ex.Node == null) ex.Node = this; throw; } catch (Exception ex) { throw new QueryExecutionException(ex.Message, ex) { Node = this }; } }
protected virtual void Reset <TSource, TResult>(IQueryable <TSource> queryable, IQueryCriteria criteria, IQueryExecutionOptions options) { ResetInterceptors <TSource, TResult>(criteria, queryable); Criteria = criteria ?? throw new ArgumentNullException("criteria"); QueryableAtStart = queryable ?? throw new ArgumentNullException("queryable"); CurrentQueryable = QueryableAtStart; Options = options; }
internal int GetCount(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IDictionary <string, object> parameterValues) { if (_eagerSpool == null) { _eagerSpool = Source.Execute(dataSources, options, parameterTypes, parameterValues).ToArray(); } return(_eagerSpool.Length); }
public DataTable Execute(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IDictionary <string, object> parameterValues) { _executionCount++; var startTime = DateTime.Now; try { var schema = Source.GetSchema(dataSources, parameterTypes); var dataTable = new DataTable(); foreach (var col in ColumnSet) { var sourceName = col.SourceColumn; if (!schema.ContainsColumn(sourceName, out sourceName)) { throw new QueryExecutionException($"Missing column {col.SourceColumn}") { Node = this } } ; var dataCol = dataTable.Columns.Add(col.PhysicalOutputColumn, schema.Schema[sourceName]); dataCol.Caption = col.OutputColumn; } foreach (var entity in Source.Execute(dataSources, options, parameterTypes, parameterValues)) { var row = dataTable.NewRow(); foreach (var col in ColumnSet) { if (!entity.Contains(col.SourceColumn)) { throw new QueryExecutionException($"Missing column {col.SourceColumn}") { Node = this } } ; row[col.PhysicalOutputColumn] = entity[col.SourceColumn]; } dataTable.Rows.Add(row); } return(dataTable); } finally { var endTime = DateTime.Now; _duration += (endTime - startTime); } }
public string Execute(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IDictionary <string, object> parameterValues) { _executionCount++; try { if (!dataSources.TryGetValue(DataSource, out var dataSource)) { throw new QueryExecutionException("Missing datasource " + DataSource); } using (_timer.Run()) { var query = ((FetchXmlToQueryExpressionResponse)dataSource.Connection.Execute(new FetchXmlToQueryExpressionRequest { FetchXml = FetchXmlString })).Query; var meta = dataSource.Metadata[query.EntityName]; var req = new BulkDeleteRequest { JobName = $"SQL 4 CDS {GetDisplayName(0, meta)} Bulk Delete Job", QuerySet = new[] { query }, StartDateTime = DateTime.Now, RecurrencePattern = String.Empty, SendEmailNotification = false, ToRecipients = Array.Empty <Guid>(), CCRecipients = Array.Empty <Guid>() }; var resp = (BulkDeleteResponse)dataSource.Connection.Execute(req); return($"Bulk delete job started: {resp.JobId}"); } } catch (QueryExecutionException ex) { if (ex.Node == null) { ex.Node = this; } throw; } catch (Exception ex) { throw new QueryExecutionException(ex.Message, ex) { Node = this }; } }
/// <summary> /// Run the raw SQL query against the T-SQL endpoint /// </summary> /// <param name="org">The <see cref="IOrganizationService"/> to execute the query against</param> /// <param name="options">The options that indicate if the T-SQL endpoint should be used</param> /// <param name="result">The results of running the query</param> /// <returns><c>true</c> if this method has executed the query, or <c>false otherwise</c></returns> private bool ExecuteTSQL(IOrganizationService org, IQueryExecutionOptions options, out DataTable result) { result = null; if (String.IsNullOrEmpty(Sql)) { return(false); } if (!options.UseTSQLEndpoint) { return(false); } if (!(org is CrmServiceClient svc)) { return(false); } if (String.IsNullOrEmpty(svc.CurrentAccessToken)) { return(false); } if (!TSqlEndpoint.IsEnabled(svc)) { return(false); } using (var con = new SqlConnection("server=" + svc.CrmConnectOrgUriActual.Host + ",5558")) { con.AccessToken = svc.CurrentAccessToken; con.Open(); using (var cmd = con.CreateCommand()) { cmd.CommandText = Sql; result = new DataTable(); using (var adapter = new SqlDataAdapter(cmd)) { adapter.Fill(result); } return(true); } } }
public IEnumerable <Entity> ApplyTo(IEnumerable <Entity> source, IQueryExecutionOptions options) { foreach (var entity in source) { if (options.Cancelled) { throw new OperationCanceledException(); } foreach (var field in _calculatedFields) { entity[field.Key] = field.Value(entity); } yield return(entity); } }
private void ExecuteAggregate(IDictionary<string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary<string, Type> parameterTypes, IDictionary<string, object> parameterValues, Dictionary<string, AggregateFunction> aggregates, ConcurrentDictionary<Entity, Dictionary<string, AggregateFunctionState>> groups, FetchXmlScan fetchXmlNode, SqlDateTime minValue, SqlDateTime maxValue) { parameterValues["@PartitionStart"] = minValue; parameterValues["@PartitionEnd"] = maxValue; var results = fetchXmlNode.Execute(dataSources, options, parameterTypes, parameterValues); foreach (var entity in results) { // Update aggregates var values = groups.GetOrAdd(entity, _ => ResetAggregates(aggregates)); lock (values) { foreach (var func in values.Values) func.AggregateFunction.NextPartition(entity, func.State); } } }
public IEnumerable <Entity> ApplyTo(IEnumerable <Entity> source, IQueryExecutionOptions options) { var unique = new HashSet <EntityKey>(); foreach (var entity in source) { if (options.Cancelled) { throw new OperationCanceledException(); } var key = new EntityKey(entity); if (unique.Add(key)) { yield return(entity); } } }
public OptionsWrapper(IQueryExecutionOptions options) { _options = options; Cancelled = options.Cancelled; BlockUpdateWithoutWhere = options.BlockUpdateWithoutWhere; BlockDeleteWithoutWhere = options.BlockDeleteWithoutWhere; UseBulkDelete = options.UseBulkDelete; BatchSize = options.BatchSize; UseTDSEndpoint = options.UseTDSEndpoint; UseRetrieveTotalRecordCount = options.UseRetrieveTotalRecordCount; LocaleId = options.LocaleId; MaxDegreeOfParallelism = options.MaxDegreeOfParallelism; ColumnComparisonAvailable = options.ColumnComparisonAvailable; UseLocalTimeZone = options.UseLocalTimeZone; JoinOperatorsAvailable = new List <JoinOperator>(options.JoinOperatorsAvailable); BypassCustomPlugins = options.BypassCustomPlugins; PrimaryDataSource = options.PrimaryDataSource; UserId = options.UserId; }
public void SetValue(object value, IQueryExecutionOptions options) { if (value == null) { if (_filter.Items.Contains(_contradiction)) { return; } _filter.Items = _filter.Items.Except(new[] { _condition }).Concat(new[] { _contradiction }).ToArray(); } else { if (!_filter.Items.Contains(_condition)) { _filter.Items = _filter.Items.Except(new[] { _contradiction }).Concat(new[] { _condition }).ToArray(); } var formatted = value.ToString(); if (value is SqlDateTime dt) { DateTimeOffset dto; if (options.UseLocalTimeZone) { dto = new DateTimeOffset(dt.Value, TimeZoneInfo.Local.GetUtcOffset(dt.Value)); } else { dto = new DateTimeOffset(dt.Value, TimeSpan.Zero); } formatted = dto.ToString("yyyy-MM-ddTHH':'mm':'ss.FFFzzz"); } _condition.value = formatted; } }
public IEnumerable <Entity> ApplyTo(IEnumerable <Entity> source, IQueryExecutionOptions options) { return(source.Take(_top)); }
public override string Execute(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IDictionary <string, object> parameterValues) { _executionCount++; try { if (!dataSources.TryGetValue(DataSource, out var dataSource)) { throw new QueryExecutionException("Missing datasource " + DataSource); } List <Entity> entities; EntityMetadata meta; Func <Entity, object> primaryIdAccessor; Func <Entity, object> secondaryIdAccessor = null; using (_timer.Run()) { entities = GetDmlSourceEntities(dataSources, options, parameterTypes, parameterValues, out var schema); // Precompile mappings with type conversions meta = dataSource.Metadata[LogicalName]; var attributes = meta.Attributes.ToDictionary(a => a.LogicalName); var dateTimeKind = options.UseLocalTimeZone ? DateTimeKind.Local : DateTimeKind.Utc; var primaryKey = meta.PrimaryIdAttribute; string secondaryKey = null; // Special cases for the keys used for intersect entities if (meta.LogicalName == "listmember") { primaryKey = "listid"; secondaryKey = "entityid"; } else if (meta.IsIntersect == true) { var relationship = meta.ManyToManyRelationships.Single(); primaryKey = relationship.Entity1IntersectAttribute; secondaryKey = relationship.Entity2IntersectAttribute; } var fullMappings = new Dictionary <string, string> { [primaryKey] = PrimaryIdSource }; if (secondaryKey != null) { fullMappings[secondaryKey] = SecondaryIdSource; } var attributeAccessors = CompileColumnMappings(meta, fullMappings, schema, attributes, dateTimeKind); primaryIdAccessor = attributeAccessors[primaryKey]; if (SecondaryIdSource != null) { secondaryIdAccessor = attributeAccessors[secondaryKey]; } } // Check again that the update is allowed. Don't count any UI interaction in the execution time if (options.Cancelled || !options.ConfirmDelete(entities.Count, meta)) { throw new OperationCanceledException("DELETE cancelled by user"); } using (_timer.Run()) { return(ExecuteDmlOperation( dataSource.Connection, options, entities, meta, entity => CreateDeleteRequest(meta, entity, primaryIdAccessor, secondaryIdAccessor), new OperationNames { InProgressUppercase = "Deleting", InProgressLowercase = "deleting", CompletedLowercase = "deleted" })); } } catch (QueryExecutionException ex) { if (ex.Node == null) { ex.Node = this; } throw; } catch (Exception ex) { throw new QueryExecutionException(ex.Message, ex) { Node = this }; } }
public DataTable Execute(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IDictionary <string, object> parameterValues) { _executionCount++; var startTime = DateTime.Now; try { if (!dataSources.TryGetValue(DataSource, out var dataSource)) { throw new QueryExecutionException("Missing datasource " + DataSource); } if (options.UseLocalTimeZone) { throw new QueryExecutionException("Cannot use automatic local time zone conversion with the TDS Endpoint"); } #if NETCOREAPP if (!(dataSource.Connection is ServiceClient svc)) { throw new QueryExecutionException($"IOrganizationService implementation needs to be ServiceClient for use with the TDS Endpoint, got {dataSource.Connection.GetType()}"); } #else if (!(dataSource.Connection is CrmServiceClient svc)) { throw new QueryExecutionException($"IOrganizationService implementation needs to be CrmServiceClient for use with the TDS Endpoint, got {dataSource.Connection.GetType()}"); } #endif if (svc.CallerId != Guid.Empty) { throw new QueryExecutionException("Cannot use impersonation with the TDS Endpoint"); } #if NETCOREAPP using (var con = new SqlConnection("server=" + svc.ConnectedOrgUriActual.Host)) #else using (var con = new SqlConnection("server=" + svc.CrmConnectOrgUriActual.Host)) #endif { con.AccessToken = svc.CurrentAccessToken; con.Open(); using (var cmd = con.CreateCommand()) { cmd.CommandTimeout = (int)TimeSpan.FromMinutes(2).TotalSeconds; cmd.CommandText = Sql; var result = new DataTable(); using (var adapter = new SqlDataAdapter(cmd)) { adapter.Fill(result); } // SQL doesn't know the data type of NULL, so SELECT NULL will be returned with a schema type // of SqlInt32. This causes problems trying to convert it to other types for updates/inserts, // so change all-null columns to object // https://github.com/MarkMpn/Sql4Cds/issues/122 var nullColumns = result.Columns .Cast <DataColumn>() .Select((col, colIndex) => result.Rows .Cast <DataRow>() .Select(row => DBNull.Value.Equals(row[colIndex])) .All(isNull => isNull) ) .ToArray(); var columnSqlTypes = result.Columns .Cast <DataColumn>() .Select((col, colIndex) => nullColumns[colIndex] ? typeof(object) : SqlTypeConverter.NetToSqlType(col.DataType)) .ToArray(); var columnNullValues = columnSqlTypes .Select(type => SqlTypeConverter.GetNullValue(type)) .ToArray(); // Values will be stored as BCL types, convert them to SqlXxx types for consistency with IDataExecutionPlanNodes var sqlTable = new DataTable(); for (var i = 0; i < result.Columns.Count; i++) { sqlTable.Columns.Add(result.Columns[i].ColumnName, columnSqlTypes[i]); } foreach (DataRow row in result.Rows) { var sqlRow = sqlTable.Rows.Add(); for (var i = 0; i < result.Columns.Count; i++) { var sqlValue = DBNull.Value.Equals(row[i]) ? columnNullValues[i] : SqlTypeConverter.NetToSqlType(DataSource, row[i]); sqlRow[i] = sqlValue; } } return(sqlTable); } } } catch (QueryExecutionException ex) { if (ex.Node == null) { ex.Node = this; } throw; } catch (Exception ex) { throw new QueryExecutionException(ex.Message, ex) { Node = this }; } finally { var endTime = DateTime.Now; _duration += (endTime - startTime); } }
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()); }
public override IRootExecutionPlanNode FoldQuery(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints) { var result = base.FoldQuery(dataSources, options, parameterTypes, hints); if (result != this) { return(result); } if (!dataSources.TryGetValue(DataSource, out var dataSource)) { throw new NotSupportedQueryFragmentException("Missing datasource " + DataSource); } // Use bulk delete if requested & possible if (options.UseBulkDelete && Source is FetchXmlScan fetch && LogicalName == fetch.Entity.name && PrimaryIdSource.Equals($"{fetch.Alias}.{dataSource.Metadata[LogicalName].PrimaryIdAttribute}") && String.IsNullOrEmpty(SecondaryIdSource)) { return(new BulkDeleteJobNode { DataSource = DataSource, FetchXmlString = fetch.FetchXmlString }); } return(this); }
/// <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 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> /// 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);
/// <summary> /// Returns a sequence of the entities to insert /// </summary> /// <returns></returns> protected abstract Entity[] GetValues(IOrganizationService org, IAttributeMetadataCache metadata, IQueryExecutionOptions options);
/// <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"); }
public override int EstimateRowsOut(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes) { return(Sources.Sum(s => s.EstimateRowsOut(dataSources, options, parameterTypes))); }
/// <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> /// 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)); } }
/// <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)); }
public IRootExecutionPlanNode FoldQuery(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints) { return(this); }
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"); }
/// <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); }