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.

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 and Northwind 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.

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.
    _      Public Property SalesOrderDetailID() _      As Integer
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, WHERE, FROM, 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.
Figure 4 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:
   
The properties also have attributes, but there is no mention of the database columns that they map to:
   
?
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.
Figure 5 summarizes the layers of abstraction between the database and these three data access tools.
Differences in Creating the Models
Similar to the DataSet designer, LINQ to SQL's design surface lets you drag tables, views, and stored procedures directly from a DataConnection represented in Visual Studio's Server Explorer. It identifies existing primary keys, primary key/foreign key relationships, and other constraints, and builds them into the model.
You can easily add and remove objects from the model after the fact. However, the model cannot automatically update itself if the database changes. To update the model, you will have to manually remove and re-add items to the model.
One nice LINQ to SQL designer feature is that it recognizes that a class in the model is a single entity. Therefore, by default it automatically attempts to create singular names for tables that have plural names. For example, Contacts becomes Contact (you can turn off this default setting).
Entity Framework's designer uses a wizard to build the model rather than the drag-and-drop scenario. The designer is also able to update the model from the database?selecting objects that you may not have selected the first time around, or updating the schema when the data store changes. During this update, new fields in tables that already exist in the model are recognized and added as properties to the relevant entity. Most customizations to the CSDL remain intact during an update; however, it overwrites customizations that you may have made to the store schema (SSDL). I have been taking advantage of SSDL model customizations, and this overwrite behavior is definitely frustrating.
Author's Note: Unlike the LINQ to SQL designer, the EDM Wizard does not automatically fix plural entity names as it creates them, so that's one of the first things I do to a newly created model.
Customizing DataSets, LINQ to SQL, and Entity Data Models
You can, of course, make modifications to the structures of a strongly typed DataSet, the LINQ to SQL model, and the Entity Data Model. However, the degree of customization differs vastly between the three; it is here that you can see the true power of the Entity Data Model.
Cosmetic Changes to Classes
There are some basic features allowed by both the models and the strongly typed DataSet. For example, you can rename any of the Entities/DataTables or Properties/DataColumns so the names make more sense to you. This is a great convenience, because it lets you change names without having to pester the DBA or affecting other code. You can also remove properties/columns and create keys and relationships in the DataSet designer, LINQ to SQL or EDM.
Inheritance
LINQ to SQL and the EDM both let you do something that is not possible in a database?build inheritance directly into the models. LINQ to SQL supports Table per Hierarchy (TPH) inheritance, while Entity Framework supports TPH as well as Table per Type (TPT) and Table per Concrete Type (TPC) inheritance. Some recent blog posts and a sample on the ADO.NET Team's code gallery page demonstrate how to pull off TPT inheritance in LINQ to SQL as well.
Table per Hierarchy (TPH)
?
Figure 6. Table per Hierarchy Inheritance: In LINQ to SQL, you can define Table per Hierarchy inheritance using one of the table properties as a discriminator. Here, WebOrder inherits from SalesOrderHeader where OnLineOrderFlag = True.
TPH inheritance lets you create a class hierarchy from a single table in the database. As an example, the AdventureWorks' SalesOrderHeader table has a Boolean field named OnlineOrderFlag. In both LINQ to SQL and the EDM, you can treat the online orders as a separate entity that inherits from SalesOrderHeader. The method for doing this is a bit different in the two models, but the effect is the same.
Figure 6 shows the WebOrder class, which inherits from SalesOrderHeaders in LINQ to SQL. The Inheritance object's property window is also shown. The OnlineOrderFlag property discriminates between regular SalesOrderHeader types and online orders. The base type, SalesOrderHeader, will be populated by orders using OnlineOrderFlag = False, while the derived type, WebOrder, is defined by OnlineOrderFlag = True. Unfortunately, you can't use Null as a discriminator value because the discriminator doesn't have a way to represent "not-null".
You can use both base and inherited entities in LINQ to SQL queries. For example, this Visual Basic query returns only WebOrders:
   From weborder    In context.SalesOrderHeaders.OfType      (Of WebOrder)()    Select weborder
