Efcore: 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