With the combined launch of Visual Studio 2008, SQL Server 2008, and Windows Server 2008, Microsoft is introducing five implementations of .NET Language Integrated Query (LINQ).
Of these five implementations, two specifically target access to relational databases: LINQ to SQL and LINQ to Entities.
Microsoft Language Integrated Query (LINQ) offers developers a new way to query data using strongly-typed queries and strongly-typed results, common across a number of disparate data types including relational databases, .NET objects, and XML. By using strongly-typed queries and results, LINQ improves developer productivity with the benefits of IntelliSense and compile-time error checking.
LINQ to SQL, released with the Visual Studio 2008 RTM, is designed to provide strongly-typed LINQ access for rapidly developed applications across the Microsoft SQL Server family of databases.
LINQ to Entities, released in an update to Visual Studio 2008 in the first half of 2008, is designed to provide strongly-typed LINQ access for enterprise-grade applications across Microsoft SQL Server and third-party databases.
What Is LINQ to SQL?
LINQ to SQL is an object-relational mapping (ORM) implementation that allows the direct 1-1 mapping of a Microsoft SQL Server database to .NET classes, and query of the resulting objects using LINQ. More specifically, LINQ to SQL has been developed to target the rapid development scenario against Microsoft SQL Server.
Figure 1 and Figure 2 combined with the code snippet below demonstrate a simple LINQ to SQL scenario. Figure 1 shows the LINQ to SQL mapping, and Figure 2 shows the associated database diagram, using the Northwind database. This code snippet shows a simple LINQ query against the Northwind database.
DataContext db = new DataContext();
var customers = from c in db.Customers
where c.City == "London"
select c;
With this knowledge, you can see that many aspects of LINQ to SQL have been architected with simplicity and developer productivity in mind. APIs have been designed to “just work” for common application scenarios. Examples of this design include the ability to replace unfriendly database naming conventions with friendly names, map SQL schema objects directly to classes in the application [a table or view maps to a single class; a column maps to a property on the associated class], implicitly load data that has been requested but has not previously been loaded into memory, and use common naming conventions and partial methods to provide custom business or update logic.
Many aspects of LINQ to SQL have been architected with simplicity and developer productivity in mind. APIs have been designed to “just work” for common application scenarios.
Partial methods, a new feature of C# and Visual Basic in Visual Studio 2008, allow one part of a partial class to define and call methods that are invoked, if implemented in another part of the class, otherwise the entire method call is optimized away during compilation. By using common naming conventions in conjunction with these new partial methods and partial classes, introduced in Visual Studio 2005, LINQ to SQL allows application developers to provide custom business logic when using generated code. Using partial classes allows developers the flexibility to add methods, non-persistent members, etc., to the generated LINQ to SQL object classes. These partial methods can add logic for insert, update, and delete by simply implementing the associated partial method. Similarly, developers can use the same concepts to implement partial methods that hook up eventing in the most common scenarios, for example OnValidate, OnStatusChanging or OnStatusChanged.
Microsoft developed LINQ to SQL with a minimally intrusive object model. Developers can choose not to make use of generated code and instead create their own classes, which do not need to be derived from any specific base class, meaning that you can create classes that inherit from your own base class.
Inheritance, an important feature of object-oriented programming, does not translate directly into the relational database. Given this, the ability to map in inheritance is very important. LINQ to SQL supports one of the most common database inheritance mappings, where multiple classes in a hierarchy are mapped to a single table, view, stored procedure, or table valued function using a discriminator column to determine the specific type of each row/instance.
As with any application framework, developers must also have the ability to optimize the solution to best fit their scenario. LINQ to SQL offers a number of opportunities to optimize, including using load options to control database trips and compiled queries to amortize the overhead inherent in SQL generation.
By default, LINQ to SQL enables deferred loading. This means that if, for example, I query for my Customer data using the Northwind model in Figure 2, I do not automatically pull the associated Order information into memory. However, if I try to access the associated Order information via a navigation property from a Customer instance, the associated Order information is automatically pulled into memory for me in a second database round trip. In the following code snippet the Order information is not loaded into memory until I access it from the second foreach statement.
var customers = from c in db.Customers
where c.Orders.Count > 5
select c;
foreach(var row in customers)
{
Console.WriteLine("Customer ID = " + row.CustomerID);
foreach(var order in row.Orders)
Console.WriteLine("Order ID = " + order.OrderID);
}
In the above example, a separate query is executed to retrieve the Orders for each Customer. If you know in advance that you need to retrieve the orders for all customers, you can use LoadOptions to request that the associated Orders be retrieved along with the Customers, in a single request.
Also by default, LINQ to SQL enables ObjectTracking, which controls the automatic change tracking and identity management of objects retrieved from the database. In some scenarios, specifically where you are accessing the data in a read-only manner, you may wish to disable ObjectTracking as a performance optimization.
Compiled queries offer another opportunity to further optimize query performance. In many applications you might have code that repeatedly executes the same query, possibly with different argument values. By default, LINQ to SQL parses the language expression each time to build the corresponding SQL statement, regardless of whether that expression has been seen previously. Compiled queries allow LINQ to SQL to avoid reparsing the expression and regenerating the SQL statement for each repeated query.
DataContext db = new DataContext();
var customers =
CompiledQuery.Compile(
(DataContext context, string filterCountry )
=>
from c in db.Customers
where c.Orders.Count > 5
select c;
foreach(var row in customers(db, "USA"))
{
Console.WriteLine(row);
}
foreach(var row in customers(db, "Spain"))
{
Console.WriteLine(row);
}
The above code snippet shows an example of a simple compiled query, executed twice with varying parameters.
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.
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.
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.
var customers = from c in db.Customers
where c.Orders.Count > 5
select c;
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.
LINQ to Entities and the Entity Framework allow you to map Table per Hierarchy [inheritance] as well as Table per Concrete Type… and Table per Subclass.
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.
When do I Use LINQ to Entities?
The primary scenario targeted by LINQ to Entities is a flexible and more complex mapping scenario, often seen in the enterprise, where the application is accessing data stored in Microsoft SQL Server or other-third party databases.
In other words, the database in these scenarios contains a physical data structure that could be significantly different from what you expect your object model to look like. Often in these scenarios, the database is not owned or controlled by the application developer(s), but rather owned by a DBA or other third party, possibly preventing application developers from making any changes to the database and requiring them to adapt quickly to database changes that they may not have been aware of.
As an example, consider a simple HR application that uses an existing company database. The database currently exists as our modified Northwind example in Figure 3. In looking at the database, you can see that the architecture does not lend itself directly to how you may think of the business objects; specifically you do not want to deal with two separate Employee classes where each class contains half of the information that the application will be accessing. You also do not want to think about an EmployeeTerritory object that does not add value beyond what is already provided in the Employee and Territory objects.
For this example I will need to create a conceptual model that exists with a single Employee object type, and a direct relationship between Employees and Territories to allow ease of navigation.
When I created the Entity Data Model using the new EDM Designer, I begin by walking through a simple wizard to create my base mapping. In this case I chose to automatically generate my initial mapping from an existing database, and then I edited the model using the drag and drop interface. I selected the subset of tables that best fit my application, renamed tables and properties to make them friendlier, and made a few other changes such as combining the two Employees tables into a single Employee Entity. Figure 4 shows a diagram of the Entity Data Model as previewed in the EDM Designer.
Looking closely at the EDM you can see that the relationship between Employees and Territories no longer uses an intermediary class to model the many-to-many relationship, but rather models it directly, shown by the *(* symbol connecting two entities. You can also see that I have modeled a single Employees entity including all of the properties contained in the Employees_Personal and Employees_AddressBook tables in the database.
In this example, you do not see a mapping for any type of inheritance. As mentioned previously, LINQ to Entities gives you the ability to map two types of inheritance, in addition to the Table per Hierarchy used in LINQ to SQL. Figure 5 and Figure 6 show an example of Table per Subclass inheritance and how it might be mapped to an Entity Data Model. This EDM shows the RunEvent class, SwimEvent class, BikeEvent class, and the TriathlonEvent class all inherit from the Event class.
