Recently, I was building a project on ASP.NET MVC which used data from a database with a simple structure. I got used to use Entity Framework (EF) in big projects with complicated databases. I wonder what is the best way to use to access data in relational databases: LINQ to SQL or EF.
After reading blogs and discussions on forums I wrote this article to show the advantages and disadvantages of using LINQ2SQL and Entity Framework (EF).
Overview
LINQ is a framework for querying collections, which includes EF (LINQ to Entities), but also includes LINQ to SQL, LINQ to Objects, LINQ to XML, etc.
Entity Framework is an ORM framework for working with databases.
LINQ – Overview
Here’s a summary of the LINQ providers that are included with .NET 4.
LINQ to SQL:
This is the original LINQ provider for data access. It allows you to fetch data from a SQL Server database.
LINQ to Entities:
Like LINQ to SQL, LINQ to Entities allows you to perform database queries with a LINQ expression. Unlike LINQ to SQL, it supports a range of database software—anything that has an ADO.NET provider—and it gives you more options for mapping tables to differently structured objects.
LINQ to Objects:
This is the simplest form of LINQ. It allows you to query collections of in-memory objects (such as an array, an ArrayList, a List, a Dictionary, and so on).
Parallel LINQ: This is a variation of LINQ to objects that has built-in support for multithreaded execution. That means you can use it to speed up intensive searches on in-memory collections—if your web server has multiple CPU cores.
LINQ to DataSet: This form of LINQ resembles LINQ to objects, except it digs DataRow objects out of a DataTable.
LINQ to XML: This form of LINQ allows you to search the elements contained in an XElement or XDocument . In other words, it allows you to perform more powerful searches when dealing with in-memory XML data.
Entity Framework: Overview
LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. The Entity Framework includes LINQ to Entities which exposes many of the same features as LINQ to SQL over your conceptual application data model.
When to use Entity Framework and LINQ to SQL
// from Microsoft, http://blogs.msdn.com/b/data/archive/2007/04/28/microsoft-s-data-access-strategy.aspx
If you are writing an application that requires any of the following features, you should use the ADO.NET Entity Framework:
* The ability to define more flexible mapping to existing relational schema, for example:
- o Mapping a single class to multiple tables
- o Mapping to different types of inheritance
- o Directly Modeling Many to Many relationships
- o Mapping to an arbitrary query against the store
* The ability to query relational stores other than the Microsoft SQL Server family of products.
* The ability to share a model across Replication, Reporting Services, BI, Integration Services, etc.
* A full textual query language
* The ability to query a conceptual model without materializing results as objects
If you do not require any of these features, LINQ to SQL may provide a simpler solution for rapid development.
Features Comparison
LINQ to SQL has features targeting “Rapid Development” against a Microsoft SQL Server database.
The Entity Framework has features targeting “Enterprise Scenarios“.
// from Microsoft, http://blogs.msdn.com/b/data/archive/2007/04/28/microsoft-s-data-access-strategy.aspx
LINQ to SQL supports rapid development of applications that query Microsoft SQL Server databases using objects that map directly to SQL Server schemas.
LINQ to Entities supports more flexible mapping of objects to Microsoft SQL Server and other relational databases through extended ADO.NET Data Providers.
Features:
- EF – Enterprise Development, L2S – Rapid Application Development.
- EF – Works with all data sources, L2S – Mainly works with SQL Server.
- EF – Works with Conceptual model of database, L2S – Works with objects in database.
- EF – “.EDMX” is created, L2S – “.dbml” is created.
- L2S does not support POCO mapping, EF does.
- EF has more flexibility (code first, model first, database first). L2SQL has only 1.
- EF has support for SPROC -> POCO mapping.
- EF has Entity-SQL, allowing you to go back to classic ADO.NET when required. You can build queries in LINQ (or in “Entity SQL”, a canonical version of SQL extended to support concepts like strong typing, polymorphism, relationship navigation and complex types), return results as strongly typed CLR objects, execute stored procedures or table valued functions through strongly-typed methods, and process changes by calling a single save method.
- EF allows mapping a single class to multiple tables.
- EF supports inheritance (TPT, TPH)
- EF will handle M-M relationships
- EF goes hand-in-hand with Repository pattern, and deferred execution via IQueryable
- L2S is still slightly better than L2E at translating LINQ queries that use normal CLR methods into TSQL. L2E supports more or less the same things but through its’ own EntityFunctions class: http://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions.aspx.
| Feature | LINQ to SQL | Entity Framework |
| Model | domain model | conceptual data model |
| Databases Supported | SQL server only | variety of databases |
| Data Sources | tables only | tables, replication, reporting Services, BI and etc |
| Complexity | simple to use | complex to use |
| Development Time | rapid development | slower development but more capabilities |
| Mapping | class to single table | class to multiple tables |
| Inheritance | hard to apply | simple to apply |
| File Types | dbml file only | after compilation generate edmx file with 3 sections to represent the schema: csdl, msl and ssdl |
| Create complex properties | Not Support | Support in VS2010, we can manually modify in .edmx file. |
| Query | 1. LINQ to SQL (for select) 2. Data Context (for update, create, delete, store procedure, view) |
1. LINQ to Entities (for select)2. Entity SQL (is a derivative of Transact-SQL, it supports inheritance and associations)3. Object Services (for update, create, delete, store procedure, view)4. Entity Client (is an ADO.NET managed provider, it is similar to SQLClient, OracleClient, etc. It provides several components like EntityCommand, EntityTransaction) |
| Can synchronize with Database if Database Schema is changed | Not Support | Support |
| Performance | Very slow for the first query | Very slow for the first query.But overall performance is better than LINQ to SQL |
| Continue to improve features in the future | No | Yes |
| Generate database from entity model | Not Support | Support in VS2010 . |
Performance
L2S runtime is more lightweight than EF (due to only a single layer; EF has to deal with two model layers and the mappings between them).
EF often generates a bit more verbose TSQL than L2S but most of the time that only affects readability if you’re profiling and looking at the generated queries; the SQL optimizer will end up with the same execution plan most of the time. There are however some cases when the queries can grow so large and complex that it can have a performance impact.
L2S is also slightly better at doing client-side optimization of queries; it eliminates where clause predicates that can be evaluated client-side so the database don’t have to worry about them. This means less work for SQL Server’s optimizer, and less risk that you’ll end up with a ‘bad’ execution plan.
(* taken from a conversation at http://stackoverflow.com/questions/4329991/linq2sql-vs-ef-in-net-framework-4-0/4330153#4330153 *)
Is LINQ2SQL deprecated?
I read some thoughts on forums.asp.net that LINQ2SQL is obsolete or deprecated.
Although LINQ to SQL still exists in .NET 4, it’s been superseded by LINQ to Entities, which offers the same features with fewer limitations.
Entity Framework is more robust and is the way Microsoft is going. Microsoft will continue to support L2S but will put more effort into the EF and the Linq to Sql will be retired.
Microsoft wants us to use the Entity Framework vs L2S. But it’s also been written that L2S is easier/faster to use and implement.
Microsoft is defining a migration plan for customers that start with LINQ to SQL and require additional functionality, such as richer mapping capabilities or access to other stores, to migrate to the ADO.NET Entity Framework.
But we can’t find much information about Microsoft plans for it’s migration strategy or what they suggest us to do in the short term. Before you know Microsoft’s data access technologies they will probably announce yet another data access technology. They seem to do that every 3-5 years.
Conclusion
Both LINQ to SQL and Entity Framework are great choices, pick the one you feel comfortable with.
Read more:
* Entity Framework Basics – http://parassanghani.blogspot.com/2011/02/entity-framework-basics.html
* MVC 3 / EF tutorials – http://asp.net/entity-framework/tutorials
jorgen says:
Thanks, Max. Quite thoroughly
Jim Thompson says:
Hi Max,
I read your article with great interest. Would you be willing to think about my situation and help me choose a path: LINQ, Entity Framework, my own datasets???
Here is my problem domain: (I think the 3rd item represents the area where I need help picking a technology.)
1.The app will primarily write static web pages.
2.Most database update will be automated by parsing large text files.
3.There will be some reasonably complex update screens where a human can edit data that computer algorithms previously chose and wrote to the database. For example, an important screen would allow a user to enter an ID and see a couple fields of a header record and update perhaps 50 related detail records (filtered from 1,000 that match the header and half a million in total). It would also permit updating another grid of related records, and a single record from a related table. Many of the updated fields would be long varchar with one being varchar(max), so I assume those will be available in their own textbox or window.
4.I’m sole developer and don’t need to worry about coordinating with a team. To keep things simple, I envision doing data update on a Windows machine (rather than through a browser) and can reduce concurrency issues by locking the database if that saves a lot of trouble.
5.Since I’m new to this environment, I imagine whatever I choose should be thought of as a prototype that will work, but will later be re-written.
6.I’m not interested in learning Entity Framework just because it’s applicable to more general issues. I want to make this application work in whatever is the best and quickest way.
Thanks for reading such a complex question,
Jim
Anonymous says:
Great Article.. I was looking this content only from last 5 days.. Thanks a ton..
буркин ваня says:
пасибос!!! помоглос в работе-с!!!
Bonakkid says:
Great post. “Entity Framework is more robust and is the way Microsoft is going”…I’ll go for this one.
Sharma Navin24 says:
Nice read.
Chennoufialwalid says:
Thanks, God bless you
Sa2 says:
Can u say about performance .. I saw Bl toolkit have greater performance than EF… whether Which is good ??
Petar Petrov says:
Good Post
Dinesh says:
good one, Thanks!
Rajdeep says:
Good Job