如何强制 EF 使用联接而不是拆分复杂的查询

本文关键字:拆分 复杂 查询 EF 何强制 | 更新日期: 2023-09-27 17:53:19

我有一个复杂的 IQueryable,我希望 EF 使用单个数据库查询填充它,以便我可以将其与延迟执行一起使用。请考虑以下示例。

对于这些型号:

public enum AlphaState { Unknown = ''0', Good = 'G', Bad = 'B' }
[Table("MY_ALPHA")]
public class Alpha
{
    [Key]
    [Column("alpha_index")]
    public long Index { get; set; }
    [Column("alpha_id")] // user-editable field that users call ID
    public string AlphaId { get; set; }
    [Column("deleted")]
    public char? Deleted { get; set; }
    [Column("state")]
    public AlphaState State { get; set; }
    [InverseProperty("Alpha")]
    public ICollection<Bravo> Bravos { get; set; }
}
[Table("MY_BRAVO")]
public class Bravo
{
    [Key]
    [Column("bravo_index")]
    public long BravoIndex { get; set; }
    [ForeignKey("Alpha")]
    [Column("alpha_index")] // actually a 1:0..1 relationship
    public long? AlphaIndex { get; set; }
    public virtual Alpha Alpha { get; set; }
    [InverseProperty("Bravo")]
    public ICollection<Charlie> Charlies { get; set; }
}
[Table("MY_CHARLIE_VIEW")]
public class Charlie
{
    [Key]
    [Column("charlie_index")]
    public int CharlieIndex { get; set; }
    [Column("deleted")]
    public char? Deleted { get; set; }
    [Column("created_at")]
    public DateTime CreatedAt { get; set; }
    [ForeignKey("Bravo")]
    [Column("bravo_index")]
    public long BravoIndex { get; set; }
    public virtual Bravo Bravo { get; set; }
    [ForeignKey("Delta")]
    [Column("delta_index")]
    public long DeltaIndex { get; set; }
    public virtual Delta Delta { get; set; }
    [InverseProperty("Charlie")]
    public virtual ICollection<Delta> AllDeltas { get; set; }
}
[Table("MY_DELTA")]
public class Delta
{
    [Key]
    [Column("delta_index")]
    public long DeltaIndex { get; set; }
    [ForeignKey("Charlie")]
    [Column("charlie_index")]
    public long CharlieIndex { get; set; }
    public virtual Charlie Charlie { get; set; }
    [InverseProperty("Delta")] // actually a 1:0..1 relationship
    public ICollection<Echo> Echoes { get; set; }
}
public enum EchoType { Unknown = 0, One = 1, Two = 2, Three = 3 }
[Table("MY_ECHOES")]
public class Echo
{
    [Key]
    [Column("echo_index")]
    public int EchoIndex { get; set; }
    [Column("echo_type")]
    public EchoType Type { get; set; }
    [ForeignKey("Delta")]
    [Column("delta_index")]
    public long DeltaIndex { get; set; }
    public virtual Delta Delta { get; set; }
}

。请考虑以下查询:

IQueryable<Alpha> result = context.Alphas.Where(a => a.State == AlphaState.Good)
                                         .Where(a => !a.Deleted.HasValue)
                                         .Where(a => a.Bravos.SelectMany(b => b.Charlies)
                                                             .Where(c => !c.Deleted.HasValue)
                                                             .Where(c => c.Delta.Echoes.Any())
                                                             .OrderByDescending(c => c.CreatedAt).Take(1)
                                                             .Any(c => c.Delta.Echoes.Any(e => e.Type == EchoType.Two)))
var query = result as System.Data.Objects.ObjectQuery;
string queryString = query.ToTraceString();

注意:查理实际上是桌子上的视图;Delta 对 Charlie 的表有一个 FK,但该视图为链接到该 Charlie 的最新 Delta 提供了一个假 FK,因此模型使用该 FK,因为计划仅将 EF 用于查询,而不用于更新。

我希望此查询由单个数据库查询填充,但正如所写的那样,这不是正在发生的事情。如何修改此查询以获得相同的结果,但让 EF 仅将条件构建到results IQueryable 中,而不是为其预取数据?

我怎么知道它使用了两个查询

确信它被拆分为多个查询,因为出于超出此问题范围的原因,我故意为上下文提供了错误的连接字符串。 result 是一个 IQueryable,所以它应该使用延迟执行,并且在使用它之前不会实际尝试检索任何数据,但是一旦我声明它,我就会收到连接失败的异常。

背景

我们有一个现有的数据库结构,数据库访问层,以及使用上述结构和DAL的数十万行代码。我们希望添加一个 UI 以允许用户构建自己的复杂查询,EF 似乎是为此构建基础模型的好方法。但是,我们以前从未使用过 EF,因此 Powers That Be 声明它永远无法连接到数据库;我们应该使用 EF 生成 IQueryable,从中提取查询字符串,并使用现有的 DAL 运行查询。

如何强制 EF 使用联接而不是拆分复杂的查询

我怎么知道它使用了两个查询

你观察到的不是 EF 开始运行查询。将查询分配给result变量后,您仍然具有查询定义,而不是结果集。如果将探查器附加到数据库,您将看到尚未为查询执行SELECT语句。

那么,为什么要与数据库建立连接呢?原因是首次为给定派生DbContext类型生成查询时,EF 会生成并缓存该类型的内存中模型。它通过将各种约定应用于您定义的类型、属性和属性来实现此目的。从理论上讲,此过程不需要连接到数据库,但 SQL Server 的提供程序无论如何都会这样做。这样做是为了确定您正在使用的 SQL Server 版本,以便确定它是否可以在其生成的模型中使用更新的 SQL Server 功能。

有趣的是,此模型是为类型而不是上下文的实例缓存的。您可以通过释放context然后创建一个新并重复构建查询的代码行来查看这一点。在第二个实例中,你根本看不到与数据库的连接,因为 EF 会将其缓存的模型用于上下文类型。

Powers That Be 已声明它永远无法连接到数据库;我们应该使用 EF 生成 IQueryable,从中提取查询字符串,并使用我们现有的 DAL 运行查询。

由于需要完全避免让 EF 连接到数据库,因此可以在此处查看我的帖子,其中包含有关如何在代码中预先提供此信息的详细信息。

此外,请注意,EF 在第一次遇到你的DbContext类型时可能会连接到你的服务器的另一个原因:初始化。除非您禁用了初始化(使用类似 Database.SetInitializer<MyContext>(null) 的内容(,否则它将检查数据库是否存在,如果不存在,则尝试创建它。

请注意,可以直接对 EF 代码优先查询调用ToString()以获取 T-SQL 查询。无需通过中间ObjectQuery.ToTraceString()方法,该方法实际上是旧版 EF API 的一部分。但是,这两种方法都用于调试和日志记录目的。使用 EF 生成查询但不执行查询是相当不寻常的。使用此方法时可能会遇到问题,最明显的是当 EF 确定它应生成参数化查询时。此外,无法保证不同版本的 EF 将为同一输入生成类似的 T-SQL,因此在更新到较新的 EF 版本时,代码最终可能会变得相当脆弱。确保您有足够的测试!

如果您担心让用户直接连接到数据库(这是一个完全合理的安全问题(,则可以考虑另一种方法。我对此没有太多经验,但似乎 OData 可能很合适。这允许您在客户端上生成查询,通过远程连接序列化它们,然后在服务器上重新创建它们。然后,在服务器上,您可以针对数据库执行它们。您的客户不需要对数据库一无所知。

如果您决定(或被指示(坚持使用问题中详述的方法,请花时间学习如何分析 SQL Server 连接。这将是你确定 EF 如何翻译查询的绝对必要工具。

context.Foos.Where(f => f.Bars.Any(b => b.SomeOtherData == "baz"));

我在我拥有的数据库(使用 LINQPad(上尝试了类似于您的查询,最终得到

SELECT 
  [Extent1].[Property1] AS [Property1]
  -- other properties of Foo
  FROM [dbo].[Foos] AS [Extent1]
  WHERE EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[Bars] AS [Extent2]
    WHERE ([Extent1].[FooId] = [Extent2].[FooId]) AND (N'baz' = [Extent2].[SomeOtherData])
  )

。对我来说,这绝对看起来像一个查询。

IQueryable 函数中的表达式不会直接执行 - 它们用于生成 SQL,然后在需要具体化结果时用于执行查询。

尝试使用 LINQ 查询语法。

var result = (
    from f in foo
    join b in bar
        on f.fooid equals b.fooid
    where b.someotherdata = "baz"
    select new { f.fooid, f.somedata }
).Distinct().ToEnumerable();

这将推迟到枚举。