Need a LINQ code example to link two tables that have no foreign key

I need a code example please for the following scenario.

Suppose I have 2 tables, "Order" and "Supplier", and "Order" has a field called "SupplierId", and each Supplier has a "Town" field and you wanted to return all orders placed with suppliers who were located in "London", what would the LINQ look like. The tricky bit is that there is no foreign key, so no navigation properties.

So

Order
  SupplierId


Supplier
  Id
  City

Link is Supplier.Id == Order.SupplierId

Condition is: Where Supplier.Town = "London"

No foreign Key present between 2 tables.

So what would the LINQ look like please to achieve the above?

Many thanks.

Jon Skeet
people
quotationmark

You should be able to do the join explicitly:

from order in db.Orders
join supplier in db.Suppliers on order.SupplierId equals supplier.Id
where supplier.City == "London"
select order;

(You could filter on "only suppliers within London" earlier if you want, but I doubt that it'll make any significant difference to the query plan.)

EDIT: Avoiding query expressions - and moving the "where" clause for simplicity:

var londonSuppliers = db.Suppliers.Where(supplier => supplier.City == "London");
var query = db.Orders.Join(londonSuppliers,
                           order => order.SupplierId,
                           supplier => supplier.Id,
                           (order, supplier) => order);

(I'm assuming you only want the order.)

people

See more on this question at Stackoverflow