Different SQL Generation About Many-To-Many Relationship With Filter in version 5.2.0

[sahin52]

I encountered a problem when I try to update from 5.1.7 to 5.2.0

using System;
using System.Collections;
using System.Collections.Generic;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Conventions.AcceptanceCriteria;
using FluentNHibernate.Conventions.Inspections;
using FluentNHibernate.Conventions.Instances;
using FluentNHibernate.Conventions;
using FluentNHibernate.Mapping;
using NHibernate;
using NHibernate.Util;
using FilterDefinition = FluentNHibernate.Mapping.FilterDefinition;
using FluentNHibernate.Conventions.Helpers;
using System.Linq;


namespace Testing.ManyToMany
{
    internal class Program
    {
        public static void Main(string[] args)
        {
            Console.WriteLine("Testing NHibernate Many To Many  relationship");

            var version = typeof(ISession).Assembly.GetName().Version;
            Console.WriteLine($"V: {version}");

            var sessionFactory = CreateSessionFactory();

            using (var session = sessionFactory.OpenSession())
            {
                session.EnableFilter(DeletedAtIsNullFilterConvention.NotDeletedFilter.FilterName);
                using (var transaction = session.BeginTransaction())
                {
                    Console.WriteLine("session.QueryOver<Webcam>().List();");
                    var webcamExist = session.QueryOver<Webcam>().List();
                    Console.WriteLine("var count1 = webcamExist[0].Supplies.Count;");
                    var count1 = webcamExist[0].Supplies.Count;

                    Console.WriteLine("var webcamByGet = session.Get<Webcam>(123241);");
                    var webcamByGet = session.Get<Webcam>(123241);
                    Console.WriteLine("var list = webcamByGet.Supplies.ToList();");
                    var list = webcamByGet.Supplies.ToList();
                    Console.WriteLine("var count2 = webcamByGet.Supplies.Count;");
                    var count2 = webcamByGet.Supplies.Count;

                    if (count2 == 0)
                    {
                        // NHibernate old version - 5.1.7 or below
                        Console.WriteLine("count2 = 0");
                    }
                    else {
                        // NHibernate new version - 5.2.0 or above
                        Console.WriteLine("count2 != 0");
                    }
                    var currency = new Currency()
                        {
                            Code = "",
                            LastDataVersion = 100500,
                            MinorUnitDecimalDigits = 7,
                            Name = "My currency",
                            NumericCode = 42,
                        };
                    session.SaveOrUpdate(currency);


                    var hardwareSupplier = new HardwareSupplier()
                    {
                        LastDataVersion = 100500,
                        Name = "supplier1",
                        PreferredCurrency = currency
                    };
                    session.SaveOrUpdate(hardwareSupplier);

                    var webcam = new Webcam()
                    {
                        LastDataVersion = 42,
                        Manufacturer = "Manufacturer",
                        Model = "Model 100500",
                        HasMic = true,
                        Price = 1.23M
                    };

                    var supply = new Supply()
                    {
                        LastDataVersion = 100500,
                        Number = 1,
                        HardwareSupplier = hardwareSupplier
                    };

                    supply.DeletedAt = DateTime.UtcNow;

                    webcam.DeletedAt = DateTime.UtcNow;

                    supply.Webcams.Add(webcam);
                    webcam.Supplies.Add(supply);



                    session.SaveOrUpdate(webcam);
                    session.SaveOrUpdate(supply);

                    transaction.Commit();
                }
            }
        }

        public static ISessionFactory CreateSessionFactory()
        {
            return Fluently.Configure()
                .Database(MsSqlConfiguration.MsSql2005
                                            .ShowSql()
                                            .ConnectionString("Data Source=.;Initial Catalog=test_sm;User Id=sa;Password=Password123"))
                .Mappings(m =>
                    m.FluentMappings
                     .Add<WebcamMap>()
                     .Add<SupplyMap>()
                     .Add<HardwareSupplierMap>()
                     .Add<CurrencyMap>()
                     .Add(typeof(DeletedAtIsNullFilterConvention.NotDeletedFilter))
                     //.Conventions.Add(Conventions.ToArray())
                     )
                .BuildSessionFactory();
        }
        public static IEnumerable<IConvention> Conventions
        {
            get { return [DefaultLazy.Never(), new DeletedAtIsNullFilterConvention()]; }
        }
    }

