GroupBy with subselect expression

[alpergunay]

I know that my question can be related with entity framework issue but when i want to use remote grouping to group records according to sub select column i am getting "Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause" error. As far as I see from generated query, remote grouping is trying to find number of records for each group. Below you can see my linq query and generated sql. Do you have any idea to solve this problem ?

Note : On data grid, I am trying to group with using "WorkOrderCode" column.

public object GetListRemote(GetListOutboundOrderRequestModel model, DataSourceLoadOptions loadOptions)
        {
            var result = (from ord in _dbContext.Orders
                          join acc in _dbContext.Accounts
                          on ord.AccountId equals acc.Id into accl
                          from acc in accl.DefaultIfEmpty()
                          join ort in _dbContext.OrderTypes
                          on ord.OrderTypeId equals ort.Id into ortl
                          from ort in ortl.DefaultIfEmpty()
                          join orst in _dbContext.OrderStatus
                          on ord.OrderStatusId equals orst.Id into orstl
                          from orst in orstl.DefaultIfEmpty()
                          join acarrier in _dbContext.Accounts
                          on ord.CarrierAccountId equals acarrier.Id into acarrierl
                          from acarrier in acarrierl.DefaultIfEmpty()
                          join camp in _dbContext.Campaigns
                          on ord.CampaignId equals camp.Id into campl
                          from camp in campl.DefaultIfEmpty()
                          join timeSlots in _dbContext.Slots
                          on ord.PlannedSlotId equals timeSlots.Id into timeSlotsl
                          from timeSlots in timeSlotsl.DefaultIfEmpty()
                          join cReason in _dbContext.CancellationReasons
                          on ord.CancellationReasonId equals cReason.Id into cReasonl
                          from cReason in cReasonl.DefaultIfEmpty()
                          join opst in _dbContext.OperationStatuses
                          on ord.OperationStatusId equals opst.Id into opstl
                          from opst in opstl.DefaultIfEmpty()
                          join oods in _dbContext.DeliveryStatuses
                          on ord.DeliveryStatusId equals oods.Id into oodsl
                          from oods in oodsl.DefaultIfEmpty()
                          join spm in _dbContext.ShipmentPaymentMethods
                          on ord.ShipmentPaymentMethodId equals spm.Id into spml
                          from spm in spml.DefaultIfEmpty()
                          join parentOrder in _dbContext.Orders
                          on ord.ParentOrderId equals parentOrder.Id into parentOrderl
                          from parentOrder in parentOrderl.DefaultIfEmpty()

                          join mrktplc in _dbContext.Marketplaces
                          on ord.MarketplaceId equals mrktplc.Id into mrktplcl
                          from mrktplc in mrktplcl.DefaultIfEmpty()

                          join canclreq in _dbContext.OrderCancelRequestTypes
                          on ord.CancelRequestTypeId equals canclreq.Id into canclreql
                          from canclreq in canclreql.DefaultIfEmpty()


                          from orderDocumentInv in _dbContext.OrderDocuments.Where(o => ord.Id == o.OrderId && o.DocumentTypeId == (long)DocumentTypeEnum.INV).Take(1).DefaultIfEmpty()

                          from orderDocumentDvn in _dbContext.OrderDocuments.Where(o => ord.Id == o.OrderId && o.DocumentTypeId == (long)DocumentTypeEnum.DVN).Take(1).DefaultIfEmpty()

                          join pt in _dbContext.PackTypes
                          on ord.DefaultPackTypeId equals pt.Id into ptl
                          from pt in ptl.DefaultIfEmpty()

                          join r in _dbContext.RecordStatus
                          on ord.RecordStatusId equals r.Id

                          join strge in _dbContext.StorageLocations on ord.StorageLocationId equals strge.Id into stg
                          from storage in stg.DefaultIfEmpty()

                          where (model.RecordStatusId == null || ord.RecordStatusId == model.RecordStatusId)
                          && (model.OrderTypeId == null || ord.OrderTypeId == model.OrderTypeId)
                          && ord.SiteId == _user.SiteId
                          orderby ord.ChangeTime descending
                          select new
                          {
                              Id = ord.Id,
                              CustomerAccountDescription = acc.Code,
                              CustomerAccountId = ord.AccountId,
                              OrderNumber = ord.Number,
                              RevisionNumber = ord.RevisionNo,
                              OrderTypeDescription = ort.Description,
                              OrderTime = ord.Time,
                              OrderEntryTime = ord.CreateTime,
                              OrderStatusId = ord.OrderStatusId,
                              OrderTypeId = ord.OrderTypeId,
                              OrderStatusDescription = orst.Description,
                              OrderCancelReason = canclreq.Description,
                              PackTypeId = (long?)ord.DefaultPackTypeId,
                              PackTypeDescription = pt.Description,
                              OrderOperationDescription = opst.Description,
                              DeliveryStatusDescription = oods.Description,
                              ParentOrderNo = parentOrder.Number,
                              CarrierAccountDescription = acarrier.Description,
                              Hold = ord.OnHold,
                              CustomerOrderNumber = ord.CustomerOrderNumber,
                              PlannedDeliveryTime = ord.PlannedDeliveryTime,
                              PlannedShipmentTime = ord.PlannedShipmentTime,
                              CampaignDescription = camp.Description,
                              CustomerReferenceNumber = ord.CustomerReferenceNumber,
                              DeliveryTypeDescription = ord.DeliveryOrderCode,
                              IfSameDayDelivery = ord.IfSameDayDelivery,
                              PlannedSlotDescription = timeSlots.Description,
                              OrderNumberToBeCancelled = ord.CancelOrderNumber,
                              CancelReasonDescription = cReason.Description,
                              WaybillPrintCount = ord.WaybillPrintCount,
                              InvoicePrintCount = ord.InvoicePrintCount,
                              ApprovePacking = ord.ApprovePacking,
                              IfCargoCompanyCalculated = ord.IfCargoCompanyCalculated,
                              IfSalesDataCreated = ord.IfSalesDataCreated,
                              Desi = ord.Desi,
                              IfGift = ord.IfGift,
                              GiftNote = ord.GiftNote,
                              ShipmentPaymentMethodCode = spm.Code,
                              ShipmentPaymentMethod = spm.Description,
                              WorkOrderCode = (from subTa in _dbContext.WhopTasks
                                               join subOrde in _dbContext.OrderDetails on subTa.OrderDetailId equals subOrde.Id
                                               join subWo in _dbContext.WorkOrders on subTa.WorkOrderId equals subWo.Id into subTa_subWo
                                               from subWo in subTa_subWo.DefaultIfEmpty()
                                               where subOrde.OrderId == ord.Id
                                                  && subTa.TaskStatusId != (long)WhopTaskStatusEnum.CANCELED
                                                  && subTa.RecordStatusId == (long)RecordStatusEnum.ACTIVE
                                               select subWo.Code).FirstOrDefault(),
                              InvoiceNumber = orderDocumentInv.DocumentNumber,
                              DeliveryNoteNo = orderDocumentDvn.DocumentNumber,
                              NumberOfSku = ord.numberOfSku,
                              MarketplaceInfo = mrktplc.Description,
                              OrderQuantity = ord.OrderQuantity,
                              CancelRequestDescription = ord.CancelRequestTypeId == (long)OrderCancelRequestTypeEnum.ORDERCANCEL ? "ORDERCANCEL" : "",
                              StoregeLocationDescription = storage.Description,
                              ShipmentTime = (from orde in _dbContext.OrderDetails
                                              join shde in _dbContext.ShipmentDetails on orde.Id equals shde.OrderDetailId
                                              join sh in _dbContext.Shipments on shde.ShipmentId equals sh.Id
                                              where orde.OrderId == ord.Id
                                              select sh.ShipmentTime).FirstOrDefault()
                          });
            return DataSourceLoader.Load(result, loadOptions);
        }

