/// <summary> /// Convert a FilterDescriptorCollection to FilterExpression /// </summary> /// <param name="filterDescriptors"></param> /// <returns></returns> public static FilterExpression Convert(FilterDescriptorCollection filterDescriptors) { FilterExpression filter = null; FilterExpression tmpFilter = null; List <FilterExpression> tmpFilterExpressions = new List <FilterExpression>(); foreach (var f in filterDescriptors) { if (f.GetType() == typeof(CompositeFilterDescriptor)) { CompositeFilterDescriptor compositeFilterDescriptor = (CompositeFilterDescriptor)f; FilterExpression compositeFilter = null; compositeFilter = Convert(compositeFilterDescriptor.FilterDescriptors); if (compositeFilter != null) { if (filter != null) { tmpFilter = filter; filter = BinaryFilterExpression.And(tmpFilter, compositeFilter); } else { filter = compositeFilter; } } } else if (f.GetType() == typeof(FilterDescriptor)) { FilterExpression fe = convert(f); if (fe != null) { if (filter != null) { tmpFilter = filter; filter = BinaryFilterExpression.And(tmpFilter, fe); } else { filter = fe; } } } } return(filter); }
public void CreateAndExpressionForQueryingTest() { var dsHelper = new DatasetHelper(); StructuredDataStructure dataStructure = dsHelper.CreateADataStructure(); dataStructure.Should().NotBeNull("Failed to meet a precondition: a data strcuture is required."); string var1Name = "var" + dataStructure.Variables.First().Id; string var2Name = "var" + dataStructure.Variables.Skip(1).First().Id; FilterExpression fex = BinaryFilterExpression .And( new FilterNumberItemExpression() { Field = new Field() { DataType = Utils.NH.Querying.DataType.Ineteger, Name = var1Name } , Operator = NumberOperator.Operation.GreaterThan , Value = 12 } , new FilterStringItemExpression() { Field = new Field() { DataType = Utils.NH.Querying.DataType.String, Name = var2Name } , Operator = StringOperator.Operation.EndsWith , Value = "Test" } ); fex.ToSQL().Should().Be($"(({var1Name}) > (12)) AND (({var2Name}) ILIKE ('%Test'))"); // this is to show how to apply a NOT operator on any other expression. // It can be applied on Numeric, String, Date, and any other type of expression FilterExpression notFex = UnaryFilterExpression.Not(fex); notFex.ToSQL().Should().Be($"NOT ((({var1Name}) > (12)) AND (({var2Name}) ILIKE ('%Test')))"); notFex.ToSQL().Should().Be($"NOT ({fex.ToSQL()})"); OrderByExpression orderByExpr = new OrderByExpression( new List <OrderItemExpression>() { new OrderItemExpression(var1Name), new OrderItemExpression(var2Name, SortDirection.Descending) }); orderByExpr.ToSQL().Should().Be($"{var1Name} ASC, {var2Name} DESC"); // create a dataset and test the filter, sorting, and projectgion long numberOfTuples = 100; var dm = new DatasetManager(); var rsm = new ResearchPlanManager(); var mdm = new MetadataStructureManager(); try { dataStructure.Should().NotBeNull("Failed to meet a precondition: a data strcuture is required."); var rp = dsHelper.CreateResearchPlan(); rp.Should().NotBeNull("Failed to meet a precondition: a research plan is required."); var mds = mdm.Repo.Query().First(); mds.Should().NotBeNull("Failed to meet a precondition: a metadata strcuture is required."); Dataset dataset = dm.CreateEmptyDataset(dataStructure, rp, mds); dataset = dsHelper.GenerateTuplesForDataset(dataset, dataStructure, numberOfTuples, "Javad"); dataset.Should().NotBeNull("The dataset tuple generation has failed!"); dm.CheckInDataset(dataset.Id, "for testing purposes 2", "Javad", ViewCreationBehavior.None); dm.SyncView(dataset.Id, ViewCreationBehavior.Create | ViewCreationBehavior.Refresh); dataset.Id.Should().BeGreaterThan(0, "Dataset was not persisted."); dataset.LastCheckIOTimestamp.Should().NotBeAfter(DateTime.UtcNow, "The dataset's timestamp is wrong."); dataset.DataStructure.Should().NotBeNull("Dataset must have a data structure."); dataset.Status.Should().Be(DatasetStatus.CheckedIn, "Dataset must be in the CheckedIn status."); dm.GetDatasetLatestVersionEffectiveTupleCount(dataset.Id).Should().Be(numberOfTuples); // pass this filter to get a subset of dataset X var dst = dm.GetLatestDatasetVersionTuples(dataset.Id, fex, null, null, 1, 10); dst.Should().NotBeNull(); dst.Rows.Count.Should().BeLessOrEqualTo(10); dm.DatasetVersionRepo.Evict(); dm.DataTupleRepo.Evict(); dm.DatasetRepo.Evict(); dm.PurgeDataset(dataset.Id, true); dsHelper.PurgeAllDataStructures(); } finally { dm.Dispose(); rsm.Dispose(); mdm.Dispose(); } }