Optimize query expression with .contains()

Question

I have a many-to-many table that correlates an object to a place, for example

I have a different table of many places, and there are several tables linked into it by foreign key like address book entries and history. What I need is to send a collection to my View in MVC of all the information for a place, but only if it is a place used in my many-to-many table, ie in the example places 1, 3, 9, and 2

So right now I do two queries

var places = myodataservice.Get(new QueryBuilder<MapTable>())
                           .Select(t => t.PlaceId)
                           .Distinct();

var returnable = myodataservice.Get(
    new QueryBuilder<Places>(),
    p => p.Address,
    p => p.Address.State,
    p => p.Logo
).Where(p => places.Contains(p.Id))

This is both very slow, and seems like a terrible way to run a query. Is there a better way to achieve this, in possibly a single query?

I'm not quite sure but can you try .Any(p => places.Contains(p.Id)) instead of .Where(p => places.Contains(p.Id)) please? — gkc, Mar 21 '14 at 00:10

score 0 · Accepted Answer · answered Mar 21 '14 at 07:56

The $crossjoin in OData v4 may solve your problem, but currently it is not implemented in WebApi. But the OData Unbound Function can meet your requirement, here is an example about it: http://aspnet.codeplex.com/SourceControl/latest#Samples/WebApi/OData/v4/ODataFunctionSample/FunctionSample/

Say your function name is GetReferencedThings() then you can call it through below url:

~/service-prefix/GetReferencedThings()

In implementation of it, you can leverage the sql script to get the distinct Things and return to the client.

Optimize query expression with .contains()

1 Answers1