Generated SQL

SELECT COUNT(*) AS [I0], (
    SELECT TOP(1) [subWo2].[CODE]
    FROM [WHOP_TASK] AS [subTa2]
    INNER JOIN [OBOP_ORDER_DETAIL] AS [subOrde2] ON [subTa2].[ORDER_DETAIL_ID] = [subOrde2].[ID]
    LEFT JOIN [WHOP_WORK_ORDER] AS [subWo2] ON [subTa2].[WORK_ORDER_ID] = [subWo2].[ID]
    WHERE (([subOrde2].[ORDER_ID] = [ord].[ID]) AND ([subTa2].[TASK_STATUS_ID] <> CAST(6 AS bigint))) AND ([subTa2].[RECORD_STATUS_ID] = CAST(1 AS bigint))
) AS [I1]
FROM [OBOP_ORDER] AS [ord]
LEFT JOIN [PLAT_ACCOUNT] AS [acc] ON [ord].[ACCOUNT_ID] = [acc].[ID]
LEFT JOIN [PLAT_ORDER_TYPE] AS [ort] ON [ord].[ORDER_TYPE_ID] = [ort].[ID]
LEFT JOIN [PLAT_ORDER_STATUS] AS [orst] ON [ord].[ORDER_STATUS_ID] = [orst].[ID]
LEFT JOIN [PLAT_ACCOUNT] AS [acarrier] ON [ord].[CARRIER_ACCOUNT_ID] = [acarrier].[ID]
LEFT JOIN [PLAT_CAMPAIGN] AS [camp] ON [ord].[CAMPAIGN_ID] = [camp].[ID]
LEFT JOIN [PLAT_SLOT] AS [timeSlots] ON [ord].[PLANNED_SLOT_ID] = [timeSlots].[ID]
LEFT JOIN [PLAT_CANCELLATION_REASON] AS [cReason] ON [ord].[CANCELLATION_REASON_ID] = [cReason].[ID]
LEFT JOIN [PLAT_OPERATION_STATUS] AS [opst] ON [ord].[OPERATION_STATUS_ID] = [opst].[ID]
LEFT JOIN [OBOP_DELIVERY_STATUS] AS [oods] ON [ord].[DELIVERY_STATUS_ID] = [oods].[ID]
LEFT JOIN [PLAT_SHIPMENT_PAYMENT_METHOD] AS [spm] ON [ord].[SHIPMENT_PAYMENT_METHOD_ID] = [spm].[ID]
LEFT JOIN [OBOP_ORDER] AS [parentOrder] ON [ord].[PARENT_ORDER_ID] = [parentOrder].[ID]
LEFT JOIN [PLAT_MARKETPLACE] AS [mrktplc] ON [ord].[MARKETPLACE_ID] = [mrktplc].[ID]
LEFT JOIN [OBOP_ORDER_CANCEL_REQUEST_TYPE] AS [canclreq] ON [ord].[CANCEL_REQUEST_TYPE_ID] = [canclreq].[ID]
CROSS APPLY (
    SELECT [t].*
    FROM (
        SELECT NULL AS [empty]
    ) AS [empty]
    LEFT JOIN (
        SELECT TOP(1) [o].*
        FROM [OBOP_ORDER_DOCUMENT] AS [o]
        WHERE ([ord].[ID] = [o].[ORDER_ID]) AND ([o].[DOCUMENT_TYPE_ID] = CAST(5 AS bigint))
    ) AS [t] ON 1 = 1
) AS [t0]
CROSS APPLY (
    SELECT [t1].*
    FROM (
        SELECT NULL AS [empty]
    ) AS [empty0]
    LEFT JOIN (
        SELECT TOP(1) [o0].*
        FROM [OBOP_ORDER_DOCUMENT] AS [o0]
        WHERE ([ord].[ID] = [o0].[ORDER_ID]) AND ([o0].[DOCUMENT_TYPE_ID] = CAST(2 AS bigint))
    ) AS [t1] ON 1 = 1
) AS [t2]
LEFT JOIN [PLAT_PACK_TYPE] AS [pt] ON [ord].[DEFAULT_PACK_TYPE_ID] = [pt].[ID]
INNER JOIN [PLAT_RECORD_STATUS] AS [r] ON [ord].[RECORD_STATUS_ID] = [r].[ID]
LEFT JOIN [PLAT_STORAGE_LOCATION] AS [strge] ON [ord].[STORAGE_LOCATION_ID] = [strge].[ID]
WHERE ([ord].[RECORD_STATUS_ID] = @__model_RecordStatusId_0) AND ([ord].[SITE_ID] = @___user_SiteId_1)
GROUP BY (
    SELECT TOP(1) [subWo2].[CODE]
    FROM [WHOP_TASK] AS [subTa2]
    INNER JOIN [OBOP_ORDER_DETAIL] AS [subOrde2] ON [subTa2].[ORDER_DETAIL_ID] = [subOrde2].[ID]
    LEFT JOIN [WHOP_WORK_ORDER] AS [subWo2] ON [subTa2].[WORK_ORDER_ID] = [subWo2].[ID]
    WHERE (([subOrde2].[ORDER_ID] = [ord].[ID]) AND ([subTa2].[TASK_STATUS_ID] <> CAST(6 AS bigint))) AND ([subTa2].[RECORD_STATUS_ID] = CAST(1 AS bigint))
)
ORDER BY [I1]

