DataService - query not working

MartinBrandt · March 1, 2021, 8:16am

I have a query that has been working fine for a while, for getting certain Organizations.
My customer wanted me to add an exception, that all customers with a flag called “Deleted” set to true, should not be returned. So I added this flag under the MustNot-term in the query.
It does not listen to this, I still get organisations that are deleted.

Its for organizations, and it looks like this:

    var _dataService = IoC.Resolve<DataService>();
    using (var query = _dataService.CreateQuery<Organization>())
    {
        var q = query.Filter(filter => filter
            .Bool(boolFilter => boolFilter
                .Must(boolFilterMust => boolFilterMust
                    .Field("show_as_reseller", "True", true)
                    .Field("CustPositiveRevenue", "True", true))
                .MustNot(boolFilterMustNot => boolFilterMustNot
                    .Field("NoCoordinates", "True", true)
                    .Field("Deleted", "True", true))));
                   
        var orderedList = q.ToList().OrderBy(x => x.Name).ToList();
        return orderedList;
    }

As you can see, there are two terms in “MustNot”… that “NoCoordinates” is true, and that Deleted is true, and these “must not” be.

When I remove the “NoCoordinates” term… the deleted term starts working. But when I have both, it does not work. I have also tried having two separate MustNot-conditions with one term each. Did not get that to work either.

Am I doing something wrong, or is there a bug here?

/Martin

NilsN · March 1, 2021, 3:27pm

Looks like multiple MustNot are treated with an OR in between, whereas multiple Must uses AND. Could you please report a bug on Docs?

patric.forsgard · April 16, 2021, 9:47am

The usage of the AND or OR operator is based on Do Morgan’s law that describes how the evaluation is done.

using System;
using System.Collections.Generic;
using System.Linq;
using Litium.Data;
using Litium.Data.Queryable;
using Litium.FieldFramework;
using Litium.Products;
using Litium.Products.Queryable;
using Microsoft.Extensions.DependencyInjection;
using Xunit;
using static Litium.Application.Bugs.Bug_54056;

namespace Litium.Application.Bugs
{
    public class Bug_54056 : IClassFixture<Fixture>
    {
        private readonly Fixture _app;
        private readonly DataService _dataService;

        // The field map is a matrix of the the true/false values that we will test
        private static readonly (bool?, bool?, bool?, bool?)[] _fieldMap = new[]
        {
            (default(bool?), default(bool?), default(bool?), default(bool?)), // Product 0

            (true, true, true, true),       // Product 1
            (true, true, true, false),      // Product 2
            (true, true, false, true),      // Product 3
            (true, true, false, false),     // Product 4
            (true, false, true, true),      // Product 5
            (true, false, true, true),      // Product 6
            (true, false, false, true),     // Product 7
            (false, true, true, false),     // Product 8
            (false, false, false, false),   // Product 9
            (false, true, true, true),      // Product 10
            (false, false, false, true),    // Product 11

            (null, true, true, true),       // Product 12
            (null, true, true, false),      // Product 13
            (null, true, false, true),      // Product 14
            (null, true, false, false),     // Product 15
            (null, false, true, true),      // Product 16
            (null, false, false, true),     // Product 17
            (null, false, false, false),    // Product 18

            (null, null, true, true),       // Product 19
            (null, null, true, false),      // Product 20
            (null, null, false, true),      // Product 21
            (null, null, false, false),     // Product 22

            (null, null, null, true),       // Product 23
            (null, null, null, false),      // Product 24

            (true, null, true, true),       // Product 25
            (true, null, true, false),      // Product 26
            (true, null, false, true),      // Product 27
            (true, null, false, false),     // Product 28
            (false, null, true, false),     // Product 29
            (false, null, false, false),    // Product 30
            (false, null, true, true),      // Product 31
            (false, null, false, true),     // Product 32

            (true, null, null, true),       // Product 33
            (true, null, null, false),      // Product 34
            (false, null, null, false),     // Product 35
            (false, null, null, true),      // Product 36

            (true, true, null, true),       // Product 37
            (true, true, null, false),      // Product 38
            (true, false, null, true),      // Product 39
            (false, true, null, false),     // Product 40
            (false, false, null, false),    // Product 41
            (false, true, null, true),      // Product 42
            (false, false, null, true),     // Product 43

            (null, true, null, true),       // Product 44
            (null, true, null, false),      // Product 45
            (null, false, null, true),      // Product 46
            (null, false, null, false),     // Product 47
        };


