0

I'm using LINQ to query my database and my records have a status (Active, Inactive, Hold, Pending, etc...). When I do a contains with the inactive status it also returns active since it does contain that value.

My UI allows the user to check off which statuses they want to return which I pass to my back-end as an array. I know that doing an IN clause in LINQ goes like this "where statuses.contains(t.Status)". My problem is that if I pass in Inactive it will match active because it does contain the word. In T-SQL if I had WHERE status IN ('Inactive') it would only return records that are inactive. Has anyone one done something that behaves like a T-SQL IN clause?

Zoey
  • 226
  • 2
  • 13
  • What's `statuses`? A List of enums? – Diado Sep 04 '19 at 13:38
  • 1
    You need to post the code as well. It's not very clear what you are doing at the moment. What type is status? – Marius Stănescu Sep 04 '19 at 13:39
  • 2
    `Contains` is a method on `IEnumerable` and also on `String` so it is not clear what you are using when you mention `where statuses.contains(t.Status)` – Canica Sep 04 '19 at 14:03
  • It is likely that @Zoey has created a string with comma separated values in it `"a,b,c"`. When she should be using instead an **array** of strings `["a", "b", "c"]`. https://stackoverflow.com/questions/44991481/in-clause-in-linq-expression is likely worth a read. – mjwills Sep 04 '19 at 14:05
  • If I'm reading this correctly, the problem is that "Inactive" contains "active". That means you're checking to see if a string contains a string, not checking whether a set of strings contains a string. But either way, this is is impossible to answer without some code. – Scott Hannen Sep 04 '19 at 15:11
  • Sorry for the delay, had to demo my app. statuses is a comma delimited string of statuses: "Active, Inactive". Maybe that's why it's doing a CHARINDEX instead of IN (I ran profiler to see what the query was doing). I'm deserializing JSON from Angular so my filters are a Dictionary of which one is a list of statuses. Maybe if I switch this to a List it will do an in clause. – Zoey Sep 04 '19 at 15:43

2 Answers2

0

Its difficult to tell exactly what you want without code here.

but this is what im guessing...

You are doing a .Contains("active") and that's returning rows that are also status of Inactive?

If that's the case can't you do something like this? 

var active = rows.Where(r => { r.status == "active" });

If this is not correct please can you post some code to help us out a little.

Thanks.

Jayson Harris
  • 11
  • 1
0

Ok, so I'm embarrassed to say my problem was because it was using a string with comma separated values rather than a string array. As soon as I switch to a string array my query used an IN clause rather than CHARINDEX.

My apologies for not being more specific in my question. @mjwills I noticed you had the answer after I refreshed.

Zoey
  • 226
  • 2
  • 13