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