Sabuncu Sabuncu - 4 years ago 126
C# Question

How to fetch one column from another table using Fluent NHibernate

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download