    public class Webcam
    {
        public virtual int Id { get; protected set; }

        public virtual int LastDataVersion { get; set; }

        public virtual string Manufacturer { get; set; }

        public virtual string Model { get; set; }

        public virtual decimal Price { get; set; }

        public virtual bool HasMic { get; set; }

        public virtual IDictionary Attributes { get; set; }

        public virtual ISet<Supply> Supplies { get; set; } = new HashSet<Supply>();
        
        public virtual DateTime DeletedAt { get; set; }
    }
    public class WebcamMap : ClassMap<Webcam>
    {
        public WebcamMap()
        {
            Schema("mod_list_management");
            Table("DynWebcams");
            Id(x => x.Id);
            Map(x => x.LastDataVersion);
            Map(x => x.Manufacturer);
            Map(x => x.Model);
            Map(x => x.Price);
            Map(x => x.HasMic);
            Map(x => x.DeletedAt);
            ApplyFilter<DeletedAtIsNullFilterConvention.NotDeletedFilter>();
            HasManyToMany(x => x.Supplies)
               .Schema("mod_list_management")
               .Table("DynSuppliesWebcams")
               .ParentKeyColumn("WebcamId")
               .ChildKeyColumn("SupplyId")
               .ApplyChildFilter<DeletedAtIsNullFilterConvention.NotDeletedFilter>()
               .Cascade.All()
               ;
        }
    }

    public class Supply
    {
        public virtual int Id { get; protected set; }

        public virtual int LastDataVersion { get; set; }

        public virtual int Number { get; set; }
        public virtual HardwareSupplier HardwareSupplier { get; set; }

        public virtual IDictionary Attributes { get; set; }

        public virtual ISet<Webcam> Webcams { get; set; } = new HashSet<Webcam>();
        
        public virtual DateTime DeletedAt { get; set; }
    }
    public class SupplyMap : ClassMap<Supply>
    {
        public SupplyMap()
        {
            Schema("mod_list_management");
            Table("DynSupplies");
            Id(x => x.Id);
            Map(x => x.LastDataVersion);
            Map(x => x.Number);
            Map(x => x.DeletedAt);

            References(x => x.HardwareSupplier, "HardwareSupplierId").Cascade.All();
            ApplyFilter<DeletedAtIsNullFilterConvention.NotDeletedFilter>();
            HasManyToMany(x => x.Webcams)
                .Schema("mod_list_management")
                .Table("DynSuppliesWebcams")
                .ParentKeyColumn("SupplyId")
                .ChildKeyColumn("WebcamId")
                .ApplyChildFilter<DeletedAtIsNullFilterConvention.NotDeletedFilter>()
                .Cascade.All()
                .Inverse()
                ;
        }
    }

    public class HardwareSupplier
    {
        public virtual int Id { get; protected set; }

        public virtual int LastDataVersion { get; set; }

        public virtual string Name { get; set; } = "";
        public virtual IDictionary Attributes { get; set; }

        public virtual Currency PreferredCurrency { get; set; }

        private readonly ISet<Supply> supplies = new HashSet<Supply>();

        public virtual IEnumerable<Supply> Supplies
        {
            get { return supplies; }
        }
    }

    public class HardwareSupplierMap : ClassMap<HardwareSupplier>
    {
        public HardwareSupplierMap()
        {
            Schema("mod_list_management");
            Table("DynHardwareSuppliers");
            Id(x => x.Id);
            Map(x => x.LastDataVersion);
            Map(x => x.Name);
            References(x => x.PreferredCurrency, "PreferredCurrencyId").Cascade.None();
            ApplyFilter<DeletedAtIsNullFilterConvention.NotDeletedFilter>();
            HasMany(x => x.Supplies)
                .KeyColumn("HardwareSupplierId")
                .ApplyFilter<DeletedAtIsNullFilterConvention.NotDeletedFilter>()
                .Cascade.AllDeleteOrphan()
                .Inverse()
                .LazyLoad();

            DynamicComponent(x => x.Attributes, c =>
            {
                c.Map<DateTime>("LastSupplyReceivedAt").Nullable();
            });
        }
    }


