在Entity Framework中映射自定义数据库值功能 [英] 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:

类型为DE.ZA.TrailerLoadingAssistant.Web.Models.DatabaseEntities的方法System.String StripCharacters(System.String, System.String)无法转换为LINQ to Entities存储表达式

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

这是数据库功能:

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?

推荐答案

我实际上犯了两个错误.首先，您必须手动将函数声明添加到EDMX文件中:

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>

第二，DbFunction属性的第一个参数不得为数据库的架构名称，而应为Entity Framework模型名称空间.可以再次在EDMX文件中找到它:

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

正确的属性是:

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

这篇关于在Entity Framework中映射自定义数据库值功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！