VB.Net Linq To SQL Sub Select

Question

Hello, I have the following query which groups some records and then filters where the count of the grouped records is 1.

I'd like to take the returned result and perform another query to retrieve the entire record from the JobcodesWorkingRollup table where the ParentNode column equals the result of this query:

        Dim query = From r In context.GetTable(Of JobcodesWorkingRollup)() _
        Group r By r.ParentNode Into g = Group _
        Where g.Count = 1 _
        Select New With {.cnt = g.Count, .nm = g.FirstOrDefault.ParentNode}

Thanks!

Answer 1

I think I have it... is there a better way?

        Dim query = From r In context.GetTable(Of JobcodesWorkingRollup)() _
        Group r By r.ParentNode Into g = Group _
        Where g.Count = 1 _
        From rr In context.GetTable(Of JobcodesWorkingRollup)() _
        Where rr.ParentNode = g.FirstOrDefault.ParentNode _
        Select New With {.record = rr}