Linq error: Not a member of the group key (when GroupBy many nonkey columns)

Question

I need to group by many columns that aren't part of the table's index. The key of the table is the property "id".

I've read and mostly understood the various approaches on the MSDN blogs, and SO, extension attributes and so on, but this approach is the only one I can use that compiled.

var query = from f in this.dbContext.Tokens
            group f by new { f.PI, f.TI, f.UIDP, f.AttributesJSON, f.IssuerParameters, f.IsDeviceProtected , f.FriendlyName}
                        into myGroup
                        where myGroup.Count() > 0
                        select new
                        {
                            myGroup.Key.UIDP,
                            myGroup.Key.TI,
                            myGroup.Key.PI,
                            myGroup.Key.AttributesJSON,
                            myGroup.Key.IssuerParameters,
                            myGroup.Key.FriendlyName,
                            Count = myGroup.Count(),
                            Subgroups = from privateTokens in myGroup
                                        select new
                                        {
                                           // AttributesJSON = privateTokens.AttributesJSON,
                                           // FriendlyName = privateTokens.FriendlyName,
                                            Id = privateTokens.Id,
                                          //  IsDeviceProtected = privateTokens.IsDeviceProtected,
                                           // IssuerParameters = privateTokens.IssuerParameters,
                                           // PI = privateTokens.PI,
                                           // PrivateKey = privateTokens.PrivateKey,
                                           // TI = privateTokens.TI,
                                            TokenData = privateTokens.TokenData,
                                            UIDP = privateTokens.UIDP,
                                        }
                        };

The ORM I"m using is Telerik Openaccess, however I think this issue is a SQL one.

The error is below.

Not a member of the group key.
Parameter name: memberInfo
Actual value was Int32 Id.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.ArgumentOutOfRangeException: Not a member of the group key.
Parameter name: memberInfo
Actual value was Int32 Id.

Source Error: 


Line 86:                             };
Line 87: 
Line 88:             string asdfdsfd = query.ToString();
Line 89: 
Line 90:             // todo, limit to a per user

Source File: \\psf\home\Documents\Visual Studio 2013\Projects\BitClearTokens\BitclearClient\Controllers\TokenController.cs    Line: 88 

Stack Trace: 


[ArgumentOutOfRangeException: Not a member of the group key.
Parameter name: memberInfo
Actual value was Int32 Id.]
   Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQueryImpl(Type resultType, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid) +2571
   Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid) +97

[InvalidOperationException: An exception occured during the execution of 'Extent<BitclearProverModel.Tokens>().GroupBy(f => new <>f__AnonymousType3`7(PI = f.PI, TI = f.TI, UIDP = f.UIDP, AttributesJSON = f.AttributesJSON, IssuerParameters = f.IssuerParameters, IsDeviceProtected = f.IsDeviceProtected, FriendlyName = f.FriendlyName)).Where(myGroup => (myGroup.Count() > 0)).Select(myGroup => new <>f__AnonymousType5`8(UIDP = myGroup.Key.UIDP, TI = myGroup.Key.TI, PI = myGroup.Key.PI, AttributesJSON = myGroup.Key.AttributesJSON, IssuerParameters = myGroup.Key.IssuerParameters, FriendlyName = myGroup.Key.FriendlyName, Count = myGroup.Count(), Subgroups = myGroup.Select(privateTokens => new <>f__AnonymousType4`3(Id = privateTokens.Id, TokenData = privateTokens.TokenData, UIDP = privateTokens.UIDP))))'. Failure: Not a member of the group key.
Parameter name: memberInfo
Actual value was Int32 Id.
See InnerException for more details.
Complete Expression:
.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.Where(
        .Call System.Linq.Queryable.GroupBy(
            .Constant<Telerik.OpenAccess.Query.ExtentQueryImpl`1[BitclearProverModel.Tokens]>(Extent<BitclearProverModel.Tokens>()),
            '(.Lambda #Lambda1<System.Func`2[BitclearProverModel.Tokens,<>f__AnonymousType3`7[System.String,System.String,System.String,System.String,System.String,System.Boolean,System.String]]>))
        ,
        '(.Lambda #Lambda2<System.Func`2[System.Linq.IGrouping`2[<>f__AnonymousType3`7[System.String,System.String,System.String,System.String,System.String,System.Boolean,System.String],BitclearProverModel.Tokens],System.Boolean]>))
    ,
    '(.Lambda #Lambda3<System.Func`2[System.Linq.IGrouping`2[<>f__AnonymousType3`7[System.String,System.String,System.String,System.String,System.String,System.Boolean,System.String],BitclearProverModel.Tokens],<>f__AnonymousType5`8[System.String,System.String,System.String,System.String,System.String,System.String,System.Int32,System.Collections.Generic.IEnumerable`1[<>f__AnonymousType4`3[System.Int32,System.String,System.String]]]]>))

.Lambda #Lambda1<System.Func`2[BitclearProverModel.Tokens,<>f__AnonymousType3`7[System.String,System.String,System.String,System.String,System.String,System.Boolean,System.String]]>(BitclearProverModel.Tokens $f)
{
    .New <>f__AnonymousType3`7[System.String,System.String,System.String,System.String,System.String,System.Boolean,System.String](
        $f.PI,
        $f.TI,
        $f.UIDP,
        $f.AttributesJSON,
        $f.IssuerParameters,
        $f.IsDeviceProtected,
        $f.FriendlyName)
}

.Lambda #Lambda2<System.Func`2[System.Linq.IGrouping`2[<>f__AnonymousType3`7[System.String,System.String,System.String,System.String,System.String,System.Boolean,System.String],BitclearProverModel.Tokens],System.Boolean]>(System.Linq.IGrouping`2[<>f__AnonymousType3`7[System.String,System.String,System.String,System.String,System.String,System.Boolean,System.String],BitclearProverModel.Tokens] $myGroup)
{
    .Call System.Linq.Enumerable.Count($myGroup) > 0
}

