Associations To Joins in LINQ TO SQL

When your SQL server tables has foreign and primary keys constraint defined, dragging those tables on the linq to SQL designer creates association relationship that can help traverse other related objects. For instance, if you have an order table than you may want to traverse the customer for that order and as well as find out the employee who submitted the order. When you are using the dot operator to do the traversal, linq to SQL uses different join based on the schema defined for the relationship. Let's look at a couple of different traversal to understand what kind of query gets sent to SQL server.

Above is the linq to SQL database diagram in which order is related to customer and employee. Order has many to one relationship with customer based on CustomerID column. Order has many to one relationship with Employee based on EmployeeID column. Let's build a query that uses those associations to get us orderid from order table, company name from customer table,first name from employee table.

In the query above, I am using associations created by linq to SQL to traverse customer and employee object on the order object. The SQL that gets generated by the query is as follows.

In the SQL that gets generated you would notice that although both order to customer and order to employee is defined as many to one relationship. However for order to customer linq to SQL generates left outer join where as order to employee linq to SQL generates inner join. The reason is, CustomerID in orders table is defined as allow null and EmployeeID in orders table is not marked as allow null meaning for every order there is an employee associated with it. Theoretically an order should always have a customer, but to prove the point I modified the constraint to demonstrate the SQL that gets generated.