LINQ to SQL and Entity Framework… issues I have encountered with EF.

For the past 6 months or so there has been various discussions raised on the LINQ to SQL (released with .NET 3.5) vs. Entity Framework (released with .NET 3.5 SP1). Including several blog posts claiming that LINQ to SQL is dead (here, here and here are a few of them) and that LINQ to SQL is to be replaced by EF; these blogs were a result of an ADO.NET team blog post.

This is probably in part to do with the fact that LINQ to SQL was written by the C# development team, and EF is developed by the ADO.NET team, and they were both developed in parallel.

The ADO.NET team started development of the Entity Framework in 2003; at around the same time the C# design group embarked on developing a LINQ to SQL extension as a “stand-in” for the abandoned ObjectSpaces O/RM tool. I have read that LINQ to SQL was mainly written to help aid the development of LINQ in particular for the AND/OR query clauses.

Apparently the two projects were developed with little communication between the teams and in 2006 the ADO.NET team gained ownership of the LINQ to SQL and LINQ to DataSet implementations, adding LINQ to Entities into the mix.

The Entity Framework was originally meant to be included in .NET 3.5, however it was removed from early betas. It was later included in .NET 3.5 SP1. LINQ to SQL was originally developed to work with other databases, but prior to it’s release (for non-technical reasons) it was restricted to work with MS SQL Server only.

Having been playing with LINQ to SQL for a bit I found it very easy to implement and program against. Pretty much just dragging my tables into a DBML and by customising the MySettings class it allowed me to use the same connection settings as previous datasets and non-.NET apps. It made it very easy to integrate some LINQ to SQL with existing application development.

The database I am currently developing with has 364 tables (with around 850 FK links between them). Though dropping all of these onto a LINQ to SQL DBML file takes a while for Visual Studio to process, it works. Customising the DBML would be a bit of a pain however, given the lack of update facility in the designer (though there is a tool I am looking at http://www.huagati.com/dbmltools/, which would also allow me to standardise legacy naming conventions).

Would be nice to be able to spread the tables across several DBML files, allowing commonly used tables to be included in each LINQ to SQL class. Not had much chance to look at this but seems to cause problems when just dropping the tables into separate files.

Dropping the same tables into an Entity Data Model takes around the same time, but crucially any time I attempt to update the diagram in the designer, or often just selecting a table, Visual Studio bombs out, crashing.

There are bound to be several benefits to using the EF, such as: – 

  • Full provider model with support for multiple RDBMSs, including SQL Server Compact.
  • Not tied to a one:one relationship between entities and database tables.
  • Support for Table per Class and Table per Concrete Class hierarchy models.
  • Support for entity-splitting and complex types.

Though it may be the most serious issue I have encountered, this is not the only issue I have had with EF.

One common thing I do through-out code is check and see if changes have been made to a dataset or LINQ to SQL model. something easily achieved in both (<context>.GetChangeSet.Deletes, etc in LINQ to SQL and <DataTable>.GetChanges for DataTables).

It took me a fair amount of research to find how to do it for an Entity Data Model… and disappointingly it is not as simple a command: –

Dim changes  = _
     <context>.ObjectStateManager.GetObjectStateEntries( _
            EntityState.Added Or _
            EntityState.Modified Or _
           
EntityState.Deleted  _
            ).[Select](Function(o) o.Entity).OfType( _
                  Of <my table>)

Another problem I have had is to do with the connection settings. When you use an Entity Data Model, the connection string is not added to the MySettings class, it is just added to app.config. This make it a bit more involved to share connection settings in a common method (as discussed here). Plus when you go into the entity model, the connection setting is not selectable (or editable) in the design view.

Overall both are a good base and are much improved over having to write my own custom functions to build DataSets and DataAdapter from the large database schema. With both LINQ to SQL and LINQ to Entity, I can get past having to embed query strings in a DataAdpter. Building some classes that achieve the same thing but allow them to be debugged and syntax checked by Visual Studio when a schema update occurs.

As for which to use… Entity Framework looks to be the focus of future Microsoft developments in Visual Studio 2010, but in Visual Studio 2008 I like LINQ to SQL because it seems more integrated with the development environment, development is easier and it doesn’t crash Visual Studio! I do hope that the Entity Framework issues are fixed in .NET 4.0.

For now I will be continuing with LINQ to SQL, but ensuring it is separated out as much as I can, with as little customisation as possible, so that if need it can be easily replaced by Entity Data Models in the future.

Paul B
MCP

PS Eric Nelson posted a small blog and slides on Entity framework (Battle of the ORMs slides and links) today at http://geekswithblogs.net/iupdateable/archive/2009/02/16/battle-of-the-orms-slides-and-links.aspx.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s