Custom functions in LINQ

One of the cool features of LINQ is that it allows you to write your queries in terms of familiar .NET methods and have LINQ automatically translate them into the necessary SQL. For example, you can write something like this:

var tweetableMessages = from m in unitOfWork.Messages
                        where m.MessageText.Length < 140
                        select m;

and LightSpeed will generate a SQL WHERE clause that looks something like:

WHERE LEN(Message.MessageText) < 140

But what if you want to call a SQL function, either built into your database or created by you as a user-defined function, which LINQ doesn’t have a .NET method for? For example, SQL Server has built-in functions to support SOUNDEX matching, a rudimentary way to look for text that “sounds like” a target string rather than being an exact text match. This isn’t a feature you’ll find on the .NET string class. So what if we want to do a SOUNDEX search using LightSpeed and LINQ?

Custom mapping .NET methods to SQL functions

In recent nightly builds we’ve added a basic and experimental custom function mapping capability to enable this. Let’s take a look at how we might use this to handle the SOUNDEX example.

The first thing we’re going to need is a suitable .NET method. Sometimes you get lucky and there’s already a suitable .NET method defined: for example, if you want to call the SQL Server CHARINDEX or Oracle INSTR function, you could map the String.IndexOf method. But the designers of the String class unaccountably failed to bake in SOUNDEX functionality, so we’re going to have to create our own extension method:

public static class SoundexExtensions
{
  public static int SimilarityTo(this string first, string second)
  {
    throw new NotImplementedException();
  }
}

We’re never actually going to implement this method, by the way, because nothing will ever call it. It exists solely to be mentioned in LINQ expressions and translated to SQL. Welcome to the weird world of LINQ.

Once we’ve got the function, we can use it in our LINQ query. Here the user has misspelled “ivan,” so a string matching query wouldn’t find anything, but using SOUNDEX we can offer the user some alternative guesses:

var query = from m in UnitOfWork.Members
            orderby m.UserName.SimilarityTo("ivon") descending
            select m;

This will select all Members, in order of how much their username sounds like “ivon.” (SQL Server returns higher values for closer matches, so this needs to be a descending order.)

If you run this query, you’ll get a tasty NotSupportedException complaining that LightSpeed doesn’t know what to do with the SimilarityTo method. So the remaining step is to tell LightSpeed how to translate this unknown method. The API for this is currently rather rudimentary and may change, but in the current nightly build, you do it using the new ServerFunctionDescriptor class, as follows:

MethodInfo similarityTo = typeof(SoundexExtensions).GetMethod("SimilarityTo");
ServerFunctionDescriptor.Register(similarityTo, "DIFFERENCE");

First, we get a .NET MethodInfo representing the .NET method to be translated. We can’t just use a name because the same method name might be used on different classes or there might be multiple overloads. Then we register the translation of that method to a SQL function. In the SOUNDEX example, SQL Server’s function for evaluating the SOUNDEX similarity of two strings is called DIFFERENCE. You can find the ServerFunctionDescriptor class in the Mindscape.LightSpeed.Linq namespace.

Now when we run the query, LightSpeed generates the SQL you’d expect, so you get back the list of members appropriately sorted:

SELECT
  Members.Id,
  -- other columns elided
  Members.UserName
FROM
  Members
ORDER BY
  DIFFERENCE(Members.UserName, 'ivon') DESC,
  Members.Id
 
-- Results:
-- ivan
-- jd
-- jb
-- andrew

Mapping issues: argument ordering

One issue that doesn’t matter with SOUNDEX but does with some methods is argument ordering. You can see from the above SQL that the expression to which the mapped function was applied (m.UserName) became the first argument to the DIFFERENCE function. That doesn’t always work. Suppose we mapped String.IndexOf to SQL Server’s CHARINDEX function, and wrote m.UserName.IndexOf(” “) in our query. This would get mapped to CHARINDEX(Members.UserName, ‘ ‘) which is the wrong way round: SQL Server wants the string to search for to be the first argument, and the string to search in the second. To handle this, there’s an overload of ServerFunctionDescriptor.Register which takes an implicit argument index, meaning the 0-based index in the argument list at which to put the expression to which the mapped function was applied. So when mapping IndexOf to CHARINDEX we’d write:

ServerFunctionDescriptor.Register(indexOfMethod, "CHARINDEX", 1);

Note the implicit argument index of 1. Now the generated SQL will have the translation of m.UserName at index 1: CHARINDEX(‘ ‘, Members.UserName).

Of course, even further complications are possible if the .NET method takes its arguments in a completely different order to the SQL function. We don’t handle that yet, but we will — let us know if you need it.

Mapping issues: when there’s no SQL function for what you want

Sometimes you’ll be in the opposite position: there’s a .NET method that represents what you want, and you’d like to have executed on the server, but no corresponding SQL function. In this case, provided your database supports it, you can create a user-defined SQL function, and map the .NET method to that. (In fact, on some databases your UDF can actually be a .NET or Java method — at least provided your architect and DBA don’t have heart attacks when you suggest the idea.)

