Inner Join and Outer Join In Odata

Question

I ma trying to consume data from an OData Service. I want to know Is there any concept of inner and outer join in OData?If not, is there any way to carry out the same.

CustomerID----------------------CustomerName
1 -----------------------------------Alfreds Futterkiste
2------------------------------------Ana Trujillo
3 ---------------------------------- Antonio Moreno

And a selection from the "Orders" table:

OrderID--------CustomerID

10308------------------2

10309-----------------37

10310----------------77

Output should be in case of Outer Join---

Name-------------------------------OrderId

Alfreds Futterkiste-----------------null

Ana Trujillo------------------------10308

Antonio Moreno----------------------null

null--------------------------------10309

null--------------------------------10310

What will be OData URL for this case?

Solution 1

In Sql Server, you would need to issue a FULL OUTER JOIN to obtain the above results. However, the results you posted can only be obtained if there is no database enforced Foreign Key relationship on Order.CustomerId, otherwise, every Order would have a Customer, and the first column would never have nulls.

(A CROSS JOIN would simply match every Customer with every Order.)

So the FULL JOIN sql query would be:

         SELECT ISNULL(C.CustomerName,'') AS CustomerName, ISNULL(O.OrderId,0) AS OrderId
           FROM Customers C
FULL OUTER JOIN Orders O ON C.CustomerId = O.CustomerId

I am not aware of any oData syntax to produce such a result from an entity model based on both tables. However, if it was truly needed, you could produce a view using the above syntax, and then create a single oData entity representing the results of a full join, and query for it.

Assuming you create such a view, and use some default values instead of nulls as above, the oData syntax would simply be:

/CustomerOrders()?$expand=Customer,Order&$select=Customer/CustomerName,Order/OrderId

which produces:

CustomerName                       OrderId
Ana Trujillo Emparedados y helados 10308
                                   10309
                                   10310
Alfreds Futterkiste                    0
Antonio Moreno Taquería                0

My C# CustomerOrder class/model looks like this:

public class CustomerOrder
{
  [Key]
  public int Id { get; set; }

  public int OrderId { get; set; }

  public int CustomerId { get; set; }

  [ForeignKey("CustomerId")]
  public Customer Customer { get; set; }

  [ForeignKey("OrderId")]
  public Order Order { get; set; }

}

Solution 2

For OData V4.0, there is an extension for data aggregation, in which the Cross-join concept is presented.