OData WebAPI SubQuery

Question

I am using ODATA in my WebApi controller.

Id      ProductID   SubProductID    ProductName                       
1       111         NULL            Car       
2       111         666             Wheels    
3       111         777             Seats     
4       112         123             brakes

https://localhost:30004/api/ProductManagement/Product?$filter=ProductName eq 'Car'

Car is the main Product and Wheels,Seats are subproducts.When I make above call it will retrieve 'Car' but my requirement is to get also sub products related to Car.

Car ProductID is 111 and Wheels,Seats ProductID is also 111.

So I am looking for a ODATA query where it will return Car and its subproducts.Something like below but I know only name of ProductName "Car" but not ProductID.

My requirement is to get a record with ProductName "Car" and use its ProductID "111" in the same call or query.

https://localhost:30004/api/ProductManagement/Product?$filter=ProductName eq 'Car' and ProductID = Product.ProductID

@TitianCernicova-Dragomir one Product will have mutiple SubProducts. — reddy

JJa JJa · Accepted Answer · 2018-02-14T18:01:30

To achieve required behavior, changed the table structure as below

 Id      ProductID   ParentProductID ProductName                       
 1       111         NULL            Car       
 2       666         111             Wheels    
 3       777         111             Seats     
 4       123         112             brakes 
 5       112         null            secondparent

Created primary key on productid and foreignkey on parentproductid referencing productid on same table.

So it created below class when I added entity model from DB

  public partial class ProductMangement
   {
    public ProductMangement()
    {
        this.ProductMangement1 = new HashSet<ProductMangement>();
    }

    public int id { get; set; }
    public int productid { get; set; }
    public Nullable<int> subproductid { get; set; }
    public string productname { get; set; }
    public virtual ICollection<ProductMangement> ProductMangement1 { get; set; }
    public virtual ProductMangement ProductMangement2 { get; set; }
}

And using $expand,$filter in OData options as below

http://localhost:2233/api/ProductMangements?$filter=productname%20eq%20%27car%27&$expand=ProductMangement1

Got below JSON data

[{"id":1,"productid":111,"subproductid":null,"productname":"car","ProductMangement1":[{"id":2,"productid":666,"subproductid":111,"productname":"wheels"},{"id":3,"productid":777,"subproductid":111,"productname":"Seats"}]}]

OData WebAPI SubQuery

1 Answers