CODEWITHSUNDEEP
linq-to-sqlparent-childtopmost
Serexx
Serexx

Reputation: 1250

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

Upvotes: 1

Views: 1469

Answers (2)

Sajid Ali
Sajid Ali

Reputation: 779

  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.

Upvotes: 0

alex
alex

Reputation: 3720

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.

Upvotes: 1

Related Questions