How to join 2 entities in Odata model builder

Question

I am currently using OData V4 and wish to join 2 tables Account and Product:

The tables are as follows: Account: Id, Name, Address, ColorCode,

Product: Id, AccountId

AccountId in the Product table is a foreign key mapped to the Id field in the Account table

In my builder I have :

var ProductType= builder.EntityType<Product>();

When I build the Product entity I wish to get the "ColorCode" values from the Account entity.

How can i establish this relationship in the model builder?

I would like the product class to look like this:

public  class Product
{
    public string Id { get; set; }
    public string AccountId { get; set; }
    public string ColorCode { get; set; }

}

Answer 1

OData enables you to define relationships between entities. It seems that you're using Web API 2.2 for OData V4 to write your service. In this case you can build the relationship between Products and Accounts like this:

Firstly, in the definition of your POCO classes for Products, you need to add a navigation property for its account(s):

public class Product{
    public int Id {get;set;}
    public string AccountId {get;set;}
    public Account Account {get;set;} // define "Account" as a navigation property of Product

public class Account{
    public int Id {get;set;}
    public string Name {get;set;}
    public Address Address {get;set;} // Address may be a complex type
    public int ColorCode {get;set;}
}

Then in the class that inherit from the DbContext, add information about both entities in:

public virtual DbSet<Product> Products { get; set; }
public virtual DbSet<Account> Accounts { get; set; }

Finally in WebApiConfig.cs, you define the model using ODataConventionModelBuilder according to your need. The model builder will automatically recognize the relationship from the POCO classes and generate the model for you.

After the service is built, on the client side, a client send such request to get the Product and the ColorCode of its Account:

GET http://host/service/Products?$expand=Account($select=ColorCode)

An example can be viewed here: http://services.odata.org/v4/(S(xrtmlkz1igiqidututicmb2t))/TripPinServiceRW/People?$expand=Trips($select=TripId)