When you create new WebOrder objects, the discriminator value gets filled automatically; when you insert the object, the OnlineOrderFlag will already be set to True.
With the Entity Data Model, the inheritance is built in nearly the same way, although EDM uses conditional mapping to differentiate between the base class and the derived class.
EDM's Other Types of Inheritance
Table per Type (TPT) inheritance describes a scenario where there are multiple tables in the database that can be represented as base and inherited types in the Entity Data Model. A simple example of this would be a database with a Person table and an Employees table that extends the data available in the Employees table, perhaps with fields such as Hire Data. In the Entity Data Model it is possible to create an inheritance relationship between the two.
Table per Concrete Type (TPC)  is a less common scenario supported by the Entity Data Model. You'll find this useful when a table with duplicate schema exists that contains a subset of data. For example, you may have a Product table accumulating so many discontinued products that the DBA split the discontinued products off into their own table. The mapping is still able to represent the DiscontinuedProduct entity type as inheriting from Product.
Additional EDM Mapping Capabilities
?
Figure 7. Common Properties: The circled areas show Customer and Employee entities before centralizing the Address info into a complex type.
The Entity Data Model supports two other types of complex mapping as well.
Entity Splitting: It is possible to create an entity whose properties come from multiple tables that have one-to-one relationships; this is referred to as "splitting an entity".
Complex Types: You use a ComplexType to define common properties among various entities. For example, if you have a number of entities that each have address information, such as in Figure 7, you can create a complex type that encapsulates these properties:
         
You can then use that type as a property for the entities in place of the scalar properties that it encapsulates.
   
While the column names of the tables that these entities map to are different, the mappings resolve the differences. The documentation contains more information about constructing and mapping complex types.
Microsoft offers quite a number of other ways to customize an Entity Data Model. You make some of these customizations in the mapping layer and others in the layer that represents the data store. The latter lets you add "virtual" tables, views, and queries to the model that don't truly exist in the data.
Unfortunately, some of the model customization possibilities (such as complex types) did not make it into the current version of the designer. You can still add these customizations manually, but after they exist in the model, you won't be able to open the model in the designer. Some customizations don't prevent you from opening the model in the designer, yet they don't show up in design view. For the most part, the lack of designer support for some modeling features isn't critical, because the unsupported features aren't commonly used.
Querying
Querying against the target database or model is very different in ADO.NET, LINQ to SQL, and the Entity Framework.
LINQ
LINQ to SQL creates a set of classes that represent the database; you write queries with the benefit of IntelliSense and strong typing against the database. And it's extremely easy to do.
LINQ wins hands down when it comes to writing queries, whether you are writing LINQ to SQL, LINQ to Entities or some other type of LINQ. LINQ's strongly typed operators and functions and a slew of useful extension methods make it easy to create queries against the strongly typed objects produced by LINQ to SQL and Entity Data Model.
LINQ to SQL Queries
With LINQ to SQL you must first instantiate a class that represents the model. This class is automatically created as part of the model and inherits from LINQ to SQL's DataContext class. Through the DataContext, you can access the other classes. You can write LINQ queries against the model using LINQ to SQL syntax. LINQ to SQL transforms your query into a T-SQL query, orchestrates the query execution, and plugs the resulting data into the model objects. The query isn't executed until your application executes code that enumerates the query results.
The following example shows a query that returns OrderDetail objects for large orders where the customer received a discount. Note that because LINQ to SQL is aware of the relationships between the tables, you don't need a JOIN; you can simply traverse into the related data. The strongly typed classes combine with IntelliSense to make writing queries very easy:
   Dim context = New AWModelDataContext   Dim query = From detail _      In context.OrderDetails _      Where detail.Order.TotalDue > 1000 _      And detail.UnitPriceDiscount > 0 _      Select detail   Dim detailList=query.ToList()
The preceding query returns a List of OrderDetail types?a class that exists in the model. It is also possible to project specific columns or functions in a query. As an example, you could replace the above Select clause with the following clause.
   Select New With {.ID = _      detail.Order.SalesOrderID, _      detail.Order.AccountNumber, _      detail. Order.TotalDue, _      detail.UnitPrice, _      detail.UnitPriceDiscount, _      detail.LineTotal}
The query syntaxes in VB and C# are slightly different. For example, the above query with the projection would look like this in C#:
   from detail in context.OrderDetails    where detail.Order.TotalDue > 1000      && detail.UnitPriceDiscount > 0   select new {ID =       detail.Order.SalesOrderID,      detail.Order.AccountNumber,      detail.Order.TotalDue,      detail.UnitPrice,      detail.UnitPriceDiscount,      detail.LineTotal};
