linq return all items where string field is like any in a list of strings

Question

I have a SQL table storing all enquiries on my site. the database isn't normalized and destinations of the site are stored as a comma separated variable based on the ID of the country they want to go to.

I've used LINQ to get a list of all country ID's deemed as 'close'. I'd then like to use that in linq to get all enquiries where the CSV contains any of those country ID's.

so for example my countries may be stored as a list 6 | 20 | 24

and i would like to return all enquiries where the CSV value has any of those values in it. so if the CSV value was '18,54,23,27,6' then it would return that enquiry. My main problem is because this value is stored as a string in the table, so any comparison looking for a part of that string is unavaliable. If it were SQL directly i would type where Destinations like('%6%').Repeated for each example in the list.

I have had a go, and it isn't working.

Dim count As Integer = ctx.tbl_Enquiries.Where(Function(f) f.Destinations.Contains(shorthaulIds.Any())).Count I tried this, but It compares against the whole destination CSV. rather than looking for a string within the string.
I tried turning the csv to a list on the fly, which obviously wont work as string functions in linq tend not to work. - if you're interested in that then Dim count As Integer = ctx.tbl_Enquiries.SelectMany(Function(f) f.Destinations.Split(",").Where(Function(fe) fe.Split("c").Contains(shorthaulIds.Any()))).Count

I also tried some intersects, but as I hadnt used them before I wasn't really sure what I was doing

for c# users

int count = ctx.tbl_Enquiries.Where(f => f.Destinations.Contains(shorthaulIds.Any())).Count

@RahulSingh sure, I'll just update the answer now, as i have tried a few — LiamHT, Dec 03 '14 at 11:02
refer http://stackoverflow.com/questions/500925/to-check-if-a-string-contains-an-element-from-a-list-of-strings-is-there-a-b — İsmet Alkan, Dec 03 '14 at 11:17
@IsThatSo i'm pretty sure that would have worked, however i'm using linqtosql so cannot use the Contains operator — LiamHT, Dec 03 '14 at 11:35
its just a drawback of linqtosql. one of the many reasons why EF is so prevailant now — LiamHT, Dec 03 '14 at 15:33

score 1 · Answer 1 · answered Dec 03 '14 at 11:16

1

The code

shorthaulIds.Any()

returns true if shorthaulIds has a count of one or more, otherwise false.

So the code

f.Destinations.Contains(shorthaulIds.Any())

if asking if the Destinations container contains true. I'm sure that's not what you want.

Try this:

 int count = ctx.tbl_Enquries
                  .Where(f => f
                               .Destinations
                               .Any(d => shortHaulIds.Contains(d))
                        )
                  .Count();

answered Dec 03 '14 at 11:16

Andrew Shepherd

44,254
30
139
205

that seemed to error... Additional information: Sequence operators not supported for type 'System.String'. However i'm pretty sure that the code you're implying gives me the same problem of comparing against the whole comma seperated string. infact in this example we are looking for where the idtable contains the whole destination string. so we are asking if '30' contains '30,24,54,12,64' – LiamHT Dec 03 '14 at 11:26

score 0 · Answer 2 · answered Dec 03 '14 at 11:57

Thanks for all your help. As I was using LinqToSQL I couldn't use the contains in my main query. so after a bit of faffing i got it to work.

for those interested:

I got all of the corresponding enquiry destinations as a list of string, taking it out of the context of linqtosql. then did a contains on the next query. props to @IsThatSo

Dim allFrench As List(Of String) = ctx.tbl_Enquiries.Where(Function(f) f.Subjects = (",30") Or f.Subjects = (",60")).Select(Function(s) s.Destinations).ToList()

Dim count As Integer = allFrench.Where(Function(f) shorthaulIds.Any(Function(s) f.Contains(s))).Count()

linq return all items where string field is like any in a list of strings

for c# users

2 Answers2