Custom Method in LINQ to SQL query

Question

Is it possible to use custom method In query for example:

var result = from u in context.MyTable where MyMethod(u) == 10 select u;

Answer 1

Yes, but if you are using Linq-to-Sql - your method has to have special code to handle to SQL conversion.

Answer 2

Check this full article : What is and what isn't possible with linq

Following is not possible

// function used in filter
static bool MyFunc(Nwind.Product p)
{
  return p.ProductName.StartsWith("B");
}
// query that uses MyFunc
var q = 
  from p in db.Products
  where MyPriceFunc(p.UnitPrice) > 30m
  select p

It compiles with no errors, but when you execute it LINQ to SQL throws an exception saying: "Static method System.Boolean MyTest(LINQTest.Nwind.Product) has no supported translation to SQL."

The exception is actually thrown when you try to fetch results from q (for example using the foreach statement), because LINQ to SQL attempts to convert the expression trees to T-SQL only when the results are needed and the query must be executed.

To fix the example you can simply copy the code that checks whether product name starts with "B" to the where clause of the query and it would work fine.

Answer 3

As Pranay explains, you cannot have a custom (C#) method as part of the LINQ to SQL query, because LINQ to SQL wouldn't be able to look at the expression tree of the method and so it cannot translate it to SQL.

One option that you have is to write your function in SQL and store it as a SQL function on the SQL Server (possibly, you could also use SQL CLR, but I have not tried that). Then you can add the function to your DataContext type and LINQ to SQL will translate it to calls to the function on SQL server. Something like:

var result = from u in context.MyTable 
             where context.MyMethod(u) == 10 select u; 

The problem, of course, is that you'll need to write the function in SQL (I think SQL CLR could also work - not sure about the performance and other possible complications though)

I also wrote an article (some time ago) that shows how to do this when you write the "method" as an expression tree way (as a value of type Expression<Func<...>>), which is possible, because in this case, the code is compiled as an expression tree. However, there is some postprocessing that has to be done and you can still write just a single expression that can be easily inlined in the LINQ query.