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

开发者_StackOverflow社区

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.