Linq2Sql Or Entity Framework?

On the first look Entity Framework looks so much similar to Linq2Sql that its natural to question if we really need another framework? The question seem to be both puzzling and relevant at the same time as Linq2Sql seem to be more simpler and more matured. With two competing technology the question is which one to incorporate in the next project. Here we try to answer this question.

For those who came in late…

This section is intended for those who are new to the idea of LINQ and LINQ Provider and LINQ 2 SQL. This section doesn’t intend to discuss any of these things in details. This section just provides an overview which is important for comparing the two technology. If you feel you know this section completely; feel free to jump to next section here.

Linq2Sql framework was shipped with .Net 3.5 framework to offer a convenient mechanism to perform data base operations (queries and updates) using the new LINQ syntax. (People may argue in the reverse that LINQ offers a database like query over .Net objects and XML). Here is the brief overview of the various related components.

LINQ

LINQ stands for Language INtegrated Query. Essentially what LINQ provide is new smart SQL like construct that can operate and use .Net object notation. In the simplest sense, you can apply LINQ on any enumerable (array, list etc).

var result= from b in books
where b.Title.Contains("Linq")
Select new { b.Title, b.Author, b.Publisher};

The above code operates on a collection of books (which can be any IEnumerable). It searches all the books whose Title Contains the word “Linq” and then returns a collection of new Object type each containing only a subset of fields from the type Book.

But the beauty of LINQ lies not just in its syntax but in the fact that that it is an extensible design and the same syntax can be applied to different source with the help of pluggable providers.

LINQ Provider

To put in most generic terms – LINQ operates on a data source. That means Data source can be things other than a .Net object collection. It can be anything that can be considered as data source. For example – An XML file or a CSV file or a excel sheet and of course the most obvious of them all – a relational database.

Another important point to understand is that although each of these are data sources; accessing each of them will require a different mechanism – Thus we need different plug-ins that will allow accessing different sources with a uniform syntax. This Plug-in is LINQ Provider.

LINQ allows different data sources to be accessed using a uniform syntax. However, different data source needs different internal mechanism to operate upon them. A LINQ Provider takes care of translating the LINQ syntax to necessary underline operation to access the given source.

Microsoft shipped three different Providers to work with LINQ. The providers are:

LINQ 2 Object : The provider that allows LINQ to work with any Object collection.
LINQ 2 Xml : A new set of XML API’s that allows LINQ to operate on an XML Document allowing a new and efficient way to create and query and Xml Document.
LINQ 2 Sql: A new set of API that allows accessing a Sql Database using LINQ and object notation. The next section highlights this aspect in a greater detail.

LINQ 2 Sql Provider

