EF Core queries all columns in SQL when mapping to object in Select

This is fundamental problem with IQueryable from the very beginning, with no out of the box solution after so many years.

The problem is that IQueryable translation and code encapsulation/reusability are mutually exclusive. IQueryable translation is based on knowledge in advance, which means the query processor must be able to "see" the actual code, and then translate the "known" methods/properties. But the content of the custom methods / calculable properties is not visible at runtime, so query processors usually fail, or in limited cases where they support "client evaluation" (EF Core does that only for final projections) they generate inefficient translation which retrieves much more data than needed like in your examples.

To recap, neither C# compiler nor BCL helps solving this "core concern". Some 3rd party libraries are trying to address it in different level of degree - LinqKit, NeinLinq and similar. The problem with them is that they require refactoring your existing code additionally to calling a special method like AsExpandable(), ToInjectable() etc.

Recently I found a little gem called DelegateDecompiler, which uses another package called Mono.Reflection.Core to decompile method body to its lambda representation.

Using it is quite easy. All you need after installing it is to mark your custom methods / computed properties with custom provided [Computed] or [Decompile] attributes (just make sure you use expression style implementation and not code blocks), and call Decompile() or DecompileAsync() custom extension method somewhere in the IQueryable chain. It doesn't work with constructors, but all other constructs are supported.

For instance, taking your extension method example:

public static class ItemExtensionMethods
{
    [Decompile] // <--
    public static MinimalItem MapToMinimalItem(this Item source)
    {
        return new MinimalItem
        {
            Id = source.Id,
            Property1 = source.Property1
        };
    }
}

(Note: it supports other ways of telling which methods to decompile, for instance all methods/properties of specific class etc.)

and now

ctx.Items.Decompile()
    .Select(x => x.MapToMinimalItem())
    .ToList();

produces

// SELECT i."Id", i."Property1" FROM "Items" AS i

The only problem with this approach (and other 3rd party libraries) is the need of calling custom extension method Decompile, in order to wrap the queryable with custom provider just to be able to preprocess the final query expression.

It would have been nice if EF Core allow plugging custom query expression preprocessor in its LINQ query processing pipeline, thus eliminating the need of calling custom method in each query, which could easily be forgotten, and also custom query providers does not play well with EF Core specific extensions like AsTracking, AsNoTracking, Include/ ThenInclude, so it should really be called after them etc.

Currently there is an open issue Please open the query translation pipeline for extension #19748 where I'm trying to convince the team to add an easy way to add expression preprocessor. You can read the discussion and vote up.

Until then, here is my solution for EF Core 3.1:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.Extensions.DependencyInjection;

namespace Microsoft.EntityFrameworkCore
{
    public static partial class CustomDbContextOptionsExtensions
    {
        public static DbContextOptionsBuilder AddQueryPreprocessor(this DbContextOptionsBuilder optionsBuilder, IQueryPreprocessor processor)
        {
            var option = optionsBuilder.Options.FindExtension<CustomOptionsExtension>()?.Clone() ?? new CustomOptionsExtension();
            if (option.Processors.Count == 0)
                optionsBuilder.ReplaceService<IQueryTranslationPreprocessorFactory, CustomQueryTranslationPreprocessorFactory>();
            else
                option.Processors.Remove(processor);
            option.Processors.Add(processor);
            ((IDbContextOptionsBuilderInfrastructure)optionsBuilder).AddOrUpdateExtension(option);
            return optionsBuilder;
        }
    }
}

namespace Microsoft.EntityFrameworkCore.Infrastructure
{
    public class CustomOptionsExtension : IDbContextOptionsExtension
    {
        public CustomOptionsExtension() { }
        private CustomOptionsExtension(CustomOptionsExtension copyFrom) => Processors = copyFrom.Processors.ToList();
        public CustomOptionsExtension Clone() => new CustomOptionsExtension(this);
        public List<IQueryPreprocessor> Processors { get; } = new List<IQueryPreprocessor>();
        ExtensionInfo info;
        public DbContextOptionsExtensionInfo Info => info ?? (info = new ExtensionInfo(this));
        public void Validate(IDbContextOptions options) { }
        public void ApplyServices(IServiceCollection services)
            => services.AddSingleton<IEnumerable<IQueryPreprocessor>>(Processors);
        private sealed class ExtensionInfo : DbContextOptionsExtensionInfo
        {
            public ExtensionInfo(CustomOptionsExtension extension) : base(extension) { }
            new private CustomOptionsExtension Extension => (CustomOptionsExtension)base.Extension;
            public override bool IsDatabaseProvider => false;
            public override string LogFragment => string.Empty;
            public override void PopulateDebugInfo(IDictionary<string, string> debugInfo) { }
            public override long GetServiceProviderHashCode() => Extension.Processors.Count;
        }
    }
}

