Linq to SQL - Group By Table

Question

This doesn't work:

Dim Qry = From RE In DB.Res_Estab Where _
(RE.Estab_Code = Estab_Code) Group By RE.Research Into Group Select Research

Because Research is a table, not a value. It renders the error "A group by expression can only contain non-constant scalars that are comparable by the server."

So I'm splitting in two queries:

Dim Qry = From RE In DB.Res_Estab Where _
(RE.Estab_Code = Estab_Code) Group By RE.Res_Code Into Group Select Res_Code

Dim Qry2 = From R In DB.Research Where Qry.Contains(R.Res_Code) Order By R.Descr

Which works, but seems redundant. Is there a way of doing this in a single query?

Answer 1

You might find the answer here -

From RE In DB.Res_Estab _
Group By RE.Research 
Into Group
Where RE.Estab_Code = Estab_Code
Select Research