LINQ to SQL: Cannot use SQL Server UDF in LINQ query

Question

I am doing LINQ in Sql. To have good performance, I am trying to filter out some records on the SQL Server side. Therefore, I want to use the user-defined function (UDF) GiveWordCount available in my database. Based on this post, this is what I am trying to use on the C # side to use this UDF.

Declare the body of a dummy function 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."); } 

Then I call this function in my LINQ query, as shown below. The call to the GiveWordCount function must be converted by the LINQ to Sql provider to an equivalent embedded SQL server function before the final SQL query is launched into the database. Instead, this results in an error:

The 'Int32 GiveWordCount (System.String)' method is not supported by SQL translation.

Here is my main function:

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

Here is my client class:

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

Answer 1

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

eg. Adding a Mapping to an 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 } } } 

Then the method is called as follows:

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