How can I filter by nested properties in OData?

Question

I'm using OData together with Web API to return the following JSON:

[
{
    "EmployeeID": 1,
    "FirstName": "Nancy",
    "LastName": "Davolio",
    "Title": "Sales Representative",
    "HireDate": "\/Date(704649600000)\/",
    "Territories": [
        {
            "TerritoryID": "06897",
            "TerritoryDescription": "Wilton"
        },
        {
            "TerritoryID": "19713",
            "TerritoryDescription": "Neward"
        }
    ]
}
]

How can I filter the JSON to display items that belong to the Wilton territory, for example? I've tried this but doesn't seem to work:

http://localhost:62559/Home/Read?$filter=Territories/TerritoryDescription eq Wilton

This is the code I'm using to return objects from the database using a repository pattern:

[Queryable]
public IQueryable<EmployeeViewModel> Employees
{
  get
  {
    return context.Employees.Select(e => new EmployeeViewModel
    {
        EmployeeID = e.EmployeeID,
        FirstName = e.FirstName,
        LastName = e.LastName,
        HireDate = e.HireDate,
        Title = e.Title,
        Territories = e.Territories.Select(t => new TerritoryViewModel
        {
            TerritoryID = t.TerritoryID,
            TerritoryDescription = t.TerritoryDescription
        })
    });
  }
}

Here is the controller that returns objects in JSON:

public ActionResult Read()
{
    return Json(repository.Employees, JsonRequestBehavior.AllowGet);
}

Solution 1

I came across the following answer while reading up on questions related to mine:
Nested filter on Data Transfer Object using OData Wep Api

Funny thing is, I never came across that question in my previous searches on SO. Otherwise I wouldn't have to ask this question. Anyway this method works for me:

http://localhost:62559/Home/Read?$filter=Territories/any(c:%20c/TerritoryDescription eq 'Wilton')

Solution 2

Assume that you have a nested JSON Array Object like this and have to apply Odata filters on the below batters/topping keys, then you have to use a / to refer to the key.

For example, you have to select batters id - The syntax would be Filter: batters/id eq '1001' ; batters/type ne 'Chocolate'

{
"id": "0001",
"type": "Cook Cake",
"name": "Customized",
"batters":
            [
                { "id": "1001", "type": "Regular" },
                { "id": "1002", "type": "Chocolate" },
                { "id": "1003", "type": "Blueberry" },
                { "id": "1004", "type": "Devil's Food" }
            ],
"topping":
    [
        { "id": "5001", "type": "None" },
        { "id": "5002", "type": "Glazed" },
        { "id": "5005", "type": "Sugar" },
        { "id": "5007", "type": "Powdered Sugar" },
        { "id": "5006", "type": "Chocolate with Sprinkles" },
        { "id": "5003", "type": "Chocolate" },
        { "id": "5004", "type": "Maple" }
    ]
}