    public class Currency
    {
        public virtual int Id { get; protected set; }

        public virtual int LastDataVersion { get; set; }

        public virtual string Name { get; set; }

        public virtual string Code { get; set; }

        public virtual int NumericCode { get; set; }

        public virtual byte MinorUnitDecimalDigits { get; set; }

    }
    public class CurrencyMap : ClassMap<Currency>
    {
        public CurrencyMap()
        {

            Schema("dbo");
            Table("arCurrencies");
            Id(x => x.Id);
            Cache.NonStrictReadWrite();
            Map(x => x.LastDataVersion);
            Map(x => x.Name, "Currency");
            Map(x => x.Code, "Code");
            Map(x => x.NumericCode, "NumericCode");
            Map(x => x.MinorUnitDecimalDigits, "MinorUnit");
            ApplyFilter<DeletedAtIsNullFilterConvention.NotDeletedFilter>();
        }
    }
   
    public class DeletedAtIsNullFilterConvention :
                                            IClassConvention,
                                            IClassConventionAcceptance,
                                            IHasManyConvention,
                                            IHasManyConventionAcceptance,
                                            IHasManyToManyConvention,
                                            IHasManyToManyConventionAcceptance
    {
        public void Apply(IClassInstance instance)
        {
            instance.ApplyFilter<NotDeletedFilter>();
        }

        public void Accept(IAcceptanceCriteria<IClassInspector> criteria)
        {
            criteria.Expect(x => x.EntityType.HasProperty("DeletedAt"));
        }

        public void Apply(IOneToManyCollectionInstance instance)
        {
            instance.ApplyFilter<NotDeletedFilter>();
        }

        public void Accept(IAcceptanceCriteria<IOneToManyCollectionInspector> criteria)
        {
            criteria.Expect(x => x.ChildType.HasProperty("DeletedAt"));
        }

        public void Apply(IManyToManyCollectionInstance instance)
        {
            instance.ApplyFilter<NotDeletedFilter>();
        }

        public void Accept(IAcceptanceCriteria<IManyToManyCollectionInspector> criteria)
        {
            criteria.Expect(x => x.ChildType.HasProperty("DeletedAt"));
        }
        public class NotDeletedFilter : FilterDefinition
        {
            public const string FilterName = "notDeleted";

            public NotDeletedFilter()
            {
                WithName(FilterName)
                    .WithCondition("DeletedAt IS NULL");
            }
        }
    }
}

In version 5.1.7 this code generates that output:

Testing NHibernate Many To Many  relationship
V: 5.1.0.0
session.QueryOver<Webcam>().List();
NHibernate: SELECT this_.Id as id1_0_0_, this_.LastDataVersion as lastdataversion2_0_0_, this_.Manufacturer as manufacturer3_0_0_, this_.Model as model4_0_0_, this_.Price as price5_0_0_, this_.HasMic as hasmic6_0_0_, this_.DeletedAt as deletedat7_0_0_ FROM mod_list_management.DynWebcams this_ WHERE this_.DeletedAt IS NULL
var count1 = webcamExist[0].Supplies.Count;
NHibernate: SELECT supplies0_.WebcamId as webcamid1_1_1_, supplies0_.SupplyId as supplyid2_1_1_, supply1_.Id as id1_2_0_, supply1_.LastDataVersion as lastdataversion2_2_0_, supply1_.Number as number3_2_0_, supply1_.DeletedAt as deletedat4_2_0_, supply1_.HardwareSupplierId as hardwaresupplierid5_2_0_ FROM mod_list_management.DynSuppliesWebcams supplies0_ left outer join mod_list_management.DynSupplies supply1_ on supplies0_.SupplyId=supply1_.Id WHERE supply1_.DeletedAt IS NULL AND  supply1_.DeletedAt IS NULL and supplies0_.WebcamId=@p0;@p0 = 123241 [Type: Int32 (0:0:0)]
var webcamByGet = session.Get<Webcam>(123241);
var list = webcamByGet.Supplies.ToList();
var count2 = webcamByGet.Supplies.Count;
count2 = 0
NHibernate: INSERT INTO dbo.arCurrencies (LastDataVersion, Currency, Code, NumericCode, MinorUnit) VALUES (@p0, @p1, @p2, @p3, @p4); select SCOPE_IDENTITY();@p0 = 100500 [Type: Int32 (0:0:0)], @p1 = 'My currency' [Type: String (4000:0:0)], @p2 = '' [Type: String (4000:0:0)], @p3 = 42 [Type: Int32 (0:0:0)], @p4 = 7 [Type: Byte (1:0:0)]

