LINQ-SQL重用-CompiledQuery.Compile [英] LINQ-SQL reuse - CompiledQuery.Compile

问题描述

我一直在使用LINQ-SQL，试图获取可重用的表达式块，然后将它们热插入其他查询中.所以，我从这样的事情开始:

I have been playing about with LINQ-SQL, trying to get re-usable chunks of expressions that I can hot plug into other queries. So, I started with something like this:

Func<TaskFile, double> TimeSpent = (t =>
t.TimeEntries.Sum(te => (te.DateEnded - te.DateStarted).TotalHours));

然后，我们可以在类似下面的LINQ查询中使用以上内容(以LINQPad为例):

Then, we can use the above in a LINQ query like the below (LINQPad example):

TaskFiles.Select(t => new {
    t.TaskId,
    TimeSpent = TimeSpent(t),
})

这将产生预期的输出，除外，这是为插入的表达式生成每行查询.这在LINQPad中可见.不好.

This produces the expected output, except, a query per row is generated for the plugged expression. This is visible within LINQPad. Not good.

无论如何，我注意到了CompiledQuery.Compile方法.尽管这将DataContext作为参数，但我认为我将忽略它，并尝试相同的Func.因此，我得出以下结论:

Anyway, I noticed the CompiledQuery.Compile method. Although this takes a DataContext as a parameter, I thought I would include ignore it, and try the same Func. So I ended up with the following:

static Func<UserQuery, TaskFile, double> TimeSpent =
     CompiledQuery.Compile<UserQuery, TaskFile, double>(
        (UserQuery db, TaskFile t) => 
        t.TimeEntries.Sum(te => (te.DateEnded - te.DateStarted).TotalHours));

请注意，我没有使用db参数.但是，现在，当我们使用此更新的参数时，仅生成 1 个SQL查询.表达式已成功转换为SQL，并包含在原始查询中.

Notice here, that I am not using the db parameter. However, now when we use this updated parameter, only 1 SQL query is generated. The Expression is successfully translated to SQL and included within the original query.

所以我的最终问题是，是什么使CompiledQuery.Compile如此特别?似乎根本不需要DataContext参数，在这一点上，我认为它是生成完整查询的更方便的参数.

So my ultimate question is, what makes CompiledQuery.Compile so special? It seems that the DataContext parameter isn't needed at all, and at this point i am thinking it is more a convenience parameter to generate full queries.

像这样使用CompiledQuery.Compile方法是否被认为是一个好主意?看来这是一个巨大的突破，但它似乎是LINQ重用的唯一可行途径.

Would it be considered a good idea to use the CompiledQuery.Compile method like this? It seems like a big hack, but it seems like the only viable route for LINQ re-use.

更新

在Where陈述中使用第一个Func，我们看到以下异常:

Using the first Func within a Where statment, we see the following exception as below:

NotSupportedException: Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

类似以下内容:

.Where(t => TimeSpent(t) > 2)

但是，当我们使用CompiledQuery.Compile生成的Func时，查询将成功执行并生成正确的SQL.

However, when we use the Func generated by CompiledQuery.Compile, the query is successfully executed and the correct SQL is generated.

我知道这不是重用Where语句的理想方法，但是它显示了表达式树是如何生成的.

I know this is not the ideal way to re-use Where statements, but it shows a little how the Expression Tree is generated.

推荐答案

执行摘要:

Expression.Compile生成CLR方法，而CompiledQuery.Compile生成代表SQL占位符的委托.

Expression.Compile generates a CLR method, wheras CompiledQuery.Compile generates a delegate that is a placeholder for SQL.

到目前为止，您没有得到正确答案的原因之一是示例代码中的某些内容不正确.而且，如果没有数据库或通用样本，其他人可以玩的机会将进一步减少(我知道很难提供这种机会，但这通常是值得的).

One of the reasons you did not get a correct answer until now is that some things in your sample code are incorrect. And without the database or a generic sample someone else can play with chances are further reduced (I know it's difficult to provide that, but it's usually worth it).

关于事实:

Expression<Func<TaskFile, double>> TimeSpent = (t =>
    t.TimeEntries.Sum(te => (te.DateEnded - te.DateStarted).TotalHours));

