Kentico 10 ObjectQuery join multiple tables

Question

I am basically trying to run a query that gives me all the Users that have purchased a product with a particular SKU. Essentially this SQL here:

SELECT u.FirstName, u.LastName, u.Email
FROM COM_OrderItem oi INNER JOIN COM_Order o ON oi.OrderItemOrderID = o.OrderID
    INNER JOIN COM_Customer c ON o.OrderCustomerID = c.CustomerID
    INNER JOIN CMS_User u ON c.CustomerUserID = u.UserID
WHERE oi.OrderItemSKUID = 1013

I was trying to use the ObjectQuery API to try and achieve this but have no idea how to do this. The documentation here does not cover the specific type of scenario I am looking for. I came up with this just to try and see if it works but I don't get the three columns I am after in the result:

var test = OrderItemInfoProvider
                    .GetOrderItems()
                    .Source(orderItems => orderItems.Join<OrderInfo>("OrderItemOrderID", "OrderID"))
                    .Source(orders => orders.Join<CustomerInfo>("OrderCustomerID", "CustomerID"))
                    .Source(customers => customers.Join<UserInfo>("CustomerUserID", "UserID"))
                    .WhereEquals("OrderItemSKUID", 1013).Columns("FirstName", "LastName", "Email").Result;

I know this is definitely wrong and I would like to know the right way to achieve this. Perhaps using ObjectQuery is not the right approach here or maybe I can somehow just use raw SQL. I simply don't know enough about Kentico to understand the best approach here.

zolex zolex · Accepted Answer · 2019-03-26T06:58:10

Actually, the ObjectQuery you created is correct. I tested it and it is providing the correct results. Are you sure that there are indeed orders in the system, which contain a product with SKUID 1013 (you can check that in the COM_OrderItem database table)?

Also, how are you accessing the results? Iterating through the results should look like this:

    foreach (DataRow row in test.Tables[0].Rows)
    {
        string firstName = ValidationHelper.GetString(row["FirstName"], "");
        string lastName = ValidationHelper.GetString(row["LastName"], "");
        string email = ValidationHelper.GetString(row["Email"], "");
    }

Kentico 10 ObjectQuery join multiple tables

1 Answers