Outer Join Query to Most Recent Record in the Outer tables.

kimbonics

I've got two tables, A and B. I want to outer join from A to B, but only take the first record in B ordered by a datetime column desending. This query gave me an error:
---
from a in A
join b in B on A.Aid equals B.Aid into bs
from bLatest in bs.OrderByDescending( bs.TimeStampCol ).FirstOrDefault()
select new { a, bLatest }

This gives a compile error. Any ideas on how to do this in the cleanest fashion?

JoeAlbahari

Your lambda expression is malformed - it should read b => b.TimeStampCol.

Another problem is that your query will perform an inner join, not an outer join.

The trick to joining in LINQ is not to join at all. Instead, use association properties - this makes joining unnecessary and the queries much simpler. LINQPad automatically creates association properties based on the foreign key constraints in your database. Visual Studio's LINQ-to-SQL and Entity Framework designers do the same.

Here's what your query looks like using an association property:

from a in A
select new
{
  a,
  bLatest = a.B.OrderByDescending(b => b.TimeStampCol).FirstOrDefault()
}

This will translate into an outer join when LINQ-to-SQL converts it into a SQL statement.

If you still don't want to use association properties, here's how to write the query:

from a in A
select new
{
  a,
  bLatest = B.Where (b => b.Aid == a.Aid)
             .OrderByDescending(b => b.TimeStampCol)
             .FirstOrDefault()
}

kimbonics

Thanks. Couldn't do assoc prop (relation) because this job is for sync'ing up to a different system and the db foreign keys, etc. can't/won't be in place.

JFLN

Concerning the second suggestion:

from a in A
select new
{
a,
bLatest = B.Where (b => b.Aid == a.Aid)
.OrderByDescending(b => b.TimeStampCol)
.FirstOrDefault()
}

The SQL generated for me queried A once, then queried B (n) times. It didn't LEFT OUTER JOIN. That's my results anyhow.

JoeAlbahari

Must be a bug in LINQ to SQL. Interestingly, it works OK in EF6. EF translates it to an OUTER APPLY.

JFLN

Interesting. I don't have EF6 installed yet, so I'll take your word on OUTER APPLY in the meantime. I think the important take though is that the construct doesn't appear to result in an SQL LEFT OUTER JOIN. Thanks for checking.