实体框架IQueryable扩展方法不能用作子查询 [英] Entity framework IQueryable extension methods do not work as a sub query

问题描述

我希望尽可能使用扩展方法来编写查询.因此，以下是对我有用的查询:

I like to write my queries using extension methods where possible. So the following is a query which works for me:

int studentId = 
    (
        from u in db.Users
            .FromOrganisation(org.Id)
            .IsStudent()
            .IsActive()
        where u.ExtId == dto.StudentExtId
        select u.Id
    ).FirstOrDefault();

扩展方法如下:

public static IQueryable<User> IsStudent(this IQueryable<User> u)
{
    return u.Where(x => x.Type == (int)UserTypes.Student);
}

但是，当我在子查询中使用扩展方法时，会收到以下消息:

However, when I use extension methods in a sub-query I get the following message:

LINQ to Entities无法识别方法'System.Linq.IQueryable`1 [eNotify.Domain.Models.User] IsActive(System.Linq.IQueryable'1 [eNotify.Domain.Models.User])'方法，而且该方法无法转换为商店表达式.

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[eNotify.Domain.Models.User] IsActive(System.Linq.IQueryable`1[eNotify.Domain.Models.User])' method, and this method cannot be translated into a store expression.

这是导致该消息的查询:

Here is the query which causes that message:

var vm = from o in db.Organisations
         select new StaffStudentVm
         {
             StudentId = (
                 from u in db.Users
                     .FromOrganisation(org.Id)
                     .IsStudent()
                     .IsActive()
                 where u.ExtId == dto.StudentExtId
                 select u.Id
                 ).FirstOrDefault(),
             StaffId = (
                 from u in db.Users
                     .FromOrganisation(org.Id)
                     .IsStaff()
                     .IsActive()
                 where u.ExtId == dto.StaffExtId
                 select u.Id
                 ).FirstOrDefault()
         };

return vm.FirstOrDefault();

我在做什么错了?

更新: 亚历山大·德克(Alexander Derck)发布了一个行之有效的解决方案，但不如原始问题查询好.我向EF团队提出来，经过调查，他们提出了一个更优雅的解决方法.我已在下面将其发布为可接受的答案.

Update: Alexander Derck posted a solution which worked well, but wasn't quite as nice as the original problem query. I raised it with the EF team, and after investigating they came up with a more elegant work-around. I have posted that below as the accepted answer.

推荐答案

我最终与GitHub上的Entity Framework团队提出了这一点.您可以在此处看到该线程，并对其发生原因进行完整说明:

I eventually raised this with the Entity Framework team on GitHub. You can see the thread here, with a full description of why it happens:

https://github.com/aspnet/EntityFramework6/issues/98

似乎有人提出将其包含在EF 6.2中，但在此之前，提出了一种非常优雅的解决方法.您可以在线程中阅读它，但我已在此处复制它以供快速参考.

It seems to have been raised as a suggestion for inclusion in EF 6.2, but until then, a very elegant work-around was suggested. You can read it in the thread, but I have copied it here for quick reference.

这是原始查询(由于子查询中使用IQueryable扩展方法而发生错误):

Here is the original query (where an error occurs due to an IQueryable extension method being used in a sub-query):

var vm = from o in db.Organisations
         select new StaffStudentVm
         {
             StudentId = (
                 from u in db.Users
                     .FromOrganisation(org.Id)
                     .IsStudent()
                     .IsActive()
                 where u.ExtId == dto.StudentExtId
                 select u.Id
                 ).FirstOrDefault(),
             StaffId = (
                 from u in db.Users
                     .FromOrganisation(org.Id)
                     .IsStaff()
                     .IsActive()
                 where u.ExtId == dto.StaffExtId
                 select u.Id
                 ).FirstOrDefault()
         };

return vm.FirstOrDefault();

这是如何编写它以便不发生错误的方法:

And here is how to write it so that no error occurs:

var stuList = db.Users.FromOrganisation(org.Id).IsStudent().IsActive();
var staffList = db.Users.FromOrganisation(org.Id).IsStaff().IsActive();

var vm = from o in db.Organisations
         select new StaffStudentVm
         {
             StudentId = (
                 from u in stuList
                 where u.ExtId == dto.StudentExtId
                 select u.Id
                 ).FirstOrDefault(),
             StaffId = (
                 from u in staffList
                 where u.ExtId == dto.StaffExtId
                 select u.Id
                 ).FirstOrDefault()
         };

return vm.FirstOrDefault();

我可以确认这种样式仍然只能导致数据库往返1次.将查询分为多个语句实际上在很多地方也提高了可读性.

I can confirm that this style still only results in 1 round trip to the database. Breaking the query into multiple statements actually improves readability in a lot of places too.

这篇关于实体框架IQueryable扩展方法不能用作子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！