Linq2Sql remove char

Question

    public ActionResult Index(string title)
    {
        var post = (from p in dataContext.Posts
                    where RemoveChar(p.Title) == title && !p.Deleted   
                    select p).Single();

        post.Visit = post.Visit + 1;

        dataContext.SubmitChanges();

        return View(new ViewData.PostIndexViewData(post));
    }

    public string RemoveChar(string Item)
    {
        var s = from ch in Item
                where !Char.IsPunctuation(ch)
                select ch;
        var bytes = UnicodeEncoding.ASCII.GetBytes(s.ToArray());
        var stringResult = UnicodeEncoding.ASCII.GetString(bytes);
        return stringResult;
    }

Is this possible as I get Method 'System.String RemoveChar(System.String)' has no supported translation to SQL.

Answer 1

No, you can't use your own custom methods within LINQ to SQL like that - it wouldn't know how to translate them into SQL. (The expression tree just contains a call to RemoveChar - it doesn't build up an expression tree to represent the code within RemoveChar.) I'm not sure that there's a particularly nice way of doing this - you may be able to write a user-defined function or stored procedure and map that appropriately in LINQ. (Heck, you could use a CLR user-defined function if you're using SQL Server and have willing DBAs.)

I would add that your RemoveChar implementation is far from ideal in a few ways - I'd suggest using Regex.Replace instead, for in-process punctuation removal.

Answer 2

The problem is that you're mixing a local function into a Linq expression that Linq to SQL wants to translate into SQL to send to the server.

To resolve this you'd need to load the entire list of posts and query the result in memory which is probably less than ideal.

I think that you need to make a change to the application - you should be able to search the database with a key that doesn't require that the data in the database is modified to match.

I'd suggest that you add a column "SearchTitle" that is the title with the punctuation removed as per your example then your query becomes trivial (and more efficient) at the cost of a small amount of additional space in your database.