Tuesday, September 07, 2010

Entity Framework : Part 2 – Comparison between EF and LINQ 2 SQL


As I said in the previous post, I’ll be covering the Entity Framework related concepts in a series. This is the 2nd post of the Entity Framework 4 Learning Series. in this series. If you have not read the first part of this series, I would suggest you do it to get a feeling of what Entity Framework has on offer.

LINQ seems to be the common factor between both these technologies. This also leads to confusion in the minds of many developers that if the queries are almost similar in syntax why do we have two different technologies trying to do the same thing. This post is an attempt to demystify some of the myths.

Major differences between Entity Framework and LINQ to SQL

LINQ to SQL was released along with LINQ. In a very simple terms I would say it allows developers to use the LINQ technology to access SQL databases as in-memory collection. The data model in database is converted into an object model in the programming language of developers choice. LINQ to SQL handles the process of creating and executing the SQL statements behind the scenes. As developers we need to call methods on the object model by changing the required attributes.

Various elements like Table, Column, Foreign-key relationship and Stored Procedures in relational database schema get mapped onto Entity, Class member, Association and method respectively in LINQ to SQL. The entities are normal classes annotated with special attributes to associate with relational database. Some of the commonly used attributes are

  • Table – maps class to a table
  • Column – maps field or property to a column
  • Association – maps property to Primary key or Foreign Key
  • Function – maps stored procedure or user defined functions

The model generated by the LINQ to SQL designer is a dbml file which closely resembles the relational model. These are the domain model classes which are used during the LINQ query execution.

LINQ to SQL supports only MS SQL server as the database provider. I believe since Microsoft did not have any product in Object Relational Mapper (ORM) space, they came up with LINQ to SQL in the first place. It works well if we are not doing too many fancy things and want to perform some CRUD operations on the database.

Entity Framework on the other hand is a full fledged ORM product which can work with multiple data providers which support ADO.NET. Its in the mould of something like NHibernate.
The designer stored the model as an .edmx file.

Entity framework has a conceptual model which is not directly linked to the domain model as in case of LINQ to SQL. This enables us to have a mapping layer in between the conceptual model and the persistent model. Entity framework uses 3 layers

  1. SSDL – Store schema definition language. This is the logical model which stores the database objects. This layer contains the Tables, Views, Stored Procedure, Functions etc. These objects can be queried using SqlCommand, SqlConnection, SqlDataAdapter etc.
  2. CSDL – Conceptual Schema definition language. This is the object model or the entity model that we use to query the data. This layer consists of Container, EntitySets, AssociationSets, AssociationTypes, Functions etc. These objects can be queried using an ADO.NET provider which exposes objects like EntityConnection, EntityCommand, EntityDataReader using a language called Entity SQL (ESQL).
  3. MSL – Mapping Schema Language. This layer maps the conceptual model with the logical model. We use code attributes to map the conceptual model with the logical model. The following diagram taken from an MSDN article demonstrates the Architecture of Entity Framework.

entity framework architechture

Entity framework provides an abstraction over the database layer. This is sometimes referred to as Object space model or O space model. This layer consists of DOTNET classes which are different from CSDL. The classes in object space provide strongly typed collections or individual objects. These objects can be queried using an object model which is queried using ObjectQuery<T>. The object model query takes a ESQL query. If we do not want to write ESQL queries we can also use the LINQ queries.

In case of Entity Framework it becomes easier to model the complex object relationships like inheritance mapping. Because we have a mapping layer between the object and the conceptual model it becomes easier to introduce complex types in the object model which might not have one to one mapping with the logical model in the database.


It would depend on the kind of solution we need to use these technologies. If we are only going to work with SQL server LINQ to SQL is a better choice. If we want something which is cross platform or needs to interact with different database provide we can use the entity framework as it provides a layer of abstraction.

With code first approach added to Entity Framework as well as support for fluent mappings I would expect people to migrate to Entity Framework.In the long run Microsoft seems to be pushing Entity Framework as a replacement for LINQ to SQL. I believe if we understand the basic concepts behind the two technologies it will be easier to understand their relevance.
I haven’t added any code here so there is nothing that I can attach with this post for download. I’ll add specific topics related to entity framework in future posts.

Until next time Happy Programming :)

1 comment:

  1. Fantastic article ! You havemade some very astute statements and I appreciate the the effort you have put into your writing. Its clear that you know what you are writing about. I am excited to read more of your sites content.