如何注册CLR用户定义的函数用于LINQ查询用？ [英] How to register CLR User Defined Function for use in linq query?

问题描述

使用SQL Server 2008，我想比较之前运行在DB值的正则表达式。

Using SQL Server 2008 I'd like to run a regex on a DB value before comparing it.

我寻找到CLR用户定义函数（我调查EDM功能，但我得到的印象是，UDF是用正则表达式更合适 - 请纠正我，如果我错了）。

I'm looking into CLR User-Defined Functions (I investigated EDM Functions but I got the impression that UDFs were more appropriate with a regex - please correct me if I'm wrong).

在理想情况下，我想作LINQ电话是这样的：

Ideally I'd like to make a linq call like this:

var results= db.Items.Where(i => i.CustomFormatFunction() == xyz);

到目前为止，我有这个C＃代码：

So far I have this c# code:

public static partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlString CustomFormatFunction(string str)
    {
        return Regex.Replace(Regex.Replace(HttpUtility.HtmlDecode(str), @"\s+", "-"), "[^a-zA-Z0-9/-]+", "").ToLower();
    }
}

为了需要哪些进一步的措施，我是能够在LINQ查询使用它？

What further steps are required in order for me to be able to use it in a linq query?

推荐答案

其实，我编写了这个确切的功能，前一段时间。煤矿更是一个位通用，但它看起来是这样的：

I actually coded up this exact function some time ago. Mine's a bit more general purpose, but it looks like this:

[SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true)]
public static string Replace(string input, string pattern, string replacement, int options)
{
    return Regex.Replace(input, pattern, replacement, (RegexOptions)options);
}

您接下来要在SQL中使用

You then have to register it in SQL with

CREATE ASSEMBLY [MyAssembly] 
FROM 'C:\Path\To\Assembly\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE FUNCTION [dbo].[Replace](@input [nvarchar](4000), @pattern [nvarchar](4000),  @replacement [nvarchar](4000), @options [int] = 0)
   RETURNS [nvarchar](4000) NULL
   WITH EXECUTE AS CALLER
   AS EXTERNAL NAME [MyAssembly].[MyNamespace.UserDefinedFunctions].[Replace]
GO

这将在SQL创建CLR-UDF。我从来没有尝试连接的回的一个LINQ查询，但我相信它会工作像任何其他EDM功能。

That will create your CLR-UDF in SQL. I've never tried linking back to a linq query, but I assume it would work like any other EDM function.

[EdmFunction("dbo", "Replace")] 
public static string Replace(Replace input, pattern, replace, int flags) 
{ 
    throw new NotSupportedException("Direct calls not supported"); 
}

您的可能的甚至可以把 SqlFunction 和 EdmFunction 属性上相同的方法，但是我不建议这样做（这也似乎是推动圆的限制）。我宁愿让我的CLR-UDF函数在一个完全独立的组件，因为它们变化非常频繁，我的组件从而消耗的数据是非常有活力。

You might even be able to put SqlFunction and EdmFunction attributes on the same method, but I don't recommend it (it also seems like pushing the limit of circularity). I prefer to keep my CLR-UDF functions in a completely separate assembly, since they change very infrequently and my assemblies which consume the data are very dynamic.

这篇关于如何注册CLR用户定义的函数用于LINQ查询用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！