Entity framework IQueryable extension methods do not work as a sub query

c# entity-framework entity-framework-6 linq linq-to-sql

Question

Where possible, I try to use extension methods when writing my queries. As a result, the following is a query that I can use:

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

These are the extension techniques:

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

However, I see the following error message when I utilize extension methods in a sub-query:

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.

The query that results in that message is as follows:

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

Why am I misusing this?

Update: Although it was good, Alexander Derck's solution wasn't quite as appealing as the problem's initial query. I brought it up to the EF team, and after some research, they came up with a more tasteful solution. That is the accepted response, which I have posted below.

1
4
12/28/2016 7:45:27 PM

Accepted Answer

I eventually brought up this issue on GitHub with the Entity Framework team. The thread can be found here, along with a detailed explanation of why it occurs:

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

Up until that point, a fairly elegant workaround was suggested, but it appears to have been raised as a recommendation for inclusion in EF 6.2. You may read it on the thread, but for ease of use, I've copied it here.

Here is the original query (where an error happens because a sub-query uses an IQueryable extension method):

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's how to format it to ensure accuracy:

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

I'm able to attest that this approach still only necessitates one round trip to the database. The query's readability is actually enhanced in many areas by breaking it up into many statements.

1
12/28/2016 7:38:10 PM

Popular Answer

ZZZ_tmp


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow