Regex in Linq (EntityFramework), String processing in database

Question

I have a column in my table which contains values as

    "FilterA:123,234,34;FilterB:12,23;FilterC:;FilterD:45;"

Filters are separated by ';' and the values of each filter are separated by ','. There is a ':' in between a Filter's name and it's values.

Now, can I do anything which could fetch out the values part only? Like "123,234,34" for "FilterA". Or can I give it a number like "234" to search in the value part of "FilterA" and/or "54" in the value part of "FilterB"? I know it is possible with using regex, i guess, but I have no idea how.

Answer 1

If your underlying database provider is SQL Server then you could make use of SqlMethods.Like to filter the database result set down to a manageable subset of data that can then be analysed locally with RegEx

Answer 2

You can't use regular expressions in Linq to Entities queries, because they cannot be translated into SQL. You even can't use String.Split to split your filters by ;. You have two options here:

• Change database table structure. E.g. create table Foo_Filter which will link your entities to filters. And then create table Filters which will contain filters data.
• Execute query in memory and use Linq to Objects. This option will be slow, because you have to fetch all data from database to memory