How to create a method that suport translation to sql?

Question

Hi,

I wanted to use a method I created inside a query coz i need to implement a peculiar type of filter...

return manager.Clients.SelectAll().Where(cli => cli.Name.SatisfyFilter(filter.Name) && cli.LastName.SatisfyFilter(filter.LastName) && cli.MiddleName.SatisfyFilter(filter.MiddleName)).ToList();

But i get the:

"Method 'Boolean SatisfyFilter(System.String, System.String)' has no supported translation to SQL."

Error

My method is:

public static bool SatisfyFilter(this string palavra, string filtro)

same thing as the

public bool Contains(string value)

in the string type, and Contains works just fine...

I need this method to run on the IQueryable, coz my table has 25 million clients...

I saw on the sql profiler that the Contains is transformed in sql...

How do i implement my method to send the correlative filter code to the sql? =/

Answer 1

A simple answer is you can't. The LINQ to SQL translator recognizes only some of the standard .NET methods and there is no way you could add others.

• If you need something like that, you'll need to create expression tree explicitly (to be used as the body of the lambda expression). A direct way of doing that is to use methods of the Expression class, but it can be simplfied a lot.

• Perhaps the best option is to use the LINQKit project. It makes it possible to call other lambda expressions (not quite method, but close). It provides an AsExpandable extension method that allows you to write:

  Expression<Func<Purchase, bool>> customFunction = ...
  var data = new MyDataContext();
  var query =
    from c in data.Purchases.AsExpandable()
    where customFunction.Compile()(c)
    select c.Name;
  

  The customFunction is a lambda function compiled as an expression tree and you can use it inside a query. The AsExpandable extension replaces the use with the body of the function, so LINQ to SQL translator can handle this. You can read more about how this works in my blog post.

• Other alternative that is in more details discussed by others is to implement the functionality as an SQL user defined function. Then you can drag the function to your data context and call the function from the query. The translator will simply insert a call to your SQL function.

Answer 2

If you can nap this to a udf you can drag the udf into the data-contex. When accessed via the data-context LINQ-to-SQL can do the translation.

If you are just filtering an iqueryable, you can write a method that just uses Where (returning a new iqueryable) which can work well.

If you need mug more, you might have to mess with expression trees manually.

Answer 3

You do two things:

1. Create user defined scalar function in SQL: http://msdn.microsoft.com/en-us/library/bb386973.aspx
2. Call it as you like in Linq To SQL: http://msdn.microsoft.com/en-us/library/bb399416.aspx

Answer 4

The simplest way to do it is to figure out how to convert your 'SatisfyFilter' function into a series of linq commands with an accepted translation. I'm afraid there really is no other "easy" way to use your custom function without filtering your 25 million clients down to a more materialize-able number.

Answer 5

Create a user function on your SQL server that does the equivalent to your C# code. Say it was called "dbo.SatsFilter".

Create a method on your datacontext override, say it looks like:

public bool SatisfiesFilter(string name, string filter)
{
  // some sort of implementation.
}

Decorate the C# method with [Function] and [Parameter] attributes so it looks something like:

[Function(Name="dbo.SatsFilter",IsComposable=true)]
public bool SatisfiesFilter([Parameter name="@name",DbType="nvarchar(50)"]string name, [Parameter name="@filter",DbType="nvarchar(50)"]string filter)

IsComposable=true means that its a function rather than a stored procedure, and can hence be used as part of a larger query.

You can now use this method of the DataContext and it will be converted into SQL when appropriate, or the C# will be used in queries being executed in memory.

Note also, that if you wanted to just use SQL all the time (sometimes useful) you could call into SQL when the method is called in C# code:

[Function(Name="dbo.SatsFilter",IsComposable=true)]
public bool SatisfiesFilter([Parameter name="@name",DbType="nvarchar(50)"]string name, [Parameter name="@filter",DbType="nvarchar(50)"]string filter)
{
  return (bool)ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), name, filter).ReturnValue;
}

This isn't very useful when the C# equivalent is convenient, as it means a hit to the database and some translation, but it is useful if the user function depends on database state or is hard to translate well into C#