How to count specific column in Entity Framework Core

.net-core-3.0 entity-framework-core linq-to-sql

Question

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
1
9/10/2019 4:43:00 AM

Popular Answer

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()
    };
0
9/10/2019 3:13:47 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow