Column 'x' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

[AndersMalmgren]

I use latest EF core, time of writing, 3.1.4

This is the query

var foo = await db.Set<Party>()
    .Where(p => p.CreditAccountType.HasValue)
    .Select(p => new
    {
        p,
        ActiveAndOpenAgreements = p.AutogiroAgreements.Where(agp => agp.IsActive && agp.Agreement.State == AutogiroAgreementState.Accepted)
    })
    .Select(info => new 
    {
        info.p.PartyName,
        info.p.PartyNumber,
        HasActiveAgreements = info.ActiveAndOpenAgreements.Any(),
        HasMultipleSystems = info.ActiveAndOpenAgreements.GroupBy(agp => agp.Agreement.ExternalSystem).Count() > 1
    })
    .ToListAsync();

I have tried doing this both in one select and with two selects as above, same outcome. The problem is that I do a GroupBy to find out if we got duplicate rows. When executing this query I get

'Column 'dbo.AutogiroAgreementPayerParty.AutogiroAgreementId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

Projected SQL looks like

SELECT [p].[PartyName], [p].[PartyNumber], CASE
    WHEN EXISTS (
        SELECT 1
        FROM [dbo].[AutogiroAgreementPayerParty] AS [a]
        INNER JOIN (
            SELECT [a0].[Id], [a0].[BgcMessage], [a0].[CreatedBy], [a0].[CreatedUTC], [a0].[ExternalId], [a0].[ExternalSystemId], [a0].[LegacyId], [a0].[PayerBankAccountNumber], [a0].[PayerClearingNumber], [a0].[ReceiverAccountId], [a0].[AutogiroAgreementStateId], [a0].[UpdatedBy], [a0].[UpdatedUTC], [a1].[Id] AS [Id0], [a1].[AccountName], [a1].[AccountNumber], [a1].[AccountTypeId], [a1].[BgcCustomerId], [a1].[IsPbfAccount], [a1].[OwnerPartyId], [a1].[RelatedAccountId], [a2].[Id] AS [Id1], [a2].[AccountName] AS [AccountName0], [a2].[AccountNumber] AS [AccountNumber0], [a2].[AccountTypeId] AS [AccountTypeId0], [a2].[BgcCustomerId] AS [BgcCustomerId0], [a2].[IsPbfAccount] AS [IsPbfAccount0], [a2].[OwnerPartyId] AS [OwnerPartyId0], [a2].[RelatedAccountId] AS [RelatedAccountId0], [b].[Id] AS [Id2], [b].[BrokerPartyId], [b].[CustomerName], [b].[CustomerNumber], [b].[ExternalSystemId] AS [ExternalSystemId0]
            FROM [dbo].[AutogiroAgreement] AS [a0]
            INNER JOIN [dbo].[Account] AS [a1] ON [a0].[ReceiverAccountId] = [a1].[Id]
            LEFT JOIN [dbo].[Account] AS [a2] ON [a1].[RelatedAccountId] = [a2].[Id]
            LEFT JOIN [dbo].[BgcCustomer] AS [b] ON [a2].[BgcCustomerId] = [b].[Id]
            WHERE (@__ef_filter__p_0 = CAST(1 AS bit)) OR [b].[BrokerPartyId] IS NULL
        ) AS [t] ON [a].[AutogiroAgreementId] = [t].[Id]
        WHERE ([p].[Id] = [a].[PayerPartyId]) AND (([a].[IsActive] = CAST(1 AS bit)) AND ([t].[AutogiroAgreementStateId] = 1))) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [HasActiveAgreements], CASE
    WHEN (
        SELECT COUNT(*)
        FROM (
            SELECT [a3].[AutogiroAgreementId], [a3].[PayerPartyId], [a3].[IsActive], [a3].[IsOwner]
            FROM [dbo].[AutogiroAgreementPayerParty] AS [a3]
            INNER JOIN (
                SELECT [a4].[Id], [a4].[BgcMessage], [a4].[CreatedBy], [a4].[CreatedUTC], [a4].[ExternalId], [a4].[ExternalSystemId], [a4].[LegacyId], [a4].[PayerBankAccountNumber], [a4].[PayerClearingNumber], [a4].[ReceiverAccountId], [a4].[AutogiroAgreementStateId], [a4].[UpdatedBy], [a4].[UpdatedUTC], [a5].[Id] AS [Id0], [a5].[AccountName], [a5].[AccountNumber], [a5].[AccountTypeId], [a5].[BgcCustomerId], [a5].[IsPbfAccount], [a5].[OwnerPartyId], [a5].[RelatedAccountId], [a6].[Id] AS [Id1], [a6].[AccountName] AS [AccountName0], [a6].[AccountNumber] AS [AccountNumber0], [a6].[AccountTypeId] AS [AccountTypeId0], [a6].[BgcCustomerId] AS [BgcCustomerId0], [a6].[IsPbfAccount] AS [IsPbfAccount0], [a6].[OwnerPartyId] AS [OwnerPartyId0], [a6].[RelatedAccountId] AS [RelatedAccountId0], [b0].[Id] AS [Id2], [b0].[BrokerPartyId], [b0].[CustomerName], [b0].[CustomerNumber], [b0].[ExternalSystemId] AS [ExternalSystemId0]
                FROM [dbo].[AutogiroAgreement] AS [a4]
                INNER JOIN [dbo].[Account] AS [a5] ON [a4].[ReceiverAccountId] = [a5].[Id]
                LEFT JOIN [dbo].[Account] AS [a6] ON [a5].[RelatedAccountId] = [a6].[Id]
                LEFT JOIN [dbo].[BgcCustomer] AS [b0] ON [a6].[BgcCustomerId] = [b0].[Id]
                WHERE (@__ef_filter__p_0 = CAST(1 AS bit)) OR [b0].[BrokerPartyId] IS NULL
            ) AS [t0] ON [a3].[AutogiroAgreementId] = [t0].[Id]
            WHERE ([p].[Id] = [a3].[PayerPartyId]) AND (([a3].[IsActive] = CAST(1 AS bit)) AND ([t0].[AutogiroAgreementStateId] = 1))
            GROUP BY [t0].[ExternalSystemId]
        ) AS [t1]) > 1 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [HasMultipleSystems]
