Refactor LINQ to Entities query

I've implemented a search-query. It replaces special chars with "normalized" ones. I've apply this rule on different fields. Actually, the query looks very ugly and is full of DRY-violations.

But refacotring this, doesn't seem to be an easy thing (for me). Of course, I just tried to refactor the whole Replace-Stuff into a separate method, but this resulted in an error like

LINQ to Entities does not recognize the method 'System.String Help(System.String)' method, and this method cannot be translated into a store expressio...

The code below shows a part of the query, there are even more statements like this in it. If somebody would have an idea, to make this nicer, it would be great!

qry = qry.Where(guest =>
                (guest.FirstName
                    .Replace("ü", "u").Replace("ue", "u").Replace("û", "u").Replace("ù", "u").Replace("ú", "u")
                    .Replace("ä", "a").Replace("ae", "a").Replace("â", "a").Replace("à", "a").Replace("á", "a")
                    .Replace("ë", "e").Replace("ê", "e").Replace("è", "e").Replace("é", "e")
                    .Replace("ö", "o").Replace("oe", "o").Replace("ô", "o").Replace("ò", "o").Replace("ó", "o")
                    .Replace("ï", "i").Replace("ì", "i").Replace("ì", "i").Replace("í", "i")
                    .Replace("ç", "c")
                    .Replace(".", "").Replace("-", "").Replace("_", "").Replace("´", "").Replace("'", "").Replace("\"", "")
                    .Replace("(", "").Replace(")", "").Replace("[", "").Replace("]", "").Replace("{", "").Replace("}", "")
                    .Replace("$", "").Replace("+", "").Replace("*", "").Replace("@", "")
                    .Replace("|", "").Replace("\\", "").Replace("/", "").Replace("<", "").Replace(">", "")
                    .Replace(".", "").Replace(",", "").Replace(";", "").Replace(":", "")
                    .Replace("=", "").Replace("%", "").Replace("^", "").Replace("?", "").Replace("!", "")
                    .StartsWith(firstName) 
                && (guest.LastName
                    .Replace("ü", "u").Replace("ue", "u").Replace("û", "u").Replace("ù", "u").Replace("ú", "u")
                    .Replace("ä", "a").Replace("ae", "a").Replace("â", "a").Replace("à", "a").Replace("á", "a")
                    .Replace("ë", "e").Replace("ê", "e").Replace("è", "e").Replace("é", "e")
                    .Replace("ö", "o").Replace("oe", "o").Replace("ô", "o").Replace("ò", "o").Replace("ó", "o")
                    .Replace("ï", "i").Replace("ì", "i").Replace("ì", "i").Replace("í", "i")
                    .Replace("ç", "c")
                    .Replace(".", "").Replace("-", "").Replace("_", "").Replace("´", "").Replace("'", "").Replace("\"", "")
                    .Replace("(", "").Replace(")", "").Replace("[", "").Replace("]", "").Replace("{", "").Replace("}", "")
                    .Replace("$", "").Replace("+", "").Replace("*", "").Replace("@", "")
                    .Replace("|", "").Replace("\\", "").Replace("/", "").Replace("<", "").Replace(">", "")
                    .Replace(".", "").Replace(",", "").Replace(";", "").Replace(":", "")
                    .Replace("=", "").Replace("%", "").Replace("^", "").Replace("?", "").Replace("!", "")
                    .StartsWith(lastName)
                    ||
                    guest.LastName
                    .Replace("ü", "u").Replace("ue", "u").Replace("û", "u").Replace("ù", "u").Replace("ú", "u")
                    .Replace("ä", "a").Replace("ae", "a").Replace("â", "a").Replace("à", "a").Replace("á", "a")
                    .Replace("ë", "e").Replace("ê", "e").Replace("è", "e").Replace("é", "e")
                    .Replace("ö", "o").Replace("oe", "o").Replace("ô", "o").Replace("ò", "o").Replace("ó", "o")
                    .Replace("ï", "i").Replace("ì", "i").Replace("ì", "i").Replace("í", "i")
                    .Replace("ç", "c")
                    .Replace(".", "").Replace("-", "").Replace("_", "").Replace("´", "").Replace("'", "").Replace("\"", "")
                    .Replace("(", "").Replace(")", "").Replace("[", "").Replace("]", "").Replace("{", "").Replace("}", "")
             开发者_如何学C       .Replace("$", "").Replace("+", "").Replace("*", "").Replace("@", "")
                    .Replace("|", "").Replace("\\", "").Replace("/", "").Replace("<", "").Replace(">", "")
                    .Replace(".", "").Replace(",", "").Replace(";", "").Replace(":", "")
                    .Replace("=", "").Replace("%", "").Replace("^", "").Replace("?", "").Replace("!", "")
                    .Contains(" " + lastName)
                    ||
                    guest.LastName.Replace(" ", "")
                    .Replace("ü", "u").Replace("ue", "u").Replace("û", "u").Replace("ù", "u").Replace("ú", "u")
                    .Replace("ä", "a").Replace("ae", "a").Replace("â", "a").Replace("à", "a").Replace("á", "a")
                    .Replace("ë", "e").Replace("ê", "e").Replace("è", "e").Replace("é", "e")
                    .Replace("ö", "o").Replace("oe", "o").Replace("ô", "o").Replace("ò", "o").Replace("ó", "o")
                    .Replace("ï", "i").Replace("ì", "i").Replace("ì", "i").Replace("í", "i")
                    .Replace("ç", "c")
                    .Replace(".", "").Replace("-", "").Replace("_", "").Replace("´", "").Replace("'", "").Replace("\"", "")
                    .Replace("(", "").Replace(")", "").Replace("[", "").Replace("]", "").Replace("{", "").Replace("}", "")
                    .Replace("$", "").Replace("+", "").Replace("*", "").Replace("@", "")
                    .Replace("|", "").Replace("\\", "").Replace("/", "").Replace("<", "").Replace(">", "")
                    .Replace(".", "").Replace(",", "").Replace(";", "").Replace(":", "")
                    .Replace("=", "").Replace("%", "").Replace("^", "").Replace("?", "").Replace("!", "")
                    .StartsWith(lastName))
                ) || (
                guest.FirstName
                    .Replace("ü", "u").Replace("ue", "u").Replace("û", "u").Replace("ù", "u").Replace("ú", "u")
                    .Replace("ä", "a").Replace("ae", "a").Replace("â", "a").Replace("à", "a").Replace("á", "a")
                    .Replace("ë", "e").Replace("ê", "e").Replace("è", "e").Replace("é", "e")
                    .Replace("ö", "o").Replace("oe", "o").Replace("ô", "o").Replace("ò", "o").Replace("ó", "o")
                    .Replace("ï", "i").Replace("ì", "i").Replace("ì", "i").Replace("í", "i")
                    .Replace("ç", "c")
                    .StartsWith(lastName)
                && (guest.LastName
                    .Replace("ü", "u").Replace("ue", "u").Replace("û", "u").Replace("ù", "u").Replace("ú", "u")
                    .Replace("ä", "a").Replace("ae", "a").Replace("â", "a").Replace("à", "a").Replace("á", "a")
                    .Replace("ë", "e").Replace("ê", "e").Replace("è", "e").Replace("é", "e")
                    .Replace("ö", "o").Replace("oe", "o").Replace("ô", "o").Replace("ò", "o").Replace("ó", "o")
                    .Replace("ï", "i").Replace("ì", "i").Replace("ì", "i").Replace("í", "i")
                    .Replace("ç", "c")
                    .Replace(".", "").Replace("-", "").Replace("_", "").Replace("´", "").Replace("'", "").Replace("\"", "")
                    .Replace("(", "").Replace(")", "").Replace("[", "").Replace("]", "").Replace("{", "").Replace("}", "")
                    .Replace("$", "").Replace("+", "").Replace("*", "").Replace("@", "")
                    .Replace("|", "").Replace("\\", "").Replace("/", "").Replace("<", "").Replace(">", "")
                    .Replace(".", "").Replace(",", "").Replace(";", "").Replace(":", "")
                    .Replace("=", "").Replace("%", "").Replace("^", "").Replace("?", "").Replace("!", "")
                    .StartsWith(firstName)
                    ||
                    guest.LastName
                    .Replace("ü", "u").Replace("ue", "u").Replace("û", "u").Replace("ù", "u").Replace("ú", "u")
                    .Replace("ä", "a").Replace("ae", "a").Replace("â", "a").Replace("à", "a").Replace("á", "a")
                    .Replace("ë", "e").Replace("ê", "e").Replace("è", "e").Replace("é", "e")
                    .Replace("ö", "o").Replace("oe", "o").Replace("ô", "o").Replace("ò", "o").Replace("ó", "o")
                    .Replace("ï", "i").Replace("ì", "i").Replace("ì", "i").Replace("í", "i")
                    .Replace("ç", "c")
                    .Replace(".", "").Replace("-", "").Replace("_", "").Replace("´", "").Replace("'", "").Replace("\"", "")
                    .Replace("(", "").Replace(")", "").Replace("[", "").Replace("]", "").Replace("{", "").Replace("}", "")
                    .Replace("$", "").Replace("+", "").Replace("*", "").Replace("@", "")
                    .Replace("|", "").Replace("\\", "").Replace("/", "").Replace("<", "").Replace(">", "")
                    .Replace(".", "").Replace(",", "").Replace(";", "").Replace(":", "")
                    .Replace("=", "").Replace("%", "").Replace("^", "").Replace("?", "").Replace("!", "")
                    .Contains(" " + firstName)
                    ||
                    guest.LastName.Replace(" ", "")
                    .Replace("ü", "u").Replace("ue", "u").Replace("û", "u").Replace("ù", "u").Replace("ú", "u")
                    .Replace("ä", "a").Replace("ae", "a").Replace("â", "a").Replace("à", "a").Replace("á", "a")
                    .Replace("ë", "e").Replace("ê", "e").Replace("è", "e").Replace("é", "e")
                    .Replace("ö", "o").Replace("oe", "o").Replace("ô", "o").Replace("ò", "o").Replace("ó", "o")
                    .Replace("ï", "i").Replace("ì", "i").Replace("ì", "i").Replace("í", "i")
                    .Replace("ç", "c")
                    .Replace(".", "").Replace("-", "").Replace("_", "").Replace("´", "").Replace("'", "").Replace("\"", "")
                    .Replace("(", "").Replace(")", "").Replace("[", "").Replace("]", "").Replace("{", "").Replace("}", "")
                    .Replace("$", "").Replace("+", "").Replace("*", "").Replace("@", "")
                    .Replace("|", "").Replace("\\", "").Replace("/", "").Replace("<", "").Replace(">", "")
                    .Replace(".", "").Replace(",", "").Replace(";", "").Replace(":", "")
                    .Replace("=", "").Replace("%", "").Replace("^", "").Replace("?", "").Replace("!", "")
                    .StartsWith(firstName))
                ));

