是否有可能使用使用LINQ /接入标量函数到SQL？ [英] Is it possible to use/access scalar functions with LINQ to SQL?

问题描述

我们已经在我们的数据库标量函数用于返回像一个客户的任务数或发票总金额为客户。

We have scalar functions in our database for returning things like "number of tasks for a customer" or "total invoice amount for a customer".

我们正在试验，并希望尝试这样做W / O存储过程通常...我们只想调用该函数在存储过程并返回它作为一个单一值。

We are experimenting and looking to try to do this w/o stored procedures ... normally we would just call this function in our stored procedure and return it as a single value.

是否有使用或访问标量函数与LINQ to SQL中的方法吗？如果是这样，我有兴趣看看如何...如果不是，怎么会是最好地处理这种情况...如果它是可行的，甚至一个例子。

Is there a way to use or access scalar functions with LINQ to SQL? If so, I would be interested in see an example of how to ... if not, how would it be best to handle this type of situation ... if it is even doable.

推荐答案

LINQ到SQL支持使用UDF的使用，如果这是你的意思。只需拖动UDF到设计表面即可大功告成。这将创建的数据上下文匹配的方法，标志着 [功能（...，IsComposable = TRUE）] 或类似的，告诉LINQ到SQL，它可以在查询中使用它（注意，EF不支持此。使用）

LINQ-to-SQL supports use with UDFs, if that is what you mean. Just drag the UDF onto the designer surface and you're done. This creates a matching method on the data-context, marked [Function(..., IsComposable=true)] or similar, telling LINQ-to-SQL that it can use this in queries (note that EF doesn't support this usage).

您会然后用它在您的查询，如：

You would then use it in your query like:

var qry = from cust in ctx.Custs
           select new {Id = cust.Id, Value = ctx.GetTotalValue(cust.Id)};

这将成为TSQL是这样的：

which will become TSQL something like:

SELECT t1.Id, dbo.MyUdf(t1.Id)
FROM CUSTOMER t1

（或有-abouts）

(or there-abouts).

的事实是，你可以在查询中使用值组合的方式 - 在例如一个其中（） / WHERE - ，从而减少数据从服务器带回（虽然很明显的UDF还是会需要以某种方式来执行）。

The fact that it is composable means that you can use the value in queries - for example in a Where()/WHERE - and so reduce the data brought back from the server (although obviously the UDF will still need to be executed in some way).

这里的一个类似的例子时，表示在上一个数据上下文使用伪UDF中，示出了该方法的C＃版本不被使用。

Here's a similar example, showing a pseudo-UDF at use on a data-context, illustrating that the C# version of the method is not used.

事实上，我目前正在研究这样的UDF以一个组合的方式提供出模型的数据 - 即系统的特定部分需要访问某些数据（即恰好是在同一个数据库）是不是真的一样模型的一部分​​，但我想加入以有趣的方式。我也有现有的SP为此...所以我期待在这些移植来的SP表格UDF的，它提供了周围超出模型的数据合同/抽象水平。所以，因为这不是我的模型的一部分​​，我只能通过UDF得到它 - 但我保留我的正常模式，这个组合的能力。

Actually, I'm currently looking at such UDFs to provide "out of model" data in a composable way - i.e. a particular part of the system needs access to some data (that happens to be in the same database) that isn't really part of the same model, but which I want to JOIN in interesting ways. I also have existing SPs for this purpose... so I'm looking at porting those SPs to tabular UDFs, which provides a level of contract/abstraction surrounding the out-of-model data. So because it isn't part of my model, I can only get it via the UDF - yet I retain the ability to compose this with my regular model.

这篇关于是否有可能使用使用LINQ /接入标量函数到SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！