Map custom database value function in Entity Framework

Question

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?

André Reichelt · Accepted Answer · 2019-09-27 14:05:17Z

2

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")]

answered Sep 27, 2019 at 14:05

André Reichelt

1,6691 gold badge24 silver badges63 bronze badges

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

Collectives™ on Stack Overflow

Map custom database value function in Entity Framework

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related