C# – Cannot insert the value NULL into column , table ; column does not allow nulls. INSERT fails

cfluent-nhibernatenhibernatesql

I found many similar questions on SO, I tried said resolutions but none of those worked for me, can anyone tell me what was wrong in following code, what corrections needs to be made to make it workable:

Models:

public class ParentDataClassData
    {
        public ParentDataClassData()
        {
            TitleSetTitles = new List<ChildDataClass1>();
            TitlesSetEvents = new List<ChildDataClass2>();
        }

        public virtual int TitleSetID { get; set; }
        public virtual string Name { get; set; }
        public virtual IList<ChildDataClass1> TitleSetTitles { get; set; }

        public virtual IEnumerable<ChildDataClass2> TitlesSetEvents { get; set; }

        public virtual object IdentityField
        {
            get { return TitleSetID; }
        }
    }


public class ChildDataClass1
    {
        public ChildDataClass1()
        {
            TitleSet = new ParentDataClassData();
            TitleSetTitleEventMedias = new List<TitleSetTitleEventMediaData>();
        }

        public virtual int TitleSetID
        {
            get { return TitleSet.TitleSetID; }
        }

        public virtual ParentDataClassData TitleSet { get; set; }

        public virtual IList<TitleSetTitleEventMediaData> TitleSetTitleEventMedias { get; set; }

        public virtual object IdentityField
        {
            get { return TitleSetTitleID; }
        }

    }


 public class ChildDataClass2
    {
        public virtual ParentDataClassData TitleSet { get; set;  }

        public virtual int TitleSetEventID
        {
            get;
            set;
        }


        public virtual object IdentityField
        {
            get { return TitleSetEventID; }
        }
    }

Mappings:

public class ParentDataClassMap : ClassMap<ParentDataEntity>
    {
        public ParentDataClassMap()
        {
            Table("TitleSet");

            LazyLoad();

            Id(x => x.TitleSetID)
                .Column("TitleSetID")
                .GeneratedBy.Native()
                .UnsavedValue(0);


            HasMany(x => x.TitleSetTitles)
            .LazyLoad()
            .Inverse()
            .Fetch.Subselect()
            .Cascade.SaveUpdate()
            .KeyColumn("TitleSetID");

            HasMany(x => x.TitlesSetEvents)
                .LazyLoad()
                .Inverse()
                .Cascade.SaveUpdate()
                .Fetch.Subselect()
                .KeyColumn("TitleSetID");
        }
    }


 public class ChildDataClass1Map : ClassMap<ChildDataClass1>
    {
        public ChildDataClass1Map()
        {
            Table("TitleSetTitle");

            Id(x => x.TitleSetTitleID)
                .Column("TitleSetTitleID")
                .GeneratedBy.Native()
                .UnsavedValue(0);

            References(x => x.TitleSet)
                .Fetch.Join()
                .Not.Nullable()
                .Column("TitleSetID")
                .Cascade.None();


            HasMany(x => x.TitleSetTitleEventMedias)
                .LazyLoad()
                .Inverse()
                .Cascade.AllDeleteOrphan()
                .Fetch.Subselect()
                .KeyColumn("TitleSetTitleID");
        }
}


 public class ChildDataClass2Map : ClassMap<ChildDataClass2>
    {
        public ChildDataClass2Map()
        {
            Table("TitleSetEvent");

            Id(x => x.TitleSetEventID)
                .Column("TitleSetEventID")
                .GeneratedBy.Native()
                .UnsavedValue(0);


            References(x => x.TitleSet)
                .Column("TitleSetID")
                .Not.Update()
                .Not.Insert()
                .Cascade.None();

            Map(x => x.LengthSec).Not.Nullable();
            Map(x => x.EventID).Not.Nullable();

            HasMany(x => x.TitleSetDefaultEventMedia)
                .LazyLoad()
                .Inverse()
                .Cascade.None()
                .Fetch.Join()
                .KeyColumn("TitleSetEventID");

            HasMany(x => x.TitleSetTitleEventMedias)
                .LazyLoad()
                .Inverse()
                .Cascade.None()
                .Fetch.Join()
                .KeyColumn("TitleSetEventID");
        }
    }

Test:

[Test]
        public void CanAddNewTitleSet()
        {
            var titleSet = new TitleSetDataEntity
                {
                    Name = "Somename",
                    ProgramServiceID = 1,
                    CreatedBy = "someuser",
                    CreatedDate = DateTime.Now,
                    ModifiedBy = "someuser",
                    ModifiedDate = DateTime.Now,
                    TitleSetTitles = new List<TitleSetTitleData>
                    {
                        new TitleSetTitleData
                        {
                            IsIncluded = true,
                            IsPremiere = true,
                            TitleTypeCode = "somecode",
                        }
                    },
                    TitlesSetEvents = new List<TitleSetEventData>()
                };


                Session.SaveOrUpdate(titleSet);

            }

and getting exception:

NHibernate.AdoNet.AbstractBatcher: ERROR
NHibernate.AdoNet.AbstractBatcher [(null)] – Could not execute query:
INSERT INTO TitleSetTitle ([TitleID], …, TitleSetID) VALUES (@p0,
@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8); select SCOPE_IDENTITY()
System.Data.SqlClient.SqlException (0x80131904): Cannot insert the
value NULL into column 'TitleSetID', table 'TitleSetTitle'; column
does not allow nulls. INSERT fails. The statement has been terminated.

P.S.

  1. I used inverse as of bidirectional mappings
  2. Anyhow inverse does not obey the things, per my understanding it should save ParantClassData first to make sure Child classes get proper ids.
  3. Not sure, what I missed

Any help for above issue will be warmly appreciated!

Update
Added more info to explain the issue, instead of lengthy code, I am just wondering if anyhow TitleSetID readonly property pertaining issue.

Best Answer

The issue in fact is hidden in this mapping:

HasMany(x => x.TitleSetTitles)
    ...
    .Inverse();

This mapping is instructing NHibernate:

The chlild will care about this relation.

But that means, that child must know about its parent. which is not true in this code:

var titleSet = new TitleSetDataEntity
{
    Name = "Somename",
    ...
    TitleSetTitles = new List<TitleSetTitleData>
    {
        new TitleSetTitleData...
    },
};
session.Save(titleSet);

In this code, the child instance created by new TitleSetTitleData is missing the parent reference! And that's the problem

NOTE - I guess that the property TitleSetTitles is of type IList<ChildDataClass1> TitleSetTitles but in the code we use new List<TitleSetTitleData> ... I guess it is a typo

The correct syntax would be:

var titleSet = new TitleSetDataEntity
{
    Name = "Somename",
    ...
    TitleSetTitles = new List<TitleSetTitleData>();
};
var child = new TitleSetTitleData
{
    ...
    ParentDataClassData = titleSet,
};
titleSet.TitleSetTitles.Add(child);
session.Save(titleSet);

And now, NHibernate will have enough information. Parent has a Child added in the list, Child knows about the Parent and .Inverse() mapping will work, because the ID of Parent won't be null anymore

Related Topic