ansaurus

Question

Answer 1

A:

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.

Siva Gopal 2010-09-23 07:06:53

The OP has specifically talked about a private method - and that it doesn't translate into a LINQ to EF expression.

Jon Skeet 2010-09-23 07:17:06

`Func<>` wouldn't work, but `Expression<Func<>>` would, as DoctaJonez showed.

Gabe 2010-09-25 02:27:18

Answer 2

+1 A:

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.

Jon Skeet 2010-09-23 07:20:08

Hey Jon, wouldn't it be easier and less prone to error to get the expression tree from a lambda expression instead of building it manually. For example; Expression<Func<string, string>> clean = s => s.Replace("ü", "u").Replace("ue", "u").Repl...snip...;

DoctaJonez 2010-09-25 02:23:31

I think that'd be more readable and have less maintenance cost than a hand crafted tree. No need to roll your own when the compiler will do it for you eh? :-) Or is there something that I missed?

DoctaJonez 2010-09-25 02:25:08

Answer 3

A:

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.

Craig Stuntz 2010-09-23 13:09:25

Collations, I didn't knew that there are also accent insensitive collations. I think I will use them. I thought about the other approach too (save a cleaned, searchable string in a seperate column), but I don't like it. It creates kind of redundancy, ok it's generated, but it's still redundant.

Daniel 2010-09-25 23:13:49

btw: I'm using SQL Server 2008 (and R2)

Daniel 2010-09-25 23:14:21

Answer 4

+2 A:

Hi Daniel,

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

DoctaJonez 2010-09-25 02:20:21

wow, you did a lot of work for me :-) Thank you very much, this gives me a good idea (or almost the solution) to refactor to my needs.

Daniel 2010-09-25 23:10:14

You're welcome! :-) I use LINQ to EF and LINQ to SQL a lot and found this question pretty interesting. I hadn't thought of combining expression trees before, but now I think it'll prove to be quite a useful technique for future work.

DoctaJonez 2010-09-26 10:08:02

Sorry, I wasn't able to try your code earlier. Did you successfully ran that code against LINQ to Entities? I get an Exception: The LINQ expression node type 'Invoke' is not supported in LINQ to Entities :-(

Daniel 2010-10-01 12:05:33

Found it: AsExpandable() is my friend. Thx

Daniel 2010-10-01 12:53:03

Nice, glad you got it working :-)

DoctaJonez 2010-10-01 13:01:43

ansaurus

tags:

views:

answers:

Refactor LINQ to Entities query

related questions