How do I write a Linq query that needs a subquery?

Question

I need to extract some information about support tickets from a database. Each ticket is associated with a medical imaging system, and each system may or may not have service cover associated with it. If it does, there may be multiple service cover entries, but only one that interests us.

I know this is not valid Linq, but what I would really like to do is the following...

var tickets = cxt.SupportTickets
  .Select( t => new {
    ID = t.ID,
    Customer = t.Customer.Name,
    var cover = t.System.CoverItems.FirstOrDefault(ci => // some query)
    CoverLevel = cover?.Level.Name,
    Expiry = cover?.Expiry.ToLongDateString()
  });

Is there any way to do this? I know that I could repeat the t.CoverItems.FirstOrDefault(...) bit for every bit of data I want from the cover, but apart from the absolutely awful code mess this would produce, it would be very inefficient, as it would need to do the same subquery multiple times for every ticket.

I thought about breaking it all up into a foreach loop, but then I couldn't see how to create the tickets collection. I can't create an empty collection and then add objects to it, as they are anonymous types, and I wouldn't like to think about how you would specify the generic type!

Anyone any ideas?

Answer 1

You could improve readability:

var tickets = cxt.SupportTickets
  .Select(t => new { 
       Ticket = t, 
       CoverItem = t.System.CoverItems.FirstOrDefault(ci => // some query)
    })
  .Select(x => new {
    ID = x.Ticket.ID,
    Customer = x.Ticket.Customer.Name,
    CoverLevel = x.CoverItem?.Level.Name,
    Expiry = x.CoverItem?.Expiry.ToLongDateString()
  });