Entity Framework extension method ICollection / IQueryable

Question

I need to check the same specific condition (where clause) many times:

return _ctx.Projects.Where(p => p.CompanyId == companyId &&
                          (p.Type == Enums.ProjectType.Open || 
                           p.Invites.Any(i => i.InviteeId == userId))).ToList()

The part after the '&&' will cause that the user isn't able to retrieve restricted projects.

I wanted to abstract this check to a function. In the future these conditions could change and I don't want to replace all the LINQ queries.

I did this with the following extension method:

public static IQueryable<Project> IsVisibleForResearcher(this IQueryable<Project> projects, string userId)
{
    return projects.Where(p => p.Type == Enums.ProjectType.Open || 
                               p.Invites.Any(i => i.InviteeId == userId));
}

Now I can change the LINQ query to:

return _ctx.Projects.Where(p => p.CompanyId == companyId)
                    .IsVisibleForResearcher(userId).ToList()

This generates the same SQL query. Now my problem starts when I want to use this extension method on another DbSet that has projects.

Imagine that a company has projects. And I only want to retrieve the companies where the user can at least see one project.

return _ctx.Companies
       .Where(c => c.Projects.Where(p => 
            p.Type == Enums.ProjectType.Open || 
            p.Invites.Any(i => i.InviteeId == userId))
       .Any())

Here I also like to use the extension method.

return _ctx.Companies
       .Where(c => c.Projects.AsQueryable().IsVisibleForCompanyAccount(userId).Any())

This throws following exception:

An exception of type 'System.NotSupportedException' occurred in Remotion.Linq.dll but was not handled in user code

Additional information: Could not parse expression 'c.Projects.AsQueryable()': This overload of the method 'System.Linq.Queryable.AsQueryable' is currently not supported.

Than I created the following extension methods:

public static IEnumerable<Project> IsVisibleForResearcher(this ICollection<Project> projects, string userId)
{
    return projects.Where(p => p.Type == Enums.ProjectType.Open || 
                               p.Invites.Any(i => i.InviteeId == userId));
}

But this didn't work also.

Does anyone has an idea? Or a step in the right direction.

Btw I'm using Entity Framework Core on .NET Core

UPDATE:

Using a Expression<Func<>> resulted in the same exception:

'System.Linq.Queryable.AsQueryable' is currently not supported.

UPDATE 2

Thx @ivan-stoev for providing a solution. I still have one problem. I also want to retrieve the count of 'visible' projects.

I fixed it by doing this:

var companies = _ctx.Companies
                .WhereAny(c => c.Projects, Project.IsProjectVisibleForResearcher(userId))
                .Select(c => new CompanyListDto
                {
                    Id = c.Id,
                    Name = c.Name,
                    LogoId = c.LogoId,                   
                    ProjectCount = _ctx.Projects.Where(p => p.CompanyId == c.Id)
                                       .Count(Project.IsProjectVisibleForResearcher(userId))     
                });

But I don't find a way to just use c.Projects instead of ctx.Projects.Where(p => p.CompanyId == c.Id)

The SQL that gets generated is correct, but I'd like to avoid this unneeded check.

Sincerely, Brecht

Answer 1

Using expressions / custom methods inside the IQueryable<T> query expression has been always problematic and requires some expression tree post processing. For instance, LinqKit provides AsExpandable, Invoke and Expand custom extension methods for that purpose.

While not so general, here is a solution for your sample use cases w/o using 3rd party packages.

First, extract the expression part of the predicate in a method. The logical place IMO is the Project class:

public class Project
{
    // ...
    public static Expression<Func<Project, bool>> IsVisibleForResearcher(string userId)
    {
        return p => p.Type == Enums.ProjectType.Open ||
                    p.Invites.Any(i => i.InviteeId == userId);
    }
}

Then, create a custom extension method like this:

public static class QueryableExtensions
{
    public static IQueryable<T> WhereAny<T, E>(this IQueryable<T> source, Expression<Func<T, IEnumerable<E>>> elements, Expression<Func<E, bool>> predicate)
    {
        var body = Expression.Call(
            typeof(Enumerable), "Any", new Type[] { typeof(E) },
            elements.Body, predicate);
        return source.Where(Expression.Lambda<Func<T, bool>>(body, elements.Parameters));
    }
}

With this design, there is no need of your current extension method, because for Projects query you can use:

var projects = _ctx.Projects
    .Where(p => p.CompanyId == companyId)
    .Where(Project.IsVisibleForResearcher(userId));

and for Companies:

var companies = _ctx.Companies
    .WhereAny(c => c.Projects, Project.IsVisibleForResearcher(userId)); 

Update: This solution is quite limited, so if you have different use cases (especially inside the Select expression as in your second update), you'd better resort to some 3rd party package. For instance, here is the LinqKit solution:

// LInqKit requires expressions to be put into variables
var projects = Linq.Expr((Company c) => c.Projects);
var projectFilter = Project.IsVisibleForResearcher(userId);
var companies = db.Companies.AsExpandable()
    .Where(c => projects.Invoke(c).Any(p => projectFilter.Invoke(p)))
    .Select(c => new CompanyListDto
    {
        Id = c.Id,
        Name = c.Name,
        LogoId = c.LogoId,
        ProjectCount = projects.Invoke(c).Count(p => projectFilter.Invoke(p))
    });