Join Tables using Fluent Nhibernate

Question

I have following table structure

Author[AuthorId,AuthorName];
Book [BookId, BookName, AuthorId];

And already created following classes for F.N mappings

public class Author
{
    public virtual int AuthorId         { get; set; }
    public virtual string AuthorName    { get; set; }

    public IList<Book> Book { get; set; }
}

public class AuthorDataMap : ClassMap<Auther>
{
    public MaisonUserDataMap()
    {
        Table("author");
        Id(x => x.AuthorId).Column("authorID");
        Map(x => x.AuthorName).Column("authorName");

        HasMany<MonsterData>(p => p.monsterData)
        .KeyColumn("authorID")
        .PropertyRef("AuthorId").Inverse();
}


public class Book
{
    public virtual int BookId           { get; set; }
    public virtual string BookName      { get; set; }
    public virtual int AuthorId         { get; set; }

    public virtual Author Author { get; set; }

        public Book()
        {
            Author = new Author();
        }
}


class BookMap : ClassMap<Book>
{
        public BookMap()
        {
            Table("book");
            Id(x => x.BookId).Column("bookId");
            Map(x => x.BookName).Column("bookName");
            Map(x => x.AuthorId).Column("authorId");

            References(x => x.Author);
        }
}

And using following NH query it will return single object for each author with list of their associated books

var authorList = session.CreateCriteria<Auther>("AuthorList").Add(Restrictions.Eq("AuthorList.AuthorId", 1)).List<Author>(); // working

Console.WriteLine("AuthorList Count :" + authorList.Count);
foreach (var item in authorList)
{

    foreach (var k in item.book)
    {
        Console.WriteLine("BookId :" + k.bookId);
    }

}

Now i want to get list of book objects with its author details. (the opposite of above). I tried to do it using following code but it didn't return any author details

var bookList = session.CreateCriteria<Book>("Book").Add(Restrictions.Eq("Book.AuthorId", 1)).List<Book>();
Console.WriteLine("BookList Count :" + bookList.Count);
foreach (var item in bookList)
{
    Console.WriteLine("BookId :" + item.bookId);
    Console.WriteLine("AuthorName :" + item.Author.authorName);
}

If anyone knows how to do this please let me know. Thanks

Answer 1

In case we want to use ONE column to be mapped to TWO properties, the mapping should be like this:

public BookMap()
{
    Table("book");
    Id(x => x.BookId).Column("bookId");
    Map(x => x.BookName).Column("bookName");

    // authorId column for both
    Map(x => x.AuthorId)
       .Column("authorId")
       .Not.Insert()
       .Not.Update()
       ;

    References(x => x.Author, "authorId");
}

So, now we explicitly said to fluent NHibernate - there are value type and reference type representation of the "authorId". One of them must be readonly - to properly generate INSERT and UPDATE.

And now this query would work

var bookList = session
  .CreateCriteria<Book>("Book")
  .Add(Restrictions.Eq("Book.AuthorId", 1))
  .List<Book>();

Console.WriteLine("BookList Count :" + bookList.Count);
foreach (var item in bookList)
{
    Console.WriteLine("BookId :" + item.bookId);
    Console.WriteLine("AuthorName :" + item.Author.authorName);
}

NOTE, the other side (Author) could have HasMany now define without property ref

HasMany(a => a.Books)
    .KeyColumn("authorID")
    //.PropertyRef("AuthorId") // there will be Author reference
    .Inverse();