[AlekseyMartynov]

Looks like a general SQL Server error.
Check this thread at StackOverflow.

[alpergunay]

I don't think that this is sql server error. This is sql default behaviour. I am thinking that after grouping with using subselect column in order to find count of rows for each group, generated sql should be nested select. For example;

SELECT COUNT(*), subSelectColumn FROM  
(
       SELECT a, b, c, subSelectColumn
)
Group By subSelectColumn

[AlekseyMartynov]

I don't think that this is sql server error. This is sql default behaviour.

I mean that the error message ('...group by list of a GROUP BY clause') indicates that the error originates from the SQL Server rather than from EF or our code.

generated sql should be nested select

We don't generate SQL, we build LINQ queries that are translated into SQL by a LINQ provider. When we build a GroupBy expression, it only contains group keys and aggregates.

Would you please share loadOptions used in the failing DataSourceLoader.Load call as we did last time in #332 (comment)?

[alpergunay]

Thanks for the response. Below you can find our loadOptions.

var loadOptions = new DataSourceLoadOptions
            {                
                DefaultSort = options.ContainsKey("defaultSort") ? options["defaultSort"].ToString() : null,
                Filter = options.ContainsKey("filter") ? (IList)options["filter"] : null,
                StringToLower = true,
                RequireTotalCount = !options.ContainsKey("requireTotalCount") || (bool)options["requireTotalCount"],
                Sort = options.ContainsKey("sort")
                    ? (SortingInfo[])JsonConvert.DeserializeObject(options["sort"].ToString(), typeof(SortingInfo[]))
                    : (SortingInfo[])JsonConvert.DeserializeObject("[{\"selector\":\"id\",\"desc\": true}]", typeof(SortingInfo[])),
                Group = options.ContainsKey("group")
                    ? (GroupingInfo[])JsonConvert.DeserializeObject(options["group"].ToString(), typeof(GroupingInfo[]))
                    : null,
                GroupSummary = options.ContainsKey("groupSummary")
                    ? (SummaryInfo[])JsonConvert.DeserializeObject(options["groupSummary"].ToString(), typeof(SummaryInfo[]))
                    : null,
                IsCountQuery = options.ContainsKey("isCountQuery") && (bool)options["isCountQuery"],
                PreSelect = options.ContainsKey("preSelect") ? (string[])options["preSelect"] : null,
                PrimaryKey = options.ContainsKey("id") ? (string[])options["id"] : null,
                RemoteGrouping = true, //options.ContainsKey("remoteGrouping") && (bool)options["remoteGrouping"],
                RemoteSelect = options.ContainsKey("remoteSelect") && (bool)options["remoteSelect"],
                RequireGroupCount = options.ContainsKey("requireGroupCount") && (bool)options["requireGroupCount"],
                Select = options.ContainsKey("select") ? (string[])options["select"] : null,
                TotalSummary = options.ContainsKey("totalSummary")
                    ? (SummaryInfo[])JsonConvert.DeserializeObject(options["totalSummary"].ToString(), typeof(SummaryInfo[]))
                    : null
            };
            if (options.ContainsKey("take"))
                loadOptions.Take = Convert.ToInt32(options["take"]);
            if (options.ContainsKey("skip"))
                loadOptions.Skip = Convert.ToInt32(options["skip"]);
            return loadOptions;
        }

