How to register CLR User Defined Function for use in linq query?

Question

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

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).

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

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

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();
    }
}

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

sorry I realised that wasn't clear. I edited it to: What further steps are required in order for me to be able to use it in a linq query? — Carmax
– Carmax, Commented Mar 27, 2013 at 21:38
any way that would allow me to call it in this fashion would be acceptable: db.Items.Where(i => i.CustomFormatFunction() == xyz); — Carmax
– Carmax, Commented Mar 28, 2013 at 13:22

p.s.w.g · Accepted Answer · 2013-03-27 22:50:28Z

1

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);
}

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

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"); 
}

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.

edited Mar 27, 2013 at 22:50

answered Mar 27, 2013 at 22:43

p.s.w.g

150k31 gold badges307 silver badges339 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Carmax Over a year ago

Hi again, and thanks for the answer. So should I be investigating how to put the 2 x Replace functions (SqlFunction and EdmFunction) into a separate assembly from the rest of the code? Or would they conflict and therefore need to be put in 2 separate assemblies?

p.s.w.g Over a year ago

@MartinHansenLennox I recommend creating an assembly exclusively for SqlFunction methods. The EdmFunction can remain with in the same assembly as your EdmModel. You won't need to reference the SqlFunction assembly at all.

Carmax Over a year ago

We have it sorted... thanks ever so much for your persistent help on this one

Collectives™ on Stack Overflow

How to register CLR User Defined Function for use in linq query?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related