How to create a method that supports translation in sql?

Question

I wanted to use the method that I created inside the request because I need to implement a special 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 Boolean SatisfyFilter Method (System.String, System.String) 'method does not support SQL translation. "

Mistake

My method:

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

same as

 public bool Contains(string value) 

in string type, but Contains just fine ...

I need this method to run in IQueryable, because my table has 25 million clients ...

I saw in the sql profile that Contains is converted to sql ...

How to implement your own method for sending correlation filter code in sql? = /

Answer 1

Create a custom function on your SQL server that will be equivalent to your C # code. Let's say it was called "dbo.SatsFilter".

Create a datacontext override method, let's say it looks like this:

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

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

 [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 its function, not a stored procedure, and therefore can be used as part of a larger request.

Now you can use this DataContext method and, if necessary, it will be converted to SQL, or C # will be used in queries executed in memory.

Note also that if you just want to use SQL all the time (sometimes useful), you could call 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 is not very useful when the C # equivalent is convenient, since it means getting into the database and some translation, but useful if the user function depends on the state of the database or it is difficult to translate to C #

Answer 2

The simple answer is you cannot . The LINQ to SQL translator only recognizes some of the standard .NET methods, and you cannot add others.

• If you need something like this, you need to explicitly create an expression tree (which will be used as the body of the lambda expression). A direct way to do this is to use Expression class methods, but this can be greatly simplified.

• Perhaps the best option is to use the LINQKit project . This allows you to call other lambda expressions (not quite a method, but close). It provides AsExpandable extensions to AsExpandable which 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; 

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

• Another alternative, which is discussed in more detail by others, is to implement functionality as a custom SQL function. Then you can drag the function into the data context and call the function from the request. The translator will simply insert a call to your SQL function.

Answer 3

If you can match this with udf, you can drag udf into data-contex. When accessed through a data context, LINQ-to-SQL can perform translation.

If you just filter iqueryable, you can write a method that just uses Where (returning a new iqueryable) that might work well.

If you need a mug more, you may need to manually corrupt expression trees.

Answer 4

You do two things:

• Create a custom scalar function in SQL: http://msdn.microsoft.com/en-us/library/bb386973.aspx
• Call it whatever you want in Linq To SQL: http://msdn.microsoft.com/en-us/library/bb399416.aspx

Answer 5

The easiest way to do this is to figure out how to convert your SatisfyFilter function to a sequence of linq commands with the accepted translation. I'm afraid that there really is no other “easy” way to use your custom function without filtering your 25 million customers to a more tangible number.

Answer 6

good decision,

Declare class properties that can be converted for remote LINQ execution

Examples

Nuget package

Source on GitHub