How can I use Linq to find items with matching words from a comma separated string?

asp.net-core c# entity-framework-core linq

Question

I am trying to write a Linq query to find items with the string property Tags containing at least one of the words in a comma separated string, queryTags.

The property Tags is also a comma separated string, and a value could be "toy,ball,red,plastic".

The search-string queryTags could be for instance "red,ball". In this case, the item should be selected because both "red" and "ball" matches.

This is what I have so far:

Model

public class WebContentsImage
{
    public int Id { get; set; }
    public string Tags { get; set; } // E.g. "toy,ball,red,plastic"
    // some more properties
}

Query

List<WebContentsImage> Images = await db.WebContentsImages
    .Where(t => t.Tags.Any(queryTags.Contains))
    .ToListAsync()
    .ConfigureAwait(false);

I don't really understand what the query above should be doing. I have just copied it from this answer, hoping it aligned with what I am trying to do. Anyway, I get this runtime exception from it:

NotSupportedException: Could not parse expression 't.Tags.Any(Convert(__CreateDelegate_0, Func`2))': The given arguments did not match the expected arguments: Object of type 'System.Linq.Expressions.UnaryExpression' cannot be converted to type 'System.Linq.Expressions.LambdaExpression'.

Previously, I had this:

List<WebContentsImage> Images = await db.WebContentsImages
    .Where(t => t.Tags.Contains(queryTags.Split(",")))
    .ToListAsync()
    .ConfigureAwait(false);

... which has this compile time error:

Argument 1: cannot convert from string[] to char

And this (which is just plain wrong):

List<WebContentsImage> Images = await db.WebContentsImages
    .Where(t => t.Tags.Contains(queryTags, StringComparison.CurrentCultureIgnoreCase))
    .ToListAsync()
    .ConfigureAwait(false);
1
0
1/25/2020 2:09:41 PM

Accepted Answer

You’re using a db it seems, so you need to remain mindful of what can and what cannot be converted into an SQL

You might find better success with something like:

var x = db.WebContentsImages;

foreach(string tag in queryTags.Split(','))
    x = x.Where(t => (',' + t.Tags + ',').Contains(',' + tag + ',', StringComparison.CurrentCultureIgnoreCase));


var list = x.ToListAsync();

The repeated where will act cumulatively and hopefully generate a set of sql like:

SELECT * FROM table 
WHERE ','+tags+',' LIKE '%,red,%' AND
      ','+tags+',' LIKE '%,ball,%'

I should point out though that this is a fairly horrific way to store the data in the db and ask it to return things based on string LIKE.. tags should really have their own table and then a middleman table maps what Things have which Tags

You’re becoming confused with the current structure because string has a Contains method that returns true if a substring exists within this string, and Linq extends ienumerable collections to also have a different Contains method that tells whether a collection Contains a particular element. You’re mixing the two and asking string Contains to report on whether the string Contains an array etc. You need to be using the LINQ Contains all the time, which means splitting your Tags on comma and then asking if the resulting array of strings contains all of the array of strings resulting from splitting the queryTags on comma too

The problem is that while that can happen on the client I doubt your ORM will be able to carry out a split on the server side which means it will have to drag the entire db table down to the client. This is why I went the other way and converted everything to use String Contains in the hopes that it will become a bunch of LIKE AND on the server..

It would be better not to store the data this way

If Tags and queryTags were some kind of IEnumerable of string, you’d stand a better chance of saying

products.Where(
  product => queryTags.All(queryTag => product.Tags.Contains(queryTag)
)

In English this is “for all products, filter to only products where all of the queryTags are present in the product.Tags list

You could manipulate your current data thus; make queryTags a string result from splitting the query string on comma, and rename your Tags csv string as TagsCsv and make a new property called Tags that returns TagsCsv.Split(',') but I wholly expect that it would have to be executed on the client, not in the db

var queryTags = “red,ball”.Split(',');

//horrific, for illustration purposes only
class Product{
  String TagsCsv = “red,plastic,round,ball”;
  String[] Tags { get { return TagsCsv.Split(',') } }
}
3
1/25/2020 2:52:35 PM

Popular Answer

this should do it

List<WebContentsImage> Images = await db.WebContentsImages
    .Where(i => i.Tag.Split(",").Any(t => queryTags.Split(",").Contains(t)));
    .ToListAsync()
    .ConfigureAwait(false);

you were nearly there



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow