Regular Expressions and Linq in C#

Question

3.00/5 (2 votes)

See more:

Suppose that I have a table such as

"t.[vf]$" | "Russian"
"tt[io]$" | "Italian"
"[dl]ez$" | "Spanish"

so that, given Potov or Potef I'll get Russian, etc. Evidently EndsWith doesn't cut the mustard, and

var q = from r in db.Origins where Regex.IsMatch("Potov", r.Pattern) select r.Origin;

gets me

"Method 'Boolean IsMatch(System.String, System.String)' has no supported translation to SQL."

Fair enough. Is there any way to do this?

Thanks

Posted 29-Apr-11 10:34am

Julio Kuplinsky

Add a Solution

3 solutions

Solution 1

You'll have to either do the regular expression comparison in a Linq-to-Objects query rather than Linq-to-SQL, or call a stored procedure that will make use of a CLR user-defined function to apply the regular expression.

See this MSDN article for an example of using the CLR in SQL Server to use regular expressions: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx[^]

Posted 29-Apr-11 11:43am

Kythen

Solution 3

Do you mean to derive nationality or ethnicity from the form of the name using some set of rules?
This is what I can see from this question. Is is something like that?

Please correct me if I'm wrong, but it this is the case, this is not just impossible or makes no sense — this is totally wrong. This is not about programming but about simple logic. I would not even explain why it's wrong — this would be really beyond good and evil…

—SA

Posted 29-Apr-11 16:03pm

Sergey Alexandrovich Kryukov

Comments

James H 19-Jul-21 12:26pm

Hey Sergey - I can tell from your name you're Russian ("t.[vf]$" | "Russian") :-)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chris Trelawny-Ross · Accepted Answer · 2011-04-29T11:47:00

You'll have to force the Origins collection into an in-memory Enumerable, first:

var q = from r in db.Origins.AsEnumerable() where Regex.IsMatch("Potov", r.Pattern) select r.Origin;

Now Linq is not looking for IsMatch() as a SQL function, but as a .Net object's method - which it will find.

See Enumerable.AsEnumerable<TSource>[^].

Linq-to-SQL objects implement IQueryable<TSource>[^] - which translates the Lambdas into SQL queries (which, in the case of your example above, fails). The cast to AsEnumerable forces Linq to use the Linq-to-objects Where method instead.