How to adapt a simple String Extension to support SQL

Question

I've got a C# string extension that really makes life easy, but I am getting the exception:

Method 'System.String ToUrlString(System.String)' has no supported translation to SQL.

I've seen this as a common problem and some people have found ways around fixing this for their method, but I'm not sure it's possible for mine. It's just a simple short cut and I've tried using it with a Regular Expression as well, and the same exception comes up.

public static string ToUrlString(this String val)
{
    return val.Trim().ToLower().Replace(" ", "-").Replace(":", string.Empty);
}

Are there any additional decorators or ways I can adapt this code so that it can support SQL? I'm calling this on the where clause in my LINQ expressions. .ToTagString() is not the exact same method as .ToUrlString() but it's very similar.

EDIT: Per a suggestion below, here is something else I tried, but I am still getting the same error.

public IEnumerable<Post> GetPostsByTag(string tagName)
{
    var query = from p in db.Posts
           join pt in db.PostTags on p.PostID equals pt.PostID
           where pt.Tag.TagName.ToTagString().Equals( tagName.ToTagString() )
           orderby p.PostDate descending
           select p;

    var result = query.AsEnumerable();

    return from r in result
           select r;
}

Answer 1

No you cannot translate that to SQL, at least without implementing your own LINQ TO SQL provider.

The problem is that you are using it inside a Linq query, so it tries to translate your method to SQL language.

LINQ uses IQueryable to do the tranlation job of your expression to SQL language. While you are using IQueryable (all entities returned from the datacontext implement IQueryable), all the methods applied to it will be translated to SQL.

To avoid your custom method being translated to SQL, you should first convert your LINQ expression to IEnumerable, so no further tranlation will occur:

public IEnumerable<String> Example()
{
     var query = from f in foos
            where f.Id == 'myId'
            select f;

     var result = query.AsEnumerable();

     return from r in result
            select r.Url.ToUrlString();
}

Hope it helps!

Answer 2

I think you can create UDF to perform required strings manipulation on DB level. http://msdn.microsoft.com/en-us/library/bb386973.aspx http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

1. Create UDF. Use LTRIM, RTRIM, LOWER, REPLACE functions. Something like this.

   CREATE FUNCTION dbo.ToUrlString(@string VARCHAR(MAX)) RETURNS VARCHAR(MAX) BEGIN RETURN REPLACE(REPLACE(LOWER(LTRIM(RTRIM(@string))), ' ','-'), ':', '') END

2. Add it as a method to your DataContext. Just drag'n'drop it from Server Explorer onto L2S designer.

I hope it helps.