Home » Blog

Archive for August, 2009

Catch Mindscape on Facebook

Tagged as News

Last week we launched our official Facebook presence.

We did this because we know that everyone likes to interact in different ways. We have some folks who like to follow us on Twitter, some by RSS feed on our blog and some by visiting the blog itself. Facebook is growing at a crazy rate and I’m always surprised at how much some people use the site so it seemed like a natural place to also connect with users.

The Mindscape Facebook page:

What’s it good for?

The usual Facebook functionality is available to those who are friends of Mindscape: you can post on the Mindscape Wall, post pictures, start discussion groups and more. It’s a great way to connect with other users more easily and discuss things – we’ll be there to add our thoughts too and appreciate the full feedback cycle. You don’t need to limit yourself to Mindscape Product Propaganda discussion either – we’re a pretty geeky company so if there’s something cool you think like minded people should be aware of then post it for everyone to see :-)

Also, if you’ve created an application using Mindscape tools then we’d love to see you post about it on our wall – nothing is cooler than seeing our software make great applications :-)

We’ve currently set up the main wall to read our blog feed so if you don’t like RSS readers but do like Facebook then you can easily keep up to date with what we’re blogging about.

Looking for support?

The facebook site isn’t intended to provide support for Mindscape software. If you have a support query you should still use the support forums which are always buzzing with other users and Mindscape staff.

Next steps!

Jump on facebook and become a fan of Mindscape. While you’re there, why not leave a message and introduce yourself :-)

Add your comments
Posted by John-Daniel Trask on 17 August 2009

Custom functions in LINQ

Tagged as LightSpeed

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!

Add your comments
Posted by Ivan Towlson on 10 August 2009