The syntax for mapping to UDFs is exactly the same as for mapping to built-in functions, though you need to be careful to get the SQL function name in exactly the format that the database wants it. For example, on SQL Server, you can’t map a method to “MyCustomFunction”: you need to map it to “dbo.MyCustomFunction” (or whatever the owner/schema is).

Mapping issues: data types

Right now, data type mapping is at the mercy of whatever the database provider comes up with. This isn’t always perfect. For example, if you map the String.IndexOf(char) overload to the Oracle INSTR function, you’ll get bad results back, because the Oracle provider maps char to DbType.Byte, and INSTR doesn’t handle bytes. In this case, you can work around it by mapping the String.IndexOf(string) overload. Other cases might be more tricky. You can diagnose data type mapping problems by turning on logging and setting VerboseLogging to true. The current nightly doesn’t provide a way to fix bad type mappings but if you run into a problem like this, let us know and we’ll try to add something to help you out.

Take it for a spin

The custom mapping feature is at an early, somewhat experimental stage at the moment and there are a several limitations, plus a few bugs we still need to iron out. If you’ve got a particular scenario you’d like to try out, grab the latest nightly (free Express edition here, or retail versions from the store) and give it a go — if we don’t currently handle your case, or if we handle it wrong, then let us know!

Tagged as LightSpeed

15 Responses to “Custom functions in LINQ”

  • Hi Ivan,

    That’s cool stuff… Two questions come to mind:

    1. How would you do this if you plan to support multiple databases? Say you want to use the SOUNDEX example in an SQL Server database and an oracle database?

    2. Can you also use this in the ‘other’ dialect for LINQ (don’t know the official name). I would write it as follows:

    var query = UnitOfWork.Members.OrderByDescing(m => m.UserName.SimilarityTo(“ivon”));

    would that work?

    Keep up the good work!
    Robert
    select m;

  • That should be:

    var query = UnitOfWork.Members.OrderByDescending(m => m.UserName.SimilarityTo(”ivon”));

  • Have you considered also including the option of marking the method up with an attribute? For methods the LightSpeed LINQ provider doesn’t recognize it could check if there is the attribute present to map it to a function.

  • Definitely. But our first motivating case was a customer who wanted to use the DateTime.ToLocalTime method, which is a built-in CLR method and therefore can’t be attributed up. So we decided to first do an API that would support methods outside your control. But I certainly want to do an attribute-based API at some point because I agree that’s much more natural than the procedural API for methods that the developer controls.

  • Very well then. As you were.

    :P

  • Custom functions in LINQ made easy with LightSpeed…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  • Don’t use Soundex.. Double Metaphone, Levenshtein, n-grams or even ConceptNet would provide better results.

  • I was using Soundex only as an example of a SQL function that wasn’t surfaced in .NET/LINQ — didn’t intend to suggest it was state of the art in sound-alike matching! Thanks for the pointers though!

  • Hello Robert, and sorry for the delay in replying — for some reason WordPress stuck your comment in moderation but neglected to tell me about it…

    1. At present we do not provide a way to register different translations for different database engines, but it would be easy for us to add this if it would be useful to you. Any additional info you can provide about the scenario would be useful e.g. would you need to register translations on a per-database-instance basis (e.g. you want to translate DateTime.ToLocalTime(), but the UDF is called TO_LOCAL_TIME in the Customers database and APPLY_TZ_OFFSET in the Logistics database, even though both are Oracle), or just on a per-database-type basis?

    2. Yes, this should work fine in “query operator” syntax. Most of our testing has been with the “language integrated” syntax but they both boil down to the same thing and the tests we have done with “query operator” syntax have worked fine. Do let us know if you run into any bugs though!

  • Hi Ivan,

    For the first point, I don’t have a specific implementation in mind, but I can imagine where an application would support multiple databases (for instance for different customers, where a customer would have a preference for a particular RDBMS), so I was thinking about the per-database-type basis.

    Good to know that the “query operator” syntax works the same way. And also nice to know the name of the syntax ;-)

  • WordPress ? why are you using a php blogging system ? you couln’d find any .Net alternative ?

  • JFYI
    http://www.darknet.org.uk/2009/08/wordpress-2-8-3-admin-reset-exploit/

  • Hi Joan,

    We use WordPress because we consider it the best tool for the job (especially over what was available at the time a few years ago).

    We’re now running 2.8.4 – the built in upgrade from the admin console means it’s a one click upgrade (something I’m not sure any .net blog does).

    Cheers for the reminder to keep up to date however :-)

  • [...] Want to call a SQL function from your LINQ query, but we don’t provide a translation from a CLR method, or worse still there’s no corresponding CLR method at all? Now you can create a custom function mapping that allows you to keep writing strongly-typed LINQ queries and having the desired function appear in the SQL translation. Find out how here. [...]

  • [...] need to teach it about the spatial methods you want to use.As we’ve discussed before, you can use the ServerFunctionDescriptor.Register method to map a CLR function to a SQL function. However, the SQL Server 2008 spatial methods aren’t normal SQL functions: they’re [...]

  • Leave a Reply

Archives

Join our mailer

You should join our newsletter! Sent monthly:

Back to Top