问题描述

我们的数据库中有标量函数，用于返回诸如客户的任务数"或客户的总发票金额"之类的信息.

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

我们正在试验并希望尝试在没有存储过程的情况下执行此操作……通常我们只会在存储过程中调用此函数并将其作为单个值返回.

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-to-SQL 支持与 UDF 一起使用，如果这就是您的意思的话.只需将 UDF 拖到设计器表面上即可.这会在数据上下文中创建一个匹配方法，标记为 [Function(..., IsComposable=true)] 或类似的，告诉 LINQ-to-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

(或那里的相关信息).

(or there-abouts).

它是可组合的这一事实意味着您可以在查询中使用该值 - 例如在 Where()/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 以可组合的方式提供模型外"数据 - 即系统的特定部分需要访问某些数据(恰好位于同一数据库中)'实际上不是同一模型的一部分，但我想以有趣的方式JOIN.我也有用于此目的的现有 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.