Entity Framework query a comma separated field

Question

I have a table called Article:

-Id
-Title
-Content
-Tags (This is a comma seperated list of tags e.g. 'Sports,Italy,Ferrari')

Using entity framework i want to be able to find all articles that have certain tags.

List<Article> GetArticles(List<String> tags)
{
  //do an entity framework query here to find all articles which have the tags specified
}

The returned entries should contain all the tags specified. e.g. If the input to the function was 'car','blue', then all entries with these at least these 2 tags should be returned.

I can't begin to imagine how i might implement this. I know how to achieve this using a stored procedure which is my plan b.

I assume you have no control over this data structure? You can't make article have a ICollection instead? Comma separated fields in dbs are just a pain, using Linq or SQL. There are ways to deal with it, but they are all more complicated and slower than just doing in properly. — Mant101, Feb 12 '16 at 16:52

Chris Pratt · Accepted Answer · 2016-02-12T17:27:42.327

5

A stored procedure is actually a good option, if not the best option, really. However, you can still achieve it with Contains:

var articles = db.Articles.Where(m => tags.Any(t => m.Tags.Contains(t)));

What this does is basically iterate over the passed in tags and creates a query that tests whether Tags is LIKE any of those. Bear in mind that because this is going to be a LIKE query that it's going to be sloooooowwwww, unless your Tags column is indexed, which then also requires that be a defined length, rather than something like NVARCHAR(MAX).

Another potential issue is false matches. Let's say you have the tags, "Read" and "Reading", because this is a LIKE query, search for the "Read" tag, will return both. The only way around this, really, is to add a tag-delimiter to your data, for instance: "[Read],[Reading]". Then, you search instead of for "[Read]", instead of "Read", and you're guaranteed to only pull back the right one.

When I need to pack/unpack strings like this, I'll typically do something like:

public string Tags
{
    get
    {
        return TagsList != null
            ? String.Join(",", TagsList.Select(tag => "[" + tag + "]"))
            : null;
    }
    set
    {
        TagsList = !String.IsNullOrWhiteSpace(value)
            ? value.Replace("[", "").Replace("]", "").Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList()
            : new List<string>();
    }
}

[NotMapped]
public List<string> TagsList { get; set; }

That looks fairly complicated, but all that's going on is that the setter is unpacking the list of tags on the delimiter, removing the phrase-delimiting characters I chose to use, [ and ]. The getter packs the list back into a string with all that.

This allows you to then just work with TagsList and the string version will be persisted automatically without having to think about it. However, when querying, you will still have to query on Tags, since that is the actual database-backed property.

edited Feb 12 '16 at 17:27

answered Feb 12 '16 at 16:41

Chris Pratt

232,153
36
385
444

Could you suggest a better approach instead of using comma separated value? :) – Triet Doan Feb 12 '16 at 16:53
3

Well, you could always make your tags first-class objects in their own right, so that you actually have a `Tags` table, and then a M2M relationship between that and `Article`. That removes pretty much all the headache and makes querying very simple and fast. However, if you need to keep it as a string, then no, that's your only option. – Chris Pratt Feb 12 '16 at 16:55
`Tags` table has a M2M relationship with `Article`. Yes, it's normalized. However, It's hard to update data, right? – Triet Doan Feb 12 '16 at 17:02
Somewhat, yes. For an M2M, you need to be careful about replacing the collection. Adding a tag, removing a tag, etc. is simple. However, if you have something like a multiple select and you're sending back a new list of tags, that might not include some existing ones while also may contain some new ones, then you have to account for that by removing the appropriate tags and adding the new tags individually on the entity's collection rather than just simply setting the collecting to the posted list. – Chris Pratt Feb 12 '16 at 17:07
By the way, I updated the code above. The custom getter and setter needs to be on `Tags`, not `TagsList`. – Chris Pratt Feb 12 '16 at 17:09
I think, because we often read the tags much more more than updating them, the disadvantage of M2M relationship approach is not too big. So it is the best solution :) – Triet Doan Feb 12 '16 at 17:13
I think [this](https://stackoverflow.com/a/35368849/1349365) is pretty good solution. – Moshi Sep 27 '19 at 14:29

Ivan Stoev · Answer 2 · 2016-02-12T17:44:00.847

Since there is no construct that allows parsing inside the query, you can use the following trick

var query = db.Articles
    .Where(article => tags.All(tag => ("," + article.Tags + ",").Contains("," + tag + ",")))
    .ToList();

It basically does the opposite of parsing by checking if the table field contains all the passed tags by using string.Contains and Enumerable.All constructs which are both supported by the EF. Concatenating the comma at the beginning/end is needed to correctly handling the first/last tags inside the string.

score 0 · Answer 3 · edited Nov 23 '17 at 11:10

how to go for multiple data in column have comma separated data.

eg: Ienumerable<string> searchItems --- it will list say A,B,C table with column Subscribe --- having data say "E,A,R,C,B,D,H"

So we need predicate to generate query with where clause having search to be generate query as:

WHERE Subscribe LIKE'%A%' OR LIKE'%B%' OR LIKE'%C%'

currently it generated:

WHERE Subscribe LIKE'%A%' AND LIKE'%B%' AND LIKE'%C%'

when we try - searchItems.ForEach(y => query = query.Where(y => y.Subscribe.Contains(y)));

score -1 · Answer 4 · edited May 23 '17 at 11:52

-1

You need a csv parser. Entity framework should only get you the field (Tags) as a string variable. The parser will then be able to split that single string into your List Tags.

edited May 23 '17 at 11:52

Community

1
1

answered Feb 12 '16 at 16:39

Necoras

6,743
3
24
45

Entity Framework query a comma separated field

4 Answers4

Linked