CODEWITHSUNDEEP
linqentity-framework-6
Brannon
Brannon

Reputation: 5414

How do I parent-child pairs efficiently?

Given a table structure like this with Tops, Mids, and Bots exposed in a DbContext:

Top { long TopId; IList<Mid> Mids; ... }
Mid { long MidId; long TopId; IList<Bot> Bots; ...}
Bot { long BotId, long MidId; DataTime Timestamp; string Data; ...}

How do I write an efficient query to give me the entire Mid table with the first Bot item for each Mid. By "first" I mean the one with the smallest Timestamp. I'm for something like this only better:

var results = _db.Mids.Select(m => new {Mid = m, Bot = 
       _db.Bots.Where(b => b.MidId = m.Id)
          .OrderBy(b => b.Timestamp).FirstOrDefault()});

Upvotes: 0

Views: 52

Answers (1)

nlips
nlips

Reputation: 1288

Simply write:

var results = _db.Mids.Select(m => new
{
    Mid = m,
    Bot = m.Bots.OrderBy(b => b.Timestamp).FirstOrDefault()
});

You can see the SQL query generated using results.ToString()

Upvotes: 1

Related Questions