LINQ to SQL:无法在LINQ查询中使用SQL Server UDF [英] LINQ to SQL : Unable to use SQL Server UDFs in LINQ query

问题描述

我正在对SQL执行LINQ.为了获得良好的性能，我正在尝试在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查询中调用此函数，如下所示. LINQ to Sql provider应该将对"GiveWordCount"函数的调用转换为等效的内置sql服务器函数，然后将最终的SQL查询激发到数据库上.而是会导致错误:

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

然后按如下所示调用该方法:

The method is then called as follows:

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

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