Because the new type created by this projection does not exist in the model, LINQ returns an object that is an anonymous type. Anonymous types are another new language construct in VB and C# that let you create short-lived, strongly typed objects on the fly, without having to define classes in advance.
?
Figure 8. Many-to-Many Relationships: Many-to-many relationships can hide the link table in the model if the link table contains only the key fields for the join.
LINQ to Entities Queries
The same simplistic query expressed against an EDM using LINQ to Entities looks identical, though this won't always be the case. Instead of a DataContext, Entity Framework has a similar wrapper class called an ObjectContext. This wrapper is defined in the model and generated as a class. In the code below, the ObjectContext is named AWEntities:
   Dim context = New AWEntities   Dim query = From detail _      In context.OrderDetail _      Where detail.Order.TotalDue > 1000 _      And detail.UnitPriceDiscount > 0 _      Select detail   Dim detailList=query.ToList()
LINQ to Entities queries that do projection will also return an anonymous type.
Object Services
Underneath the covers, LINQ to Entities leverages one of Entity Framework's critical APIs, Object Services. It is Object Services that has the ability to send the query down the stack for execution, and then turn the results back into objects (aka "object materialization").
You can write queries using Object Services directly with the ObjectQuery class. The ObjectQuery class does not use the LINQ to SQL syntax. Instead, it takes query expressions in the form of Entity SQL, a T-SQL like syntax designed specifically for querying against the Entity Data Model.
Here is the same query again using an ObjectQuery. Because of the projection, again, an unknown entity is returned. Object Services cannot return anonymous types, so the objects returned are System.Common.dbDataRecords, which you can think of as being similar to a row in a DataReader. One major exception with this comparison, however, is that Entity Framework's results can contain shaped data. While returned entities are a breeze to work with, dbDataRecords require extracting the record items one by one:
   Using context = New AWEntities      Dim eSQL = _         "SELECT VALUE detail " & _         "FROM AWEntities.OrderDetail " & _         "AS detail " & _         "WHERE detail.Order.TotalDue " & _         ">1000 " & _         "AND detail.UnitPriceDiscount > 0"      Dim query = context.CreateQuery( _         Of OrderDetail)(eSQL)      Dim qlist = query.ToList   End Using
The VALUE keyword indicates the query returns a single value or object. Alternatively, the projection used in the LINQ queries above would look like this in the Entity SQL:
   Dim eSQL = "SELECT " & _      "detail.Order.SalesOrderID, " & _      "detail.Order.AccountNumber," & _      "detail.Order.TotalDue, " & _      "detail.UnitPrice," & _      "detail.UnitPriceDiscount, " & _      "detail.LineTotal " & _      "FROM AWEntities.Detail as detail "& _      "WHERE detail.Order.TotalDue > 1000 " & _      "AND detail.UnitPriceDiscount > 0 "
When using projections, rather than using CreateQuery of a specific type, the call would be:
   Dim query = context.CreateQuery _      (Of dbDataRecord)(eSQL)
Entity SQL
Entity SQL has its roots in T-SQL, but has modifications made to enable working against an Entity Data Model. At first glance, compared to LINQ, Entity SQL seems as if it goes back to cave-dweller times; you're back to writing strings and hoping that they don't blow up at run time. But it can be useful when LINQ's strong typing might lock you into a corner. LINQ depends on the CLR for its functionality; if you want to express a query that LINQ's functions and providers won't allow you can get stuck. With LINQ to SQL, you can get around this by writing pass-through queries with ExecuteCommand. But in Entity Framework, you can step down to a lower level and use Entity SQL with Object Services. Because Entity SQL is string-based, you can build query strings dynamically in code, and you can use provider-specific query operators and functions in the string.
Here's an example of an Entity SQL query that picks which properties to use in the projection. Given an array of desired properties, you can build the query string dynamically, for example:
   Dim arrayOfFields = New String() _      {"FirstName", "LastName"}   Dim strFlds As New Text.StringBuilder   For Each fld In arrayoffields      strFlds.Append("c." & fld & ",")   Next   'trim off last comma   strFlds.Remove(strFlds.Length - 1, 1)   Dim queryString = _      "SELECT " & strFlds.ToString & _      " FROM AWEntities.Customers AS c" & _      " WHERE LEFT(c.lastname,1)='S'"