FROM [dbo].[Party] AS [p]
WHERE [p].[CreditAccountTypeId] IS NOT NULL

[joakimriedel]

GroupBy support is very limited in EF Core and have found myself to try different workarounds whenever I would have liked a simple GroupBy.

Perhaps it would be possible to get around it by using something like (untested)

HasMultipleSystems = info.ActiveAndOpenAgreements.Select(agp => agp.Agreement.ExternalSystemId).Distinct().Count() > 1

or resolve again from db root (pseudocode since I do not know your table layout)

HasMultipleSystems = db.Set<ExternalSystem>().Where(s => info.ActiveAndOpenAgreements.Contains(agp => agp.Agreement.ExternalSystemId == s.Id)).Count() > 1

[AndersMalmgren]

Thats sad to hear, this used to work just fime in Ef 6. Hope they are working on getting it supported.

Edit: will try the distinct approuch

[AndersMalmgren]

@joakimriedel Distinct actually did work, which is fine in this case. But maybe you want to operate on that data in a grouped by fashion than distinct will not cut it. Thanks for intput!

edit: I played aroudn some more, on another data entity this time, same idea though, this works

db.Set<DataAccess.Entities.dbo.Party>()
                        .Select(p => new
                        {
                            p.PartyName,
                            p.PartyNumber,
                            HasDuplicates = p
                                .CreditAccounts
                                .GroupBy(ca => ca.AccountNumber).Any(grp => grp.Count() > 1)
                        })
                        .ToListAsync();

This does not

db.Set<DataAccess.Entities.dbo.Party>()
                        .Select(p => new
                        {
                            p.PartyName,
                            p.PartyNumber,
                            Duplicates = p
                                .CreditAccounts
                                .GroupBy(ca => ca.AccountNumber).Where(grp => grp.Count() > 1)
                        })
                        .ToListAsync();

Gives Client side GroupBy is not supported.

Sensitive bugger :D

[joakimriedel]

@AndersMalmgren happy to hear that, let's hope for a more capable query processor in the near future

[AndersMalmgren]

@joakimriedel Yeah agreed. I updated above with some more interesting findings :D

[smitpatel]

This works in 6.0 release.

@AndersMalmgren The 2nd query posted in https://github.com/dotnet/efcore/issues/21036#issuecomment-636907978 does not work because projection contains groupings. Where preserves the enumerable of groupings which cannot translated to server. First query works since it is applying Any operation over grouping so at that point, grouping can be represented in server as enumerable of grouping key.