CODEWITHSUNDEEP
c#vb.netlinq
Tom S
Tom S

Reputation: 227

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.

Upvotes: 0

Views: 69

Answers (2)

Ross Brasseaux
Ross Brasseaux

Reputation: 4150

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).

Upvotes: 3

Dave Doknjas
Dave Doknjas

Reputation: 6542

You're treating the two object initializers differently - a consistent conversion is:

Dim CodesnotListed() As String = { "F97", "F98", "F99" }
Model1 = From x In db.CIPCodes
    Where ((x.ClusterCode.StartsWith(filter) OrElse x.ClusterName.StartsWith(filter)) AndAlso Not CodesnotListed.Contains(x.ClusterCode))
    Order By x.ClusterCode
    Group x By GroupKey = New AutoCompleteResults With {
        .ACCode = x.ClusterCode,
        .ACName = x.ClusterName
    } Into Alpha = Group
    Select New AutoCompleteResults With {
        .ACCode = GroupKey.ACCode,
        .ACName = GroupKey.ACCode & " - " & GroupKey.ACName
    }

There may be better ways of constructing the query, but this is consistent with the original C#.

Upvotes: 0

Related Questions