Saturday, July 12, 2008

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:

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))

1 comments:

Kristofer said...

The difference in timings are both interesting and scary at the same time. If RTM shows the same behavior it absolutely need to be investigated in more detail before EF can even be considered for use in a new project.

If you repeat the same test with SQL profiler running a profiler trace, what are the main differences you see in the Linq2SQL vs EF? Does EF generate any additional database roundtrips (retrieving data, meta data, preparing statements), transaction wrappers, setting/resetting connection specific settings, connection recycling etc...?