Select Top for Subquery with Entity Framework

Question

I have a Master-Detail model relationship set up in C# Entity Framework. One Master record might have multiple Detail records. I wish to bring back ALL Master records but only the first associated Detail record per Master record.

e.g. Master: Id=5

Detail: Id=1; EventMasterId=5;

Detail: Id=2; EventMasterId=5;

Detail: Id=3; EventMasterId=5;

When "Master Id 5" comes back, I only want the first associated record "Detail Id 1". I don't want the other details.

I can do this in raw SQL but can't figure out how to convert this to LINQ:

SELECT * FROM EventMasters
JOIN EventDetails
ON EventDetails.Id =
  (SELECT TOP 1 Id
    FROM EventDetails
    WHERE EventMasterId=EventMasters.Id
    ORDER BY StartDate)

It's the "Select Top 1" from the EventDetails that's especialy tripping me.

Would anyone know how to convert this SQL statement to LINQ please?

Thank you

spender spender · Accepted Answer · 2014-12-31T02:02:05

Masters
   .Select(m => new{
       Master = m, 
       Detail = m.Details.OrderBy(d => d.StartDate).FirstOrDefault())
   })

Select Top for Subquery with Entity Framework

1 Answers