Cannot insert the value NULL into column - NHibernate entity with 2 parents

Question

{"Cannot insert the value NULL into column 'RootID', table 'Legacy.dbo.Middle'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated."}

I have a Root class, Middle class, and 'bottom' class. The root class as "Middle" as children, then Middle has "Bottom" as children. However, in this legacy database with poor design there is also a reference to "Root" from the "Bottom".

root table

  CREATE TABLE [dbo].[Root](
    [RootID] [int] IDENTITY(1,1) NOT NULL,
    [RootName] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Root] PRIMARY KEY CLUSTERED 
(
    [RootID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

middle table

   CREATE TABLE [dbo].[Middle](
        [MiddleID] [int] IDENTITY(1,1) NOT NULL,
        [MiddleName] [varchar](max) NOT NULL,
        [RootID] [int] NOT NULL,
     CONSTRAINT [PK_Middle] PRIMARY KEY CLUSTERED 
    (
        [MiddleID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

bottom table

 CREATE TABLE [dbo].[Bottom](
        [BottomID] [int] IDENTITY(1,1) NOT NULL,
        [BottomName] [varchar](max) NOT NULL,
        [MiddleID] [int] NOT NULL,
        [RootID] [int] NOT NULL,
     CONSTRAINT [PK_Bottom] PRIMARY KEY CLUSTERED 
    (
        [BottomID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

the foreign keys

  ALTER TABLE [dbo].[Middle]  WITH CHECK ADD  CONSTRAINT [FK_Middle_Root] FOREIGN KEY([RootID])
    REFERENCES [dbo].[Root] ([RootID])
    GO

    ALTER TABLE [dbo].[Middle] CHECK CONSTRAINT [FK_Middle_Root]

    ALTER TABLE [dbo].[Bottom]  WITH CHECK ADD  CONSTRAINT [FK_Bottom_Middle] FOREIGN KEY([MiddleID])
    REFERENCES [dbo].[Middle] ([MiddleID])
    GO

    ALTER TABLE [dbo].[Bottom] CHECK CONSTRAINT [FK_Bottom_Middle]
    GO

ALTER TABLE [dbo].[Bottom]  WITH CHECK ADD  CONSTRAINT [FK_Bottom_Root] FOREIGN KEY([RootID])
REFERENCES [dbo].[Root] ([RootID])
GO

ALTER TABLE [dbo].[Bottom] CHECK CONSTRAINT [FK_Bottom_Root]

c# entities

  public class Root
    {
        public Root()
        {
            Middles = new SortedSet<Middle>();
        }

        public int RootID { get; set; }
        public string RootName { get; set; }
        public ISet<Middle> Middles { get; set; }
    }

    public class Middle
    {
        public Middle()
        {
            Bottoms = new SortedSet<Bottom>();
        }

        public int MiddleID { get; set; }
        public Root Root
        {
            get;
            set;
        }

        public string MiddleName { get; set; }
        public ISet<Bottom> Bottoms { get; set; }
    }

    public class Bottom
    {
        public int BottomID { get; set; }
        public Root Root { get; set; }
        public Middle Middle { get; set; }
        public string BottomName { get; set; }
    }

I am using the following mappings via FluentNHibernate, but I've tried a ton of variations and just cannot get it to work in any way.

public class RootMap : IAutoMappingOverride<Root>
{
    public void Override(AutoMapping<Root> mapping)
    {
        mapping.Not.LazyLoad();
        mapping.Id(x => x.RootID);
        mapping.HasMany(x => x.Middles).KeyColumn("RootID").Cascade.AllDeleteOrphan().Inverse();
    }
}

public class MiddleMap : IAutoMappingOverride<Middle>
{
    public void Override(AutoMapping<Middle> mapping)
    {
        mapping.Not.LazyLoad();
        mapping.Id(x => x.MiddleID);
        mapping.References(x => x.Root);
        mapping.HasMany(x => x.Bottoms).KeyColumn("MiddleID").Cascade.AllDeleteOrphan();
    }
}

public class BottomMap : IAutoMappingOverride<Bottom>
{
    public void Override(AutoMapping<Bottom> mapping)
    {
        mapping.Not.LazyLoad();
        mapping.Id(x => x.BottomID);
        mapping.References(x => x.Root);
        mapping.References(x => x.Middle);
    }
}

would love some time to let people answer this... I think I have a solution using constructors and FACTORY METHOD on the AGGREGATE "Root" — JDPeckham
– JDPeckham, Commented Oct 21, 2012 at 23:06

Owen Pauling · Accepted Answer · 2016-01-07 09:10:00Z

5

You need to declare the many valued side of the relation as "inverse". See last paragraph of section 6.4: http://nhibernate.info/doc/nh/en/index.html#collections-onetomany

edited Jan 7, 2016 at 9:10

Owen Pauling

12.1k20 gold badges58 silver badges67 bronze badges

answered Oct 22, 2012 at 12:12

Oskar Berggren

5,6561 gold badge21 silver badges37 bronze badges

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

3 Comments

JDPeckham Over a year ago

I tried that, then I get 'cannot insert null value into column RootID on table dbo.Bottom'. I will give you a +1 though because that's the first time anyone has explained the 'Inverse()' part and made sense! :)

Oskar Berggren Over a year ago

It might be that you need to use "inverse" also on the "HasMany(bottoms)" part. You should also declare .Not.Nullable() on the references.

Edgar Froes Over a year ago

@OskarBerggren You were frickin' right, saved me from my torment. 'ppreciate mate.

Collectives™ on Stack Overflow

Cannot insert the value NULL into column - NHibernate entity with 2 parents

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related