You can indeed refactor the code into another function that returns an expression tree (as Jon stated).

I've put together a solution for you, although it is a little lengthy. Unfortunately working with expressions is pretty complicated, I do hope you find this useful though.

Note: to make this solution work you'll need to also use LinqKit (it's very useful when you're using LINQ with an ORM)

I'll start with how you use the code, and then show you the methods that make it work.

I created a function called TestCleanString. You need to give it a selector to select the property you want to test, and you also need to give it a predicate to test the string with.

For example; here we want to test the FirstName property, and we want to test if it starts with firstName. It will select the FirstName property, then clean it using your cleaning rules, and then test the result against the predicate.

TestCleanString<Guest>(g => g.FirstName, s => s.StartsWith(firstName));

Here it is in action:

//make an expression tree to check the first name
var firstnameOk = TestCleanString<Guest>(g => g.FirstName, s => s.StartsWith(firstName));
//make an expression tree to check the last name
var lastnameOk = TestCleanString<Guest>(g => g.LastName, s => s.StartsWith(lastName));
//make your additional filter expressions here ...
//...    

//combine the expression trees together using the "And" and "Or" methods from LinqKit
var filter = firstnameOk.And(lastnameOk);

//pass the filter into the where method
qry = qry.Where(filter);

I placed your string cleaning code into the following function that returns an expression tree.

