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

Side note: why do you join entities that don't contribute to the end result? Another note: *if* you're gonna need these entities, don't join but use these nice navigation properties you have. Then: the long execution time probably means that EF is auto-switching to client-side evaluation which means that it pulls far too many data from the database. Check the executed SQL. — Gert Arnold, Jun 08 '19 at 21:00
@GetArnold EF is evaluating my first example query locally. Getting warnings like `The LINQ expression 'Sum()' could not be translated and will be evaluated locally.` — Muhammad Hannan, Jun 08 '19 at 21:13
Yep, there you go. EF core still isn't capable of translating somewhat complex LINQ queries into fully server-side evaluated SQL. Nothing you can do about it other than working around the issue by ugly solutions like running raw SQL or creating views/stored procedures for anything that EF can't handle properly. — Gert Arnold, Jun 08 '19 at 21:19
@GetArnold I used `Query Types` for the solution that uses Raw sql. But I wasn't satisfied. I hope this resolves in EF core 3.0. — Muhammad Hannan, Jun 08 '19 at 21:22
One point that you may want to be careful regarding you query is, do the Where Filtering before joining data, just changing the sequence may make your query faster, since you are doing it on `ServiceRecords` data, which is then joined with other tables / collections — Mrinal Kamboj, Jun 09 '19 at 08:22

score 2 · Accepted Answer · answered Jun 10 '19 at 06:22

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()
    });

I do have navigation property to `ServiceRecord` in `District`. I totally forgot about this navigation property :D. — Muhammad Hannan, Jun 10 '19 at 14:31

Jake Steffen · Answer 2 · 2019-06-08T20:38:26.267

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)
});

I'm getting exception at `.Join(x => x.District,district=> district.Id, serviceRecord=> serviceRecord.Id)`. `No overload for method 'Join' takes 3 arguments`. Do I need to add reference to any assembly? — Muhammad Hannan, Jun 08 '19 at 20:36

Converting SQL query to LINQ or LINQ fluent Syntax

2 Answers2