LINQ and REGEX.REPLACE

Question

I am trying to pull address records out of a database and group them together by address. Simple enough right?

The problem I has is the LOCATION field is formatted as such

BUILDING:  some building description
ADDRESS: 555 1st Street

or

BUILDING:  some building description  ADDRESS:  555 1st STREET

There are multiple instances where the address, city and state match. But the BUILDING: description does not. So I need to remove anything prior to and including ADDRESS:

These seems simple enough. But I cannot seem to get it to work. Here is my current LINQ

Dim addressesP1 = _
        (From R In tickets _
         Where R.PRIORITY = 1 _
         Group R By R.Z_LOC_CITY, R.Z_LOC_STATE, _
         ZLOC = Regex.Replace(R.Z_LOCATION, "/.+address:/is", "") _
         Into Group _
         Where Group.Count >= P1Count _
         Select ZLOC, Z_LOC_CITY, Z_LOC_STATE, thecount = Group.Count)

For Each c In addressesP1
        list.Add( _
                New TicketAddress With _
                     {.z_location = c.ZLOC, _
                      .z_loc_city = c.Z_LOC_CITY, _
                      .z_loc_state = c.Z_LOC_STATE, _
                      .thecount = c.thecount() _
                      })
    Next


    Return list

When ran I get the following error:

Method 'System.String Replace(System.String, System.String, System.String)' has no supported translation to SQL

Answer 1

Were you expecting to perform all the querying within SQL? If so, I think you'll be disappointed - I don't think LINQ to SQL supports regular expressions.

You could use AsEnumerable() after the first Where clause if you're happy to do the rest of the query in-process.

Note that you could build the list more easily by performing another Select and then calling ToList, too.

Answer 2

If you want to allow Regex queries in your LINQ to SQL you can create a SQL Function that uses SQLCLR (an example here), and define that method in your data context. This way you can use regular expressions within your LINQ queries.