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

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)] 

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)] 

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

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