ErazerBrecht ErazerBrecht - 1 month ago 20
C# Question

Entity Framework extension method ICollection / IQueryable

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.


Sincerely,
Brecht

Answer

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