Get data from MySQL table with foreign keys using NHibernate

Question

I have a MySQL database with tree tables: Author, PublishingHouse and Book. The last one has two foreign keys - AuthorId and PublishingId. I've made classes for each table and a mapping file for NHibernate. The next point is to get data from Book table and pass it to the View of my MVC project. I can't do this, because in my BookController I got an exception like this: NHibernate.PropertyAccessException: Invalid Cast (check your mapping for property type mismatches);

That is my code for now:

Models

public class Author
{
    public virtual int Id { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
}

public class PublishingHouse
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string City { get; set; }

    }

public class Book
    {
        public virtual int Id { get; set; }
        public virtual string UDK { get; set; }
        public virtual string BBK { get; set; }

        public virtual string AuthorsSign { get; set; }
        public virtual string ISBNrus { get; set; }
        public virtual string ISBNeng { get; set; }

        public virtual Author Author { get; set; }
        public virtual string Name { get; set; }
        public virtual PublishingHouse PublishingHouse { get; set; }

        public virtual int Year { get; set; }
        public virtual int Pages { get; set; }
        public virtual int Circulation { get; set; }

        public virtual bool IsRead { get; set; }
        public virtual int Rating { get; set; }
        public virtual string Annotation { get; set; }
    }

NHibernate mapping

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" assembly="InFolio_HomeLib" namespace="InFolio_HomeLib.Models">
  <class name="Author" table="Author" dynamic-update="true" >
    <cache usage="read-write"/>
    <id name="Id" column="Id" type="int">
      <generator class="native" />
    </id>
    <property name="FirstName" />
    <property name="LastName" />
  </class>

  <class name="PublishingHouse" table="PublishingHouse" dynamic-update="true" >
    <cache usage="read-write"/>
    <id name="Id" column="Id" type="int">
      <generator class="native" />
    </id>
    <property name="Name" />
    <property name="City" />
  </class>

    <class name="Book" table="Book" dynamic-update="true" >
    <cache usage="read-write"/>
    <id name="Id" column="Id" type="int">
      <generator class="native" />
    </id>
    <property name="UDK" />
    <property name="BBK" />
    <property name="AuthorsSign" />
    <property name="ISBNrus" />
    <property name="ISBNeng" />   

    <set name="Author" table="Author">
      <key column ="Id" />
      <one-to-many class="Author"/>
    </set>    

    <property name="Name" /> 

    <set name="PublishingHouse" table="PublishingHouse">
      <key column ="Id" />
      <one-to-many class="PublishingHouse"/>
    </set>    

    <property name="Year" />
    <property name="Pages" />
    <property name="Circulation" />
    <property name="IsRead" />
    <property name="Rating" />
    <property name="Annotation" />
  </class>
</hibernate-mapping>

BookController where I get an exception:

    public ActionResult Index()
    {
        using (ISession connectionDB = NHibertnateSession.OpenSession())
        {
            var books = connectionDB.Query<Book>().ToList();
            return View(books);
        }
    }

I can easy get data from Author and Publishing tables, they are quite simple, but Book table is not so obvious for me. Am I wrong in mapping? Or somewhere else?

Answer 1

The Book class

<class name="Book" table="Book" dynamic-update="true" >

has one author and one publishing house. We need many-to-one mapping

so, instead of this:

<set name="Author" table="Author">
  <key column ="Id" />
  <one-to-many class="Author"/>
</set>    

<set name="PublishingHouse" table="PublishingHouse">
  <key column ="Id" />
  <one-to-many class="PublishingHouse"/>
</set>  

we need that:

<many-to-one name="Author" column="Author_ID" />
<many-to-one name="PublishingHouse" column="PublishingHouse_ID" />

Expecting the Book table to have reference columns Author_ID and PublishingHouse_ID

The doc:

5.1.11. many-to-one

An ordinary association to another persistent class is declared using a many-to-one element. The relational model is a many-to-one association. (It's really just an object reference.) ...

Also, do not miss this:

• Minimal and correct way to map one-to-many with NHibernate