Wednesday, March 2, 2011

Should I ever use Entity Framework 4.0?

I did a small in-house presentation for the young architects in our company and though of sharing my findings. We compared different ORMs, and discussed more about entity framework vs NHibernate vs performance that ADO.NET provides. I will also post the difference of performance that we found while comparing NHibernate and ADO.NET. You count this in continuum to my previous post that I wrote sometime back, about EF4: ADO.NET Entity Framework v4.0

What is Entity Framework?


  • Is a data access framework comes with .NET 4.0
  • Bridges the gap between database and objects(POCO)
  • Its an evolution of ADO.NET
  • Uses Entity SQL (eSQL) – Query to entity framework model (.edmx files)
  • Sits on top of ADO.NET 2.0
  • Has an out-the-box answer with regard to n-tier development, in "self-tracking entities“ - eventually.
Architecture
ado.net evolution
FIG 1: ADO.NET Evolution and technology support

entity framework arch
FIG 2: Entity Framework mapping to ADO.NET

entity framework layer
FIG 3: Entity Framework components

Provides three types of approach:
  • Database first (the old way – then generate the model, then code)
  • Model first (the .edmx model – and then generate the database)
  • Code first (and then generate the model, and then the database)
What does it provide?
  • Generates strongly-typed entity objects
  • Customizable beyond 1-1 map
  • Generates mapping/plumbing code
  • Translates LINQ queries to database queries (LINQ to Entities)
  • Materializes objects from db calls
  • Tracks changes, generates updates/inserts
  • Giving intellisense, compile-time and debugger support
Where can it help?
  • Create ER-->Database
  • Generate ER Model
  • Quickly provide the CRUD operations, along with lazy/eager loading options.
  • Data layer (Enumerated tables, query generation, default crud, complex joins - reports, data core Wrapper, business logic, etc?) – You may want to utilize the Unit of Work pattern along with the Repository pattern.
  • Specific modules (UI logic - Handling data/complex grids, cells within cells)
Are there any EF data providers?
  • The Progress DataDirect 100% managed code ADO.NET data provider eliminates the need for database clients, which boosts .NET application performance and delivers a flexible, secure connection to all major databases, including Oracle, DB2, Sybase, and Microsoft SQL Server.
  • VistaDB is the only .NET embedded database that allows you to Xcopy a single DLL for both 32 and 64 bit runtime support.
  • Devart, offers ADO.NET Data Providers supporting access to Oracle, MySQL, SQL Lite, PostgreSQL, and Microsoft SQL Server databases.
  • OpenLink's Virtuoso ADO.NET Data Provider allows developers to access native Virtuoso data (SQL, XML, and RDF) and any Virtuoso Linked Tables from external ODBC and JDBC accessible data sources. Known-compatible external data sources include Oracle (versions 7.x to 11.x), Microsoft SQL Server (6.x to 2005), IBM DB2 , Sybase (4.2 to 12.x+), IBM Informix (5.x to 11.x), Ingres (6.4 to 9.x), Progress (7.x to 10.x), MySQL, PostgreSQL, and Firebird.
  • IBM supplies ADO.NET data providers for access to DB2, Informix, and U2 databases.
  • Sybase SQL Anywhere 11 includes Sybase iAnywhere's ADO.NET Data Provider.
  • Sample Entity Framework Provider for Oracle - community effort project
Some points to ponder
  • Primary purpose is providing basic CRUD operations
  • Not really feasible for reporting.
    • eSQL Learning Curve, usually required to learn eSQL(unless generating complex reports?).
    • Requires knowledge of eSQL for complex joins/GROUPBY
    • Uses traditional string based queries with a more familiar SQL like syntax
    • Dynamic queries may be composed in a traditional way at run time using string manipulation
    • Projection is not possible; you must always return an ObjectQuery<T>
  • Develop against a conceptual view(the model, that is, .edmx classes) of your underlying data, not the store itself
  • Doesn't have a lot of providers for other databases.
  • Very young
  • Small community behind it.
  • Requires workaround for LEFT OUTER JOINs. For instance, see following loc:
   1: var v = from en in context.Entity.Include("PostalAddress").Include("PostalAddressType")
   2:  
   3: let leftouter = (from pa in en.PostalAddress
   4:                 select new //Project into a new object
   5:                 {
   6:                 PA = pa,
   7:                 PAT = pa.PostalAddressType
   8:                 }).FirstOrDefault()
   9:                 
  10:             select new {
  11:             EntityID = en.EntityID,
  12:             PostalAddressID = (Guid?)leftouter.PA.PostalAddressID,
  13:             PostalTypeID = (Guid?)leftouter.PAT.PostalAddressTypeID
  14:             };
 Entity Framework problems with SQL IN keyword
…AND D.STATE_NAME IN ('Approved', 'Scanned', 'Returned') is invalid!    
eSQL is not SQL!
  • Cannot access multiple database! Quite rare in our case…
