SQL Translation

When writing LINQ to SQL queries, you may have noticed that when specifying expressions such as where clauses, the expressions are in the native programming language, as opposed to SQL. After all, this is part of the goal of LINQ, language integration. For this book, the expressions are in C#. If you haven't noticed, shame on you.

For example, in Listing 14-2, I have a query that looks like this:

Example. An Example of a LINQ to SQL Query

Customer cust = (from c in db.Customers
                 where c.CustomerID == "LONEP"
                 select c).Single<Customer>();

Notice that the expression in the where clause is indeed C# syntax, as opposed to SQL syntax that would look more like this:

Example. An Example of an Invalid LINQ to SQL Query

Customer cust = (from c in db.Customers
                 where c.CustomerID = 'LONEP'
                 select c).Single<Customer>();

Notice that instead of using the C# equality operator, (==), the SQL equality operator (=) is used. Instead of enclosing the string literal in double quotes (""), single quotes ('') enclose it. One of the goals of LINQ is to allow developers to program in their native programming languages. Remember, LINQ stands for Language Integrated Query. However, since the database won't be executing C# expressions, your C# expressions must be translated to valid SQL. Therefore, your queries must be translated to SQL.

Right off the bat, this means that what you can do does have limitations. But, in general, the translation is pretty good. Rather than attempt to recreate a reference similar to the MSDN help for this translation process and what can and cannot be translated, I want to show you what to expect when your LINQ to SQL query cannot be translated.

First, be aware that the code may compile. Don't be caught off guard because your query compiled. A failed translation may not actually reveal itself until the time the query is actually performed. Because of deferred query execution, this also means the line of code defining the query may execute just fine. Only when the query is actually performed does the failed translation rear its ugly head, and it does so in the form of an exception similar to this:

Unhandled Exception: System.NotSupportedException: Method 'TrimEnd' has no supported
translation to SQL.
...

That is a pretty clear error message. Let's examine the code in Listing 14-24 that produces this exception.

Example. A LINQ to SQL Query That Cannot Be Translated

Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");

IQueryable<Customer> custs = from c in db.Customers
                             where c.CustomerID.TrimEnd('K') == "LAZY"
                             select c;

foreach (Customer c in custs)
{
  Console.WriteLine("{0}", c.CompanyName);
}

Notice that the TrimEnd method that caused the translation exception is called on the database field, not my local string literal. In listing 14-25, I'll reverse the side I call the TrimEnd method on, and see what happens.

Example. A LINQ to SQL Query That Can Be Translated

Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");

IQueryable<Customer> custs = from c in db.Customers
                             where c.CustomerID == "LAZY".TrimEnd('K')
                             select c;

foreach (Customer c in custs)
{
  Console.WriteLine("{0}", c.CompanyName);
}

The output of Listing 14-25 looks like this:

OK, you got me; there is no output. But that is fine because this is the appropriate output for the query, and no SQL translation exception is thrown.

So, calling an unsupported method on a database column causes the exception, while calling that same method on the passed parameter is just fine. This makes sense. LINQ to SQL would have no problem calling the TrimEnd method on our parameter, because it can do this prior to binding the parameter to the query, which occurs in our process environment. Calling the TrimEnd method on the database column would have to be done in the database, and that means, instead of calling the method in our process environment, that call must be translated to a SQL statement that can be passed to the database and executed. Since the TrimEnd method is not supported for SQL translation, the exception is thrown.

One thing to keep in mind is that if you do need to call an unsupported method on a database column, perhaps you can instead call a method that has the mutually opposite effect on the parameter? Say, for example, you want to call the ToUpper method on the database column, and it's not supported; perhaps you could call the ToLower method on the parameter instead. However, in this case, the ToUpper method is supported so the point is moo, like a cow's opinion. Also, you must insure that the method you do call does indeed have a mutually opposite effect. In this case, the database column could have mixed case, so calling the ToLower method would still not have exactly the opposite effect. If your database column contained the value "Smith" and your parameter was "SMITH", and you were checking for equality, calling the ToUpper method on the database column would work and give you a match. However, if the ToUpper method were not supported, trying to reverse the logic by calling the ToLower method on the parameter would still not yield a match.

You may be wondering how you would know that the TrimEnd method is not supported by SQL translation. Because the nature of which primitive types and methods are supported is so dynamic and subject to change, it is beyond the scope of this book to attempt to document them all. There are also a lot of restrictions and disclaimers to the translation. I suspect SQL translation will be an ongoing effort for Microsoft. For you to know what is supported, you should consult the MSDN documentation titled ".NET Framework Function Translation" for LINQ to SQL. However, as you can see from the previous examples, it is pretty easy to tell when a method is not supported.