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.
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
.OrderQty = 2
.SalesOrderHeader = order
Dim detail2 = New SalesOrderDetail
.OrderQty = 5
.SalesOrderHeader = order
Then create a context and insert only the order object before calling SubmitChanges
Using context = New AWModelDataContext
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
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 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.
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.
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.
Average of 100 Tests
DataAdapter with UpdateBatch=1
DataAdapter with UpdateBatch=100
Entity Framework Object Services
LINQ to SQL
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.
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
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
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!