CODEWITHSUNDEEP
c#entity-framework-6
André Reichelt
André Reichelt

Reputation: 1631

Map custom database value function in Entity Framework

I have a simple database function, which expects two strings as parameter and returns a string. I want to map this with entity framework. Similar to this question, I've created a simple function header:

[DbFunction("dbo", "StripCharacters")]
public static string StripCharacters(string input, string pattern = "^A-Z0-9") => throw new NotSupportedException();

Like in the linked post, I get the same error message, as soon as I try to use this method inside one of my queries. The exception message is:

Method System.String StripCharacters(System.String, System.String) in type DE.ZA.TrailerLoadingAssistant.Web.Models.DatabaseEntities cannot be translated into a LINQ to Entities store expression

await mapi.db.TrailerAutocompleteHelpers
    .Where(t => t.SearchString.Contains(DatabaseEntities.StripCharacters(userInput, "^A-Z0-9")))
    .ToListAsync();

Here's the database function:

CREATE FUNCTION [dbo].[StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX) WITH SCHEMABINDING
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END

How can I solve this problem?

Upvotes: 2

Views: 464

Answers (1)

André Reichelt
André Reichelt

Reputation: 1631

I actually made two mistakes. First off, you must add a function declaration to your EDMX file by hand:

<Function Name="StripCharacters" ReturnType="nvarchar" Schema="dbo" >
  <Parameter Name="String" Mode="In" Type="nvarchar" />
  <Parameter Name="MatchExpression" Mode="In" Type="varchar" />
</Function>

Secondly, the first parameter of the DbFunction attribute must not be the schema name of your database, but the Entity Framework model namespace. This can be found in the EDMX file again:

<Schema Namespace="MyApplicationModel.Store" ...>

The correct attribute would be:

[DbFunction("MyApplicationModel.Store", "StripCharacters")]

Upvotes: 2

Related Questions