71

I have an OData service where I'm trying to filter by a list of IDs; the SQL equivalent would be something like:

SELECT * FROM MyTable WHERE TableId IN (100, 200, 300, 400)

The property I'm trying to filter on is typed as an Int32. I've tried the following, which gives me an error "Operator 'add' incompatible with operand types 'Edm.String' and 'Edm.Int32'":

string ids = ",100,200,300,400,";
from m in provider.Media where ids.Contains("," + t.media_id + ",")

as well as

string ids = ",100,200,300,400,";
from m in provider.Media where ids.Contains("," + t.media_id.ToString() + ",")

and

string ids = ",100,200,300,400,";
from m in provider.Media where ids.Contains("," + Convert.ToString(t.media_id) + ",")

and

string ids = ",100,200,300,400,";
from m in provider.Media where ids.Contains(string.Concat(",", t.media_id, ","))

As you can see, currently I'm using LINQ to query the service.

Is there a way I can do what I'm trying to, or am I stuck constructing a text filter and using AddQueryOption, and iterating through the list and manually adding "or media_id eq 100" clauses?

technophile
  • 3,556
  • 1
  • 20
  • 24

4 Answers4

69

With OData 4.01, in statement is supported like this:

http://host/service/Products?$filter=Name in ('Milk', 'Cheese')
martinoss
  • 5,268
  • 2
  • 45
  • 53
  • 34
    And it only took them 7 years. :D Thanks! – technophile Aug 09 '18 at 21:23
  • 1
    It seems the large API i am dealing with (famous company)... sigh is still using 2.0 so i have to result to multiple and/or conditions. – Angry 84 Mar 21 '22 at 01:57
  • What is the LINQ way of doing this? In the past I used "contains" but when I intercept the query it translates that to using a LIKE OR CHARINDEX – Zoey Aug 15 '23 at 19:13
  • @Angry84 , I'm querying a SAP API, at OData V2. However, they have implemented a custom `IN` operator, almost the same as above, without the brackets. e.g. `$filter=Name in 'Milk', 'Cheese'`. – Jodrell Aug 23 '23 at 08:06
  • @zoey Have you tried something like `.Where(x => myList.Contains(x.Name)`? myList could be an array of 'Milk' etc. – martinoss Aug 24 '23 at 16:39
41

See accepted answer, everything below is for OData v < 4.01

try this one

 var ids = new [] { 100, 200, 300 } ;
 var res = from m in provider.Media 
           from id in ids 
           where m.media_id == id 
           select m;

there is a comprehensive description on msdn on querying DataServices.

another approach would be 

var results = provider.Media
   .AddQueryOption("$filter", "media_id eq 100");

and since OData doesn't support IN statements you will come up with filter condition like this

.AddQueryOption("$filter", "(media_id eq 100) or (media_id eq 200 ) or ...");

which you can build using loop or linq Select and string.Join:

var ids = new [] { 100, 200, 300 };
var filter = string.Join(" or ", ids.Select(i=> $"(media_id eq {i})"));
var results = provider.Media.AddQueryOption("$filter", filter);

UPDATE: There is filter operation field=["a","b"] however it means something different.

UPDATE2: In OData V4 there is lambda expressions any and all, paired with array literal ["a", "b"] they might work as in but I was not able to come up with working example using v4 endpoint at OData.org

vittore
  • 17,449
  • 6
  • 44
  • 82
  • That throws an error: "Error translating Linq expression to URI: The method 'Select' is not supported." Good thought, though. – technophile Oct 12 '11 at 19:31
  • that is what i expected. so your only option to use second approach, which would be easy in your case when you do not know how many elements in a list you have in advance – vittore Oct 12 '11 at 19:57
  • 1
    Yeah, the loop with $filter is what I ended up doing. Here's hoping I never manage to exceed the URL length limitation! :-D – technophile Oct 12 '11 at 19:59
  • you can set a limit and split your list of ids, querying say 50 at a time – vittore Oct 12 '11 at 21:30
  • 2
    "...and since OData doesn't support `IN` statements...", this is the key I was looking for – Nate Anderson Aug 18 '16 at 19:45
  • There is also a possible LINQ extension (also check the comment under the answer): https://stackoverflow.com/questions/3521346/select-entities-where-id-in-int-array-wcf-data-services-linq/25259225#25259225 – BalintPogatsa Mar 27 '19 at 15:15
13

Expanding on vittore's answer (of which the second part is the correct answer), I've written something similar to the following for a demo project:

var filterParams = ids.Select(id => string.Format("(media_id eq {0})", id));
var filter = string.Join(" or ", filterParams);
var results = provider.Media.AddQueryOption("$filter", filter).Execute().ToList();

It's not elegant, and you wouldn't want to use this for a large list of ids (> ~60), but it'll do the trick.

Community
  • 1
  • 1
MCattle
  • 2,897
  • 2
  • 38
  • 54
2

Expanding on MCattle suggestion if we need more 50 or 60 ids then its advisable to do in 2 or more parallel calls and add them to concurrent dictionary or something similar as we get results from server. Though this increases the number of calls to server but because we are slowly moving to cloud environment it shouldn't be a big problem in my opinion.

Navap
  • 1,050
  • 9
  • 18