EF Core 映射到 Select 中的对象时查询 SQL 中的所有列 [英] EF Core queries all columns in SQL when mapping to object in Select

查看:28
本文介绍了EF Core 映射到 Select 中的对象时查询 SQL 中的所有列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在尝试使用 EF Core 组织一些数据访问代码时,我注意到生成的查询比以前更糟糕,它们现在查询不需要的列.基本查询只是从一个表中选择并将列的子集映射到 DTO.但是在重写之后,现在所有的列都被获取了,而不仅仅是 DTO 中的那些.

While trying to organize some data access code using EF Core I noticed that the generated queries were worse than before, they now queried columns that were not needed. The basic query is just selecting from one table and mapping a subset of columns to a DTO. But after rewriting it now all columns are fetched, not just the ones in the DTO.

我创建了一个最小示例,其中包含一些显示问题的查询:

I created a minimal example with some queries that show the problem:

ctx.Items.ToList();
// SELECT i."Id", i."Property1", i."Property2", i."Property3" FROM "Items" AS i


ctx.Items.Select(x => new
{
  Id = x.Id,
  Property1 = x.Property1
}
).ToList();
// SELECT i."Id", i."Property1" FROM "Items" AS i

ctx.Items.Select(x => new MinimalItem
{
  Id = x.Id,
  Property1 = x.Property1
}
).ToList();
// SELECT i."Id", i."Property1" FROM "Items" AS i

ctx.Items.Select(
  x => x.MapToMinimalItem()
).ToList();
// SELECT i."Id", i."Property1", i."Property2", i."Property3" FROM "Items" AS i

ctx.Items.Select(
  x => new MinimalItem(x)
).ToList();

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

对象定义如下:

  public class Item
  {
    public int Id { get; set; }
    public string Property1 { get; set; }
    public string Property2 { get; set; }
    public string Property3 { get; set; }

  }

  public class MinimalItem
  {
    public MinimalItem() { }

    public MinimalItem(Item source)
    {
      Id = source.Id;
      Property1 = source.Property1;
    }
    public int Id { get; set; }
    public string Property1 { get; set; }
  }

  public static class ItemExtensionMethods
  {
    public static MinimalItem MapToMinimalItem(this Item source)
    {
      return new MinimalItem
      {
        Id = source.Id,
        Property1 = source.Property1
      };
    }
  }

第一个查询按预期查询所有列,第二个使用匿名对象的查询仅查询选定的查询,一切正常.使用我的 MinimalItem DTO 也可以使用,只要它是直接在 Select 方法中创建的.但最后两个查询获取所有列,即使它们与第三个查询完全相同,只是分别移动到构造函数或扩展方法.

The first query queries all columns as intended, and the second query with an anonymous object only queries the selected queries, that works all fine. Using my MinimalItem DTO also works as long as it is created directly in the Select method. But the last two queries fetch all columns even though they do exactly the same thing as the third query, just moved to a constructor or an extension method, respectively.

显然 EF Core 无法遵循此代码并确定如果我将其移出 Select 方法,它只需要两列.但我真的很想这样做,以便能够重用映射代码,并使实际查询代码更易于阅读.如何提取这种简单的映射代码,而不会使 EF Core 一直低效地获取所有列?

Obviously EF Core can't follow this code and determine that it only needs the two columns if I move it out of the Select method. But I'd really like to do that to be able to reuse the mapping code, and make the actual query code easier to read. How can I extract this kind of straightforward mapping code without making EF Core inefficiently fetching all columns all the time?

推荐答案

这是 IQueryable 从一开始就存在的基本问题,这么多年后没有现成的解决方案.

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

问题在于 IQueryable 翻译和代码封装/可重用性是相互排斥的.IQueryable 翻译是基于预先的知识,这意味着查询处理器必须能够看到"实际代码,然后翻译已知"的方法/属性.但是自定义方法/可计算属性的内容在运行时不可见,因此查询处理器通常会失败,或者在支持客户端评估"的有限情况下(EF Core 仅对最终预测执行此操作),它们会生成低效的翻译,从而检索很多比您的示例中需要的数据多.

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.

总结一下,C# 编译器和 BCL 都没有帮助解决这个核心问题".一些 3rd 方库正试图在不同程度上解决它 - LinqKitNeinLinq 和类似的.它们的问题在于,除了调用诸如 AsExpandable()ToInjectable() 等特殊方法外,它们还需要重构您现有的代码.

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.

最近我发现了一个名为 DelegateDecompiler 的小宝石,它使用了另一个名为 Mono.Reflection.Core 将方法体反编译为其 lambda 表示.

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

使用起来非常简单.安装后,您只需要使用自定义提供的 [Computed][Decompile] 属性标记您的自定义方法/计算属性(只需确保使用表达式样式实现和不是代码块),并在 IQueryable 链中的某处调用 Decompile()DecompileAsync() 自定义扩展方法.它不适用于构造函数,但支持所有其他构造.

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

现在

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

生产

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

这种方法(和其他 3rd 方库)的唯一问题是需要调用自定义扩展方法 Decompile,以便使用自定义提供程序包装可查询,以便能够预处理最终查询表达式.

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.

如果 EF Core 允许在其 LINQ 查询处理管道中插入自定义查询表达式预处理器就太好了,从而消除了在每个查询中调用自定义方法的需要,这很容易被遗忘,而且自定义查询提供程序也不会播放与 EF Core 特定的扩展(如 AsTrackingAsNoTrackingInclude/ThenInclude)配合得很好,所以它真的应该被称为 之后等

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.

目前有一个未解决的问题 请打开扩展 #19748 的查询翻译管道我试图说服团队添加一种简单的方法来添加表达式预处理器.您可以阅读讨论并投票.

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.

在那之前,这是我对 EF Core 3.1 的解决方案:

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

您不需要理解该代码.其中大部分(如果不是全部)是样板管道代码,用于支持当前缺少的 IQueryPreprocessorAddQueryPreprocesor(类似于最近添加的拦截器).如果 EF Core 将来添加该功能,我会对其进行更新.

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.

现在您可以使用它来将 DelegateDecompiler 插入到 EF Core 中:

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)

在 EF Core 处理之前,但现在您只需要调用

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

optionsBuilder.AddDelegateDecompiler();

在您的派生上下文中 OnConfiguring 覆盖,您的所有 EF Core LINQ 查询都将被预处理和反编译体注入.

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

用你的例子

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

会自动转换为

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

因此被 EF Core 翻译为

thus translated by EF Core to

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

这是目标.

另外,组合投影也可以,所以下面的查询

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.

这篇关于EF Core 映射到 Select 中的对象时查询 SQL 中的所有列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