Entity Framework get all columns with group by-CodePudding

we are migrating reports from SQL to EF. In the process I just stumbled over the following query:

SELECT
    pl.clientid,
    pl.loginname,
    COUNT(*)
FROM
    (
        SELECT
            DISTINCT
            s.clientid,
            s.doctype
        FROM
            Specifications s
    ) A
    INNER JOIN portal_logins pl ON A.clientid = pl.clientid
WHERE
    A.clientid != '0'
GROUP BY
    A.clientid,
    A.doctype

My current solution is the following

return await _nfContext.Specifications
    .Select( x => new
    {
        Clientid = x.Clientid,
        Doctype = x.Doctype
    })
    .Distinct()
    .Join(
        _nfContext.PortalLogins,
        s => s.Clientid,
        p => p.Clientid,
        (spec, login) => new
        {
                loginName = login.Loginname,
                clientId  = spec.Clientid,
                doctype   = spec.Doctype
        }
    )
    .Where(x => x.clientId != "0")
    .GroupBy(c => new
    {
        c.clientId,
        c.doctype,
        c.loginName
    })
    .Select(result => new TotalActiveConnections
    {
        ActiveConnections = result.Count(),
        ClientId = result.Key.clientId,
        LoginName = result.Key.loginName
    })
    .OrderByDescending(x => x.ActiveConnections)
    .ToListAsync()
    .ConfigureAwait(false);

My problem is, I need all three columns as described in the Group by, but I don't want to group by login name. Does anyone have an idea what I can do? Thanks in advance for the help....

CodePudding user response：

You should just use Min or Max to get any random login value

    .GroupBy(c => new
    {
        c.clientId,
        c.doctype
    })
    .Select(result => new TotalActiveConnections
    {
        ActiveConnections = result.Count(),
        ClientId = result.Key.clientId,
        LoginName = result.Min(g => g.loginName),
    })

Presumably it worked in SQL because you were using MySQL/MariaDB and did not have ONLY_FULL_GROUP_BY turned on.