How to perform a joined query for a one-to-many tables with collection property?

I have two tables; t_users(user_id pk, user_name, password) and t_transactions(trans_id pk, user_id fk, item_name, amount). As we can see, t_users is the parent table while t_transactions is the child table so I created two classes for them:

public class User
    {       
        public int ID {get; set;}
        public string Name {get; set;}
        public string Password {get; set;}
        public IList<Transaction> Transactions {get; set;}
        public IList<Group> Groups {get; set;}
    }

public class Transaction
    {
        public Transaction()
        {
        }

        public int ID {get; set;}
        public int UserID {get; set;}
        public string ItemName {get; set;}
        public decimal Amount {get; set;}
        public User User {get; set;}
    }

and here are my mappings:

User

<class name="User" table="t_users" lazy="false">
        <id name="ID" column="user_id" />
        <property name="Name" column="user_name"/>
        <property name="Password" column="password" />
        <bag name="Transactions" lazy="false" cascade="all">
            <key column="user_id" />            
            <one-to-many class="Transaction" />
        </bag>      
        <bag name="Groups" table="t_users_groups" lazy="false" cascade="all" inverse="true">
            <key column="user_id" />
            <many-to-many column="group_id" class="Group" />
        </bag>      
    </class>

Transaction:

<class name="Transaction" table="t_transactions" lazy="false">
        <id name="ID" column="trans_id" />
        <property name="UserID" column="user_id"/>
        <proper开发者_StackOverflow社区ty name="ItemName" column="item_name" />
        <property name="Amount" column="amount" />
        <many-to-one name="User" column="user_id" class="User" />
    </class>

How would I call this query

SELECT u.user_id, u.user_name, u.password, t.trans_id, t.user_id, t.item_name, t.amount
      FROM t_users u INNER JOIN t_transactions t on u.user_id = t.user_id
      WHERE amount <= :limit

Which should return unique parent objects(u) and the duplicate child table(t) should be part/placed in the property Transactions of class User?

Thanks!

You are doing it wrong, because you can't have a "half" of a domain entity. What you want is transactions so you should query for transactions.

In hql:

from Transaction t 
where t.Amount <= :limit

Do you want to eager fetch the User property?

from Transaction t
join fetch t.User
where t.Amount <= :limit

Then you can create a named query or you can execute

session.CreateQuery(query).List<Transaction>();

If you need an object structure like your domain object you can do the following:

public class UserWithTransactionsViewModel
{
  public string UserName {get; set; }
  public IEnumerable<TransactionViewModel> Transactions {get; set;}
}

you can create a projection in memory as follows:

var vms = session.CreateQuery(query)
       .List<Transaction>()
       .GroupBy(t => t.User)
       .Select( g => new UserWithTransactionViewModel
                        { 
                          UserName = g.Key.Name
                          Transactions = g.Select(t => new TransactionViewModel(....)).ToArray()
                        });

Another aproach will be to query for users with one transaction with amount greater than... and then filter the transactions in memory when projecting to your viewmodel or dto. But I don't like this because you have the logic for filtering in two different places it is worse in performance because you will get to memory even transactions that you don't want.

My general advice is that when you work with ORMs, your instances should be consistent in memory always.

PS: you don't need the UserId property in the Transaction class. It is a design fault.