Join two tables based on value only present in one

I needed to join two tables where one is a list of orders, and the other is a list of items in the orders.
These items can only be for orders created by a specific customer.

The orders table has a foreign key via the customer ID, but the items do not.

You could get all the items, and then load it into memory and filter based on the order’s customer ID value, but doing this uses up a lot of memory and is very slow. I needed to do this as lazy as possible.

Fortunately in LINQ, you can join based on anonymous objects that you can create using the table values and other values not in the table. And as it’s all done via IQueryables, so the execution is deferred as late as possible.
int customerID = 12345;

return from item in context.items
join order in context.Orders
on new { orderID = item.OrderID, customerID = customerID } equals new { orderID = order.ID, customerID = order.CustomerID }
select item;

So even though there is no customer ID column in the items table, the anonymous object I created has one that is set to the same value as the customerID I’m looking for. So as long as the customer ID value in an order row matches that, I will get the items I want.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s