LINQ to Relational Data: Who’s Who? (Cont.)

When Do I Use LINQ to SQL?

The primary scenario for using LINQ to SQL is in applications with a rapid development cycle and a simple one-to-one object to relational mapping. In other words, you want the object model to be structured similarly to the existing structure of your database; you can use LINQ to SQL to map a subset of tables directly to classes, with the required columns from each table represented as properties on the corresponding class. Usually in these scenarios, the database has not been heavily normalized.

As an example, consider a simple retail application that uses the Northwind database. As you look at the Northwind database you can see a simple architecture that maps easily to a simple object model.

Using the LINQ to SQL Designer you can select the subset of tables that best fit your application, rename tables or properties to make them friendlier, and create an object relational mapping to develop against. Figure 1 & Figure 2 show the LINQ to SQL mapping and associated database diagram for a subset of tables from the Northwind database. If you look more closely at the object mapping you can see that foreign keys from the database are represented in the object model as relationships between classes and allow you to navigate from one object to another.

In looking at the many-to-many relationship between Employees and Territories in the diagram, and by digging further into the associated relationship properties, you can see that LINQ to SQL does not directly support many-to-many relationships. Rather, LINQ to SQL uses an intermediary class named EmployeeTerritory with a one-to-many relationship to Employees and to Territories.

The LINQ to SQL Designer also allows additional functionality for you to expose stored procedures and/or table valued functions as strongly typed methods on the generated DataContext, and map inserts, updates, and deletes to stored procedures if you choose not to use dynamic SQL.

The above example does not show a mapping for any type of inheritance, although using the Northwind database and LINQ to SQL you could have chosen to use inheritance to create a Products class and a DiscontinuedProducts class that inherits from Products. The DiscontinuedProducts class may include additional information, for example stating that the product has been discontinued, etc. LINQ to SQL supports Table per Hierarchy (TPH) inheritance and would therefore map this two-class hierarchy directly to the existing Northwind database as the single Products table within, using the discriminator column “Discontinued”.

What Is LINQ to Entities?

LINQ to Entities provides LINQ access to data exposed through the ADO.NET Entity Framework from Microsoft SQL Server or other third-party databases.

The ADO.NET Entity Framework is a platform, implementing the Entity Data Model (EDM), which provides a higher level of abstraction when developing against databases. For further discussion of the Entity Framework and EDM, please see the sidebar called Conceptual Data Programming with ADO.NET in this article, or An Entity Data Model for Relational Data by Michael Pizzo or Programming Against the ADO.NET Entity Framework by Shyam Pather, also found in this issue of CoDe Focus.

More than a simple ORM, the ADO.NET Entity Framework and LINQ to Entities allow developers to work against a conceptual or object model with a very flexible mapping and the ability to accommodate a high degree of divergence from the underlying store.

Figure 3 and Figure 4 below show a simple LINQ to Entities scenario, using a slightly more flexible mapping than seen in LINQ to SQL. Figure 3 shows the database diagram including the changes that you could make to the Northwind database, splitting Employee information between two tables, to demonstrate two common flexible mapping concepts, and Figure 4 shows the corresponding conceptual model. In these figures you can see that the object model is not mapped directly, one-to-one, to the database. The code snippet below shows a simple LINQ query against this database.

Microsoft designed the ADO.NET Entity Framework, and in turn LINQ to Entities, to enable flexible and more complex mappings, ideal in the enterprise type scenario, allowing the database and applications to evolve separately. When a change is made in the database schema, the application is insulated from the change by the Entity Framework, and you don’t have to rewrite portions of the application, but rather to simply update the mapping files to accommodate the database change.

Similar to LINQ to SQL, LINQ to Entities uses partial classes and partial methods to allow customer update and business logic to be easily added to generated code. LINQ to Entities also provides the ability to declaratively call stored procedures and use generated Update views when persisting objects.

Three common mapping scenarios differentiate LINQ to Entities from LINQ to SQL. LINQ to Entities provides the ability (through more flexible mappings) to map multiple tables or views to a single entity or class, to directly map many-to-many relationships, and to map additional types of inheritance.

Although you can map many-to-many relationships in both LINQ to SQL and LINQ to Entities, LINQ to Entities allows you to directly map many-to-many relationships with no intermediary class, while LINQ to SQL requires that you create an intermediary class that maps one-to-many to each of the classes that are party to the many-to-many relationship.

As discussed earlier in this article, LINQ to SQL lets you map one of the most common inheritance scenarios, Table per Hierarchy. LINQ to Entities and the ADO.NET Entity Framework allow you to map Table per Hierarchy, similarly to LINQ to SQL, as well as Table per Concrete Type, a separate table for each class or type in the hierarchy, or Table per Subclass, a hybrid approach using a shared table for information about the base type and separate tables for information about the derived types.

Two features of LINQ to Entities and the ADO.NET Entity Framework that set these technologies apart is the ability to create Entity SQL views and Defining Queries. Entity SQL views allow you to define the mapping between your entity model and the store schema in terms of arbitrary Entity SQL queries. The Defining Query feature allows you to expose a tabular view of any native store query as a table in your storage schema.

Due to the explicit nature of LINQ to Entities, developers also have the ability to optimize the solution to best fit their scenario. For a moment let me revisit my previous example of LINQ to SQL implicit loading. When I queried for Customer data, Order information was not automatically pulled into memory, but rather was only pulled into memory only when the Order information was accessed. In LINQ to Entities, you have full control over the number of database round trips by explicitly specifying when to load such information from the database. Navigating to associated information that has not yet been retrieved from the database will not cause an additional database trip.

You can further optimize LINQ to Entities by disabling change tracking when working in a read-only scenario.

Conceptual Data Programming with ADO.NET

Relational databases are often designed and normalized, not to make programming against them easier, but to ensure performance, data consistency, and concurrency. Seldom do developers work directly with data in the form that is returned from a database. With the upcoming introduction of the ADO.NET Entity Framework, Microsoft is beginning down a path that will abstract away the complications of dealing with data at the logical database layer.

The ADO.NET Entity Framework, brings to life the entity-relationship model that many have used for thirty years to capture and diagram conceptual models of their data, prior to mapping this information into a final relational model. The ADO.NET Entity Framework implements the entity-relationship model in the form of the Entity Data Model (EDM), maintaining the principle concepts of entities and relationships as first class data types, and allowing developers to program against the same conceptual model or business concepts that were used in design.