Inserting records in postgres using NHibernate 3.2 (loquacious) and bycode mappings

Question

I am working on a very basic NHibernate 3.2 task, inserting records into existing Postgres tables. I am using very simple object so that they make sense for this question.

The postgres tables are as follows:

CREATE TABLE cat
(
  id serial NOT NULL,
  "name" character varying(50) NOT NULL,
  sex_id integer NOT NULL,
  CONSTRAINT cat_pkey PRIMARY KEY (id),
  CONSTRAINT cat_sex_id_fkey FOREIGN KEY (sex_id)
      REFERENCES sex (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE TABLE sex
(
  id serial NOT NULL,
  "name" character varying(10) NOT NULL,
  CONSTRAINT sex_pkey PRIMARY KEY (id),
  CONSTRAINT sex_name_key UNIQUE (name)
)
WITH (
  OIDS=FALSE
);

My mapping classes are as follows:

public class CatMap : ClassMapping<Cat>
    {
        public CatMap()
        {
            Table("cat");
            Id(x => x.Id, map =>
            {
                map.Column("id");
                map.Generator(NHibernate.Mapping.ByCode.Generators.Native);
            });
            Property(x => x.Name, map =>
            {
                map.Column("name");
                map.Length(50);
                map.NotNullable(true);
            });

            ManyToOne(x => x.Sex, map =>
            {
                map.Column("Sex");
                map.Unique(true);
                map.ForeignKey("cat_sex_id_fkey");
            });
        }
    }

public class SexMap : ClassMapping<Sex>
    {
        public SexMap()
        {
            Table("sex");
            Id(x => x.Id, map =>
                {
                    map.Column("id");
                    map.Generator(Generators.Native);
                });
            Property(x => x.Name, map => 
                {
                    map.Column("name");
                    map.Unique(true);
                    map.Length(10);
                    map.NotNullable(true);
                });
        }
    }

My data classes are as follows:

public class Sex
{
    public Sex() 
    {
    }
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

public class Cat
{
    public Cat() 
    {
    }
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Sex Sex { get; set; }
}

Finally, the class containing the code where I am actually attempting to use all of the above.

public class Class1
    {
        public string DoSomething()
        {
            var sessionFactory = CreateSessionFactory();

            using (var session = sessionFactory.OpenSession())
            {
                using (var transaction = session.BeginTransaction())
                {
                    Postgres.Tables.Sex sex1 = new Postgres.Tables.Sex() { Name = "Male" };
                    Postgres.Tables.Sex sex2 = new Postgres.Tables.Sex() { Name = "Female" };

                    Postgres.Tables.Cat cat1 = new Postgres.Tables.Cat() { Name = "Cat1" };
                    cat1.Sex = sex1;
                    Postgres.Tables.Cat cat2 = new Postgres.Tables.Cat() { Name = "Cat2" };
                    cat2.Sex = sex2;

                    session.SaveOrUpdate(sex1);
                    session.SaveOrUpdate(sex2);
                    session.SaveOrUpdate(cat1);
                    session.SaveOrUpdate(cat2);

                    transaction.Commit();
                }
            }

            return "I created the cats.";
        }

        private static ISessionFactory CreateSessionFactory()
        {
            NHibernate.Mapping.ByCode.ModelMapper modelMapper = new NHibernate.Mapping.ByCode.ModelMapper();
            System.Type[] mappingTypes = typeof(Postgres.Tables.Mappings.CatMap).Assembly.GetExportedTypes().Where(t => t.Name.EndsWith("Map")).ToArray();
            modelMapper.AddMappings(mappingTypes);
            Configuration cfg = new Configuration();
            cfg.Proxy(p => p.ProxyFactoryFactory<NHibernate.Bytecode.DefaultProxyFactoryFactory>());
            cfg.DataBaseIntegration(d =>
                {
                    d.ConnectionString = "server=192.168.1.126;Port=5432;Database=simple;User Id=postgres;Password=postgres;";
                    d.Dialect<NHibernate.Dialect.PostgreSQL82Dialect>();
                });
            cfg.AddMapping(modelMapper.CompileMappingForAllExplicitlyAddedEntities());
            return cfg.BuildSessionFactory();
        }
    }

I receive a GenericADOException at "session.SaveOrUpdate(cat1)" with the message "could not insert: [DAL.Postgres.Tables.Cat][SQL: INSERT INTO cat (name, Sex) VALUES (?, ?); select lastval()]". The InnerException is "{"ERROR: 42703: column \"sex\" of relation \"cat\" does not exist"}".

I am a bit stumped at how to properly assign "sex1" to "cat1" and "sex2" to "cat2" so that the first is Male and the second is Female.

Thank you for any input.

Answer 1

m.Column("Sex") as the name suggests denote column name and not the property name as you specified. So you should write m.Column("sex_id"). map.ForeignKey("cat_sex_id_fkey") is used by NH as a FK name when NH create db schema from your mapping.