Relationship navigation problem – Learn to navigate!
NAVIGATE operator allows a query to explicitly traverse a relationship; says don’t use joins.
   1: SELECT SalesPersonID, FirstName, LastName, HireDate
   2: FROM SalesPerson sp
   3:     INNER JOIN Employee e ON sp.SalesPersonID = e.EmployeeID
   4:     INNER JOIN Contact c ON e.EmployeeID = c.ContactID
   5:     INNER JOIN SalesOrder o ON sp.SalesPersonID = o.SalesPersonID
   6: WHERE e.SalariedFlag = 1 AND o.TotalDue > 200000
The above code is like following is eSQL:
   1: SELECT VALUE sp
   2:     FROM AdventureWorks.AdventureWorksDB.SalesPeople AS sp
   3: WHERE EXISTS(
   4:     SELECT VALUE o 
   5:         FROM NAVIGATE(p, AdventureWorks.SalesPerson_Order) AS o
   6:     WHERE o.TotalDue > 200000)

 What about EF Logging? Does it provide cool logging features?
Nope. Missing!

Use objectQuery.ToTraceString(); for query logging; yep, thats all.

Output for the above loc: Untraceable!
[SELECT 1 AS [C1], [Extent1].[CHEQUE_ID] AS [CHEQUE_ID], [Extent1].[CHEQUE_NUMBER] AS [CHEQUE_NUMBER], [Extent1].[ACCOUNT_NUMBER] AS [ACCOUNT_NUMBER], [Extent1].[ACCOUNT_TITLE] AS [ACCOUNT_TITLE], [Extent1].[MICR_AMOUNT] AS [MICR_AMOUNT] FROM [dbo].[CHEQUE] AS [Extent1] INNER JOIN [dbo].[CHEQUE_ACTIVITY] AS [Extent2] ON EXISTS (SELECT cast(1 as bit) AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] INNER JOIN [dbo].[CPS_CHEQUE] AS [Extent3] ON 1 = 1 WHERE ([Extent2].[CHEQUE_ID] = [Extent3].[CHEQUE_ID]) AND ([Extent1].[CHEQUE_ID] = [Extent3].[CHEQUE_ID]) ) INNER JOIN [dbo].[SEC_USER] AS [Extent4] ON EXISTS (SELECT cast(1 as bit) AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable2] INNER JOIN [dbo].[USER] AS [Extent5] ON 1 = 1 WHERE ([Extent2].[USER_ID] = [Extent5].[USER_ID]) AND ([Extent4].[USER_ID] = [Extent5].[USER_ID]) ) INNER JOIN [dbo].[WF_STATE] AS [Extent6] ON ([Extent1].[STATE_ID] = [Extent6].[STATE_ID]) AND (([Extent6].[STATE_NAME] = 'Approved') OR ([Extent6].[STATE_NAME] = 'Scanned') OR ([Extent6].[STATE_NAME] = 'Returned'))]
How in the world will you map Extent1 with the actual database table? think, and leme know.

Vote of no/confidence?
Here is a website, expecting “more” from Microsoft! (0: Signed by the people who doesn’t buy into the vote of confidence on this product. What does this mean? This means, no one, at least no one from the signatories.

So, should I ever use entity framework?
Yes, you should.

B…ut… Why?
Community as well as the online help (tutorials, videos, etc) growing, stabilizing, and expanding fast. Plus, dont forget the LINQ functionalities. EF has the best LINQ providers as compared the NHibernate. NHibernate is far far beyond where LINQ is concerned.

Where should I use it?
Anywhere and everywhere. Except for your production environment! If you still are, then tell your customer to be prepared for surprises. Since EF is growing faster, you may bank upon it after sometime.
Good luck.

References:
Disclaimer: Please understand, the motive behind this post is to understand the pros and cons, this means, there is NO boiler plate answer; and its up to the reader to decide whether they want to use EF or not.

    6 comments:

    1. This is a terrible post. Its dubious and self contradictory. If you are going to do a post atleast put some effort to conclude it. what a waste of my time.

      ReplyDelete
    2. Thanks for your valuable feedback.

      I'm sorry if you were looking for a "Yes/No" sort of answer.

      I believe content is clear enough for anyone to "decide" whether or not they want to use it from the available pros and cons.

      ReplyDelete
    3. Anonymous5:50 PM

      I agree, after using EF 4.1, conclusion - it is only for small sized databases. Not scalable. Very resource intensive. Slow. Slow. Long learning curve. Too much abstraction (written by programmers with lots of time to waste, and no knowledge of the real business world).

      Just because someone published a theory/concept 30 years ago (EDM) does not mean it is good or it makes sense.

      ReplyDelete
    4. Anonymous5:19 AM

      http://stackoverflow.com/a/132713

      ReplyDelete
    5. asava samuel3:36 PM

      KMan

      This is a good LINQ provider for VistaDB.
      https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

      ReplyDelete
    6. Anonymous9:03 AM

      Any ORM is an extra layer added, which has a cost in performance, no matter how small it could be. The effect could add up.

      A real programmer shouldn't use it, EF or NHibernate. There is no shortcut to an excellent database application that detours good and efficient database object programming. ADO.NET is good enough to programmatically take care of database access with high efficiency in terms of system performance, development, and maintenance. The attempt to avoid database programming will get you to nowhere.

      ReplyDelete

    Related Posts

    Popular Posts