c#linq与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来克服此障碍. dotnetcore仍将持续发展GroupBy linq查询转换为适当的sql查询的复杂性.

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与group by和join抛出异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！