OData "where ID in list" query

Question

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?

Answer 1

With OData 4.01, in statement is supported like this:

http://host/service/Products?$filter=Name in ('Milk', 'Cheese')

Answer 2

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

Answer 3

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.

Answer 4

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.