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?

Why do you insist on using a linq query? you can create a stored procedure and call it by EF — kazem, Jun 20 '17 at 15:27
@Kisame Because this is going to be run in LinqPad as a scheduled job, so I need a standalone Linq query. Anyway, the thought of doing this as a stored procedure is far worse than any Linq I could imagine in my worst nightmares! However hard this query is in Linq, it would be orders of magnitude harder in SQL — Avrohom Yisroel, Jun 20 '17 at 15:46

score 2 · Accepted Answer · answered Jun 20 '17 at 15:29

2

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()
  });

answered Jun 20 '17 at 15:29

Tim Schmelter

450,073
74
686
939

Although all three answers are great, I'm accepting yours, as this is exactly the approach I came up with myself after posting! I also think it's the cleanest syntax, partly because I find the method syntax much easier to read than the fluent (_personal opinion_). – Avrohom Yisroel Jun 20 '17 at 15:48

ocuenca · Answer 2 · 2017-06-20T15:45:18.523

2

You could use query notation instead to use let clause:

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

At the end is going to do the same that @TimSchmelter answer, but for things like that you can use let. Another thing, I'm almost sure ToLongDateString() method is not supported in EF.

edited Jun 20 '17 at 15:45

answered Jun 20 '17 at 15:31

ocuenca

38,548
11
89
102

Thanks for the suggestion, I had forgotten about the `let` clause. I rarely use fluent syntax (personal preference), and had forgotten that this is one are where it has a significant benefit. However, when I tried your query in LinqPad, I got an error `An expression tree lambda may not contain a null propagating operator.` Any ideas? – Avrohom Yisroel Jun 20 '17 at 15:51
I didn't test it in LinqPad, maybe is the C# version that is using. null conditional operator is since C# 6.0. – ocuenca Jun 20 '17 at 15:55
1

LinqPad5 (which is what I'm using) supports C#7, so I don't think that's it. – Avrohom Yisroel Jun 20 '17 at 16:03
Yes, I was reading now that probably is the linq provider you are using:https://stackoverflow.com/questions/28880025/why-cant-i-use-the-null-propagation-operator-in-lambda-expressions – ocuenca Jun 20 '17 at 16:04
Thanks for the link, sounds like that's it – Avrohom Yisroel Jun 20 '17 at 16:29

Pastor Cortes · Answer 3 · 2017-06-20T15:50:04.730

I've tried this (If you wanted to develop a subQuery separately, because of SoC principle):

var innerQuery =  cxt.SupportTickets
                    .Where(artist => artist.coverId == SomeParameter)
                    .Select(artist => new {
                        artistId = artist.artistId,
                        artistCompleteName = artist.artistName,
                        artistMasterPiece = artist.CoverName
                    });

var tickets = cxt.SupportTickets
  .Where(
    t => innerQuery.Contains(t.coverId)
  )
  .Select( t => new {
    ID = t.ID,
    Customer = t.Customer.Name,
    var cover = t.System.CoverItems.FirstOrDefault()
    CoverLevel = cover?.Level.Name,
    Expiry = cover?.Expiry.ToLongDateString()
  });

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

3 Answers3