Using string methods in EF LINQ queries

Question

I have a situation wherein two urls need to be compared, one coming in to the method and the other in the db. The one in the db may or may not be Url decoded and will have %20 for space and so on.

Here's the linq query in the method:

var Result = (from s in context.301Redirects
              where s.IsDeleted == false && s.Status == true && HttpUtility.UrlDecode(s.OldURL).ToUpper() == HttpUtility.UrlDecode(OldURLPath).ToUpper()
              select s.NewURL).FirstOrDefault();
              return Result;

Inside the method I can UrlDecode the incoming param but what about the one in the db? This one fails because EF will not recognize UrlDecode method and throw and exception saying so.

Is there a workaround?

Mathew Thompson · Accepted Answer · 2013-09-25 11:56:17Z

2

You can bring the collection in memory, then perform the UrlDecode at that point by evaluating the query. Try:

var Result = (from s in context.301Redirects
              where s.IsDeleted == false && s.Status == true)
             .AsEnumerable()
             .FirstOrDefault(HttpUtility.UrlDecode(s.OldURL).ToUpper() == HttpUtility.UrlDecode(OldURLPath).ToUpper();

            return Result;

If your database table is huge though, I'd consider creating some sort of TRIGGER/update script to either URL encode or decode all records, that way you don't have to check at all, you just encode/decode the parameter passed in.

edited Sep 25, 2013 at 11:56

answered Sep 25, 2013 at 11:35

Mathew Thompson

56.5k15 gold badges131 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Daniel Hilgarth Over a year ago

AsEnumerable instead of ToList would probably be better, because it doesn't unnecessarily manifest the whole result set into memory.

Mathew Thompson Over a year ago

@DanielHilgarth I re-worked my answer because I wasn't happy with the whole collection in memory.

Grant Thomas Over a year ago

Doesn't it now just do what I said, which you objected to (then since deleted the comment)? Typical.

Mathew Thompson Over a year ago

@GrantThomas I can no longer see your original answer (before you edited), but I'm sure you spoke about just doing the one check? I deleted my comment because you'd changed your answer.

Grant Thomas Over a year ago

No, I elaborated re the ToList thing - multiple checks were fundamental to the suggested routine (if both formats indeed were available).

|

Grant Thomas · Accepted Answer · 2013-09-25 11:36:57Z

1

Do your adjustments before the query so that you have absolute values for comparisons in the query that align with the particular formats, and always check against the ones in the database, rather than transform them - so, this potentially includes using OR in your query, instead of pulling the whole thing into memory a la ToList.

answered Sep 25, 2013 at 11:36

Grant Thomas

45.1k10 gold badges88 silver badges129 bronze badges

Collectives™ on Stack Overflow

Using string methods in EF LINQ queries

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related