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.

score 1 · Accepted Answer · answered Feb 06 '17 at 19:46

1

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.

answered Feb 06 '17 at 19:46

Fran

6,440
1
23
35

A huge thank you! It works. Now I have to understand **how** it works! :) Any good resources you can suggest on Fluent-NHibernate? – Sabuncu Feb 06 '17 at 20:20
PS: I had to use this answer: http://stackoverflow.com/a/3402839/360840 to resolve a cast problem, but other than that, it was perfect. – Sabuncu Feb 06 '17 at 20:22
yeah the query i included will return an IQueryable. ToList(), ToArray(),... will run the query an populate the list. – Fran Feb 06 '17 at 20:24
for your mapping questions, check this out http://notherdev.blogspot.com/2012/01/mapping-by-code-manytoone.html – Fran Feb 06 '17 at 20:24
I have been to that page, and had it bookmarked, but it didn't make sense at the time. I have a book titled NHibernate 3 Beginner's Guide [2011] but it's old (for example, uses Null instead of Nullable), and not at all clear. Spends too much on relational DB basics. How did you learn, if I may ask? – Sabuncu Feb 06 '17 at 20:29

How to fetch one column from another table using Fluent NHibernate

1 Answers1