Tom S Tom S - 6 months ago 10
Vb.net Question

C# to VB group by issue

I have the following C3 query that I need to convert into a vb query but I can't get the group by to work correctly. AutoComplete is a class with (2) fields ACCode and ACName.

string[] CodesnotListed = { "F97", "F98", "F99" };
Model1 = from x in db.CIPCodes
where ((x.ClusterCode.StartsWith(filter) || x.ClusterName.StartsWith(filter)) &&
!CodesnotListed.Contains(x.ClusterCode))
orderby x.ClusterCode
group x by new AutoCompleteResults { ACCode = x.ClusterCode, ACName = x.ClusterName } into Alpha
select new AutoCompleteResults
{
ACCode = Alpha.Key.ACCode,
ACName = Alpha.Key.ACCode + " - " + Alpha.Key.ACName
};


This is what I have written but it get's hung up on the Group by indicating that it needs a Type and then if I indicate the class then it tells me Range can be inferred.

Dim CodesnotListed As String() = {"F97", "F98", "F99"}
Dim x = From y In _db.CIPCodes _
Where (y.ClusterCode.StartsWith(filter) OrElse y.ClusterName.StartsWith(filter)) _
AndAlso Not CodesnotListed.Contains(y.ClusterCode) AndAlso y.cipactive = True _
Order By y.ClusterCode
Group y By New (y.ClusterCode, y.ClusterName) Into g
Select New AutoCompleteResults With {
.ACCode = y.ClusterCode,
.ACName = y.ClusterCode + " - " + y.ClusterName}
Return x.ToList


Thanks.

Answer

The correct group by syntax in VB is a little elusive. Here is the working version of your code:

    Dim CodesnotListed As String() = {"F97", "F98", "F99"}
    Dim x = (From y In _db.CIPCodes
             Where (y.ClusterCode.StartsWith("") OrElse y.ClusterName.StartsWith("")) _
                AndAlso Not CodesnotListed.Contains(y.ClusterCode) AndAlso y.cipactive
             Order By y.ClusterCode
             Group y By cc = y.ClusterCode, cn = y.ClusterName Into g = Group
             Select New AutoCompleteResults With {.ACCode = cc, .ACName = cc & " - " & cn}).ToList()

EDIT

As Dave Doknjas pointed out, there were a few errors. So I rewrote the code and put it into a unit test project and it passed. The new code should do everything you need. I also modified it a bit to just return the resulting set of data as a list instead of storing it in your temporary variable x (which I figured you could easily switch back if you needed the x variable for something).