namespace Microsoft.EntityFrameworkCore.Query
{
    public interface IQueryPreprocessor
    {
        Expression Process(Expression query);
    }

    public class CustomQueryTranslationPreprocessor : RelationalQueryTranslationPreprocessor
    {
        public CustomQueryTranslationPreprocessor(QueryTranslationPreprocessorDependencies dependencies, RelationalQueryTranslationPreprocessorDependencies relationalDependencies, IEnumerable<IQueryPreprocessor> processors, QueryCompilationContext queryCompilationContext)
            : base(dependencies, relationalDependencies, queryCompilationContext) => Processors = processors;
        protected IEnumerable<IQueryPreprocessor> Processors { get; }
        public override Expression Process(Expression query)
        {
            foreach (var processor in Processors)
                query = processor.Process(query);
            return base.Process(query);
        }
    }

    public class CustomQueryTranslationPreprocessorFactory : IQueryTranslationPreprocessorFactory
    {
        public CustomQueryTranslationPreprocessorFactory(QueryTranslationPreprocessorDependencies dependencies, RelationalQueryTranslationPreprocessorDependencies relationalDependencies, IEnumerable<IQueryPreprocessor> processors)
        {
            Dependencies = dependencies;
            RelationalDependencies = relationalDependencies;
            Processors = processors;
        }
        protected QueryTranslationPreprocessorDependencies Dependencies { get; }
        protected RelationalQueryTranslationPreprocessorDependencies RelationalDependencies { get; }
        protected IEnumerable<IQueryPreprocessor> Processors { get; }
        public QueryTranslationPreprocessor Create(QueryCompilationContext queryCompilationContext)
            => new CustomQueryTranslationPreprocessor(Dependencies, RelationalDependencies, Processors, queryCompilationContext);
    }
}

You don't need to understand that code. Most (if not all) of it is a boilerplate plumbing code to support the currently missing IQueryPreprocessor and AddQueryPreprocesor (similar to recently added interceptors). I'll update it if EF Core adds that functionality in the future.

Now you can use it to plug the DelegateDecompiler into EF Core:

using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Query;
using DelegateDecompiler;

namespace Microsoft.EntityFrameworkCore
{
    public static class DelegateDecompilerDbContextOptionsExtensions
    {
        public static DbContextOptionsBuilder AddDelegateDecompiler(this DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.AddQueryPreprocessor(new DelegateDecompilerQueryPreprocessor());
    }
}

namespace Microsoft.EntityFrameworkCore.Query
{
    public class DelegateDecompilerQueryPreprocessor : IQueryPreprocessor
    {
        public Expression Process(Expression query) => DecompileExpressionVisitor.Decompile(query);
    }
}

A lot of code just to be able to call

DecompileExpressionVisitor.Decompile(query)

before EF Core processing, but now all you need is to call

optionsBuilder.AddDelegateDecompiler();

in your derived context OnConfiguring override, and all your EF Core LINQ queries will be preprocessed and decompiled bodies injected.

With you examples

ctx.Items.Select(x => x.MapToMinimalItem())

will automatically be converted to

ctx.Items.Select(x => new
{
    Id = x.Id,
    Property1 = x.Property1
}

thus translated by EF Core to

// SELECT i."Id", i."Property1" FROM "Items" AS I

which was the goal.

Additionally, composing over projection also works, so the following query

ctx.Items
    .Select(x => x.MapToMinimalItem())
    .Where(x => x.Property1 == "abc")
    .ToList();

originally would have generated runtime exception, but now translates and runs successfully.

Entity Framework does not know anything about your MapToMinimalItem method and how to translate it into SQL, so it fetches whole entity and performs the Select on the client side.

If you take a closer look at the EF LINQ method signatures, you will see, that IQueryable operates with Expression's of Func (Select for example) instead of Funcs as it's IEnumerable counterpart, so underlying provider could analyze the code and generate what is needed (SQL in this case).

So if you want to move the projection code into separate method this method should return Expression, so EF could transform it into SQL. For example:

public static class ItemExtensionMethods
{
    public static readonly Expression<Func<Item, MinimalItem>> MapToMinimalItemExpr = 
        source => new MinimalItem
        {
            Id = source.Id,
            Property1 = source.Property1
        };
}

though it will have limited usability caused you will not able to reuse it nested projections, only in simple like this:

ctx.Items.Select(ItemExtensionMethods.MapToMinimalItemExpr)