[AlekseyMartynov]

Thanks but this code snippet is not helpful.
It doesn't demonstrate the exact values that cause the failure.

Basically, I'm asking for the same information as in #332.

[alpergunay]

I copied load options values.

{"group":[{"selector":"workOrderCode","desc":true,"isExpanded":false}],"totalSummary":[],"groupSummary":[{"summaryType":"count"},{"selector":"orderQuantity","summaryType":"sum"}],"recordStatusId":1,"_":1553433389076}

[AlekseyMartynov]

Thank you for the update.
These load options are translated into the following LINQ:

var result = (/* your source query with joins */);

var groups = result
    .GroupBy(obj => new { 
        I0 = obj.WorkOrderCode
    })
    .OrderByDescending(g => g.Key.I0)
    .Select(g => new {
        I0 = g.Count(),
        I1 = g.Key.I0,
        I2 = g.Sum(obj => obj.OrderQuantity)
    })
    .ToArray();

Would you please check whether or not it is executed with the same 'Cannot use an aggregate...' error?

[alpergunay]

The problem is here WorkOrderCode is coming from sub query (You can look at my first code snippet). We are not writing GroupBy clause. GroupBy clause is coming from remote grouping functionality of DevExtreme grid.

[AlekseyMartynov]

We are not writing GroupBy clause. GroupBy clause is coming from remote grouping functionality of DevExtreme grid.

This is clear. Have you had a chance to check the manually written GroupBy query, from my previous comment, against the source collection?