How to fetch one column from another table using Fluent NHibernate

Question

I have a Products table:

ProductId
ProductDescription
CategoryId

And a Categories table:

CategoryId
CategoryDescription

***For every product, I would like to display a line like so:

Product Id | Product Description | Category Description

I have not been successful in forming the necessary mapping that is required for the above task.

Products Mapping I am using:

public ProductsMap()
{
    Table("Products");

    Id(x => x.ProductId);
    Map(x => x.ProductDescription);
    Map(x => x.CategoryId);

    References(x => x.Categories)
        .Column("CategoryId")
        .Not.Nullable();

    // Need Join() statement here?
    ...

My Products class:

public class Products
{
    public virtual int ProductId { get; set; }
    public virtual string ProductDescription { get; set; }
    public virtual int CategoryId { get; set; }
    public virtual Category Category { get; set; }        
    public virtual int? CategoryDescription { get; set; } // Not in the db table.
}

My goal is to have the CategoryDescription field in the above class to be populated automatically by Fluent-NHibernate through the mapping specified.

I used the join statement suggested by this answer but I got various exceptions for the following statement:

List<Products> products = session.Query<Products>().ToList();

Note: I can pull in all products from the database without the corresponding column in the Categories table, so I know that my database connectivity is good, and that the basic functionality of the application is sound.

I am new to Fluent-NHibernate, have invested quite a bit of time on this, but feel I am not getting anywhere. I would appreciate some directed guidance.

Fran Fran · Accepted Answer · 2017-02-06T19:46:14

I'm a little confused because you seem to mixing singular and plural, but I would create separate domain mappings for the product and category

public class Product
{
    public virtual int ProductId { get; set; }
    public virtual string ProductDescription { get; set; }
    public virtual Category Category { get; set; }
}

public class Category
{
    public virtual int CategoryId { get; set; }
    public virtual string CategoryDescription { get; set; }
}

map them the way you are mapping in the question, then create a view model

public class ProductViewModel
{
    public virtual int ProductId { get; set; }
    public virtual string ProductDescription { get; set; }
    public virtual string CategoryDescription { get; set; }
}

that gets populated with this query

        var products = session.Query<Products>().Select(p => new ProductViewModel()
        {
                ProductId =  p.ProductId,
                ProductDescription = p.ProductDescription,
                CategoryDescription = p.Category.CategoryDescription
        });

This will produce a query that only returns the columns you need. If you return full entities, you are going to return information you don't need.

How to fetch one column from another table using Fluent NHibernate

1 Answers