/// <summary> /// Persists the DataView to the database by updating the DataViewPersistedValues for this DataView. Returns true if successful /// </summary> /// <param name="databaseTimeoutSeconds">The database timeout seconds.</param> public void PersistResult(int?databaseTimeoutSeconds = null) { List <string> errorMessages; using (var dbContext = this.GetDbContext()) { Stopwatch persistStopwatch = Stopwatch.StartNew(); Stopwatch stopwatch = Stopwatch.StartNew(); DataViewFilterOverrides dataViewFilterOverrides = new DataViewFilterOverrides(); // set an override so that the Persisted Values aren't used when rebuilding the values from the DataView Query dataViewFilterOverrides.IgnoreDataViewPersistedValues.Add(this.Id); var qry = this.GetQuery(null, dbContext, dataViewFilterOverrides, databaseTimeoutSeconds, out errorMessages); if (!errorMessages.Any()) { RockContext rockContext = dbContext as RockContext; if (rockContext == null) { rockContext = new RockContext(); } rockContext.Database.CommandTimeout = databaseTimeoutSeconds; var savedDataViewPersistedValues = rockContext.DataViewPersistedValues.Where(a => a.DataViewId == this.Id); var updatedEntityIdsQry = qry.Select(a => a.Id); if (!(rockContext is RockContext)) { // if this DataView doesn't use a RockContext get the EntityIds into memory as as a List<int> then back into IQueryable<int> so that we aren't use multiple dbContexts updatedEntityIdsQry = updatedEntityIdsQry.ToList().AsQueryable(); } var persistedValuesToRemove = savedDataViewPersistedValues.Where(a => !updatedEntityIdsQry.Any(x => x == a.EntityId)); var persistedEntityIdsToInsert = updatedEntityIdsQry.Where(x => !savedDataViewPersistedValues.Any(a => a.EntityId == x)).ToList(); stopwatch.Stop(); int removeCount = persistedValuesToRemove.Count(); if (removeCount > 0) { // increase the batch size if there are a bunch of rows (and this is a narrow table with no references to it) int?deleteBatchSize = removeCount > 50000 ? 25000 : ( int? )null; int rowRemoved = rockContext.BulkDelete(persistedValuesToRemove, deleteBatchSize); } if (persistedEntityIdsToInsert.Any()) { List <DataViewPersistedValue> persistedValuesToInsert = persistedEntityIdsToInsert.OrderBy(a => a) .Select(a => new DataViewPersistedValue { DataViewId = this.Id, EntityId = a }).ToList(); rockContext.BulkInsert(persistedValuesToInsert); } persistStopwatch.Stop(); DataViewService.AddRunDataViewTransaction(this.Id, Convert.ToInt32(stopwatch.Elapsed.TotalMilliseconds), Convert.ToInt32(persistStopwatch.Elapsed.TotalMilliseconds)); } } }
/// <summary> /// Gets the expression. /// </summary> /// <param name="serviceInstance">The service instance.</param> /// <param name="paramExpression">The parameter expression.</param> /// <param name="dataViewFilterOverrides">The data view filter overrides.</param> /// <returns></returns> /// <exception cref="Rock.Reporting.RockReportingException"> /// Unable to determine Assembly for EntityTypeId { EntityTypeId } /// or /// Unable to determine DataView EntityType for { dataViewEntityTypeCache }. /// or /// Unable to determine transform expression for TransformEntityTypeId: {TransformEntityTypeId} /// </exception> public Expression GetExpression(IService serviceInstance, ParameterExpression paramExpression, DataViewFilterOverrides dataViewFilterOverrides) { var dataViewEntityTypeCache = EntityTypeCache.Get(EntityTypeId.Value); if (dataViewEntityTypeCache?.AssemblyName == null) { throw new RockDataViewFilterExpressionException(this.DataViewFilter, $"Unable to determine DataView Assembly for EntityTypeId { EntityTypeId }"); } Type dataViewEntityTypeType = dataViewEntityTypeCache.GetEntityType(); if (dataViewEntityTypeType == null) { throw new RockDataViewFilterExpressionException(this.DataViewFilter, $"Unable to determine DataView EntityType for { dataViewEntityTypeCache }."); } // DataViews must have a DataViewFilter (something has gone wrong it doesn't have one) // Note that DataViewFilterId might be null even though DataViewFilter is not null // This is because the DataViewFilter might be just in memory and not saved to the database (for example, a Preview or a DynamicReport) if (this.DataViewFilter == null) { throw new RockDataViewFilterExpressionException(this.DataViewFilter, $"DataViewFilter is null for DataView { this.Name } ({this.Id})."); } bool usePersistedValues = this.PersistedScheduleIntervalMinutes.HasValue && this.PersistedLastRefreshDateTime.HasValue; if (dataViewFilterOverrides != null) { // don't use persisted values if this DataView in the list of DataViews that should not be persisted due to override usePersistedValues = usePersistedValues && !dataViewFilterOverrides.IgnoreDataViewPersistedValues.Contains(this.Id); } // If dataViewFilterOverrides is null assume true in order to preserve current functionality. RockLogger.Log.Debug(RockLogDomains.Reporting, "{methodName} dataViewFilterOverrides: {@dataViewFilterOverrides} DataviewId: {DataviewId}", nameof(GetExpression), dataViewFilterOverrides, DataViewFilter.DataViewId); if (dataViewFilterOverrides == null || dataViewFilterOverrides.ShouldUpdateStatics) { DataViewService.AddRunDataViewTransaction(Id); } // We need to call GetExpression regardless of whether or not usePresistedValues is true so the child queries get their stats updated. var filterExpression = DataViewFilter != null?DataViewFilter.GetExpression(dataViewEntityTypeType, serviceInstance, paramExpression, dataViewFilterOverrides) : null; if (usePersistedValues) { // If this is a persisted DataView, get the ids for the expression by querying DataViewPersistedValue instead of evaluating all the filters var rockContext = serviceInstance.Context as RockContext; if (rockContext == null) { rockContext = new RockContext(); } var persistedValuesQuery = rockContext.DataViewPersistedValues.Where(a => a.DataViewId == this.Id); var ids = persistedValuesQuery.Select(v => v.EntityId); MemberExpression propertyExpression = Expression.Property(paramExpression, "Id"); if (!(serviceInstance.Context is RockContext)) { // if this DataView doesn't use a RockContext get the EntityIds into memory as a List<int> then back into IQueryable<int> so that we aren't use multiple dbContexts ids = ids.ToList().AsQueryable(); } var idsExpression = Expression.Constant(ids.AsQueryable(), typeof(IQueryable <int>)); Expression expression = Expression.Call(typeof(Queryable), "Contains", new Type[] { typeof(int) }, idsExpression, propertyExpression); return(expression); } else { if (dataViewEntityTypeCache.Id == EntityTypeCache.Get(typeof(Rock.Model.Person)).Id) { var qry = new PersonService(( RockContext )serviceInstance.Context).Queryable(this.IncludeDeceased); Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, paramExpression, "p"); if (filterExpression == null) { filterExpression = extractedFilterExpression; } else { filterExpression = Expression.AndAlso(filterExpression, extractedFilterExpression); } } if (this.TransformEntityTypeId.HasValue) { Expression transformedExpression = GetTransformExpression(this.TransformEntityTypeId.Value, serviceInstance, paramExpression, filterExpression); if (transformedExpression == null) { // if TransformEntityTypeId is defined, but we got null back, we'll get unexpected results, so throw an exception throw new RockDataViewFilterExpressionException(this.DataViewFilter, $"Unable to determine transform expression for TransformEntityTypeId: {TransformEntityTypeId}"); } return(transformedExpression); } return(filterExpression); } }
/// <summary> /// Gets the expression. /// </summary> /// <param name="serviceInstance">The service instance.</param> /// <param name="paramExpression">The parameter expression.</param> /// <param name="dataViewFilterOverrides">The data view filter overrides.</param> /// <param name="errorMessages">The error messages.</param> /// <returns></returns> public Expression GetExpression(IService serviceInstance, ParameterExpression paramExpression, DataViewFilterOverrides dataViewFilterOverrides, out List <string> errorMessages) { errorMessages = new List <string>(); var cachedEntityType = EntityTypeCache.Get(EntityTypeId.Value); if (cachedEntityType?.AssemblyName == null) { return(null); } Type filteredEntityType = cachedEntityType.GetEntityType(); if (filteredEntityType == null) { return(null); } bool usePersistedValues = this.PersistedScheduleIntervalMinutes.HasValue && this.PersistedLastRefreshDateTime.HasValue; if (dataViewFilterOverrides != null) { // don't use persisted values if this dataview is in the list of dataviews that should not be persisted due to override usePersistedValues = usePersistedValues && !dataViewFilterOverrides.IgnoreDataViewPersistedValues.Contains(this.Id); } DataViewService.AddRunDataViewTransaction(Id); if (usePersistedValues) { // If this is a persisted dataview, get the ids for the expression by querying DataViewPersistedValue instead of evaluating all the filters var rockContext = serviceInstance.Context as RockContext; if (rockContext == null) { rockContext = new RockContext(); } var persistedValuesQuery = rockContext.DataViewPersistedValues.Where(a => a.DataViewId == this.Id); var ids = persistedValuesQuery.Select(v => v.EntityId); MemberExpression propertyExpression = Expression.Property(paramExpression, "Id"); if (!(serviceInstance.Context is RockContext)) { // if this DataView doesn't use a RockContext get the EntityIds into memory as as a List<int> then back into IQueryable<int> so that we aren't use multiple dbContexts ids = ids.ToList().AsQueryable(); } var idsExpression = Expression.Constant(ids.AsQueryable(), typeof(IQueryable <int>)); Expression expression = Expression.Call(typeof(Queryable), "Contains", new Type[] { typeof(int) }, idsExpression, propertyExpression); return(expression); } else { Expression filterExpression = DataViewFilter != null?DataViewFilter.GetExpression(filteredEntityType, serviceInstance, paramExpression, dataViewFilterOverrides, errorMessages) : null; if (cachedEntityType.Id == EntityTypeCache.Get(typeof(Rock.Model.Person)).Id) { var qry = new PersonService(( RockContext )serviceInstance.Context).Queryable(this.IncludeDeceased); Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, paramExpression, "p"); if (filterExpression == null) { filterExpression = extractedFilterExpression; } else { filterExpression = Expression.AndAlso(filterExpression, extractedFilterExpression); } } Expression transformedExpression = GetTransformExpression(serviceInstance, paramExpression, filterExpression, errorMessages); if (transformedExpression != null) { return(transformedExpression); } return(filterExpression); } }