Group By and CASE query in LinQ

47 views Asked by At

How can I write this query in LINQ:

   SELECT Name,
          READY =     max(CASE TrDet.tracktypeid WHEN 7 THEN 1 END),
          AVAILABLE = max(CASE TrDet.tracktypeid WHEN 2 THEN 1 WHEN 5 THEN 1 END)
   FROM  PENDINGAPPROVAL Apr
   JOIN  TRACKS Tr           ON Apr.TrackId = Tr.Id
   JOIN  TRACK_DETAIL TrDet  ON Tr.Id = TrDet.TrackId
   GROUP BY Tr.Name

Thanks

1

There are 1 answers

1
Robert McKee On

This doesn't answer the question you asked, but this gets the answer you want:

var results=context.PENDINGAPPROVAL.Select(p=>new {
  Name=p.Name,
  READY=p.Tracks.Any(t=>t.TRACK_DETAILS.Any(td=>td.tracktypeid==7))
  AVAILABLE=p.Tracks.Any(t=>t.TRACK_DETAILS.Any(td=>td.tracktype==2 || td.tracktype==5))
});