How Would I Write This In LINQ2SQL?

Question

I am slowly porting over an app from MySQL to use Linq2Sql - but one query has stumped me a bit.

SELECT * FROM Pages WHERE DomainID = @reportid AND (PageContent REGEXP 'display\:[ \t]*none') > 0 ORDER BY URL ASC

Any ideas on how I would write something like this with Linq2SQL? Its the REGEXP bit thats got me stumped?

Answer 1

There is no way built in to LINQ to SQL, but you have a couple of other choices. The first is to load your strings in as in-memory objects which you can apply Regex functions to. I'm not a big fan of this since it looks like you're potentially getting some very big strings to match against.

The second option is to leverage SQL CLR as described here. This effectively lets you create a stored procedure that gets linked to a CLR method that you create. Whenever you call the method in a LINQ to SQL context, it gets converted to a stored procedure call. Then you use a query like this:

var q = from p in context.Pages
        where p.DomainId == reportId && 
              RegExMatch(p.PageContent, "display\:[ \t]*none")
        select p;

Answer 2

Why not use LINQ to return items that match on reportid and that contain 'display:', to minimise the amount of data being returned from the server, and then use regex on client side to filter that list down?

var query = Pages.Where( p => p.DomainId == 1 && p.PageContent.IndexOf("display:") > 0).OrderBy( o => o.URL );

var regex = new Regex(@"display\:[\t]*none");

foreach (var page in query)
{
    if( regex.IsMatch(page.PageContent) )
    {
        // Do whatever...                    
    }                    
}