Linq To SQL Select parent row and topmost Child Table row

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

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.

1. tblLink link = ( from c in context.tblRegionLinks where c.LinkId == 3 && c.RegionId == 5 select c.tblLink).FirstOrDefault();

Working fine in my case .....

Hope it will help someone else.