CODEWITHSUNDEEP
c#linq
Codehelp
Codehelp

Reputation: 4747

Using string methods in EF LINQ queries

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?

Upvotes: 1

Views: 169

Answers (2)

Mathew Thompson
Mathew Thompson

Reputation: 56429

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.

Upvotes: 2

Grant Thomas
Grant Thomas

Reputation: 45083

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.

Upvotes: 1

Related Questions