n Visual Studio 2008 running on the .NET framework 3.5, developers can not only create DataReaders and DataSets; Microsoft has also added LINQ to SQL, Entity Framework, and ADO.NET Data Services, which leverages the first two. These new options of course, mean that you have new syntaxes to learn. LINQ, which is built into Visual Basic and C#, has one implementation for LINQ to SQL and another for LINQ to Entities. In Entity Framework, you have the option to use LINQ to Entities or make queries in two other ways with Entity SQL (see Figure 1
|Figure 1. Data Access Options in .NET 3.5: Of the six data access options available, four are new.|
In all, developers have gone from one "out-of-the-box" data access option plus a native query language of choice (e.g. T-SQL) to six different data access options with new query syntaxes. That sounds pretty scary. On the other hand, having a range of choices offers developers the flexibility to choose the right tool for the job rather than have to constantly "make do" with the existing tools.
|Author's Note: ADO.NET Data Services (aka Astoria) is a great way to expose Entity Framework and LINQ to SQL models over the web. Because it provides a wrapper around these technologies, it doesn't fit into the particular goals of this article. One important point to be aware of, however, is that it solves a number of the problems that LINQ to SQL and Entity Framework developers are running into when using these models in Web and WCF services, most of which are around persisting change tracking across tiers. A great place to start with ADO.NET Data Services is the team's blog.
Because of the number of data access options available, this article will focus on those that are native to .NET and Visual Studio; however, you should know that there are many third-party tools such as object relational mappers (ORM) and frameworks that you can use as well.
This article is not aimed at teaching you how to use the various data access technologies. While it does discuss some specifics of the different APIs, you should be able to benefit from the examples without too much prior knowledge of or experience with LINQ to SQL or Entity Framework. The goal here is to focus on some of the most prominent differences between the options so you'll have enough information to know which option makes sense for your scenario. You can then drill into the appropriate technology to learn more.
The examples in this article are based on unmodified versions of the AdventureWorksLT
databases, because they're already familiar to many readers.
Not All Databases Are Created Equal
The availability of database providers for a given data access option may impact your choice. Myriad well-established data providers exist for ADO.NET and Microsoft, such as SqlClient, OracleClient, OleDBClient. Numerous third-party providers are also available if you're not using SQL Server or Oracle, but you'll need to check to make sure one exists for your particular database.
|In comparing LINQ to SQL to the other options, it is important to keep in mind that it works only with SQL Server databases.|
Entity Framework can work with any database for which a provider exists. Microsoft has written the provider for SQL Server and included it with Entity Framework. So far, LINQ to SQL works only with SQL Server databases (2000+, Express, Compact 3.5). Matt Warren, one of the originators of LINQ to SQL, has a blog series on building IQueryable providers
As of late spring 2008, vendors have released beta versions of a number of providers that work with Entity Framework Beta 3, including Oracle, MySQL, PostgreSQL and a few IBM databases. David Sceppa at Microsoft is working with the provider writers. His blog maintains a list of who is currently working on the providers
and when they should be available.
Entity Framework will not support OleDB providers. Entity Framework needs to transform query expressions into a form recognizable by a particular database. Because OleDB does not target a specific database, this isn't possible.
Data Access Code Interactions
ADO.NET, LINQ to SQL, and Entity Framework all have different relationships to the data store.
ADO.NET Works Against the Database
Using ADO.NET, you query the database directly, so you're required to have a very good understanding of the database schema. You must know the names of the stored procedures, views, and tables as well as the names and types of the parameters. Depending on how you handle the data upon return, you may also need to be well-versed in the schema of the return data. Strongly typed DataSets and TableAdapters make this significantly simpler because you can discover the schema through IntelliSense—and they'll take care of some of the connection functions for you.
LINQ to SQL Works Against a Model Database Representation
|Figure 2. Simple Model: Here's a simple LINQ to SQL data model built from three tables in the AdventureWorksLT database.|
You write LINQ to SQL queries against classes generated from the database. You can use a designer to do this work or code your classes manually. Figure 2
shows a simple model built from three tables in the database. The existing one-to-many relationships were automatically identified by the designer.
Behind the visual model lies a set of classes and properties with attributes that point to the database tables and columns represented in the model. For example, the following SalesOrderDetail class declaration shows that it is bound directly to the database table through an attribute:
Partial Public Class SalesOrderDetail
The properties are tightly bound as well.
DbType:="Int NOT NULL IDENTITY",_
Public Property SalesOrderDetailID() _
Writing LINQ to SQL queries is simpler not only because of the strongly typed classes, but because the relationships between the classes are strongly typed as well.
While you can customize the model somewhat, it's still a one-to-one representation of the database. Therefore, in effect, LINQ to SQL classes represent the database, so writing queries feels as though you are constructing strongly typed queries against the database itself. IntelliSense not only helps you with the operators (SELECT
, etc.) but with the tables and related data as well.
You'll see more detail about customization later.
Entity Framework Works Against a Conceptual Model that Maps Back to the Database
You write Entity Framework queries against a conceptual model called an "Entity Data Model" (EDM), as pictured in Figure 3
. The EDM is very different from the database model used by LINQ to SQL, because it is loosely
coupled to the database while the LINQ to SQL classes are tightly
bound to the tables and columns in the target database.
|Figure 3. Entity Data Model: The EDM consists of a Conceptual layer, a Storage layer, and a Mapping layer in between. This enables the conceptual schema which you program against to be loosely coupled to the database.||
|Figure 4. Model View: The figure shows an EDM in the Entity Framework Designer.||
shows the conceptual layer of an Entity Data Model in the Entity Framework designer that was created from the same three tables as the LINQ to SQL model.
While the LINQ to SQL model is based on a set of classes, the EDM is based on XML schema files used to automatically generate code classes.
|Need maximum performance? Hands down, ADO.NET data readers are faster than LINQ to SQL or Entity Framework queries|
The auto-generated classes are not bound to the database. Instead, they point back to the conceptual model and inherit from the EntityObject class. The code snippet below shows a representation of the SalesOrderDetail class. For clarity, I have removed some of the class attributes and Visual Basic's requisite line-wrapping underscores:
Partial Public Class SalesOrderDetail
The properties also have attributes, but there is no mention of the database columns that they map to:
Public Property SalesOrderDetailID() _
|Figure 5. Abstraction Layers: ADO.NET, LINQ to SQL, and the Entity Framework all have different layers of separation from the database.|
The Entity Data Model also contains an XML representation of the database and mappings that describe how entities and their properties map back to objects in the database. As with LINQ to SQL, lower level APIs transform Entity Framework queries into native queries in the database and then return DataReaders, which can be used directly or materialized into objects.
While it is possible to create an EDM that matches the database, EDM truly shines in scenarios where you can benefit by querying against a conceptual model that has been highly customized to match your business needs.
Additionally, because of the loose coupling, it is possible to use a single (well-defined) conceptual layer with different databases merely by creating a new store/mapping layer pair to act as a bridge. It's not a walk in the park to do this, but can definitely be worth the effort if it solves your business needs.
summarizes the layers of abstraction between the database and these three data access tools.