NHibernate select specific column

Question

public class Company_Product
{
    public virtual Int32 Id { get; set; }
    public virtual DateTime SalesDate { get; set; }
    public virtual Company Company{ get; set; }
    public virtual Product Product { get; set; }
}

public class Company
{
    public virtual Int32 Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Company_Product> company_product { get; set; }
}

public class Product
{
    public virtual Int32 Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Company_Product> company_product { get; set; }
}

Company.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="BusinessObjectApp"
                   namespace="BusinessObjectApp.Modal">

    <!-- more mapping info here -->
    <class name="Company" table="[Company]">
        <id name="Id" column="Id">
            <generator class="native" />
        </id>
        <property name="Name" column="Name" />

        <bag name="company_product" table="[Company_Product]" inverse="true" lazy="true">
            <key column="CompanyID" />
            <one-to-many class="Company_Product" />
        </bag>
    </class>
</hibernate-mapping>

Company_Product.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="BusinessObjectApp"
                   namespace="BusinessObjectApp.Modal">

    <!-- more mapping info here -->
    <class name="Company_Product" table="[Company_Product]">
        <id name="Id" column="Id">
            <generator class="native" />
        </id>
        <property name="SalesDate" column="SalesDate" />

        <!-- Many to many -->
        <many-to-one class="Company" name="Company" column="CompanyID" />
        <many-to-one class="Product" name="Product" column="ProductID" />
    </class>
</hibernate-mapping>

Product.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="BusinessObjectApp"
                   namespace="BusinessObjectApp.Modal">

  <!-- more mapping info here -->
  <class name="Product" table="[Product]">
      <id name="Id" column="Id">
          <generator class="native" />
      </id>
      <property name="Name" column="Name" />
      <bag name="company_product" table="[Company_Product]" inverse="true" lazy="true">
          <key column="ProductID" />
          <one-to-many class="Company_Product" />
      </bag>
    </class>
</hibernate-mapping>

I able to retrieve all item if using the code below:

IList<Company_Product> test = session.QueryOver<Company_Product>()
.List<Company_Product>();

However the code below produce error. I know that is because Company_Product class does not have the properties. I want to map them to the corresponding object like the output above. Is it possible?

string query = "SELECT C.Name, P.Name, CP.SalesDate FROM [Company_Product] CP " +
"LEFT JOIN [Company] C ON CP.CompanyID = C.Id " +
"LEFT JOIN [Product] P ON CP.ProductID = P.Id";

var test = session.CreateSQLQuery(query)
.SetResultTransformer(Transformers.AliasToBean<Company_Product>())
.List<Company_Product>();

UPDATE:

Now I able to retrieve the whole object.

Company_Product company_product = null;
Company company = null;
Product product = null;
IList<Company_Product> test = session.QueryOver<Company_Product>()
                    .Left.JoinAlias(cp => cp.Company, () => company)
                    .Left.JoinAlias(cp => cp.Product, () => product)
                    .SelectList(list => list
                        .Select(cp => cp.Company).WithAlias(() => company_product.Company)
                        .Select(cp => cp.Product).WithAlias(() => company_product.Product)
                        .Select(cp => cp.SalesDate).WithAlias(() => company_product.SalesDate)
                    )
                    .TransformUsing(Transformers.AliasToBean<Company_Product>())
                    .List<Company_Product>();

But I cannot set the nested property.

Company_Product company_product = null;
Company company = null;
Product product = null;
IList<Company_Product> test = session.QueryOver<Company_Product>()
                    .Left.JoinAlias(cp => cp.Company, () => company)
                    .Left.JoinAlias(cp => cp.Product, () => product)
                    .SelectList(list => list
                        .Select(cp => cp.Company.Name).WithAlias(() => company_product.Company.Name)
                        .Select(cp => cp.Product.Name).WithAlias(() => company_product.Product.Name)
                        .Select(cp => cp.SalesDate).WithAlias(() => company_product.SalesDate)
                    )
                    .TransformUsing(Transformers.AliasToBean<Company_Product>())
                    .List<Company_Product>();

Can anyone help me? I am new to Nhibernate and sorry for my bad English.

Finally solved with this https://stackoverflow.com/questions/29644236/use-nhibernate-aliastobean-transformer-launch-n1-query — RYan.A, May 25 '18 at 02:59

Frederik Gheysels · Answer 1 · 2018-05-24T09:25:03.017

0

You do not need to use a native SQL query to achieve this. You can use QueryOver but will have to use projections.

session.QueryOver<Company_Product>()
       .JoinQueryOver<Company>(cp => cp.Company, ()=>companyAlias)
       .JoinQueryOver<Product>(cp => cp.Product, ()=>productAlias)
       .SelectList( l => l.Select( cp => companyAlias.Name)
                          .Select( cp => productAlias.Name)
                          .Select( cp => cp.SalesDate));

I haven't tested it, and it's been more than over a year since I've used NHibernate, but it should point you in the right direction. :)

Also, why are you using a bag ? I think a set might be more appropriate.

edited May 24 '18 at 09:25

answered May 24 '18 at 09:15

Frederik Gheysels

56,135
11
101
154

Thanks for your reply. I now able to map to the object. What if I want to set the property like what I mention in the update. – RYan.A May 24 '18 at 09:24
I do not understand the question in your update; I also see no difference between the 2 code samples you've posted in the update – Frederik Gheysels May 24 '18 at 09:27
I change the cp.Company to cp.Company.Name and cp.Product to cp.Product.Name. But return an error said cannot find setter for property "Name". – RYan.A May 24 '18 at 09:41
You need to use the alias that you've defined; see the code-sample in my answer. So, instead of specifying cp.Company.Name, you'll have to use company.Name, since you've defined company as the alias for it. – Frederik Gheysels May 24 '18 at 09:43
Thanks for your advice, I change it to company.Name still have the same error (Could not find a setter for property 'Name' in class 'BusinessObjectApp.Modal.Company_Product'). I think is the Company_Product that does not have the property "Name", so I cannot set it. – RYan.A May 24 '18 at 10:22
I also tried the solution you provided above. In line 3, cp.Product it shows error (Delegate 'System.Func>' does not take 1 arguments'). Don't why it happen. – RYan.A May 24 '18 at 10:24

NHibernate select specific column

1 Answers1