LINQ to SQL:无法在 LINQ 查询中使用 SQL Server UDF

问题描述

我正在做 LINQ to Sql.为了获得良好的性能，我正在尝试过滤 SQL Server 端本身的一些记录.所以我想使用我的数据库中可用的用户定义函数 (UDF) GiveWordCount.基于 这篇 帖子，这里是我正在尝试在 C# 方面使用该 UDF.

I'm doing LINQ to Sql. To have a good performance I'm trying to filter some records on the SQL Server side itself. So I want to use a user defined function (UDF) GiveWordCount available in my database. Based on this post here is what I'm trying on the C# side to use that UDF.

声明一个虚拟函数体，以便 C# 代码可以编译:

Declare a dummy function body so that C# code can compile:

[Function(Name = "dbo.GiveWordCount", IsComposable = true)]
public static int GiveWordCount([Parameter(Name="@inputValue",DbType="nvarchar(4000)")]string inputValue)
    {
        throw new NotSupportedException("Direct calls are not supported.");
    }

然后我在我的 LINQ 查询中调用这个函数，如下所示.在最终 SQL 查询被触发到数据库之前，对GiveWordCount"函数的调用应该由 LINQ to Sql 提供程序转换为等效的内置 sql server 函数.相反，它会导致错误:

Then I call this function in my LINQ query as show below. Call to 'GiveWordCount' function should get converted by LINQ to Sql provider into an equivalent in-built sql server function before the final SQL query gets fired onto the database. Instead it results in an error:

不支持方法 'Int32 GiveWordCount(System.String)'翻译成 SQL.

Method 'Int32 GiveWordCount(System.String)' has no supported translation to SQL.

这是我的主要功能:

static void Main(string[] args)         
{
    DataContext dataContext = new DataContext("data source=.;initial catalog=businessLinqToSql;integrated security=True;MultipleActiveResultSets=True");

    Table<Customer> customers = dataContext.GetTable<Customer>();
    IEnumerable<string> b = customers.Where(customer => GiveWordCount(customer.Name) <= 2).Select(x => x.Name);


    foreach (string element in b) Console.WriteLine(element);
}

这是我的客户类:

 [Table]
    public class Customer
    {
        [Column]
        public string Name { get; set; }
    }

推荐答案

我认为该方法需要位于与现有 Linq to Sql DataContext 具有相同命名空间和类名称的分部类中.如果您右键单击您的 dbml 文件 datacontext.dbml => View Code，它将为您生成空的部分类.

I think the method needs to be in a partial class with the same Namespace and Class name as your existing Linq to Sql DataContext. If you right click your dbml file datacontext.dbml => View Code, it will generate the empty partial class for you.

例如添加映射到 ISNUMERIC SQL 函数

E.g. Adding a mapping to the ISNUMERIC SQL Function

using System.Data.Linq.Mapping;
namespace SameNamespaceAsDataContext
{
    partial class SameClassnameAsDataContext
    {
        [Function(Name = "ISNUMERIC", IsComposable = true)]
        public int IsNumeric(string input)
        {
            throw new NotImplementedException(); // this won't get called
        }
    }
}

然后调用该方法如下:

IQueryable<int> results = MyDataContext.Table.Select(x => IsNumeric(x.Column));

这篇关于LINQ to SQL:无法在 LINQ 查询中使用 SQL Server UDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！