Convert this SQL statement to LINQ-to-SQL

Question

I have struggled converting this SQL statement to LINQ to SQL VB.Net 9.0. I have used Linqer but no success. Any help would be appreciated

select t.TeeId,
   t.DescriptionId,
   t.[Description],
   t.Rating,
   t.Slope,
   case when d.TotalHoles <> h.TotalHoles then 0
   else 1 end [Status]
from dbo.CourseDescription d
inner join dbo.CourseTees t
on t.DescriptionId = d.DescriptionId
inner join (select TeeId, count(*) as TotalHoles
           from dbo.CourseHoles
          group by TeeId) h
on h.TeeId = t.TeeId
where d.CourseId = 1

Answer 1

Here's a go at it. I don't do any programming in VB, but I've tried to get the syntax as correct as possible. For simplicity I split it into two queries, but with LINQ to SQL the first doesn't actually result in a query of the DB. It's simply combined with the second. Neither are executed until you enumerate the second query. Add line continuations if and when needed. I don't know if there is a translation to SQL for the ternary operator (there is in C#). If not, material the part before the select, getting both d.TotalHoles and h.TotalHoles, then use LINQ to objects to enumerate through those and construct the status.

dim holes = from h in db.TotalHoles
            groupby h.TeeId into g
            select TeeId = Key, TotalHoles = g.Count()

dim courses = from d in db.CourseDescription
              where d.CourseId = 1
              join t in CourseTees on d.DescriptionId equals t.DescriptionId
              join h in holes on h.TeeId equals t.TeeId
              select t.TeeId,
                     t.DescriptionId,
                     t.Description,
                     t.Rating, t.Slope,
                     Status  = If(d.TotalHoles = h.TotalHoles, 1, 0)