CODEWITHSUNDEEP
c#sqllinqentity-framework
Stellar Sword
Stellar Sword

Reputation: 6226

Search contains in a string column for a list of strings using LINQ

So I am using entity framework and linq, but I am using the function methods.

say I have a list of IDs like this:

List<string> bids = { "33", "44", "55", "66" }; // this is what the split ends up

But in the database, I have a string-column called "BuildingIDs" stored as comma-separated values. "query" is a IQueryable<BuildingTable>.

        List<string> bids = search_string_full_of_comma_bids.Split(';').ToList();
        query = query.Where(t => bids.Contains(t.BuildingIDs));
        //query = query.Any(t => t.BuildingIDs.Contains(bids); // Loop needed.
        return query;

So I want to search "BuildingIDs" database string column, to see if it has ANY of the "search_string_full_of_comma_bids"

BuildingTable in database:

project 1 | pname = 'building complex #1' | BuildingIDs = '33;54;42;56;21;56;32;'
project 2 | pname = 'building complex #2' | BuildingIDs = '77;42;31;12;33;'

But my search query could be "33" or "77" ... Searching "33" will deliver both. Searching "54;31" will deliver both as well.

So a standard SQL query would have been like: 

SELECT * FROM BuildingTable WHERE BuildingIDs LIKE '%54%' OR BuildingIDs LIKE '%31%'.

Unfortunately I can't just add a bunch of "ORs" in Linq.

Upvotes: 0

Views: 2709

Answers (2)

NinjaNye
NinjaNye

Reputation: 7126

I agree with @tim-s in that the data structure is the cause of the issue here, however, I created a search extensions nuget package that can help with this. NinjaNye.SearchExtensions will enable you to something like the following:

using NinjaNye.SearchExtensions;
...
List<string> bids = search_string.Split(';').Select(b => ";" + b + ";");
query = query.Search(t => ";" + t.BuildingIDs + ";").Containing(bids)

This will return just the records that contain any of the supplied BuildingIDs

Upvotes: 0

AD.Net
AD.Net

Reputation: 13399

List<string> bids = search_string_full_of_comma_bids.Split(';').ToList();
        query = query.Where(t => bids.Any(b=> 
                               t.BuildingIDs.StartsWith (b+";") //if it's in the start
                              || t.BuildingIds.EndsWith (";" + b) //end 
                              || t.BuildngIds.Contains(";" + b + ";") //;id;
                              ));

You can try something like this, but this is ugly. You should have tables instead of saving Ids in a string.

Given your data you might not need the .EndsWith at all, usually you'll use that when the last one does not have ";" at the end.

Upvotes: 4

Related Questions