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

Question

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.

I used inverse as of bidirectional mappings
Anyhow inverse does not obey the things, per my understanding it should save ParantClassData first to make sure Child classes get proper ids.
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.

Reduce the code to the smallest example that displays the error. You're trying to insert NULL into TitleSetID or you've not included TitleSetID in the insert list. — OGHaza
– OGHaza, Commented Jun 17, 2014 at 16:05
I am not supply any null value to TitleSetID, see my test, just trying to insert new collection. — Gaurav Arora
– Gaurav Arora, Commented Jun 17, 2014 at 16:42
See answer from @DanielKelley. Main issue is in its description and not in its Title — Gaurav Arora
– Gaurav Arora, Commented Jun 18, 2014 at 5:27

Radim Köhler · Accepted Answer · 2014-06-17 16:48:52Z

2

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

edited Jun 17, 2014 at 16:48

answered Jun 17, 2014 at 16:23

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gaurav Arora Over a year ago

this is there is model: object IdentityField. I cant change the schema of table as this is a legacy app. Also, I verified this column if an Identity column in Table TitleSet.

Radim Köhler Over a year ago

I updated my answer, it should clearly show you, that we need to set both sides of relation parent-child. If this will be set, all SQL statements will be processed correctly...

Gaurav Arora Over a year ago

Thanks for your help, I tried but still same error, I also referred this:nanovazquez.com/2013/07/04/… but no worth, anyhow TitleSetID is everytime null and its only when I try to save TitleSet with its children like TitleSetTitles, if there are no children collections then it works fine, it is happening only when I tried to save it with both Parent and Children.

Gaurav Arora Over a year ago

My tests passed now. I am looking into my actual things hope all will go fine, I marked +1 your answer as your answer is really beneficial, I appreciated your time spent on this conversation. Regarding down-vote to my question, I am not sure, why guys are not looking to actual issue, I believe they just read question not description, hope I could ask this to moderator of SO, anyways, you helped me a lot.

Radim Köhler Over a year ago

GREAT Job, sir! Please, do not give up, because of few questionable down votes... Enjoy NHibernate...brilliant tool ;)

CailinP · Accepted Answer · 2014-06-17 16:17:37Z

-1

Errors like these usually occur whenever you attempt to insert a NULL value into a column, or you forget to include a column in the insert statement, as some column settings require data and cannot be left blank. Make sure you're including TitleSetID in the insert statement and you're putting a non-NULL value into it.

answered Jun 17, 2014 at 16:17

CailinP

2344 silver badges15 bronze badges

1 Comment

Gaurav Arora Over a year ago

I am simply trying to insert collection, see my test TitleSetID should be populated automatically a key of Parent, while we saving child collection

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related