c# - Nhibernate构建查询

Question

我有一个可以在 MySql 中运行的查询，但我试图在 Nhibernate 中使用它，我在 Nhibernate 中遇到了问题，因为我是 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

这是查询，

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[]>();

我不知道之后如何继续，这是我一直在实现的中途。我找不到可靠的东西，关于 XML，我的映射可能存在关系未映射的问题，我不知道该怎么做。

下面的映射:

<?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>

表很简单，就像映射一样，但是查询可以在Mysql工作台中运行，数据也可以，但不知道Nhibernate。所以目前我遇到了异常问题，我知道这是错误的，但无法弄清楚如何做到这一点，而且我不想使用简单的 sql 查询来做到这一点，我很乐意以对象方式来做到这一点。

感谢 Stack-overflow 社区。

Answer 1

最常用的 JoinAlias 方法重载有 2 个参数:导航属性的表达式和别名的表达式。

您不需要指定“on”条件 - NHibernate 将使用映射来解决它。

因此，您必须为 UserFrom、UserTo、GatewayTable 添加导航属性。您可以保留旧属性，但将它们重命名为 UserFromId 并将类型更改为 int(并且还检查其他属性的类型:我猜 Amount 不应该是 字符串但十进制、 double 或int)。如果您不能或不想添加导航属性，那么您将不得不使用子查询，因为据我所知，如果没有映射的导航属性，就不可能在 NHibernate 中执行连接。

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[]>();

此外，我建议您创建一个自定义 Dto 模型并使用 ResultTransformer 而不是仅仅返回元组:

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>();