c# linq with group by 和 join 抛出异常 [英] c# linq with group by and join throwing exception

问题描述

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

运行时出现以下异常，我使用 group by 来删除结果集中的重复项

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

异常:

无法翻译 LINQ 表达式FirstOrDefault(GroupByShaperExpression: KeySelector: a.patientid, ElementSelector:EntityShaperExpression: EntityType: Appointments ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False )".以可翻译的形式重写查询，或通过插入对 AsEnumerable()、AsAsyncEnumerable()、ToList() 或 ToListAsync() 的调用显式切换到客户端评估.请参阅 https://go.microsoft.com/fwlink/?linkid=2101038了解更多信息.

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.

推荐答案

也许可以更改您的 groupby 以克服这个障碍.将 GroupBy linq 查询转换为正确的 sql 查询的复杂性仍然是 dotnetcore 的一个持续开发问题.

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.

我在 Github 上看到一个已关闭的问题:使用 GroupBy 或 GroupJoin 查询抛出异常 #17068
但我不确定他们是否仍在解决 GroupBy 问题，或者问题是否已修复，或者他们是否不会对此采取任何措施.

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.

也许您可以将查询更改为以下内容:请注意，我在计数之前删除了 .ToList() ，因为如果您只需要计数，这对我来说似乎太过分了.

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

这篇关于c# linq with group by 和 join 抛出异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！