然后，我们可以在类似下面的LINQ查询中使用以上内容:

Then, we can use the above in a LINQ query like the below:

TaskFiles.Select(t => new {
    t.TaskId,
    TimeSpent = TimeSpent(t),
})

(注意:也许您为TimeSpent使用了Func<>类型.这会产生与您在以下段落中概述的情况相同的情况.但是请务必阅读并理解它).

(Note: Maybe you used a Func<> type for TimeSpent. This yields the same situation as of you're scenario was as outlined in the paragraph below. Make sure to read and understand it though).

不，这不会编译.无法调用表达式(TimeSpent是表达式).首先需要将它们编译为委托.调用Expression.Compile()时，实际上是将表达式树编译为IL，然后将其注入到DynamicMethod中，然后您将为其获得委托.

No, this won't compile. Expressions can't be invoked (TimeSpent is an expression). They need to be compiled into a delegate first. What happens under the hood when you invoke Expression.Compile() is that the Expression Tree is compiled down to IL which is injected into a DynamicMethod, for which you get a delegate then.

以下方法将起作用:

var q = TaskFiles.Select(t => new {
    t.TaskId,
    TimeSpent = TimeSpent.Compile().DynamicInvoke()
});  

这将产生预期的输出，除了每行的查询是 为插入的表达式生成.这在LINQPad中可见. 不好.

This produces the expected output, except, a query per row is generated for the plugged expression. This is visible within LINQPad. Not good.

为什么会这样?好吧，Linq To Sql将需要获取所有TaskFiles实例，并脱水TaskFile实例，然后在内存中对其运行选择器.每个TaskFile都有一个查询，可能是因为它们包含一个或多个1:m映射.

Why does that happen? Well, Linq To Sql will need to fetch all TaskFiles, dehydrate TaskFile instances and then run your selector against it in memory. You get a query per TaskFile likely because they contains one or multiple 1:m mappings.

尽管LTS允许在选择中在内存中进行投影，但对于Wheres则不行(需要引文，据我所知).当您考虑它时，这很有意义:您可能会通过过滤内存中的整个数据库，然后通过转换内存中的子集来传输更多数据. (尽管如您所见，它会产生查询性能问题，但是在使用ORM时要注意一些事项.)

While LTS allows projecting in memory for selects, it does not do so for Wheres (citation needed, this is to the best of my knowledge). When you think about it, this makes perfect sense: It is likely you will transfer a lot more data by filtering the whole database in memory, then by transforming a subset of it in memory. (Though it creates query performance issues as you see, something to be aware of when using an ORM).

CompiledQuery.Compile()做一些不同的事情.它将查询编译为SQL，并且返回的委托仅是内部使用的SQL占位符Linq.您不能在CLR中调用"此方法，它只能用作另一个表达式树中的节点.

CompiledQuery.Compile() does something different. It compiles the query to SQL and the delegate it returns is only a placeholder Linq to SQL will use internally. You can't "invoke" this method in the CLR, it can only be used as a node in another expression tree.

那么，为什么LTS会使用CompiledQuery.Compile表达式生成高效的查询?因为它知道此表达式节点的作用，因为它知道其背后的SQL.在Expression.Compile情况下，只是一个InvokeExpression调用了DynamicMethod，正如我之前解释的那样.

So why does LTS generate an efficient query with the CompiledQuery.Compile'd expression then? Because it knows what this expression node does, because it knows the SQL behind it. In the Expression.Compile case, it's just a InvokeExpression that invokes the DynamicMethod as I explained previously.

为什么它需要一个DataContext参数?是的，创建完整查询更为方便，但这还因为表达式树编译器需要了解用于生成SQL的映射.如果没有此参数，则很难找到此映射，因此这是一个非常明智的要求.

Why does it require a DataContext Parameter? Yes, it's more convenient for creating full queries, but it's also because the Expression Tree compiler needs to know the Mapping to use for generating the SQL. Without this parameter, it would be a pain to find this mapping, so it's a very sensible requirement.

这篇关于LINQ-SQL重用-CompiledQuery.Compile的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！