Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

C# Outer Apply in LINQ

How can I achieve Outer Apply in LINQ? I'm having a bit of a problem.

Here's the SQL Query I'm using.

SELECT u.masterID
      ,u.user
      ,h.created
FROM dbo.Users u
OUTER APPLY (SELECT TOP 1 * FROM UserHistory h where h.masterID = u.masterID ORDER BY created DESC) h
like image 355
Michael D. Irizarry Avatar asked Jun 10 '10 12:06

Michael D. Irizarry


1 Answers

from u in Users 
join UserHistory on u.masterID equals h.masterID into h 
select new {
  u.masterID,
  u.user,
  Created = h.Select(x => x.created).OrderByDescending(c => c).FirstOrDefault()
}

Or, with an association:

from u in Users
let created = u.UserHistories.Select(x => x.created).OrderByDescending(c => c).FirstOrDefault()
select new
{
  u.masterID,
  u.user,
  Created = created
}
like image 193
Amy B Avatar answered Sep 20 '22 09:09

Amy B