You have the 2 examples that I've been using to do my first group by in linq to llbl:

1 customer has many depts, and one dept has many billings.

dim serviceID as integer = billings.selecteditem.cells(0).text

dim bills = from b in metaData.Billing where b.net >0 and b.billingid = serviceID _ group b by b.dept.customer into custs select custs

for each cust in custs writetoscreen(cust.name) next

that is what I want to do, but I get 2 errors: one on the custs after into: definition of method custs is not accessible in this context.

and 2 on custs after in: custs is not declared

note: the b between group and by is sometimes in your examples and sometimes not. is it optional?

Thanks,

This should work:

Dim bills = From b in metaData.Billing where b.Net > 0 and b.Billingid = serviceID _
Group By b.Dept.Customer.Name Into custs = Group

For Each cust in bills
     Writetoscreen(cust.Name)
next

Remember that:

1. You should group on a field, not an entity.

2. VB.NET users shouldn't append the Into grouping clause with a Select statement if they want to refer to the whole Group. Doing so will wrap the nested query into a derived table, which makes it impossible to fetch it, as a nested query can only be present in the projection (ref).

The VB.NET group syntax is a bit weird, at least from C#'s POV. All examples in the manual are ran and tested in live code as I ran into a problem porting the C# code over to VB.NET and discovered VB.NET's compiler built a different expression tree with group by queries (so during writing this manual section I had to write new code for some group by handlers to make it work! ).

Actually, it's not VB.NET's compiler's fault, but the C# compiler's fault. The problem with group by and a following select statement is that the C# compiler often crams the projection specified in the select INTO the groupby method call expression in the expression tree. The VB.NET compiler never does that. This 'combining' of method calls is a bad thing, because now some parameters of the GroupBy() method call are sometimes the projection and sometimes ignorable. The check on that isn't documented though I think I now have a solid routine in the runtime lib for some time which takes care of this.

Back to your query and some background on the remark in the docs

If you group on something and specify an INTO statement AND select from that INTO statement, you create a nested query. It's a hierarchy: the group by fields results as parents, and the grouped entities as grouped blocks (It's a hierarchy of IGrouping(Of <group by key>, <entity type>))

The problem with the extra select is that it is unexpected, because the 'group by into' already is a hierarchy: 2 queries. The extra select therefore wraps that whole hierarchy again and selects effectively just everything the group by into already produced.

If you look at the example linked by David above, you'll see that the VB.NET version doesn't have a select keyword.

Hopefully this describes the background of why it doesn't work. The hierarchy fetch is a nifty feature of the linq provider over the native API (you can build the query there, but it's cumbersome).

Loving it

Now it runs to the loop start of your code above and give error:

System.ArgumentNullException was unhandled by user code Message="Value cannot be null. Parameter name: member" ParamName="member" Source="System.Core" StackTrace: at System.Linq.Expressions.Expression.MakeMemberAccess(Expression expression, MemberInfo member) at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallGroupBy( MethodCallExpression expressionToHandle) at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod( MethodCallExpression expressionToHandle) at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType( MethodCallExpression expressionToHandle, Type declaringType) at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression( MethodCallExpression expressionToHandle) at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.Execute() at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.System.Collections.Generic.IEnumerable<T>. GetEnumerator() at nwm.billdefmaintpage.reportServiceUsers_Click(Object sender, EventArgs e) in C:\Websites\nwm\billdefmaint.aspx.vb:line 396 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) InnerException:

I've tried to reproduce your error with the following query: (adapter/selfservicing doesn't matter)

    <Test()> _
      Public Sub GroupByOnMultiHopElement()
        Using adapter As New DataAccessAdapter()
            Dim metaData As New LinqMetaData(adapter)

            Dim q = From ca In metaData.CustomerAddress _
              Where ca.Address.PostalCode = "98011" _
              Group ca By ca.Address.StateProvince.CountryRegion.Name Into cas = Group

            For Each x In q
                Console.WriteLine(x)
            Next
        End Using
    End Sub

This gives a different error, it crashes in the projection engine that it can't cast the string to an anonymous type. Curious, I tried the same query in C#:

[Test]
public void MiscellaneousTests10()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from ca in metaData.CustomerAddress
                where ca.Address.PostalCode == "98011"
                group ca by ca.Address.StateProvince.CountryRegion.Name into g
                select g;

        foreach(var v in q)
        {
        }
    }
}

