LINQ to SQL : Unable to use SQL Server UDFs in LINQ query

Question

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.

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

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:

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

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 Customer Class:

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

Do you mean msdn.microsoft.com/en-us/library/…? That's not for LINQ to SQL - it's for Entity Framework. — Jon Skeet
– Jon Skeet, Commented Jun 14, 2016 at 14:12
ohh. Ok @JonSkeet . My original intention was to use a UDF (present in sql server) in a LINQ query. So first I was trying if IQueryable is able to convert in-built sql equivalent functions through SqlFunctions class or not when I started getting this error. It seems I will have to take some other route. I tried even this for my original problem statement but I get very same error which I've mentioned: stackoverflow.com/questions/3865310/… I'm using sql-clr integration to create my UDF. — RBT
– RBT, Commented Jun 14, 2016 at 14:33
The LINQ to SQL equivalent of SqlFunctions is SqlMethods - but it's not as broad. I don't know about UDFs... — Jon Skeet
– Jon Skeet, Commented Jun 14, 2016 at 14:46
Thanks @JonSkeet. This is great info. SqlMethods worked like a charm. Great relief! you saved my day. Seems like EF team who later took control of LINQ to SQL didn't update this class much as they wanted to give more push to Entity framework as an ORM solution. I will dig into more on how to leverage SQL Server UDFs in LINQ queries in C# code. — RBT
– RBT, Commented Jun 14, 2016 at 15:05
@JonSkeet I've modified my question for the original problem I was facing. I was using the following link to accomplish it but it doesn't seem to be working and I get very same error I was getting in case of SqlFunctions class stackoverflow.com/questions/3865310/… — RBT
– RBT, Commented Jun 15, 2016 at 0:27

Zac Faragher · Accepted Answer · 2017-10-12 01:17:15Z

0

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.

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

edited Oct 12, 2017 at 1:17

answered Oct 12, 2017 at 0:24

Zac Faragher

1,00114 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

LINQ to SQL : Unable to use SQL Server UDFs in LINQ query

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