LINQ to SQL vs Entity Framework
June 4th, 2009 by SanguanchaiCompare features for LINQ to SQL vs Entity Framework
| 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 (e.g. Customer type may have Address property that is an Address type with Street, City, Region, Country and Postal code properties) | Not Support | Support in VS2010 Beta 1 but 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 (Please see the result in the attach file) |
| Continue to improve features in the future | No | Yes |
| Generate database from entity model | Not Support | It will support in VS2010 Beta1. |
Limitation for Entity Framework in VS2008 SP1
1. It will throw error in runtime if we use Contains statement. For example,
from p in context.Yard_Projects
where p_projectIDs.Contains(p.ProjectID)
select p
2. It will not generate method that map to store procedure that are not return as entity type. (We need to Entity Client to execute store procedure.) For example,
Void DeleteProjects(@CSVProjectIDs)
3. We need to map every column of a table in the storage schema. If some columns are not mapped, it will compile error.
4. If we delete a type from diagram, it’s difficult to put it back. We may manually update in .edmx file or delete old one and generate new model.
5. You don’t get a lot of control over the storage schema at all. What you mostly see is the client schema and the mapping to the storage schema.
6. If you want create methods that are map to store procedure that has the result from multiple tables, we need to manually modify section “SSDL” in the .edmx file.
Performance Test
I tried to test on LINQ to SQL and Entity Framework, I found the overall performance Entity Framework is better than LINQ to SQL. I separated test cases into 2 test groups.
1. Test select/create/update/delete for single table. You can see my test result in the “TestForSingleTable” sheet.
2. Test select/create/update/delete for single table with many associations. You can see my test result in te “TestForManyAssociation” sheet.
You can see the performance report in the link, PerformanceReport
Recommendation
I think we should use Entity Framework than LINQ to SQL because many reasons as follows.
1. It is more flexible to mapping entity model to database. You can map one class to multiple tables, using inheritance.
2. It supports many queries, you can use LINQ to Entities, Entity SQL, Object Services and Entity Client.
3. When the database schema is changed, you can synchronize entity model from latest database.
4. It has better overall performance when compare with LINQ to SQL.
5. It will be improved many features in the VS2010. You will create complex data type, generate database from entity model.
6. It supports many database vender other than SQL Server.
But disadvantage of using Entity Framework may be complexity and development time.
Refer to my performance report, if we use Entity Framework to implement in the data layer, we should use the following patterns.testperformancelinqtosql_entityframework
1. Use LINQ to Entity for simple queries.
2. Use Entity Client or Object to execute Store Procedure for complex queries (many joins, groups) or queries that perform delete many entities with contains many associations.
3. Use Object Service to insert, update, delete single/multiple entities.
June 5th, 2009 at 4:51 pm
[...] can take a look at his findings in the post he published yesterday - LINQ to SQL vs Entity Framework. No doubt there are other architects and developers out there trying to get a handle on the same [...]
June 25th, 2009 at 12:00 pm
This is very helpful to me, i was in search of this type of summery features and comparison.
January 30th, 2010 at 2:13 pm
Where’s the PerformanceReport link?
Very comprehensive comparison. Can you link to where Linq 2 Sql won’t continue to improve?
February 2nd, 2010 at 7:32 pm
[...] http://www.osellus.com/blogs/2009/06/04/linq-to-sql-vs-entity-framework/ [...]