Mapping a collection joined by either of two columns with Fluent NHibernate?

Question

I'm modeling accounting where I have accounts with transactions that debit one account and credit another.

Here are the details of the situation (simplified). My tables (in SQL Server 2008) are:

CREATE TABLE Account
(
    AccountID int IDENTITY(1,1) NOT NULL,
    AccountNumber nvarchar(10) NOT NULL
)

CREATE TABLE [Transaction]
(
    TransactionID [bigint] IDENTITY(1,1) NOT NULL,
    DebitAccountID [int] NOT NULL,
    CreditAccountID [int] NOT NULL,
    Amount money NOT NULL
)

My classes are:

public class Account
{
    public int Id { get; set; }
    public string AccountNumber { get; set; }
    public IList<Transaction> Transactions { get; set; }
}

public class Transaction
{
    public int Id { get; set; }
    public Account DebitAccount { get; set; }
    public Account CreditAccount { get; set; }
}

So the question is "How do I map the Transactions collection in the Account class using fluent NHibernate?"

What I want (for performance reasons) is for the accessing of the transactions collection to execute the query:

SELECT ...
FROM [Transaction]
WHERE DebitAccountID=@accountID OR CreditAccountID=@accountID

The important part there is the OR in the where clause.

So the code I need is:

public class AccountMap : SubclassMap<Account>
{
    public AccountMap()
    {
        Id(x => x.Id).Column("AccountID");
        Map(x => x.AccountNumber);
        HasMany(x => x.Transactions)
            // What goes here to explain this mapping to NHibernate?
            .Inverse().Cascade.AllDeleteOrphan()
            .Access.CamelCaseField();
    }
}

Note: I am aware that I could map the transactions as two separate collections, one for "debits" and the other for "credits". That is not an acceptable answer because of performance issues. In particular, there is actually a second type like this related to the account (resulting in even more queries) and mapping as two collections prevents the use of eager loading with Fetch(). The second type is PaymentScheduleLine which contains a plan of all the correct payment transactions over the life of the account. It is associated to the account in the same way as transaction i.e. PaymentScheduleLine has a DebitAccount and CreditAccount and Account has a PaymentSchedule collection. Typically, complex calculations involve the relationship between the transactions and the payment schedule.

Answer 1

I might revise my answer once I hear more about this "second type like this related to the asset," but for now...

Decide what's important and be willing to make sacrifices.

It sounds like performance is your primary concern here, right? You might have to relax your demands that the domain model not be changed or that the query look a certain way.

Batch queries using .Future() to avoid Cartesian products.

You're right that having collections named Debits and Credits could lead to performance problems. Maybe this is the query you're thinking of:

// BAD QUERY - DO NOT USE - cartesian product of rows - Debits X Credits.
var account = session.QueryOver<Account>()
    .Fetch(x => x.Debits).Eager
    .Fetch(x => x.Credits).Eager
    .Where(x => x.Id == accountId)
    .SingleOrDefault();

If the account had 1,000 transactions, 500 of them being debits, and 500 credits, then this query would result in 250,000 rows (500 * 500), which is clearly unacceptable!

You don't have to write the query that way, though. This one is better:

var futureAccount = session.QueryOver<Account>()
    .Fetch(x => x.Debits).Eager
    .Where(x => x.Id == accountId)
    .FutureValue();

session.QueryOver<Account>()
    .Fetch(x => x.Credits).Eager
    .Where(x => x.Id == accountId)
    .Future();

var account = futureAccount.Value;

Even though this is really two queries, it will be executed in one round-trip to the database, with only 1,000 rows returned (500 + 500). NHibernate will reuse the same Account instance for the two queries, and just populate whatever data was eagerly fetched. The result will be an Account with fully populated Debits and Credits.

Only fetch the data you need.

Now, 1,000 rows is still a lot. Are you absolutely sure you need to load all of the transactions for a given account? What are you using them for? For the scenario you mentioned, calculating how much money moved from account A to account B over a given timeframe, you would achieve much better performance if you wrote a query to calculate exactly that, or at the very least, only loaded the transactions you were actually interested in using a more specific query. A query like this...

var transactions = session.QueryOver<Transaction>()
    .Where(x => x.DebitAccount.Id == fromId
        && x.CreditAccount.Id == toId
        && x.Date >= startDate
        && x.Date < endDate.AddDays(1))
    .List();

... could easily cut the number of transactions you're working with from 1,000 to 20 or less.