Regex is not working with Linq to Sql

Question

i have the following code

Regex R = new Regex("my regex");
var q = from c in db.tble1
        where R.IsMatch(c.text)
        select c;

and while debugging i saw in the q result this message

Method 'Boolean IsMatch(System.String)' has no supported translation to SQL."} System.SystemException {System.NotSupportedException}

so what have i done wrong ?

Edit:I learned that the method has no supported translation to SQL
but how to solve this

jrummell · Accepted Answer · 2012-06-07 17:21:38Z

9

Regex has no supported translation to SQL. The error says it all. You can't use regex in LINQ to SQL.

Try using a like or substring comparison instead:

var q = from c in db.tble1
        where c.text.StartsWith("x") && c.text.Substring(2, 1) == "y"
        select c;

Or, you could perform an in memory regex comparison. You can do this by calling ToList() before using the Regex:

Regex R = new Regex("my regex");
var q = from c in db.tble1.ToList()
        where R.IsMatch(c.text)
        select c;

answered Jun 7, 2012 at 17:21

jrummell

43.2k18 gold badges116 silver badges179 bronze badges

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

1 Comment

Marc Gravell Over a year ago

@Star yes, but again: TSQL does not support regex. The only option left is a SQL-CLR function that you write and install on the server, and it won't be efficient (it will table scan)

Collectives™ on Stack Overflow

Regex is not working with Linq to Sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related