Nhibernate building query

Question

I have a query which works in MySql, but I am trying to use it in Nhibernate, where I am facing problem cause I am a noob in Nhibernate.

SELECT transaction_table.transaction_id,transaction_table.amount,transaction_table.reason,transaction_table.transaction_datetime,g.status,
       (CASE
         WHEN (transaction_table.user_from IN(0)) THEN 'Paypal'
        when  (transaction_table.user_from<>0) THEN u.user_fullname
       END)as user_name_from ,
       (CASE
         WHEN (transaction_table.user_to IN(0)) THEN 'Paypal'
        when  (transaction_table.user_to<>0) THEN us.user_fullname
       END)as user_name_to 
FROM   transaction_table 
left outer join users u on u.user_id=transaction_table.user_from 
left outer join users us on  transaction_table.user_to=us.user_id 
left join gateway_table g on g.gateway_table_id=transaction_table.gateway_table_id

This is the query,

var results = session.QueryOver<TransactionTable>(() => transactionAlias)
                .Left.JoinAlias(pr => pr.UserFrom, () => usersFromAlias.Id)
                .Left.JoinAlias(pr => pr.UserTo, () => usersToAlias.Id)
                .Left.JoinAlias(pr => pr.GatewayTableId, () => gatewayAlias.GatewayTableId)
                .SelectList(list => list
                    .Select(pr => pr.TransactionId)
                    .Select(pr => pr.Amount)
                    .Select(pr => pr.Reason)
                    .Select(pr => pr.TransactionDatetime)
                    .Select(pr => pr.GatewayTableId)
                    .Select(Projections.Conditional(
                        Restrictions.Eq(
                            Projections.Property(() => transactionAlias.UserFrom), 0),
                        Projections.Constant("Paypal"),
                        Projections.Property(() => usersFromAlias.FullName)
                    ))
                    .Select(Projections.Conditional(
                        Restrictions.Eq(
                            Projections.Property(() => transactionAlias.UserTo), 0),
                        Projections.Constant("Paypal"),
                        Projections.Property(() => usersToAlias.FullName)
                    )))
                .List<object[]>();

I cannot figure out how to proceed after this, this is midway I have been implementing. I cannot find something solid, About XML my mapping might be issue where relations are not mapped, i can't figure that how to do.

Mapping below:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="EWalletModule" namespace="EWalletModule">
  <!-- User Table -->
  <class name="Users" table="Users">
    <id name="Id" column="user_id" type="int">
      <generator class="native"></generator>
    </id>
    <property name="FullName" column="user_fullname" type="String"></property>
    <property name="Email" column="user_email" type="String"></property>
    <property name="Password" column="user_password" type="String"></property>
    <property name="CreatedDatetime" column="created_datetime" type="datetime"></property>
  </class>

  <!-- Transaction Table -->
  <class name="TransactionTable" table="transaction_table">
    <id name="TransactionId" column="transaction_id" type="int">
      <generator class="native"></generator>
    </id>
    <property name="GatewayTableId" column="gateway_table_id" type="int"></property>
    <property name="UserFrom" column="user_from" type="String"></property>
    <property name="UserTo" column="user_to" type="String"></property>
    <property name="Amount" column="amount" type="String"></property>
    <property name="Reason" column="reason" type="String"></property>
    <property name="TransactionDatetime" column="transaction_datetime" type="datetime"></property>
  </class>

  <!-- Gateway Table -->
  <class name="GatewayTable" table="gateway_table">
    <id name="GatewayTableId" column="gateway_table_id" type="int">
      <generator class="native"></generator>
    </id>
    <property name="Status" column="status" type="String"></property>
  </class>

  <!-- Wallet Table -->
  <class name="WalletTable" table="user_wallet">
    <id name="UserWalletId" column="user_wallet_id" type="int">
      <generator class="native"></generator>
    </id>
    <property name="UserId" column="user_id" type="int"></property>
    <property name="WalletBalance" column="wallet_balance" type="float"></property>
  </class>

  <!-- ManageWallet Table //For Admin -->
  <class name="ManageWalletTable" table="manage_wallet">
    <id name="ManageWalletId" column="manage_wallet_id" type="int">
      <generator class="native"></generator>
    </id>
    <property name="TransactionFee" column="transaction_fee" type="float"></property>
    <property name="WithdrawalFee" column="withdrawal_fee" type="float"></property>
    <property name="Tax" column="tax" type="float"></property>
  </class>
</hibernate-mapping>

Table is simple like the mapping, but the query works in Mysql workbench and data is alright but no idea about Nhibernate. So currently I get exception issue and I know this is wrong, but cannot figure out how to do it, and I don't want to do it using simple sql query, I will love to do it Object Way.

