How to count specific column in Entity Framework Core

When I use left join to perform a grouped query of two tables, the generated count statement is always count(*), I want to generate the count of the specified column. For example: count(InstrumentCode).

Framework: EF Core 3.0-preview9

var resultQuery = from t1 in query
    join t2 in orgCrmQuery on t1.CUSTOMER equals t2._ID
    join t3 in orgQuery on t2.NAME equals t3.ORGANIZATION_NAME into t4
    from t5 in t4.DefaultIfEmpty()
    join t6 in instrumentQuery on t5.ORGANIZATION_ID equals t6.ORGANIZATION_ID into t7
    from t8 in t7.DefaultIfEmpty()
    where t2.RECORD_TYPE == dto.RecordType && t1.MEETING_NO == dto.MeetingNo
    group t8.INSTRUMENT_CODE by new {
        t1.PERSON_NAME,
        t1.SEX,
        t1.POSTION,
        t1.PHONE,
        t1.IS_CIZHU_KEHU,
        t2.NAME,
        t2.ORG_LEVEL,
    } into g
    orderby g.Key.NAME
    select new {
        g.Key.PERSON_NAME,
        g.Key.SEX,
        g.Key.POSTION,
        g.Key.PHONE,
        g.Key.IS_CIZHU_KEHU,
        g.Key.NAME,
        g.Key.ORG_LEVEL,
        INSTRUMENT_COUNT = g.Count()
    };

SQL statement generated´╝Ü

SELECT
    [t].[PERSON_NAME],
    [t].[SEX],
    [t].[POSTION],
    [t].[PHONE],
    [t].[IS_CIZHU_KEHU],
    [t0].[NAME],
    [t0].[ORG_LEVEL],
    COUNT ( * ) AS [INSTRUMENT_COUNT] 
FROM
    [dbo].[TB_MEETING_PERSONAL] AS [t] WITH ( NOLOCK )
    INNER JOIN [dbo].[TB_ORG_CRM] AS [t0] WITH ( NOLOCK ) ON [t].[CUSTOMER] = [t0].[_ID]
    LEFT JOIN [dbo].[MS_ORGANIZATION] AS [m] WITH ( NOLOCK ) ON [t0].[NAME] = [m].[ORGANIZATION_NAME]
    LEFT JOIN [dbo].[MS_INSTRUMENT] AS [m0] WITH ( NOLOCK ) ON [m].[ORGANIZATION_ID] = [m0].[ORGANIZATION_ID] 
WHERE
    (
        ( ( [t0].[RECORD_TYPE] = @__dto_RecordType_0 ) AND ( [t0].[RECORD_TYPE] IS NOT NULL AND @__dto_RecordType_0 IS NOT NULL ) ) 
        OR ( [t0].[RECORD_TYPE] IS NULL AND @__dto_RecordType_0 IS NULL ) 
    ) 
    AND (
        ( ( [t].[MEETING_NO] = @__dto_MeetingNo_1 ) AND ( [t].[MEETING_NO] IS NOT NULL AND @__dto_MeetingNo_1 IS NOT NULL ) ) 
        OR ( [t].[MEETING_NO] IS NULL AND @__dto_MeetingNo_1 IS NULL ) 
    ) 
GROUP BY
    [t].[PERSON_NAME],
    [t].[SEX],
    [t].[POSTION],
    [t].[PHONE],
    [t].[IS_CIZHU_KEHU],
    [t0].[NAME],
    [t0].[ORG_LEVEL] 
ORDER BY
    [t0].[NAME] OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

I checked some information, but I didn't find a solution.

1 answer

  • answered 2019-09-10 02:33 metal

    Based on your linq, same result for .Count(INSTRUMENT_COUNT) and .Count(*) or .Count(1) because this will return the # of rows regardless of the value of your INSTRUMENT_COUNT.

    But if you want to get count specifically for INSTRUMENT_COUNT you can try this.

    var resultQuery = from t1 in query
        join t2 in orgCrmQuery on t1.CUSTOMER equals t2._ID
        join t3 in orgQuery on t2.NAME equals t3.ORGANIZATION_NAME into t4
        from t5 in t4.DefaultIfEmpty()
        join t6 in instrumentQuery on t5.ORGANIZATION_ID equals t6.ORGANIZATION_ID into t7
        from t8 in t7.DefaultIfEmpty()
        where t2.RECORD_TYPE == dto.RecordType && t1.MEETING_NO == dto.MeetingNo
        group t8.INSTRUMENT_CODE by new {
            t1.PERSON_NAME,
            t1.SEX,
            t1.POSTION,
            t1.PHONE,
            t1.IS_CIZHU_KEHU,
            t2.NAME,
            t2.ORG_LEVEL,
            t8.INSTRUMENT_CODE,
        } into g
        orderby g.Key.NAME
        select new {
            g.Key.PERSON_NAME,
            g.Key.SEX,
            g.Key.POSTION,
            g.Key.PHONE,
            g.Key.IS_CIZHU_KEHU,
            g.Key.NAME,
            g.Key.ORG_LEVEL,
            INSTRUMENT_COUNT =  g.Select(x => x.INSTRUMENT_CODE).Distinct().Count()
        };