How to create a method that suport translation to sql?

translate function in sql with example
'translate' is not a recognized built-in function name.
sql translate language
sql translate function oracle
translate sql to english
sql translate vs replace
replace in sql
sql translate function db2

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? =/

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#

SQL Server TRANSLATE() Function, Definition and Usage. The TRANSLATE() function returns the string from the first argument after the characters specified in the second argument are translated  SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

TRANSLATE (Transact-SQL), The behavior of the TRANSLATE function is similar to using multiple REPLACE functions. TRANSLATE does not, however, replace any  The SQL Translation Profile is a database object that contains the set of captured non-Oracle SQL statements, and their translations or translation errors. The SQL Translation Profile is used to review, approve, and modify translations. A profile is associated to a single translator. However, a translator can be used in one or more SQL

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

SQL Server TRANSLATE() Function By Practical Examples, This tutorial shows you how to use the SQL Server TRANSLATE() function to replace several single-characters, one-to-one translation in one operation. I want to, in the midst of a linq to sql query where clause, check against a public int. I am getting this error: Method 'Int32 isInDept(System.String)' has no supported translation to SQL. Vaguely related classes (from a public static class called ad): //get AD property public static string GetPr

If you can map 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.

Conceptual Modeling for Advanced Application Domains: ER 2004 , [3] proposes a method to translate an XQuery into SQL queries based on the How to enhance relational database kernel to support tree structure data For example, it needs to make union of all elements related relations together to handle  The following paragraphs describe the differences between the Standard Query Operators and their SQL translation for the SQL Server provider for LINQ to SQL. Operator Support Concat. The Concat method is defined for ordered multisets where the order of the receiver and the order of the argument are the same.

good solution,

Declare properties on a class that can be translated for remote LINQ execution with Microsoft.Linq.Translations.

examples

NuGet Package

Source on GitHub

Special Edition Using Microsoft Access 2002, ANSI SQL doesn't support use of the # symbol in queries. by an entirely different method, and doesn't support declaring variables in SQL statements. • ! TRANSLATING SQL STATEMENTS INTO QBE DESIGNS SQl When you create a​  You can't use any custom functions in a LINQ query that gets translated - only the functions supported by the given LINQ provider. If you want your query to happen on the server, you need to stick with the supported functions (even if it sometimes means having to inline code that would otherwise be reused).

TRANSLATE, Database SQL Reference. Contents. Previous · Next as well as remove character strings. TRANSLATE lets you make several single-character, one-to-​one substitutions in one operation. This function does not support CLOB data directly. LINQ to SQL works by translating expressions into TSQL. When you write a method or property it isn't an expression but is instead compiled IL code. This code can not be reverse-engineered for translation to TSQL to be executed remotely. Your options are either: 1.

SQL Translation and Examples for Oracle Database Provider for , Creation of a SQL Translation Profile that references the SQL Interface Package. Oracle Database does not support the CURRENT TIME special register construct, in Oracle; instead, Oracle SQL syntax includes a compatible CEIL function. The SQL Translation Profile. The SQL Translation Profile is a database object that contains the set of captured non-Oracle SQL statements, and their translations or translation errors. The SQL Translation Profile is used to review, approve, and modify translations. A profile is associated to a single translator.

SQL translation - Databases using R, There are two components to dplyr 's SQL translation system: is built on top of R's parsing engine and has been carefully designed to generate correct SQL. Any function that dplyr doesn't know how to convert is left as-is. Support for window functions varies from database to database, but most support the ranking​  In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution.

Comments
  • Do you mean I will have to inherit the DataContext class and then place the SatisfiesFilter method as an instance method inside it? and then while firing LINQ to SQL queries I should be using my new inherited class instead.
  • @RBT I think so. It's been a while.
  • Can you please have a look at my problem at the following link. I'm trying the same thing but getting an error. stackoverflow.com/questions/37814449/…
  • The problem is if I place this method as an intance method inside my custom data context class which inherits from DataContext class then how do I use it inside my LINQ query.