CODEWITHSUNDEEP
c#linq
Walter Lockhart
Walter Lockhart

Reputation: 1293

Retrieve all the items from a table where the text of a table column is contained in a list of items (ASP.NET Core 2.2 C# LINQ)

I have a table called strategies which contains a number of Coping Strategies:

[
    {
        "id": 6,
        "title": "Coping with Depression",
        "description": "A coping strategy for depression.  A description of the coping strategy. \r\nLorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.",
        "topic": "depression"
    },
    {
        "id": 18,
        "title": "Coping with Stress",
        "description": "A coping strategy for stress. A description of the coping strategy. \r\nLorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.",
        "topic": "stress"
    }
]

One of the columns in that table, called Topic, can contain multiple Topic strings, e.g., "stress depression", etc. The sample data, above, illustrates the scenario that works, where there is only one Topic string in the Topic column.

My code is as follows:

var listOfTerms = new List<string>() { "stress", "depression" };

var strategies = _context.Strategies
      .Where(strategy => listOfTerms.Any(t => t.Equals(strategy.Topic)))
      .ToListAsync();

I have also tried:

var strategies = _context.Strategies
   .Where(strategy => listOfTerms.Any(t => t.ToLower().Contains(strategy.Topic.ToLower())))
   .ToListAsync();

This code works for the scenario illustrated above, i.e., only one Topic string in the Topic column.

If I were to add the Topic string 'stress' to the Topic column of Scenario id = 6 then that row would not be returned in the list of strategies.

So, in summary, I want to retrieve all the items from _context.Strategies table where the text of the strategy.Topic column is contained in the text of an item in listOfTerms, were the text of the strategy.Topic column can contain more than one Topic string.

Any help would be greatly appreciated.

Thanks.

Upvotes: 0

Views: 60

Answers (1)

Casey Crookston
Casey Crookston

Reputation: 13965

Ok, I think I finally understand your question.

Given:

class Problem
{
    public string topic { get; set; }
}

If I set it up like this:

List<Problem> problems = new List<Problem>();
problems.Add(new Problem { topic = "stress" });
problems.Add(new Problem { topic = "depression" });
problems.Add(new Problem { topic = "stress, depression" });

And then create a list of terms:

var listOfTerms = new List<string>() { "stress", "depression" };

Then I can get the results you want with this:

var result = problems.Where(item => listOfTerms.Any(term => item.topic.Contains(term))).ToList();

That linq statement gets me back all three of the 'problems'.

Upvotes: 1

Related Questions