13

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.

Improve this question
2
  • Do you want to find some rows based on a filter's value or do you want to extract the filter values from some already selected rows? Commented Jan 19, 2013 at 21:46
  • I want to select some rows comparing my data with the data in the filter string Commented Jan 20, 2013 at 11:48

4 Answers 4

Reset to default
13

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

"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" this is what i have been trying to avoid!
@TahaRehmanSiddiqui then only first option left for you. There is no way to fetch FilterA data from string with Linq to Entities
EF is such a complicating and limiting pile of s....
5

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

Improve this answer

Comments

0

+1 for @lazyberezovsky, you can't do regex in Linq to Entities because it can't translate that into SQL. You could pull all records back into memory then do Linq to Objects on it (do a .ToList() to a variable then a second linq query for this regex) but that means you'll load every db record into memory to process this query. Same recomendations: change the DB structure such that you don't need to do this.

Improve this answer

Comments

0

I guess if you wanted everything to execute on the database server you'd need a SQL function that could parse these filters, perhaps returning a multi-column table. Unfortunately I am not on a machine where I could provide any sample code.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.