LINQ 2 Sql Provider allows a database to be accessed and manipulated using native .Net language syntax (C# or VB.Net) and LINQ. This is what we do when working with LINQ 2 Sql.

Add a New Item to your project –
1. LINQ 2 Sql Classes
2. A .dbml file will be added to the project.
The dbml file opens up in a designer view.
Now Drag database table form server explorer to the dbml designer surface.
1. It will automatically show table and their relationship.
What happens behind the scene is most important. The framework generates the necessary classes to represent each table in the database. So typically a class will be created for each of the table and fields will be created to map columns and relations.
Now we will execute our queries against the newly generated set of classes.
The Data source in this case will be a class with the DataContext suffix applied to the name choosen for .dbml file.

LINQ 2 Sql Provider creates classes that maps to the selected tables from the Sql database. A class per table is generated. Fields for each column and relation is also generated. A class with DataContext suffix acts as a Data Source. The DataContext Class acts a collection of Collections.

Assume we are working with the famous Northwind Database and have generated a Northwind.dbml file. Now we have a data source called NorthwindDataContext. Here is how we will work:

NorthwindDataContext ctx = new NorthwindDataContext();
var qry = from c in ctx.Customers
where c.City == "London"
select new { c.ContactName, c.CustomerID, c.Country };

LINQ 2 Sql opened a exiting door of opportunity. Now we need not really write code against data base table but we can use LINQ syntax to work with the database.

Those who worked with any ORM (Object Relationship Mapping) solution in the past quickly assumed LINQ 2 SQL as the Microsoft’s ORM solution. This conclusion was highly deceptive. This is because of two reasons – First LINQ 2 Sql lacked (or should I say differed) several elements necessary to for being considered as an ORM solution. Second because Microsoft was already working in creating a true full fledged ORM solution – The Entity Framework.

Before we look at entity framework let us understand why LINQ 2 SQL can not be considered as an ORM solution. LINQ 2 SQL allows a simplistic 1:1 relationship of database table with the generated object.

In LINK 2 SQL, the generated classes correspond to a database table. That means the generated objects design in fully influenced by the database design and may not represent a real world business entity properly. This is not the intent of an ORM solution.

Ado.Net Entity Framework

Microsoft created Entity Framework as a part of Ado.Net API. and an ORM solution. Simply speaking an ORM framework creates a mapping between applications business objects and the database which holds the associated data. This mapping allows the two elements (business object and database schema) to change independent of each other. The Framework allows you to develop the two aspects independent of each other and the connect the two using ORM. We may start either from the Business Object and go on creating the necessary design or vice-versa.

An ORM solution provides two very important promises:

The Business Object and data base schema can vary independent of each other. Change in one will not cause change in the other aspect.

The Program will concentrate on modelling business scenario independent of database design, schema and other related considerations.

A few words regarding the motivation of ORM in general (remember this article is not about ORM). Business object are expected to concentrate on representing the real world business Model. A Database design is often influenced by concerns related to security, performance and other such issues. Thus a direct mapping often results in either a business object which is influenced more by database and fails to honestly model the real world or a database schema in an attempt to match with business object turns out to be quite un-optimized. A third scenario, theoretically better than the other two, forces programmer to address the concern and the difference. When done manually, this approach turns out to be quite brittle and any change triggers a series of changes. Programmer often end up using one of the first two approaches.

ORM in, that sense is an process of automating the third solution. It moves the mapping portion to a configuration file ensuring that compilation units are isolated from the changes.

Best Part, Entity Framework comes with its own LINQ Provider that allows Entity Framework to work with LINQ in the same way as LINQ 2 SQL. Unfortunately, this puts both technology against each other and triggers the confusion.

Entity Framework Vs LINQ 2 SQL

Now we have discussed both the concept let us try to compare the two frameworks and understand the source of confusion. We do the comparison in FAQ pattern.

Q. What are the similarities between the two frameworks – LINQ 2 SQL and Entity Framework ?

A. The most common similarity between the two frameworks include:

Both Framework generates .Net classes to represent Database Elements (Table, Columns, Relations).
Both Framework Provides similar designers to create the underlying classes.
Both Framework works well with LINQ.

The similarity leads to the obvious question –

Q. Why we need Entity Framework when we already had LINQ 2 SQL?

A. Entity Framework is not designed to compete with the LINQ 2 SQL; rather it is designed to server a greater purpose. An mentioned, it is a complete ORM solution. LINQ Provider is just one aspect of it in contrast to LINQ 2 SQL which is just a LINQ Provider.

An Entity Framework provides several advantage over LINQ 2 SQL. Some of the most important aspects are:

Support for any database supported under ADO.Net
Support for a true ORM where business object and schema varies independent of each other.
Entity framework allows a business object to get mapped to more than one table. Vice-versa more than one business object may be mapped to the same table.
Entity framework supports for 1:1, 1:many, many:1 and many:many relationship.
Entity framework supports representation of inheritance hierarchy.
Entity framework allows changing the mapping which keeping the business objects unchanged.
Entity framework is designed to work well with the optimized schemas.

Entity Framework is a bigger framework with larger scope than LINQ 2 SQL. While LINQ 2 SQL is just a LINQ Provider, Entity Framework is an ORM solution and LINQ Provider is just one aspect of it.

Q. If Entity Framework Serves all the needs why do we have LINQ 2 SQL

A. Now this is a good question and the answer may actually hurt those who are either die hard fans of LINQ 2 SQL or have invested sufficient time, effort and commitment to it LINQ 2 SQL. Yes the answer is we really don’t need LINQ 2 SQL. It is now apparent that LINQ 2 SQL is meant to phase out over a period of time.

Q. If LINQ 2 SQL is not really needed why does it exist?

A. Now, while I may not officially answer this question (remember I don’t represent Microsoft) if you look at evolution of the frameworks and their release there can be no other reasonable conclusion than one I am presenting here

Microsoft, perhaps, never intended to have LINQ 2 SQL as a solution or a framework. Perhaps (again) Entity framework was not ready yet at the time of .Net 3.5 launch. Microsoft shipped LINQ 2 SQL either as a stop gap solution or merely as a sample for LINQ provider. They finally launched Entity Framework with .Net 3.5 SP1. I strongly feel that Entity Framework should had been a part of a major release and not the part of a Service pack release which fails to convey its proper significance. There can be no two doubts that Entity Framework is the ambitious and recommended way for data access and LINQ.

Q. So is it right to conclude that we should replace all our LINQ 2 SQL with Entity Framework.

A. This apparently seem to be a logical conclusion and need to be done in the long run. However, it appears that the current release of Entity Framework is not ready yet for the enterprise need and perhaps we need to wait for the release of .Net 4.0. Some independent performance tests attempted to prove LINQ 2 SQL out performs Entity Framework. I haven’t done the test myself to either endorse or contradict their finding. It makes sense to wait for .Net 4.0. However one this is more than certain

LINQ 2 SQL will be fighting a loosing battle against Entity Framework and will finally be phased out.