Linq To SQL Select parent row and topmost Child Table row

Question

There are examples around for this in SQL and I understand them, but I can't seem to wrap my head around it in Linq-SQL.

There are two tables, 'Accounts' and 'AccountTransactions' related by the primary key in Accounts, which is, surprise, AccountID.

I need to select each Account row and the 1 Most Recent (Top 1 when ordered Descending) child AccountTransaction.

I've tried to hack at some examples I've found but no luck (just not getting it I guess)...

Any help appreciated...

Answer 1

This is one possible solution; it's not pretty, but it should work:

from account in Accounts
select new
    {account.Name, account.Whatever,
    LastTransaction =
        account.AccountTransactions.OrderByDescending(t => t.Date).First()};

This will select all accounts and, if the AccountTransactions table has AccountID as a foreign key, the join will be automatically performed. All you need to do is get the details you need for the account and get the latest transaction, ordered by date.