And in 5.2.0 the same code generates that output:

Testing NHibernate Many To Many  relationship
V: 5.2.0.0
session.QueryOver<Webcam>().List();
NHibernate: SELECT this_.Id as id1_0_0_, this_.LastDataVersion as lastdataversion2_0_0_, this_.Manufacturer as manufacturer3_0_0_, this_.Model as model4_0_0_, this_.Price as price5_0_0_, this_.HasMic as hasmic6_0_0_, this_.DeletedAt as deletedat7_0_0_ FROM mod_list_management.DynWebcams this_ WHERE this_.DeletedAt IS NULL
var count1 = webcamExist[0].Supplies.Count;
NHibernate: SELECT supplies0_.WebcamId as webcamid1_1_1_, supplies0_.SupplyId as supplyid2_1_1_, supply1_.Id as id1_2_0_, supply1_.LastDataVersion as lastdataversion2_2_0_, supply1_.Number as number3_2_0_, supply1_.DeletedAt as deletedat4_2_0_, supply1_.HardwareSupplierId as hardwaresupplierid5_2_0_ FROM mod_list_management.DynSuppliesWebcams supplies0_ left outer join mod_list_management.DynSupplies supply1_ on supplies0_.SupplyId=supply1_.Id and supply1_.DeletedAt IS NULL WHERE  supply1_.DeletedAt IS NULL and supplies0_.WebcamId=@p0;@p0 = 123241 [Type: Int32 (0:0:0)]
NHibernate: SELECT supply0_.Id as id1_2_0_, supply0_.LastDataVersion as lastdataversion2_2_0_, supply0_.Number as number3_2_0_, supply0_.DeletedAt as deletedat4_2_0_, supply0_.HardwareSupplierId as hardwaresupplierid5_2_0_ FROM mod_list_management.DynSupplies supply0_ WHERE supply0_.Id=@p0;@p0 = 46801 [Type: Int32 (0:0:0)]
var webcamByGet = session.Get<Webcam>(123241);
var list = webcamByGet.Supplies.ToList();
var count2 = webcamByGet.Supplies.Count;
count2 != 0
NHibernate: INSERT INTO dbo.arCurrencies (LastDataVersion, Currency, Code, NumericCode, MinorUnit) VALUES (@p0, @p1, @p2, @p3, @p4); select SCOPE_IDENTITY();@p0 = 100500 [Type: Int32 (0:0:0)], @p1 = 'My currency' [Type: String (4000:0:0)], @p2 = '' [Type: String (4000:0:0)], @p3 = 42 [Type: Int32 (0:0:0)], @p4 = 7 [Type: Byte (1:0:0)]

In Version 5.2.0 especially this line is very important:

and supply1_.DeletedAt IS NULL WHERE  supply1_.DeletedAt IS NULL

it is like this in 5.1.7:

WHERE supply1_.DeletedAt IS NULL AND  supply1_.DeletedAt IS NULL

So, I'm not sure about which one is true but I am unable to apply child filter of "DeletedAt is NULL". Am I doing something wrong?