使用NHibernate从带有外键的MySQL表中获取数据 [英] Get data from MySQL table with foreign keys using NHibernate

问题描述

我有一个带有树表的MySQL数据库:作者，出版社和书.最后一个有两个外键-AuthorId和PublishingId. 我为每个表创建了类，并为NHibernate创建了映射文件. 接下来的一点是从Book表中获取数据，并将其传递给我的MVC项目的View. 我无法执行此操作，因为在BookController中，我遇到了如下异常: NHibernate.PropertyAccessException: Invalid Cast (check your mapping for property type mismatches);

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);

这是我现在的代码:

模型

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映射

<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 ，这里出现异常:

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

我可以很容易地从Author和Publishing表中获取数据，它们很简单，但是Book表对我而言并不那么明显.我在映射上错了吗?还是其他地方?

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?

推荐答案

Book类

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

有一位作家和一间出版社.我们需要多对一映射

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

所以，代替这个:

<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>  

我们需要:

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

期望Book表具有参考列Author_ID和PublishingHouse_ID

Expecting the Book table to have reference columns Author_ID and PublishingHouse_ID

文档:

5.1.11.多对一

使用以下方法声明与另一个持久类的普通关联 多对一元素.关系模型是多对一的 协会. (它实际上只是一个对象引用.)...

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.) ...

此外，请不要错过:

• 使用NHibernate进行一对多映射的最小正确方法

这篇关于使用NHibernate从带有外键的MySQL表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！