You can't do this in either LINQ to Entities or LINQ to SQL. I have heard that some people have been toying with functional programming to pull off something like this in LINQ, but it's certainly not "out of the box easy."
You can find more on these differences here and in this post that discusses how to parse an EntityDataReader.
The EntityClient API
The last of the APIs for Entity Framework, EntityClient, allows you to stream data back to the client without materializing objects. Instead, EntityClient returns results in a DbDataReader. While this may be the least common scenario for many developers using the Entity Framework, it is a very important one for developers who want to leverage the modeling capabilities of the EDM but don't need to use the features of Object Services. EntityClient uses Entity SQL as its query syntax.
Using the same Entity SQL projection query created above, this EntityClient query looks very similar to other ADO.NET provider queries:
   Using. eConn As _      New EntityClient.EntityConnection( _      "Name=AWEntities")      Dim eCmd As _         New EntityCommand(eSQL, eConn)      eConn.Open()      Dim dr = eCmd.ExecuteReader _         (CommandBehavior.SequentialAccess)      For Each dbrec As DbDataRecord In dr         Console.WriteLine _            ("Acct #: {0}, " & _            "Unit Price Discount: {1}", _            dbrec.Item("AccountNumber"), _            dbrec.Item(4))      Next   End Using
In the preceding code, note that you can use either the index or the returned field name when iterating through the fields.
Both EntityClient and ObjectQuery accept query parameters, just as you use SqlParameter and other dbParameters when building "classic" ADO.NET queries.
A Shaped DataReader
This is not your father's DbDataReader, however. ADO.NET DataReaders contain scalar data. EntityClient can return nested DataReaders, which means that your queries can easily result in nicely shaped data. There's a great example in the Entity Framework docs that iterates through an Entity DataReader from a query that returns shaped data. The payload consists of scalar values, dbDataRecords, and nested dbDataReaders.
ADO.NET
Unless you are completely new to .NET, you're probably already familiar with constructing queries in ADO.NET. Suffice it to say that you construct those queries using a string that is either the name of a stored procedure in the database or a dynamic query. You can create connections and commands and execute the queries manually or leverage some of the higher-level tools such as TableAdapters, and various DataSource objects in .NET to automate some of this work.
Change Tracking, Updates, and Concurrency
Because all these data access options work with disconnected data, they support optimistic concurrency, which means that database records do not get locked when they are accessed.
Who Owns the Change Tracking and State Info?
With respect to change tracking, there is a big difference between DataSets and their counterpart LINQ to SQL and Entity Framework objects. While all three provide change tracking, the DataSet is the only one that maintains its own state. Datasets include that state data even when persisted, serialized, or transferred across process boundaries.
LINQ to SQL and Entity objects, however, do not contain their state information. The context that manages the objects maintains the original values and state. This is a challenge for developers when serializing and/or moving objects across tiers. If you want to leverage the simple mechanisms for saving changes (SubmitChanges in LINQ to SQL and SaveChanges in Entity Framework), you need the state of the objects and properties (modified? deleted? added?) to persist the changes to the database. If your updates need to check for concurrent changes to the database (LINQ to SQL and Entity Framework both provide for this capability), you also need access to the original values.
There has been a lot of discussion and debate on the difficulty of handling this and the need for architectural guidance from Microsoft. Version 1 of LINQ to SQL and Entity Framework do not have any easy answers, though there are a number of people (including me) trying to help come up with patterns to use. Still others have just thrown up their hands and are waiting for the next version. Third-party tools such as Ideablade's DevForce for Entity Framework (in beta as of this writing) offer alternatives.
Rick Strahl's blog has some information on tracking changes across tiers in LINQ to SQL, and Beth Massey's blog discusses change tracking across tiers in Entity Framework.
EntityClient Is Read Only
EntityClient streams down read-only data. EntityClient is not able to perform updates.
Updates with Projections
In ADO.NET, if you are using the DataAdapter for updates, you can associate your own SQL command text to the UpdateCommand. That way, even if your strongly typed DataSets do not mirror tables in the database, you can handle updates fairly simply.
In LINQ to SQL and Entity Framework Object Services, updates using SubmitChanges and SaveChanges work only with complete entities. You would have to leverage stored procedures to update differently-shaped data. With Entity Framework, there are a few ways to tackle this including creating entities that specifically match the projection.
Bulk Updates
Whether you are performing saves manually or with the provided tools (ADO.NET's DataAdapter, LINQ to SQL SubmitChanges, and Entity Framework SaveChanges), each row's insert/update/delete command gets sent to the database individually.
ADO.NET 2.0 introduced two features to help with this. The dbDataAdapter.UpdateBatchSize property controls the number of commands to transmit to the database in batches, where they are then executed individually. The SqlBulkCopy class scan stream a SqlDataReader to the server. While this class is designed for inserts, you can get it to stream inserts, updates, and deletes to the database with a few tricks. In ADO.NET 1.0 and 1.1, it is possible to execute SQL Server BULK INSERT statements with the Execute command.
LINQ to SQL and Entity Framework do not support bulk inserts; however, LINQ to SQL's ExecuteCommand does provide for pass-through queries, so while you can't do a bulk insert, you can do other types of bulk operations such as this canonical example:
   context.ExecuteCommand _       ("UPDATE Products SET UnitPrice=" & _       " UnitPrice + 1.00")
These are important considerations when you need to upload lots of data at one time or make sweeping modifications to the database. It also introduces a scenario where you may want to mix these tools in a single application.
Alex James on the Entity Framework team has tried to attack these problems using Entity Framework in his blog.
Views and Stored Procedures
LINQ to SQL, Entity Data Model and ADO.NET all support views and stored procedures, but with varying levels of support. In LINQ to SQL and the Entity Data Model, database views are simply surfaced as read-only entities. ADO.NET's dbCommands make it easy to use either dynamic SQL or stored procedures with or without parameters.
By default, both LINQ to SQL and Entity Framework providers write their own dynamic queries for reading and writing data. For reads, you can use your own stored procedures (with caveats about simple updates if your stored procedures return anonymous types) and you can override the insert, update, and delete operations with pointers to your own stored procedures. This is easy when the stored procedures parameters and returned structures map exactly to their associated entities, but after you step off that narrow line, things definitely get limited and tricky.
READ Stored Procedures
Both LINQ to SQL and EF easily support stored procedures that perform READS when the return value matches up with an entity in the model. If the stored procedure returns something that is not an entity, the LINQ to SQL designer automatically creates a new class to match the schema of the results. This happens at design time. I would love to see the code that makes this happen! I'll have to continue looking for the right DLL so I can poke around in it with Reflector.
The EDM doesn't support this scenario as easily, because it requires creating an entity that matches the schema of the result set as well as a virtual table representation in the Store layer, and then mapping the entity to the table. Unfortunately, the designer doesn't do this automatically; it requires modifying the XML of the model manually. That's a bit of a pain, so if you have a lot of READ stored procedures to implement in your model, you may be unhappy. But perhaps the next version of the EDM designer will work that magic.
Insert, Update, and Delete Stored Procedures
Both LINQ to SQL and EF support DML stored procedures for inserts, updates, and deletes. If these procedures exist in the database, they can be built into the model.  You can use insert, update, and delete procedures whose parameters match exactly to an existing entity to override LINQ to SQL and Entity Framework's default functions.
LINQ to SQL has a little more flexibility because you can map a procedure and then customize it. That way if the procedure's parameters don't match exactly, you do have the ability to modify it. In Entity Framework, you need to use EntityClient for DML stored procedures. Here's an example:
   Using eConn As New EntityConnection _      ("Name=NWEntities")      Dim eCmd As New EntityCommand _         ("NWEntities.UpdCompName", eConn)      eCmd.CommandType = _         CommandType.StoredProcedure      eCmd.Parameters.AddWithValue _         ("CustomerID", cust.CustomerID)      eCmd.Parameters.AddWithValue _         ("CompanyName", cust.CompanyName)      eConn.Open()      eCmd.ExecuteNonQuery()   End Using
Looks pretty familiar, right? This is one of the nice things about working with EntityClient?it uses the same classes as the other client providers.
On-the-fly Stored Procedures
As you saw above, LINQ to SQL's DataContext.ExecuteCommand makes pass-through queries simple to implement. With an Entity Data Model, it is possible to build T-SQL for read-only queries directly into the model in cases where even the most intelligent and creative mapping cannot help you implement the query that you desire. You do this using an SSDL element called DefiningQuery. The result is similar to a read-only view.
Relationships
Relationships between CLR objects are as critical to application design as relationships are in the database. While ADO.NET has had its mechanism for relating DataTables since version 1, LINQ to SQL and Entity Framework add a brand new way of viewing and interacting with related data.
ADO.NET
With ADO.NET, you can query the database in any way you choose to pull down related data, then use a DataRelationship object to define parent-child relationships between in-memory DataTables. Strongly typed datasets make this a little less painful, but it is definitely cumbersome.
LINQ to SQL and Entity Framework
LINQ to SQL and the Entity Data Model define relationships between entities in the model. The relationships can be traversed when building queries either through projection or in filtering or other clauses.
LINQ to SQL handles related data simply as properties of one another. Entity Framework's relationships are first class citizens in the model, called associations. The biggest challenges for related data in both LINQ to SQL and Entity Framework comes when you need to work across tiers where in many cases you need to have a good understanding of how to connect and disconnect classes from each other and from their context.
Saving Related Data
Doing inserts on PK/FK data in ADO.NET is a pain, because you need to insert the parent, return the new key, then use that to insert the children. LINQ to SQL and Entity Framework make this a no-brainer task. If you create an order and some line items, they will all get inserted in the correct order. Both APIs will first insert the parent, grab the auto-generated Primary Key, and then use that key to perform the child inserts. All of this happens underneath the covers.
In this LINQ to SQL example, a SalesOrderHeader is created, then two Sales Order Details. Note that each detail gets its related SalesOrderHeader property set to the order that was already created:
   Dim order = New SalesOrderHeader     [code to fill order properties]   Dim detail = New SalesOrderDetail   With detail     .OrderQty = 2     [other properties]     .SalesOrderHeader = order   End With   Dim detail2 = New SalesOrderDetail   With detail2     .OrderQty = 5     [other properties]     .SalesOrderHeader = order   End With
Then create a context and insert only the order object before calling SubmitChanges:
   Using context = New AWModelDataContext      context.SalesOrderHeaders. _         InsertOnSubmit(order)      context.SubmitChanges()   End Using
Using the SQL Profiler, you can see that three commands were passed in and executed. The first inserts the order and returns the store generated values, including the new Primary Key. Then there is one insert command for each SalesOrderDetail, which includes the new primary key in the SalesOrderID field.
While it is coded a bit differently, Entity Framework does the same work on your behalf when saving related data.
Create Your Own Relationships?
By default, the model represents primary/foreign key relationships and constraints in the database. You can create additional relationships between entities in the database model?even if the relationship has not been defined in the database?as long as the fields exist to support the relationship.
Entity SQL's Navigation
Entity SQL provides a NAVIGATE operator that lets you navigate from one entity to another even when no relationship is defined. This is not a feature that will get commonly used, but when you need it, it's great to have.
Many-to-Many Relationships
Many-to-many relationships in LINQ to SQL rely on a joining table. Therefore, queries built across many-to-many relationships need to use joins.
In EDM, many-to-many relationships whose join table contains only the keys that define the relationships can eliminate the join table in the model. EDM handles the join table and the necessary joins behind the scenes with the mappings. This makes it very easy to navigate the relationship and build queries. You can even add data to the tables and Entity Framework will negotiate the necessary entries to the join table. If the join table has any additional columns in it, then you will need to have an entity in the model to represent that table, and queries will be more complex, just as in LINQ to SQL.
Performance
Whenever you introduce layers of abstraction, you're also trading ease of coding for performance.
Hands down, ADO.NET data readers are faster than LINQ to SQL or Entity Framework queries because ADO.NET connects directly to the database.
A few processes impact performance with LINQ to SQL and Entity Framework. The first is query compilation?the entire process of converting the LINQ or Entity SQL query into a native query that will be used in the data store. While this takes a lot of time, it is actually possible to pre-compile queries in both platforms and remove this performance hit completely for frequently used queries, even when are parameters involved. See this blog entry for more information.
Another hit is taken on the way back from the database when objects are materialized. You can see this in the Entity Framework by comparing EntityClient queries to Object Services or LINQ to Entities queries. Microsoft made some huge gains to the performance of materializing objects in Entity Framework throughout the betas and it is now nominal.
For updates, believe it or not, Entity Framework's SaveChanges performs updates faster than DataAdapter.Update and much faster than LINQ to SQL's SubmitChanges. While the results of my tests in this area have been confirmed by members of the Data Programmability team at Microsoft, I don't know the details of why this is the case. I'll include the results of some tests where I updated 450 records and inserted 10 new records with each of the above methods. The times are the averages of 100 runs of each test.
Table 1 comes from a recent blog post I wrote on this.
Table 1. Data Access Method Performance: The table shows the results of performance tests of several different data access methods.
Method
Average of 100 Tests
DataAdapter with UpdateBatch=1
 475ms
DataAdapter with UpdateBatch=100
 354ms
Entity Framework Object Services
 227ms
LINQ to SQL
2349ms
Performance comparisons are a big topic and a bit of a tightrope. I haven't yet come across performance comparisons between LINQ to SQL and Entity Framework queries. Some experiments (hardly lab-quality benchmark tests) show that, as you might expect, compiled LINQ to Entities queries run at about the same speed as compiled LINQ to SQL queries. In both cases, the compiled queries have already created the T-SQL. You can see some of the results of my tests along with comparisons of update performance in my blog. You can find more details about performance, including some recent benchmark tests by Brian Dawson on the Entity Framework team here.
Other Frequently Highlighted Concerns
While these are not unimportant topics, there is just not room to cover everything, so I want to at least address these.
Security
The dynamic SQL used in LINQ to SQL and Entity Framework has raised questions about SQL injection attacks. Both LINQ to SQL and LINQ to Entities generate parameterized queries so this should not be a concern. Entity SQL queries do not get parameterized, so if you don't use ObjectParameters or EntityParameters when building these queries, an injection attack is possible if you are constructing dynamic queries with user input. While it is actually difficult to write a SQL injection that will also be valid Entity SQL, it is possible. So, all the usual caveats that apply to ADO.NET also apply to Entity SQL-when dealing with user input: validate user input, use query parameters when you can, etc.
Generated SQL Quality
The quality of the SQL generated dynamically by Entity Framework and LINQ to SQL is another frequently-raised issue. You always have the option to use your own stored procedures, but for dynamic SQL, you'll need to trust the pros who are writing the APIs. For the SQL Server API, members of the SQL Server team and the folks who work specifically with query optimization have been very involved in working with the Data Programmability team on query generation.
This doesn't mean perfection, of course. Because of the nature of these tools, the query generation must be generic enough to handle a wide variety of scenarios. The resulting query often looks scary, but in most cases, that doesn't equate to denigrated performance. You can find interesting discussions in Bob Beauchemin's blog (find the start of his six-part series "MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate" in his February 2008 archives), and in SQLDownUnder's show #30.
Test-Driven and Domain-Driven Development
LINQ to SQL and Entity Framework are not particularly friendly to test-driven development. While some have found ways to unit test LINQ to SQL, Entity Framework does not support unit testing. The biggest drawback in this respect is that Entity Framework classes must have some ties to the API objects. While it is possible to create custom objects in Entity Framework, in order for them to leverage relationship management and change tracking, they need to implement some key interfaces. This makes it difficult to separate the concerns in ways that are required by many who use domain-driven development, unit testing, etc.
For some developers, there are also issues with the lack of implicit lazy loading in Entity Framework and the lack of complex types (Value Objects) in LINQ to SQL. If these are your preferred techniques, then you will be better off sticking with the current array of excellent ORMs that enable this type of development. The Data Programmability team has been listening to and learning from the DDD community and while they managed to get some interfaces into V1 to enable the use of custom classes, we should see major improvements in Version 2 in this area. See "Being Ignorant with SQL" for more information links to some of this conversation as well as this Entity Framework project written by a domain-driven developer.
Wrapping Up
This article has attempted to review some of the bigger areas of comparison between the key data access APIs in .NET 3.5: ADO.NET "Classic", LINQ to SQL, LINQ to Entities, Entity Framework Object Services, and Entity Framework EntityClient. While some of the differences will be very useful to be aware of when you start coding, others may make the difference between choosing one technology over the other for certain scenarios or even for your entire organization.
ADO.NET "classic" may continue to be your data access tool of choice, but you may find that with new applications that use SQL Server, LINQ to SQL is the perfect way to quickly get an application up and running without having to worry about a lot of implementation details. The ability of the Entity Data Model to provide a highly customized model to program against and leverage elsewhere in your enterprise may make Entity Framework your new best friend. Or you may even have learned that none of these options will be the silver bullet for you, but at least now you know!