Filter by comma-separated data

Question

My website posts have tags created to classify the posts. I am using the tags to create a tag filter for the posts. StackOverflow tags section as an example.

I am using the MSSQL database column that stores multiple row IDs separated by comma(s).

Here is an example:

Id	Title	Tags
1	Title 1	1,2,3
2	Title 2	3,4,7
...	...	...

I want to split them using comma in my Controller Action, but I am facing some issues:

Controller action method:

[HttpGet]
public async Task<IActionResult> Detail(int id)
{
    var taggedPosts = from p in _context.Posts select p;

    taggedPosts = taggedPosts.Where(p => int.Parse(p.Tags) == id);
    
    var Tags = new DetailTagsViewModel
    {
        PostCard = await taggedPosts.ToListAsync()
    };

    return View(Tags);
}

I have tried separating p.Tags using .Split(',') but it doesn't work.

Here is what I have tried:

• Creating a new variable splitTags:

var splitTags = from p in _context.Posts select p.Tags.Split(',');

Gives error:

1. CS0854 - An expression tree may not contain a call or invocation that uses optional arguments.
2. CS8602 - Dereference of a possibly null reference.

• Modifying variable taggedPosts:

taggedPosts = taggedPosts.Where(p => p.Tags.Split(',').Select(int.Parse) == id);

Gives error:

1. CS0019 - Operator '==' cannot be applied to operands of type IEnumerable<int> and int
2. CS8602 - Dereference of a possibly null reference.

Answer 1

Doubt that there is no support for converting String.Split() into SQL expression in Entity Framework Core/LINQ.

The only way to achieve by fetching the records from the database and performing filtering on the memory side. This may sacrifice the query performance as it will fetch all the records from the database.

The concept for filtering:

1. Split Tags by comma (',') and result in an array of strings.

2. Convert the array of strings to an array of integers.

3. Evaluate the id existing in the integers via .Contains().

var taggedPosts = await (from p in _context.Posts select p)
    .ToListAsync();

taggedPosts = taggedPosts.Where(p => p.Tags
        .Split(',')
        .Select(x => Convert.ToInt32(x))
        .Contains(id))
    .ToList();
    
var Tags = new DetailTagsViewModel
{
    PostCard = taggedPosts
};