c# linq with group by and join throwing exception

Question

(from p in this.m_dbContext.Patient
        join b in (from a in this.m_dbContext.Appointments
        join p in this.m_dbContext.Patient on a.Patientid equals 
        p.Patientid
        where a.Doctorid == doctorid && a.Clinicid == clinicid
        group a by a.Patientid)
        on p.Patientid equals b.FirstOrDefault().Patientid
        orderby p.Name
        select new
        {
          p.Patientid,
          p.Clinicid,
          p.Name,
          p.Mobilenumber,
          p.Gender,
          p.Dob,
          p.Age,
          p.Address,
          p.City,
          p.State,
          p.Pincode
       }).ToList().Count();

I get the below exception when i run, i use group by in order to remove the duplicates in the result set

Exception:

The LINQ expression 'FirstOrDefault(GroupByShaperExpression: KeySelector: a.patientid, ElementSelector:EntityShaperExpression: EntityType: Appointments ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False )' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Yes you do get that exception. What is your question? Keep in mind that we have no idea how much you do or do not understand about what is happening. Let us know how much you understand and where you need help. The exception itself gives advice on one potential workaround (switch to client evaluation) is that an acceptable workaround? Please write a complete question with all the details necessary for us to answer. — Wyck
– Wyck, Commented Apr 29, 2020 at 13:05
is there a specific reason to use FirstOrDefault? Are you sure you don't need the other patient records? — Richard Baldauf
– Richard Baldauf, Commented May 5, 2020 at 19:41

Silvermind · Accepted Answer · 2020-04-29 13:10:21Z

Perhaps your groupby could be changed to overcome this obstacle. The complexity of translating a GroupBy linq query to a proper sql query is still an ongoing development issue with dotnetcore.

Edit:
I see a closed issue on Github: Query with GroupBy or GroupJoin throws exception #17068
But I'm unsure whether the are still working on the GroupBy problem, or if it is fixed or if they will not do anything about it.

Perhaps you can change your query to the following: Note that I removed the .ToList() before the count, because that seems overkill to me if you just need the count.

var patientIds = (from a in this.m_dbContext.Appointments
                  join p in this.m_dbContext.Patient on a.Patientid equals p.Patientid
                  where a.Doctorid == doctorid && a.Clinicid == clinicid
                  select a.Patientid).Distinct();

var items = (from p in this.m_dbContext.Patient
             join patientId in patientIds on p.Patientid equals patientId
             orderby p.Name
             select new
             {
                 p.Patientid,
                 p.Clinicid,
                 p.Name,
                 p.Mobilenumber,
                 p.Gender,
                 p.Dob,
                 p.Age,
                 p.Address,
                 p.City,
                 p.State,
                 p.Pincode
             }).ToList();

Or if you just need the count:

var count = (from p in this.m_dbContext.Patient
             join patientId in patientIds on p.Patientid equals patientId
             orderby p.Name
             select new
             {
                 p.Patientid
             }).Count();

Collectives™ on Stack Overflow

c# linq with group by and join throwing exception

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related