Using RelationshipsEntities are connected to each other through various relationships. An Order entity is associated with a Customer entity; an Employee entity is associated with other Employees through a manager-employee relationship. LINQ to SQL provides an easy way to represent such relationships and to effectively use them in queries and updates.The relationship between objects is usually a property that references a related object, such as E . In the case of a collection of related objects, the relationship is represented as a collection property containing references to related objects. For example, E usually is a collection of references to E objects.In relational databases, relationships usually are represented through keys. The relationships are often maintained using foreign key constraints. For example, in the Northwind database, the Orders table has a foreign key ? referencing the ? column in the Customers table. LINQ to SQL bridges the database and object concepts of relationships using the 6 " attribute. A property referencing a related entity or entities is attributed as follows:
[Table(Name="Customers")]
public class Customer
{
[Column(lsPrimaryKey=true)] public string CustomerlD; [Column]
public string Country;
... [Association(OtherKey="CustomerlD")] public List<Order> Orders;
}
[Table(Name=”Orders”)]
public class Order
[Column(lsPrimaryKey=true)]
public int OrderlD; [Column]
public string CustomerlD;
...
[Association(ThisKey="CustomerlD", lsForeignKey=true)]
public Customer Customer;
The 6 " attribute provides information about the members in the containing class and the related class that map to the key values defining the relationship in the database. In the previous example, ? names the class members mapped to the primary key of the Customers table and the foreign key in the Orders table. D refers to the key in the containing class, and E D refers to the key member in the other, related class. The : D attribute indicates that this relationship is enforced as a foreign key relationship in the database. This relationship mapping can be used for querying as follows, with a few sample results shown:
var OrdersQuery = from o in db.Orders
where o.Customer.Country == "Spain"
select new { o.OrderlD, o.Customer.CustomerlD };
OrderlD=1O326 CustomerlD=BOLlD OrderlD=1O8O1 CustomerlD=BOLlD
In this query, we can easily "dot through" the relationship in and " expressions. refers to a property of the related entity Customer that can be referenced through the range variable of type E . Likewise, ? accomplishes similar navigation.This is the real power of object relational mapping-an explicit join between the Customers and Orders tables is no longer needed. The association mapping combined with the power of LINQ makes it easy to write queries simply by using the "power of the dot" in the object model. Any joins that are required are handled by LINQ to SQL under the covers. The developer can just keep using dot notation to access the object and its properties. This is true whether they are mapped to a column in the same table, such as Order.OrderID, or whether they are mapped to a different table, such as Order.Customer.Country, which is mapped to the Customers table. Collection properties can be used similarly as follows, with the output shown at the end:
var CustomerQuery = from c in db.Customerswhere c.Country == "Spain" && c.Orders.Any()select c;
The only additional thing to keep in mind is that a collection valued property must be used with operators that work on collections. For example, the previous query uses the 6 operator to see if the collection has any orders. A collection property cannot be used like the property of an entity type. You cannot directly index an E instance from the collection and navigate to its property. There is a difference between the members of E versus E . Fortunately, IntelliSense is very helpful in this case as well. The completion list shows the available set of members, as shown in Figure 8.2.Figure 8.2 The completion list for a collection property.Collection-valued properties may be used in the result of a query as well. SQL is designed for the relational domain; hence, the results in SQL are rectangular. If you want Customers and Orders, the result is tabular, with the customer information repeated for each order belonging to the customer. LINQ is designed for the object domain. Therefore, hierarchies of objects are naturally available in the result displayed using the ObjectDumper:
var CustomerQuery = from c in db.Customers
where c.Country == "Spain" && c.Orders.Any()
select new { c.CustomerlD, c.Orders };
CustomerlD=BOLlD Orders=...
OrderlD=1O326 CustomerlD=BOLlD ... OrderlD=1O8O1 CustomerlD=BOLlD ... OrderlD=1O97O CustomerlD=BOLlD ...
CustomerlD=GALED Orders=...
OrderlD=1O366 CustomerlD=GALED ...
Joining TablesNavigation based on mapped relationships is intended to cover most of the common scenarios for queries that relate two tables. The LINQ P operator is available in addition to navigation. Consider querying for Customer and Suppliers in the same city in Northwind. Although there is no navigation property to go between Customers and Suppliers, LINQ to SQL allows the use of the P standard query operator as follows:
var CustSuppQuery = from s in db.Suppliers join c in db.Customers on s.City equals c.City select new
Supplier = s.CompanyName, Customer = c.CompanyName, City = c.City
};
This LINQ query is translated into SQL's inner join as follows:
SELECT [tO].[CompanyName] AS [Supplier], [t1].[CompanyName] AS [Customer], [t1].[City]
FROM [dbo].[Suppliers] AS [tO]
lNNER JOlN [dbo].[Customers] AS [t1] ON [tO].[City] = [t1].[City]
This query eliminates suppliers that are not in the same city as some customers. But sometimes you don't want to eliminate one of the entities in an ad hoc relationship. The following query lists all suppliers, with groups of customers for each supplier. If a particular supplier does not have a customer in the same city, the result is an empty collection of customers corresponding to that supplier. Note that the results are not flat-each supplier has an associated collection. Effectively, this provides a group join. It joins two sequences and groups elements of the second sequence by the elements of the first sequence. Let's use an overload of the E > " ? % 5 call to drill down into the supplier and the corresponding group ofcustomers:
var CustSuppQuery = from s in db.Suppliers join c in db.Customers
on s.City equals c.City into scusts select new { s, scusts };ObjectDumper.Write(CustSuppQuery, 1);
Such a group join is translated into SQL's left outer join as shown in the following code. The long projection lists for each table are truncated to make the generated query easier to read. The SQL aggregate count helps LINQ to SQL build the collections of s for the join predicate-inthis case, .
SELECT [tO].[SupplierlD], [tO].[CompanyName], ... , [t1].[ContactName] AS [ContactName2], ... ,
(
SELECT COUNT(*)
FROM [dbo].[Customers] AS [t2] WHERE [tO].[City] = [t2].[City]
) AS [value]
LEFT OUTER JOlN [dbo].[Customers] AS [t1] ON [tO].[City] = [t1].[City] ORDER BY [tO].[SupplierlD], [t1].[CustomerlD]
Group joins can be extended to multiple collections as well. The following query extends the preceding query by listing employees who are in the same city as the supplier. Here, the result shows a supplier with (possibly empty) collections of customers and employees:
var EmpCustSuppQuery = from s in db.Suppliers join c in db.Customers
on s.City equals c.City into scusts join e in db.Employees
on s.City equals e.City into semps select new { s, scusts, semps };
The results of a group join can also be flattened. The results of flattening the group join between suppliers and customers are multiple entries for suppliers with multiple customers in their city-one per customer. Empty collections are replaced with nulls. This is equivalent to a left outer equijoin in relational databases.
on s.City equals c.City into sc from x in sc.DefaultlfEmpty() select new
Supplier = s.CompanyName, Customer = x.CompanyName, City = x.City
The generated SQL query contains a simple left outer join, as expected.
SELECT [tO].[CompanyName] AS [Supplier], [t1].[CompanyName] AS [Customer], [t1].[City] AS [City]
LEFT OUTER JOlN [dbo].[Customers] AS [t1] ON [tO].[City] = [t1].[City]
Thus, > is an additional tool for more complex relationships that are not mapped to navigational properties. It complements the more commonplace use of much simpler navigational properties. It can produce hierarchical or flattened results.