//returns an expression that will clean the string
private static Expression<Func<string, string>> CleanString()
{
    return s => s.Replace("ü", "u").Replace("ue", "u").Replace("û", "u").Replace("ù", "u").Replace("ú", "u")
                 .Replace("ä", "a").Replace("ae", "a").Replace("â", "a").Replace("à", "a").Replace("á", "a")
                 .Replace("ë", "e").Replace("ê", "e").Replace("è", "e").Replace("é", "e")
                 .Replace("ö", "o").Replace("oe", "o").Replace("ô", "o").Replace("ò", "o").Replace("ó", "o")
                 .Replace("ï", "i").Replace("ì", "i").Replace("ì", "i").Replace("í", "i")
                 .Replace("ç", "c")
                 .Replace(".", "").Replace("-", "").Replace("_", "").Replace("´", "").Replace("'", "").Replace("\"", "")
                 .Replace("(", "").Replace(")", "").Replace("[", "").Replace("]", "").Replace("{", "").Replace("}", "")
                 .Replace("$", "").Replace("+", "").Replace("*", "").Replace("@", "")
                 .Replace("|", "").Replace("\\", "").Replace("/", "").Replace("<", "").Replace(">", "")
                 .Replace(".", "").Replace(",", "").Replace(";", "").Replace(":", "")
                 .Replace("=", "").Replace("%", "").Replace("^", "").Replace("?", "").Replace("!", "");
} 