This also crashes, but with a different error, although it has the same cause.

The reason is this:. the select of the data as a grouped hierarchy relies on the fact that the data to group on (the 'key', in the query above the "Name" of countryregion) is related to the data being grouped in such a way that both sets are mergable.

In the query above, and also in your query, you first get a set of strings, namely the group keys. Then, the entities being grouped are fetched and the two sets are merged: all entities related to key K are placed below K, so using K as index will give the set of entities related to K.

In the query above, this merging isn't possible at the moment, as the entities fetched are in my example ContactAddresses, while the key values used for the grouping aren't in that set, they're in a related entity. So the relationship between a given keyvalue K (== a given CountryRegion.Name value) and a contactaddress entity isn't determinable in memory, so the merge can't be done.

This can only be solved if a 3rd query is ran, which ties the key values to the entities being fetched, similar to merging m:n related entities together: the prefetch path merge for M:N also executes a special query to tie parent with child using the intermediate entity.

In this case however, it's a little tricky to pull such a query from the group by data alone, and it's for the special case where the group by target is fetched in the projection. So your query will never be able to run properly, if you want to fetch the hierarchy in 1 go. That's currently not going to work.

Your specific error in your query: Your error was reproducable with this query though:

    <Test()> _
      Public Sub GroupByOnSingleKey()
        Using adapter As New DataAccessAdapter()
            Dim metaData As New LinqMetaData(adapter)

            Dim q = From c In metaData.Customer _
                    Where c.City.StartsWith("A") _
             Group By c.Country _
             Into Count() _
             Select Country, NumberOfCustomers = Count

            For Each v In q
                Console.WriteLine(v)
            Next
        End Using
    End Sub

There's still something not working in the VB.NET groupby stuff. I also couldn't get the first vb.net query with a Count() instead of the full Into to work... Looking into it.

I've fixed the error you got (which will still make the query not runnable due to the unmergable sets see above), currently working on the count issue which vanishes in this query:

    <Test()> _
      Public Sub GroupByOnMultiHopElement()
        Using adapter As New DataAccessAdapter()
            Dim metaData As New LinqMetaData(adapter)

            Dim q = From ca In metaData.CustomerAddress _
              Where ca.Address.PostalCode = "98011" _
              Group ca By ca.Address.StateProvince.CountryRegion.Name Into Count() _
              Select Name, Amount = Count

            For Each x In q
                Console.WriteLine(x)
            Next
        End Using
    End Sub

fails because 'Count' aggregate isn't in the group by query...

The grouping on a related entity's fields in vb.net is not really possible at the moment, as the set which is grouped isn't containing the field in the projection (key) and vb.net's groupby method call expression (the one produced by the compiler) has the projection inside itself, so an extra 'Select' expression has to be created, and the source (the set to project from) has to be tied to the projection, however, the source is a set of entities, not a set with the related field as well.

(I previously said that the C# compiler crammed the projection into the groupby, this isn't the case, the vb.net compiler does that in this case)

There's another odd thing.

    <Test()> _
     Public Sub GroupByOnSingleHopElement()
        Using adapter As New DataAccessAdapter()
            Dim metaData As New LinqMetaData(adapter)

            Dim q = From ca In metaData.CustomerAddress _
              Group ca By ca.AddressId Into Amount = Count() _
              Select AddressId, Amount

            For Each x In q
            Next
        End Using
    End Sub

fails

but

    <Test()> _
     Public Sub GroupByOnSingleHopElement()
        Using adapter As New DataAccessAdapter()
            Dim metaData As New LinqMetaData(adapter)

            Dim q = From ca In metaData.CustomerAddress _
              Group By ca.AddressId Into Amount = Count() _
              Select AddressId, Amount

            For Each x In q
            Next
        End Using
    End Sub

succeeds.... The second one has 4 parameters passed to the groupby, the first one 3... Long live documentation.... if it's there! Adding another check solves this too.

So.... loooong story short: - grouping on fields in a related entity isn't supported in the form as you use it - fixed 2 issues: group x by now works and the member expression error also is solved.

I've attached a new build of the linq support classes which should at least give you better grouping support. But again, your query doesn't work in the end, as it groups on related fields.

I sort of thought that would be magic if it did work since it would reduce 6 lines of code with getmulti to one simple line