Comparison of Entity Framework and LINQ to SQL Projects Using a 120-Table Database

Kristofer Andersson of Huagati Systems, Bangkok, is in the process of designing and implementing an airline passenger reservation system (APRS) sample project with Visual Studio 2008 SP1, SQL Server 2005/2008, ASP.NET 3.5, AJAX, LINQ and LINQ to SQL.

His latest Tools - Part 1 - Add-ins - Maintaining naming conventions and keeping the Linq2SQL DBML in sync with the database post of July 7, 2008 describes two add-ins for LINQ to SQL:

Add-in 1. Converts table and column names to .NET naming conventions, such as airport to Airport, and airport_code to AirportCode.

Add-in 2. Applies some database schema changes to the DBML file. The current version supports new or removed columns, column nullability and datatype changes, and tables removed. A future version will support added tables and foreign keys.

You can download the add-in and its DLL from a link on the above page.

Updated 8/19/2008: My Entity Framework Instantiation Times with a 120-Table Database post today provides a comparison with Julie Lerman's model-creation and save times for a 400-table database in her Entity Framework Designer and Large Databases post of the same date.

Earlier Posts about the UI, Application Architecture, and the Data Model

His June 2008 blog entries cover the Web UI, application architecture, and data model:

• Data Model - Basics - Part 10 - Overview #2 and SQL-DDL script
• Data Model - Basics - Part 9 - User Accounts, Security Rights
• Data Model - Basics - Part 8 - Checkin and Boarding
• Data Model - Basics - Part 7 - Travel Agencies, Corporate Customers etc
• Data Model - Basics - Part 6 - Seat Reservations
• Application Architecture - Part 3 - Interfaces/APIs for interaction with external systems
• Application Architecture - Part 2 - Data Access Layer - Dynamic Linq
• User Interface - Part 2 - Encapsulating UI Controls
• User Interface - Part 1 - Choice of User Interface technology

It’s obvious that Kristofer knows whereof he speaks when it comes to APRS; his CV says:

[For Results Reservation Technologies, Ltd., he] converted an old airline reservation system from 16-bit Windows to 32-bit and subsequently to re-developed key parts of the system, extended and improved functionality. The system as ResultS and has been deployed at Spirit Airlines, Nok Air, FlyNordic, AeroCalifornia, Mango, Viva Aerobus, ExpressJet and several other LCCs [Low-Cost Carriers].

His Data Model - Basics - Part 10 - Overview #2 and SQL-DDL script post of June 29, 2008 includes links to a database diagram as a 6964 x 4770 px Overview2.png file and a 5,138-line CreateResDB_sql.txt T-SQL script to create the final data model, which contains 120 tables.

Using the ResDB Data Model to Compare LINQ to SQL with Entity Framework Project Characteristics

Following are comparisons of executable, XML file and class size, as well as initialization times of simple Windows form LINQ to SQL and Entity Framework projects generated from an SQL Server 2005 Express ResDB database with no sample data:

Comparison	LINQ to SQL	Entity Framework
Executable file size, KB	530	1,124
Memory footprint (private working set), KB	3,425	3,568
XML file (DBML/EDMX) size, KB	213	1,090
XML file(DBML/EDMX) lines	2,176	16,297
Class file (C#) size, KB	1,438	2,220
Class file (C#) lines	50,890	44,313
Application initiation time, s.	0.042	0.039
Data/ObjectContext initiation time (first), s.	0.394	9.426
ObjectContext initiation time (precompiled), s.	N/A	2.063
Data/ObjectContext recreation time (next), s.	0.004	0.021

Notes:

1. Executable file sizes and times are for Release builds.
2. Application initiation time starts the Stopwatch timer before invoking InitializeComponent() and stops it in the MainForm_Load() event handler.
3. Data/ObjectContext initiation time (first) times initially creating, adding, and querying an aircraft entity to the aircraft(s) collection within a using block.
4. Data/ObjectContext recreation time (next) times repeated instances of the above (cached) operation.
5. Update 7/12/2008: Precompiled views with the CSharp.Views.tt T4 template from the ADO.NET Team’s How to use a T4 template for View Generation post of June 20, 2008.

Conclusion: Entity Framework takes 24 times longer to execute the first update/query combination and 5 times longer for subsequent (compiled and cached) operations with no sample data loaded in the underlying database. Despite the EF project’s 2:1 ratio of executable file sizes and larger XML and class file sizes, the memory footprint of the two projects is almost identical.

Precompiling Views and Fixing the EntityConnectionString

Update 7/12/2008: Precompiling the views reduces the initiation time from 8.426 to 2.063 seconds and the ratio of EF:LINQ to SQL time from 24:1 to 5.25:1 when built and run with F5. Even 2 seconds is a significant startup hiatus.

However, launching from the Debug or Release Huagati.exe file and clicking the Create Object Context button throws the following exception:

The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.
  at System.Data.EntityClient.EntityConnection.ChangeConnectionString(String newConnectionString)
  at System.Data.EntityClient.EntityConnection..ctor(String connectionString)
  at System.Data.Objects.ObjectContext.CreateEntityConnection(String connectionString)
  at System.Data.Objects.ObjectContext..ctor(String connectionString, String defaultContainerName)
  at HuagatiModel.HuagatiEntities..ctor() in C:\Huagati\HuagatiEntityCS\Huagati.Designer.cs:line 231

Line 231 is the first line of the default constructor:

        public HuagatiEntities() : 
                base("name=HuagatiEntities", "HuagatiEntities")
        {
            this.OnContextCreated();
        }

which doesn’t work from the Huagati.exe file.

You must build the entity connection string with the following code (from Danny Simmons’ update Entity Framework FAQ #8 post) and pass it to the constructor overload to solve Huagati.exe’s problem:

SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
sqlBuilder.MultipleActiveResultSets = true;
sqlBuilder.DataSource = @".\SQLEXPRESS";
sqlBuilder.InitialCatalog = "Huagati";
sqlBuilder.IntegratedSecurity = true;

EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
entityBuilder.ProviderConnectionString = sqlBuilder.ToString();
entityBuilder.Metadata = "res://*/";
entityBuilder.Provider = "System.Data.SqlClient";

using (HuagatiEntities hgCtx = new HuagatiEntities(entityBuilder.ConnectionString))

Technorati tags: Entity Framework, Entity Data Model, EDM, Linq to Entities, LINQ to SQL, LINQ, Performance