Lastly, lets look at the TestCleanString function. It builds an expression tree that performs the following 3 steps; select the property that you want to test, clean the resulting string, test the string against the specified conditional expression.

public static Expression<Func<TElement, bool>> TestCleanString<TElement>(Expression<Func<TElement, string>> stringSelector, Expression<Func<string, bool>> conditionalExpression)
{
    //declare the parameter: e =>
    var param = new[] { Expression.Parameter(typeof(TElement), "e") };
    //pass the parameter into the selector to get the string property
    var invokedStringSelector = Expression.Invoke(stringSelector, param.Cast<Expression>());
    //pass the string property to the clean expression
    var invokedCleanString = Expression.Invoke(CleanString(), invokedStringSelector.Expand());
    //pass the cleaned string to the conditional expression
    var invokedConditionalExpression = Expression.Invoke(conditionalExpression, invokedCleanString.Expand());
    //rebuild the expression tree so the provider can understand it
    return Expression.Lambda<Func<TElement, bool>>(invokedConditionalExpression.Expand(), param);
}

Incase you're interested, it generates SQL that will look a bit like this (i ran it against my own model so the names are different):

SELECT 
1 AS [C1], 
[Extent1].[EmailRecipientId] AS [EmailRecipientId], 
[Extent1].[Address] AS [Address], 
[Extent1].[SentOn] AS [SentOn], 
[Extent1].[FailedOn] AS [FailedOn], 
[Extent1].[FailReason] AS [FailReason], 
[Extent1].[IsTo] AS [IsTo], 
[Extent1].[IsCC] AS [IsCC], 
[Extent1].[IsBCC] AS [IsBCC], 
[Extent1].[EmailId] AS [EmailId]
FROM  [dbo].[EmailRecipients] AS [Extent1]
INNER JOIN [dbo].[Emails] AS [Extent2] ON [Extent1].[EmailId] = [Extent2].[EmailId]
WHERE ( CAST(LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Extent2].[Subject], N'ü', N'u'), N'ue', N'u'), N'û', N'u'), N'ù', N'u'), N'ú', N'u'), N'ä', N'a'), N'ae', N'a'), N'â', N'a'), N'à', N'a'), N'á', N'a'), N'ë', N'e'), N'ê', N'e'), N'è', N'e'), N'é', N'e'), N'ö', N'o'), N'oe', N'o'), N'ô', N'o'), N'ò', N'o'), N'ó', N'o'), N'ï', N'i'), N'ì', N'i'), N'ì', N'i'), N'í', N'i'), N'ç', N'c'), N'.', N''), N'-', N''), N'_', N''), N'´', N''), N'''', N''), N'"', N''), N'(', N''), N')', N''), N'[', N''), N']', N''), N'{', N''), N'}', N''), N'$', N''), N'+', N''), N'*', N''), N'@', N''), N'|', N''), N'\', N''), N'/', N''), N'<', N''), N'>', N''), N'.', N''), N',', N''), N';', N''), N':', N''), N'=', N''), N'%', N''), N'^', N''), N'?', N''), N'!', N'')) AS int)) > 0

Well, LINQ to EF isn't going to like a simple private method call from within the lambda expression... but you could write a method which builds an equivalent expression tree to what you've already got. Working with expression trees isn't always easy, but it should do the trick. You would then call the Where method, passing in your expression tree as a normal argument (i.e. not using a lambda expression).

I suggest you write a very simple statement performing 2 Replace operations and either look at what the C# compiler is doing for you (via reflection) or use the Expression Tree Visualizer in VS2010. Once you know what the tree looks like, building it programmatically shouldn't be too bad.

Note that you'd have to build the "OR" functionality into the expression tree as well (which I'd suggest you do using a separate method) but the "AND" functionality can be achieved simply by using multiple Where calls.

It would not be doing you any favors to help you refactor this code. LINQ to Entities queries are eventually translated into SQL, and this SQL is going to be a mess no matter how good the code looks. You need to reconsider your querying strategy based on the tools your database gives you. Ideally, you should be able to write a query which uses an index.

There are two strategies to consider: Collations and schema changes.

You haven't mentioned which database you are using, but most databases offer collations which are accent-insensitive for WHERE searches. You should consider changing the collation on the column to one of these.

Regarding the dollar signs and the like, you probably won't find a collation which would just ignore these unless you write it yourself. So a different option would be to have a separate column in the database, updated by a trigger, which contains the first and last name with these characters removed. Run your search against that, instead, and an index on these columns can be used.

Have you considered using Func<> delegate or a private method call where all this process will take place? That can increase the readability of the code.