Converting SQL query to LINQ or LINQ fluent Syntax

Question

I have SQL query like this

SELECT T.*
FROM 
(
SELECT ServiceRecords.DistrictId, Districts.Name as DistrictName, COUNT(Distinct(NsepServiceRecords.ClientRegNo)) AS ClientsServedCount 
FROM ServiceRecords
INNER JOIN Districts ON ServiceRecords.DistrictId = Districts.ID
INNER JOIN NsepServiceRecords ON NsepServiceRecords.ServiceRecordId = ServiceRecords.Id
WHERE ServiceRecords.CreatedAtUtc >= @StartDate
AND ServiceRecords.CreatedAtUtc <= @EndDate
AND ServiceRecords.DistrictId = @DistrictId
GROUP BY ServiceRecords.DistrictId, Districts.Name
) AS T
ORDER BY T.DistrictName ASC, T.DistrictId

Query results:

DistrictId                              DistrictName    ClientsServedCount
8d059005-1e6b-44ad-bc2c-0b3264fb4567    Bahawalpur      117
27ab6e24-50a6-4722-8115-dc31cd3127fa    Gujrat          492
14b648f3-4912-450e-81f9-bf630a3dfc72    Jhelum          214
8c602b99-3308-45b5-808b-3375d61fdca0    Lodhran         23
059ffbea-7787-43e8-bd97-cab7cb77f6f6    Muzafarghar     22
580ee42b-3516-4546-841c-0bd8cef04df9    Peshawar        211

I'm struggling converting this to LINQ to entities query. I want to get same results (except District Id column) using LINQ.

I have tried like this, but not working as expected. Can somebody tell me what I'm doing wrong?

_dbContext.ServiceRecords
.Include(x => x.District)
.Include(x=>x.NsepServiceRecords)
.GroupBy(x => x.DistrictId)
.Select(x => new DistrictClientsLookUpModel
{
    DistrictName = x.Select(record => record.District.Name).FirstOrDefault(),
    ClientsServedCount = x.Sum(t=> t.NsepServiceRecords.Count)
});

Model classes are like this

public class BaseEntity
{
    public Guid Id { get; set; }
}

public class NsepServiceRecord : BaseEntity
{
    public DateTime CreatedAtUtc { get; set; }

    public Guid ServiceRecordId { get; set; }

    public string ClientRegNo { get; set; }
    // other prop .......

    public virtual ServiceRecord ServiceRecord { get; set; }
}

public class ServiceRecord : BaseEntity
{
    public DateTime CreatedAtUtc { get; set; }
    public string DistrictId { get; set; }

    public virtual District District { get; set; }
    public virtual ICollection<NsepServiceRecord> NsepServiceRecords { get; set; }

}

public class DistrictClientsLookUpModel
{
    public string DistrictName { get; set; }
    public int ClientsServedCount { get; set; }
}

I'm using Microsoft.EntityFrameworkCore, Version 2.2.4

EDIT I have also tried like this

var startUniversalTime = DateTime.SpecifyKind(request.StartDate, DateTimeKind.Utc);
var endUniversalTime = DateTime.SpecifyKind(request.EndDate, DateTimeKind.Utc);
return _dbContext.NsepServiceRecords
.Join(_dbContext.ServiceRecords, s => s.ServiceRecordId,
    r => r.Id, (s, r) => r)
.Include(i => i.District)
.Where(x => x.DistrictId == request.DistrictId
            && x.CreatedAtUtc.Date >= startUniversalTime
            && x.CreatedAtUtc.Date <= endUniversalTime)
.OrderBy(x => x.DistrictId)
.GroupBy(result => result.DistrictId)

.Select(r => new DistrictClientsLookUpModel
{
    DistrictName = r.Select(x=>x.District.Name).FirstOrDefault(),
    ClientsServedCount = r.Sum(x=>x.NsepServiceRecords.Count())
});

Another try,

from s in _dbContext.ServiceRecords
join record in _dbContext.NsepServiceRecords on s.Id equals record.ServiceRecordId
join district in _dbContext.Districts on s.DistrictId equals district.Id
group s by new
{
    s.DistrictId,
    s.District.Name
}
into grp
select new DistrictClientsLookUpModel
{
    DistrictName = grp.Key.Name,
    ClientsServedCount = grp.Sum(x => x.NsepServiceRecords.Count)
};

It takes too long, I waited for two minutes before I killed the request.

UPDATE

EF core have issues translating GroupBy queries to server side

Answer 1

Assuming the District has a collection navigation property to ServiceRecord as it should, e.g. something like

public virtual ICollection<ServiceRecord> ServiceRecords { get; set; }

you can avoid the GroupBy by simply starting the query from District and use simple projection Select following the navigations:

var query = _dbContext.Districts
    .Select(d => new DistrictClientsLookUpModel
    {
        DistrictName = d.Name,
        ClientsServedCount = d.ServiceRecords
            .Where(s => s.CreatedAtUtc >= startUniversalTime && s.CreatedAtUtc <= endUniversalTime)
            .SelectMany(s => s.NsepServiceRecords)
            .Select(r => r.ClientRegNo).Distinct().Count()
    });

Answer 2

You don't appear to be doing a join properly.

Have a look at this: Join/Where with LINQ and Lambda

Here is a start on the linq query, I'm not sure if this will give you quite what you want, but its a good start.

Basically within the .Join method you need to first supply the entity that will be joined. Then you need to decide on what they will be joined on, in this case district=> district.Id, serviceRecord=> serviceRecord.Id.

_dbContext.ServiceRecords
.Join( _dbContext.District,district=> district.Id, serviceRecord=> serviceRecord.Id)
.Join(_dbContext.NsepServiceRecords, Nsep=> Nsep.ServiceRecord.Id,district=>district.Id)
.GroupBy(x => x.DistrictId)
.Select(x => new DistrictClientsLookUpModel
{
  DistrictName = x.Select(record => record.District.Name).FirstOrDefault(),
  ClientsServedCount = x.Sum(t=> t.NsepServiceRecords.Count)
});