COUNT(DISTINCT *) in ef core 2.1

Question

SELECT
  e.EmpName,
  me.RemarkNumber,
  me.RemarkPeopleNumber
FROM
  EmployeeInfo e
  LEFT JOIN (
    SELECT
      COUNT(RemarkId) As RemarkNumber,
      COUNT(DISTINCT MemberId) As RemarkPeopleNumber,
      CreateUser
    FROM
      MemberRemark
    WHERE
      RemarkStatus = 0
    GROUP BY
      CreateUser
  ) AS me On e.EmpName = me.CreateUser
WHERE
  BranchCode = '0000'
  And [Status] = 0

How to convert it to LINQ?

from e in db.EmployeeInfo
join me in (
    from memberRemarks in db.MemberRemark
    where memberRemarks.RemarkStatus == 0
    group memberRemarks by new
    {
        memberRemarks.CreateUser,
    } into g
    select new
    {
        RemarkNumber = g.Count(),
        RemarkPeopleNumber = g.Select(m => m.MemberId).Distinct().Count(),
        g.Key.CreateUser
    }
) on new { e.EmpName } equals new { EmpName = me.CreateUser } into meJoin
from me in meJoin.DefaultIfEmpty()
where e.BranchCode == "0000" & &e.Status == 0
select new
    {
        e.EmpName,
        me.RemarkNumber,
        me.RemarkPeopleNumber
    };

I got this error

RemarkPeopleNumber = g.Select(m=>m.MemberId).Distinct().Count(), //error

Using asp.net core mvc 2.1 + ef core 2.1 + mssql

The MySQL query may be too much for LINQ instead try to put it in stored procedure and return what’s needed as shown below https://sunali.com/2012/10/10/querying-mysql-using-entity-framework-and-mysql-stored-procedures/ — boateng, Jul 19 '18 at 04:55
i know mysql use Pomelo driver can use .DistinctBy(x => x.MemberId).Count()。but it's mssql — 全雨秋, Jul 19 '18 at 05:11

score 2 · Answer 1 · answered Jul 19 '18 at 22:38

Perhaps if you broke the query up into pieces it would be handled better? Using my SQL to LINQ Recipe I would translate your SQL like this:

var ePart = from e in db.EmployeeInfo
            where e.BranchCode == "0000" && e.Status == 0
            select e;
var mrPart = from mr in db.MemberRemark
             where mr.RemarkStatus == 0
             group mr by mr.CreateUser into mrg
             select new {
                 CreateUser = mrg.Key,
                 RemarkNumber = mrg.Count(),
                 RemarkPeopleNumber = mrg.Select(mr => mr.MemberId).Distinct().Count()
             };
var ans = from e in ePart
          join me in mrPart on e.EmpName equals me.CreateUser into mej
          from me in mej
          select new {
            e.EmpName,
            me.RemarkNumber,
            me.RemarkPeopleNumber
          };

thank you ,i use linq2db.EntityFrameworkCore , it's support – 全雨秋 Jul 31 '18 at 03:38 — 全雨秋, Jul 31 '18 at 03:38

COUNT(DISTINCT *) in ef core 2.1

1 Answers1