        public Bug_54056(Fixture app)
        {
            _app = app;
            _dataService = app.ServiceProvider.GetRequiredService<DataService>();
        }

        [Fact]
        public void Must()
        {
            using (var q = _dataService.CreateQuery<BaseProduct>())
            {
                var listResult = q
                    .Filter(filter => filter
                        .Bool(boolFilter => boolFilter
                            .Must(boolFilterMust => boolFilterMust
                                .Field(_app.FieldId1, bool.TrueString, true)
                                .Field(_app.FieldId2, bool.TrueString, true))))
                    .Sort(s => s.Id(SortDirection.Ascending))
                    .ToSystemIdList()
                    .Select(x => _app.ProductId(x))
                    .Where(x => x is not null)
                    .ToList();

                var expected = new[] {
                    _app.ProductId(1),
                    _app.ProductId(2),
                    _app.ProductId(3),
                    _app.ProductId(4),
                    _app.ProductId(37),
                    _app.ProductId(38),
                };
                Assert.Equal(expected, listResult);
            }
        }

        [Fact]
        public void Must_MustNot()
        {
            using (var q = _dataService.CreateQuery<BaseProduct>())
            {
                var listResult = q
                    .Filter(filter => filter
                        .Bool(boolFilter => boolFilter
                            .Must(boolFilterMust => boolFilterMust
                                .Field(_app.FieldId1, bool.TrueString, true)
                                .Field(_app.FieldId2, bool.TrueString, true))
                            .MustNot(boolFilterMustNot => boolFilterMustNot
                                .Field(_app.FieldId3, bool.TrueString, true)
                                .Field(_app.FieldId4, bool.TrueString, true))))
                    .Sort(s => s.Id(SortDirection.Ascending))
                    .ToSystemIdList()
                    .Select(x => _app.ProductId(x))
                    .Where(x => x is not null)
                    .ToList();

                var expected = new[] {
                    _app.ProductId(2),
                    _app.ProductId(3),
                    _app.ProductId(4),
                    _app.ProductId(37),
                    _app.ProductId(38),
                };
                Assert.Equal(expected, listResult);
            }
        }


        [Fact]
        public void Must_MustNot_inner_Should()
        {
            using (var q = _dataService.CreateQuery<BaseProduct>())
            {
                var listResult = q
                    .Filter(filter => filter
                        .Bool(boolFilter => boolFilter
                            .Must(boolFilterMust => boolFilterMust
                                .Field(_app.FieldId1, bool.TrueString, true)
                                .Field(_app.FieldId2, bool.TrueString, true))
                            .MustNot(boolFilterMustNot => boolFilterMustNot
                                .Bool(boolFilterMustNot2 => boolFilterMustNot2
                                    .Should(m => m
                                        .Field(_app.FieldId3, bool.TrueString, true)
                                        .Field(_app.FieldId4, bool.TrueString, true))))))
                    .Sort(s => s.Id(SortDirection.Ascending))
                    .ToSystemIdList()
                    .Select(x => _app.ProductId(x))
                    .Where(x => x is not null)
                    .ToList();

                var expected = new[] {
                    _app.ProductId(4),
                    _app.ProductId(38),
                };
                Assert.Equal(expected, listResult);
            }
        }

        [Fact]
        public void MustNot()
        {
            using (var q = _dataService.CreateQuery<BaseProduct>())
            {
                var listResult = q
                    .Filter(filter => filter
                        .Bool(boolFilter => boolFilter
                            .MustNot(boolFilterMustNot => boolFilterMustNot
                                .Field(_app.FieldId3, bool.TrueString, true)
                                .Field(_app.FieldId4, bool.TrueString, true))))
                    .Sort(s => s.Id(SortDirection.Ascending))
                    .ToSystemIdList()
                    .Select(x => _app.ProductId(x))
                    .Where(x => x is not null)
                    .ToList();

                var expected = new[] {
                    _app.ProductId(0),
                    _app.ProductId(2),
                    _app.ProductId(3),
                    _app.ProductId(4),
                    _app.ProductId(7),
                    _app.ProductId(8),
                    _app.ProductId(9),
                    _app.ProductId(11),
                    _app.ProductId(13),
                    _app.ProductId(14),
                    _app.ProductId(15),
                    _app.ProductId(17),
                    _app.ProductId(18),
                    _app.ProductId(20),
                    _app.ProductId(21),
                    _app.ProductId(22),
                    _app.ProductId(23),
                    _app.ProductId(24),
                    _app.ProductId(26),
                    _app.ProductId(27),
                    _app.ProductId(28),
                    _app.ProductId(29),
                    _app.ProductId(30),
                    _app.ProductId(32),
                    _app.ProductId(33),
                    _app.ProductId(34),
                    _app.ProductId(35),
                    _app.ProductId(36),
                    _app.ProductId(37),
                    _app.ProductId(38),
                    _app.ProductId(39),
                    _app.ProductId(40),
                    _app.ProductId(41),
                    _app.ProductId(42),
                    _app.ProductId(43),
                    _app.ProductId(44),
                    _app.ProductId(45),
                    _app.ProductId(46),
                    _app.ProductId(47),
                };
                Assert.Equal(expected, listResult);
            }
        }

        [Fact]
        public void MustNot_inner_Should()
        {
            using (var q = _dataService.CreateQuery<BaseProduct>())
            {
                var listResult = q
                    .Filter(filter => filter
                        .Bool(boolFilter => boolFilter
                            .MustNot(boolFilterMustNot => boolFilterMustNot
                                .Bool(boolFilterMustNot2 => boolFilterMustNot2
                                    .Should(m => m
                                        .Field(_app.FieldId3, bool.TrueString, true)
                                        .Field(_app.FieldId4, bool.TrueString, true))))))
                    .Sort(s => s.Id(SortDirection.Ascending))
                    .ToSystemIdList()
                    .Select(x => _app.ProductId(x))
                    .Where(x => x is not null)
                    .ToList();

                var expected = new[] {
                    _app.ProductId(0),
                    _app.ProductId(4),
                    _app.ProductId(9),
                    _app.ProductId(15),
                    _app.ProductId(18),
                    _app.ProductId(22),
                    _app.ProductId(24),
                    _app.ProductId(28),
                    _app.ProductId(30),
                    _app.ProductId(34),
                    _app.ProductId(35),
                    _app.ProductId(38),
                    _app.ProductId(40),
                    _app.ProductId(41),
                    _app.ProductId(45),
                    _app.ProductId(47),
                };
                Assert.Equal(expected, listResult);
            }
        }

        [Fact]
        public void MustNot_inner_Must()
        {
            using (var q = _dataService.CreateQuery<BaseProduct>())
            {
                var listResult = q
                    .Filter(filter => filter
                        .Bool(boolFilter => boolFilter
                            .MustNot(boolFilterMustNot => boolFilterMustNot
                                .Bool(boolFilterMustNot2 => boolFilterMustNot2
                                    .Must(m => m
                                        .Field(_app.FieldId3, bool.TrueString, true)
                                        .Field(_app.FieldId4, bool.TrueString, true))))))
                    .Sort(s => s.Id(SortDirection.Ascending))
                    .ToSystemIdList()
                    .Select(x => _app.ProductId(x))
                    .Where(x => x is not null)
                    .ToList();

                var expected = new[] {
                    _app.ProductId(0),
                    _app.ProductId(2),
                    _app.ProductId(3),
                    _app.ProductId(4),
                    _app.ProductId(7),
                    _app.ProductId(8),
                    _app.ProductId(9),
                    _app.ProductId(11),
                    _app.ProductId(13),
                    _app.ProductId(14),
                    _app.ProductId(15),
                    _app.ProductId(17),
                    _app.ProductId(18),
                    _app.ProductId(20),
                    _app.ProductId(21),
                    _app.ProductId(22),
                    _app.ProductId(23),
                    _app.ProductId(24),
                    _app.ProductId(26),
                    _app.ProductId(27),
                    _app.ProductId(28),
                    _app.ProductId(29),
                    _app.ProductId(30),
                    _app.ProductId(32),
                    _app.ProductId(33),
                    _app.ProductId(34),
                    _app.ProductId(35),
                    _app.ProductId(36),
                    _app.ProductId(37),
                    _app.ProductId(38),
                    _app.ProductId(39),
                    _app.ProductId(40),
                    _app.ProductId(41),
                    _app.ProductId(42),
                    _app.ProductId(43),
                    _app.ProductId(44),
                    _app.ProductId(45),
                    _app.ProductId(46),
                    _app.ProductId(47),
                };
                Assert.Equal(expected, listResult);
            }
        }