Thanks to Stack-overflow Community.

score 0 · Answer 1 · answered Feb 14 '18 at 13:57

Most used overload of JoinAlias method has 2 parameters: expression for a navigation property and expression for an alias.

You don't need to specify the "on" condition - NHibernate will resolve it using mapping.

So to do it you have to add navigation properties for UserFrom, UserTo, GatewayTable. You can leave your old properties but rename them to something like UserFromId and change type to int(And also check types for other properties: I guess Amount should not be a string but decimal, double or int). If you cannot or don't want to add navigation properties then you will have to use subqueries because as far as I know it's impossible to perform join in NHibernate without mapped navigation properties.

var results = session.QueryOver<TransactionTable>(() => transactionAlias)
                .Left.JoinAlias(pr => pr.UserFrom, () => usersFromAlias)
                .Left.JoinAlias(pr => pr.UserTo, () => usersToAlias)
                .Left.JoinAlias(pr => pr.GatewayTable, () => gatewayAlias)
                .SelectList(list => list
                    .Select(pr => pr.TransactionId)
                    .Select(pr => pr.Amount)
                    .Select(pr => pr.Reason)
                    .Select(pr => pr.TransactionDatetime)
                    .Select(pr => pr.GatewayTableId)
                    .Select(Projections.Conditional(
                        Restrictions.Eq(
                            Projections.Property(() => transactionAlias.UserFromId), 0),
                        Projections.Constant("Paypal"),
                        Projections.Property(() => usersFromAlias.FullName)
                    ))
                    .Select(Projections.Conditional(
                        Restrictions.Eq(
                            Projections.Property(() => transactionAlias.UserToId), 0),
                        Projections.Constant("Paypal"),
                        Projections.Property(() => usersToAlias.FullName)
                    )))
                .List<object[]>();

Also I would suggest you to create a custom Dto model and use ResultTransformer instead of just returning tuples:

TransactionDto resultAlias = null;

var results = session.QueryOver<TransactionTable>(() => transactionAlias)
                .Left.JoinAlias(pr => pr.UserFrom, () => usersFromAlias)
                .Left.JoinAlias(pr => pr.UserTo, () => usersToAlias)
                .Left.JoinAlias(pr => pr.GatewayTable, () => gatewayAlias)
                .SelectList(list => list
                    .Select(pr => pr.TransactionId).WithAlias(() => resultAlias.TransactionId)
                    .Select(pr => pr.Amount).WithAlias(() => resultAlias.Amount)
                    .Select(pr => pr.Reason).WithAlias(() => resultAlias.Reason)
                    .Select(pr => pr.TransactionDatetime).WithAlias(() => resultAlias.TransactionDatetime)
                    .Select(pr => pr.GatewayTableId).WithAlias(() => resultAlias.GatewayTableId)
                    .Select(Projections.Conditional(
                        Restrictions.Eq(
                            Projections.Property(() => transactionAlias.UserFromId), 0),
                        Projections.Constant("Paypal"),
                        Projections.Property(() => usersFromAlias.FullName)
                    )).WithAlias(() => resultAlias.UserFromName)
                    .Select(Projections.Conditional(
                        Restrictions.Eq(
                            Projections.Property(() => transactionAlias.UserToId), 0),
                        Projections.Constant("Paypal"),
                        Projections.Property(() => usersToAlias.FullName)
                    )).WithAlias(() => resultAlias.UserToName))
                .TransformUsing(Transformers.AliasToBean<TransactionDto>())
                .List<TransactionDto>();

Hello @Roman, Can you help me with how to modify my mapping xml so that it should work? I am sorry I am a noob and am having hard time doing it. Thanks for helping — Ajit Kumar Thakur, Feb 14 '18 at 14:08
Actually I don't have an experience with xml mapping. I would recommend you to use [FluentNhibernate](https://github.com/jagregory/fluent-nhibernate/wiki/Getting-started). — Roman Koliada, Feb 14 '18 at 14:14

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

What we need, is the relations mapping. That is represented by many-to-one and one-to-many. The doc

5.1.11. many-to-one

6.1. Persistent Collections

so, for example this

<property name="UserFrom" column="user_from" type="String"></property>

should be

<many-to-one name="UserFrom"   column="user_from_id" class="User" />
<property    name="UserFromId" column="user_from_id" type="Int32"  
                                    insert="false" update="false" />

and our entity referencing such User should have properties

public virtual User UserFrom { get; set; }
public virtual int UserFromId { get; set; }

the second is not needed.. but sometimes could be useful.

Check these:

Nhibernate building query

2 Answers2

5.1.11. many-to-one

6.1. Persistent Collections