First, Sorry for the delay in sending this.

To filter data using subquery should be done in server. We need to:

• create/modify a method in server and do subquery
• using ServiceDescriptor in Core project (client) to construct the request

In my end, I create a project using Business project template and filter Category data using subquery with a simple condition: the app will list categories in a selection list where location is equal to "My Room". Location in my project is a field from Items table (related table)

Following snippet is added into the Controller class.

[HttpGet]
public QueryResult CategoriesByLocation(string location, ODataQueryOptions<Category> options)
{
        EntityContextProvider<InventoryEntities> db2 = new EntityContextProvider<InventoryEntities>();
        List<Item> items = db2.Context.Items.Where(o => o.Location == location).ToList();

    //List<int> categories = items.Select(o => o.CategoryID).Distinct().ToList();

    List<int> categories = new List<int>();
    foreach (var item in items)
    {
        if (!categories.Contains(item.CategoryID))
            categories.Add(item.CategoryID);
    }


    var query = db.Context.Categories.Where(o => categories.Contains(o.CategoryID));
    IQueryable results = options.ApplyTo(query);
    return new QueryResult { Results = results, InlineCount = this.Request.GetInlineCount() };
}

The subquery is done in here. This request: http://[server url]/data/inventory/categoriesbylocation?location=My%20Room will be handled by CategoriesByLocation().

In client, we can use ServiceDescriptor to contruct the request. Simply add following snippet code in ViewModel.

public CategoryListViewModel()
{
    this.ServiceDescriptor = new ServiceDescriptor();
    this.ServiceDescriptor.Parameters.Add("location", "My Room");
    this.ServiceDescriptor.ServiceName = "CategoriesByLocation";
    this.ServiceDescriptor.HttpMethod = HttpMethod.GET;
}

Hope this help. I will prepare the sample that I used in my local end to be uploaded soon.

Hi Yudi,

Thanks for your feedback. Please do provide the sample solution for this sample.

Sorry for the delay in sending this.

Please obtain the sample in here and let us know whether it helps or not.

Hello Jimmy,

The incremental loading, filtering and paging are actually features that were built into the EntityController. Behind the scene, the options passed from the client is transformed into LINQ expression which is then sent to theserver for execution.

Per our experience, it will be quite tedious to achieve these functions at SP level, since you need to parse each option and apply it to your query subsequently.

However, let's say you already managed to implement the functions at SP, you can continue using QueryResult using the same method signature. But, instead of executing the query using db.Context, you call the desired SP and pass the needed parameters manually. Once your query returns, pass the result (should be either IList orIEnumerable) to the Results property of the QueryResult. This should allow the method to be still compatible withe client request, while at the same time enable you to use custom SP call in the server.

Hope this helps.

Hello Jimmy,

I was previously assuming that you already have a custom way to call the SP. Our best practice is usually generating the functions automatically from the EDMX to avoid mistakes in parameters definition, which is also goodfor maintenance. When the specific SP is added to the EDMX, Intersoft Entity Designer will automatically generate the code in both EntityContext and in the Controller. Technically, the underlying code in the EntityContext actually use "Context" to perform the SP call which is completely fine since it routes the call to SP directly without requiring LINQ.

You can then simply call the SP in your query method such as shown in the simple code snippet below:

[HttpGet]public QueryResult ItemsByName(string name, ODataQueryOptions<Item> options)
{
    return new QueryResult()
    {
        Results = this.GetItemsByName(name), 
        InlineCount = this.Request.GetInlineCount() 
    };
}

In the above example, the query calls the GetItemsByName method which was an auto-generated method fromthe Intersoft Entity Designer.

However, please note that the InlineCount might not work using the built-in Request.GetInlineCount method since the actual query is performed with a custom SP. In that case, you'll need to come up with another SP that returns the total number of rows of the query. But, if the app doesn't require paging, then InlineCount is not required.

Hope this helps!