EF Core 2.x - IQueryable extension is getting evaluated at the client and not the database

Question

I have the follow extension method I have been using. I was not aware until recently that EF Core 2.x, by default, performs mixed evaluation, which means that for the things it doesn't know how to convert into SQL, it will pull down everything from the database, and then perform the LINQ queries in memory. I have since disabled this behavior. Anyway, here is my extension method:

public static class RepositoryExtensions
{
        public static IQueryable<T> NonDeleted<T>(this IQueryable<T> queryable) where T : IDeletable
        {
            return queryable.Where(x => !x.Deleted);
        }
}

Entity framework then throws an exception (because I have configured it at this point to do this, instead of take everything from the DB and evaluate locally) with the following message:

Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'where Not(Convert([x], IDeletable).Deleted)' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

This extension method it being used in quite a few places, so I would rather fix if and make it work (be evaluated at the database) rather than go and remove it across many projects, then have to replace it with .Where(x => !x.Deleted).

Has anyone else ran into this and know how to make an IQueryable extension that is evaluated at the Database (converted to SQL at run time)? It seems that perhaps EF is looking at only the concrete class when it comes to converting LINQ to SQL?

UPDATE: One of the comments asked for an example. Doing some additional testing, I can see that this is when applied to an IQueryable returned from EF Core's .FromSql. If .NonDeleted() is applied directly on the entity Organization, it seems to work correctly.

// Service layer
public class OrganizationService
{
    public IEnumerable<Data.Entities.Organization> GetAllForUser(int? userId)
    {
        return _repository.GetOrganizationsByUserId(userId.GetValueOrDefault()).NonDeleted();
    }
}

// Repository layer

using PrivateNameSpace.Common.EntityFramework;
using PrivateNameSpace.Data.DbContext;
using PrivateNameSpace.Data.Repos.Interfaces;
using Microsoft.EntityFrameworkCore;
using System.Data.SqlClient;
using System.Linq;

public class OrganizationRepository: Repository<Entities.Organization, int, OrganizationContext>, IOrganizationRepository
{
    private const string storedProcedure = "dbo.sproc_organizations_by_userid";

    public OrganizationRepository(OrganizationContext context) : base(context)
    {
        DbSet = Context.Organizations;
    }

    public IQueryable<Entities.Organization> GetOrganizationsByUserId(int userId)
    {
        var sql = $"{Sproc} @UserId";
        var result = Context.Organizations.FromSql(sql, new SqlParameter("@UserId", userId));
        return result;
    }
}

Answer 1

While you can compose raw SQL queries with additional query expressions, eg

var searchTerm = ".NET";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();

Raw SQL Queries - Composing with LINQ

You can't do that if the raw SQL Query isn't a simple SELECT. You'll always be filtering the results of the stored procedure, anyway, and you'd have to load the procedure results into a temp table on the server to apply additional query operators on the server side. Which would be complicated to implement, and of little value.