        public class Fixture : ApplicationFixture
        {
            private readonly string _fieldPrefix;
            private readonly List<BaseProduct> _products;

            public Fixture()
            {
                var baseProductService = ServiceProvider.GetRequiredService<BaseProductService>();
                var dataService = ServiceProvider.GetRequiredService<DataService>();
                using var batch = dataService.CreateBatch();

                var displayTemplageService = ServiceProvider.GetRequiredService<DisplayTemplateService>();
                var productDisplayTemplate = new ProductDisplayTemplate();
                displayTemplageService.Create(productDisplayTemplate);
                AddDisposable(() => displayTemplageService.Delete(productDisplayTemplate));

                var fieldTemplateService = ServiceProvider.GetRequiredService<FieldTemplateService>();
                var productFieldTemplate = new ProductFieldTemplate(this.UniqueString(), productDisplayTemplate.SystemId);
                fieldTemplateService.Create(productFieldTemplate);
                AddDisposable(() => fieldTemplateService.Delete(productFieldTemplate));
                productFieldTemplate = fieldTemplateService.Get<ProductFieldTemplate>(productFieldTemplate.Id);

                var fieldDefinitionService = ServiceProvider.GetRequiredService<FieldDefinitionService>();
                _fieldPrefix = this.UniqueString();
                foreach (var item in new[] { FieldId1, FieldId2, FieldId3, FieldId4 })
                {
                    var field = new FieldDefinition<ProductArea>(item, SystemFieldTypeConstants.Boolean);
                    fieldDefinitionService.Create(field);
                    AddDisposable(() => fieldDefinitionService.Delete(field));
                }

                _products = new List<BaseProduct>();
                for (var i = 0; i < _fieldMap.Length; i++)
                {
                    var item = _fieldMap[i];
                    var baseProduct = new BaseProduct(_fieldPrefix + "P" + (i < 10 ? "0" + i : i), productFieldTemplate.SystemId);
                    baseProduct.Fields.AddOrUpdateValue(FieldId1, item.Item1);
                    baseProduct.Fields.AddOrUpdateValue(FieldId2, item.Item2);
                    baseProduct.Fields.AddOrUpdateValue(FieldId3, item.Item3);
                    baseProduct.Fields.AddOrUpdateValue(FieldId4, item.Item4);

                    batch.Create(baseProduct);
                    AddDisposable(() => baseProductService.Delete(baseProduct));
                    _products.Add(baseProduct);
                }

                batch.Commit();
            }

            public string FieldId1 => _fieldPrefix + "_F1";
            public string FieldId2 => _fieldPrefix + "_F2";
            public string FieldId3 => _fieldPrefix + "_F3";
            public string FieldId4 => _fieldPrefix + "_F4";

            public string ProductId(int id) => _products[id].Id.Substring(_fieldPrefix.Length);
            public string ProductId(Guid systemId) => _products.FirstOrDefault(x => x.SystemId == systemId)?.Id.Substring(_fieldPrefix.Length);
        }
    }
}

patric.forsgard · April 23, 2021, 6:00am

This topic was automatically closed after 6 days. New replies are no longer allowed.

Topic		Replies	Views
Filter on datetime with empty/null value in dataservice Questions data-service , litium-7	4	602	July 27, 2021
Customer Search using Data Service Questions	4	604	September 16, 2019
Search Organization with wildcard Questions litium-7	4	430	August 6, 2019
Dataservice BoolFilterDesciptor.Should data-service	1	682	October 15, 2018
Search query FilterTags Questions litium-5	7	1273	December 20, 2018

DataService - query not working

Related Topics