Using LINQ to SQL and chained Replace

Question

I have a need to replace multiple strings with others in a query

from p in dx.Table
where p.Field.Replace("A", "a").Replace("B", "b").ToLower() = SomeVar
select p

Which provides a nice single SQL statement with the relevant REPLACE() sql commands.

All good :)

I need to do this in a few queries around the application... So i'm looking for some help in this regard; that will work as above as a single SQL hit/command on the server

It seems from looking around i can't use RegEx as there is no SQL eq

Being a LINQ newbie is there a nice way for me to do this?

eg is it possible to get it as a IQueryable "var result" say and pass that to a function to add needed .Replace()'s and pass back? Can i get a quick example of how if so?

EDIT: This seems to work! does it look like it would be a problem?

var data = from p in dx.Videos select p;
data = AddReplacements(data, checkMediaItem);
theitem = data.FirstOrDefault();

...

public IQueryable<Video> AddReplacements(IQueryable<Video> DataSet, string checkMediaItem)
{
    return DataSet.Where(p =>
            p.Title.Replace(" ", "-").Replace("&", "-").Replace("?", "-") == checkMediaItem);
}

Answer 1

Wouldn't it be more performant to reverse what you are trying to do here, ie reformat the string you are checking against rather than reformatting the data in the database?

public IQueryable<Video> AddReplacements(IQueryable<Video> DataSet, string checkMediaItem)
{
    var str = checkMediaItem.Replace("-", "?").Replace("&", "-").Replace("-", " "));
    return DataSet.Where(p => p.Title == str);
}

Thus you are now comparing the field in the database with a set value, rather than scanning the table and transforming the data in each row and comparing that.