.Lambda #Lambda3<System.Func`2[System.Linq.IGrouping`2[<>f__AnonymousType3`7[System.String,System.String,System.String,System.String,System.String,System.Boolean,System.String],BitclearProverModel.Tokens],<>f__AnonymousType5`8[System.String,System.String,System.String,System.String,System.String,System.String,System.Int32,System.Collections.Generic.IEnumerable`1[<>f__AnonymousType4`3[System.Int32,System.String,System.String]]]]>(System.Linq.IGrouping`2[<>f__AnonymousType3`7[System.String,System.String,System.String,System.String,System.String,System.Boolean,System.String],BitclearProverModel.Tokens] $myGroup)
{
    .New <>f__AnonymousType5`8[System.String,System.String,System.String,System.String,System.String,System.String,System.Int32,System.Collections.Generic.IEnumerable`1[<>f__AnonymousType4`3[System.Int32,System.String,System.String]]](
        ($myGroup.Key).UIDP,
        ($myGroup.Key).TI,
        ($myGroup.Key).PI,
        ($myGroup.Key).AttributesJSON,
        ($myGroup.Key).IssuerParameters,
        ($myGroup.Key).FriendlyName,
        .Call System.Linq.Enumerable.Count($myGroup),
        .Call System.Linq.Enumerable.Select(
            $myGroup,
            .Lambda #Lambda4<System.Func`2[BitclearProverModel.Tokens,<>f__AnonymousType4`3[System.Int32,System.String,System.String]]>)
    )
}

.Lambda #Lambda4<System.Func`2[BitclearProverModel.Tokens,<>f__AnonymousType4`3[System.Int32,System.String,System.String]]>(BitclearProverModel.Tokens $privateTokens)
{
    .New <>f__AnonymousType4`3[System.Int32,System.String,System.String](
        $privateTokens.Id,
        $privateTokens.TokenData,
        $privateTokens.UIDP)
}
]
   Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid) +531
   Telerik.OpenAccess.Query.ExpressionCompiler.GetBackendQuery(Type resultType) +125
   Telerik.OpenAccess.Query.ExpressionExecution.PerformQueryCompilation(Expression expr, ChainedContext ctx, Boolean tryGetObjectById, Type resultType, String dump, QueryOptions options) +230
   Telerik.OpenAccess.Query.Piece`1.ToString() +415
   BitclearClient.Controllers.<Index>d__0.MoveNext() in \\psf\home\Documents\Visual Studio 2013\Projects\BitClearTokens\BitclearClient\Controllers\TokenController.cs:88
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +93
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +52
   System.Runtime.CompilerServices.TaskAwaiter.GetResult() +21
   lambda_method(Closure , Task ) +64
   System.Threading.Tasks.TaskHelpersExtensions.ThrowIfFaulted(Task task) +31
   System.Web.Mvc.Async.TaskAsyncActionDescriptor.EndExecute(IAsyncResult asyncResult) +67
   System.Web.Mvc.Async.<>c__DisplayClass3f.<BeginInvokeAsynchronousActionMethod>b__3e(IAsyncResult asyncResult) +16
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +48
   System.Web.Mvc.Async.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33() +57
   System.Web.Mvc.Async.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() +223
   System.Web.Mvc.Async.<>c__DisplayClass37.<BeginInvokeActionMethodWithFilters>b__36(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +48
   System.Web.Mvc.Async.<>c__DisplayClass2a.<BeginInvokeAction>b__20() +24
   System.Web.Mvc.Async.<>c__DisplayClass25.<BeginInvokeAction>b__22(IAsyncResult asyncResult) +102
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +43
   System.Web.Mvc.<>c__DisplayClass1d.<BeginExecuteCore>b__18(IAsyncResult asyncResult) +14
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +57
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +47
   System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
   System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__3(IAsyncResult asyncResult) +25
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +47
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9688704
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

Answer 1

Starting from Q3 2013 SP1 release of OpenAccess (which will be made publicly available in the next 10 days) grouping by boolean is fixed and you should be able to use your query with some modifications outlined below.

Unfortunately the second projection that should build the Subgroups is not supported by OpenAccess. You can overcome this limitation by processing it on the client side by invoking the query using .ToList() for the grouping query. Performance-wise it will be a bit slower, but the penalty should not be big.

Modified query:

var query = from f in this.dbContext.Tokens
        group f by new { f.PI, f.TI, f.UIDP, f.AttributesJSON, f.IssuerParameters, f.IsDeviceProtected , f.FriendlyName}
                    into myGroup
                    where myGroup.Count() > 0
                    select new
                    {
                        myGroup.Key.UIDP,
                        myGroup.Key.TI,
                        myGroup.Key.PI,
                        myGroup.Key.AttributesJSON,
                        myGroup.Key.IssuerParameters,
                        myGroup.Key.FriendlyName,
                        Count = myGroup.Count(),
                        Subgroups = myGroup
                    };
var groupedResult = query.ToList();
var resultWithSubGroups = from gr in groupedResult
                          select new
                          {
                              gr.UIDP,
                              gr.TI,
                              gr.PI,
                              gr.AttributesJSON,
                              gr.IssuerParameters,
                              gr.FriendlyName,
                              gr.Count,
                              Subgroups = from privateTokens in gr
                                          select new 
                                          {
                                              // AttributesJSON = privateTokens.AttributesJSON,
                                              // FriendlyName = privateTokens.FriendlyName,
                                              Id = privateTokens.Id,
                                              //  IsDeviceProtected = privateTokens.IsDeviceProtected,
                                              // IssuerParameters = privateTokens.IssuerParameters,
                                              // PI = privateTokens.PI,
                                              // PrivateKey = privateTokens.PrivateKey,
                                              // TI = privateTokens.TI,
                                              TokenData = privateTokens.TokenData,
                                              UIDP = privateTokens.UIDP,
                                          }
                           };

Answer 2

From trial and error I discovered that the "subgroups" are what was causing the problem. By removing them, I discovered a second issue.

Telerik Open Access didn't want to Group By a bool field, which is exactly what "is device protected" is.

  // this is the removed line
   Subgroups = from privateTokens in myGroup
                                    select new
                                    {
                                       // AttributesJSON = privateTokens.AttributesJSON,
                                       // FriendlyName = privateTokens.FriendlyName,
                                        Id = privateTokens.Id,
                                      //  IsDeviceProtected = privateTokens.IsDeviceProtected,
                                       // IssuerParameters = privateTokens.IssuerParameters,
                                       // PI = privateTokens.PI,
                                       // PrivateKey = privateTokens.PrivateKey,
                                       // TI = privateTokens.TI,
                                        TokenData = privateTokens.TokenData,
                                        UIDP = privateTokens.UIDP,
                                    }