DAL Layer : EF 4.0 or Normal Data access layer with Stored Procedure

Question

Hello Experts,

Application :

I am working on one mid-large size application which will be used as a product, we need to decide on our DAL layer. Application UI is in Silverlight and DAL layer is going to be behind service layer. We are also moving ahead with domain model, so our DB tables and domain classes are not having same structure. So patterns like Data Mapper and Repository will definitely come into picture.

I need to design DAL Layer considering below mentioned factors in priority manner

1. Speed of Development with above average performance
2. Maintenance
3. Future support and stability of the technology
4. Performance

Limitation :

1) As we need to strictly go ahead with microsoft, we can not use NHibernate or any other ORM except EF 4.0

2) We can use any code generation tool (Should be Open source or very cheap) but it should only generate code in .Net, so there would not be any licensing issue on per copy basis.

Questions

1. I read so many articles about EF 4.0, on outset it looks like that it is still lacking in features from NHibernate but it is considerably better then EF 1.0

So, Do you people feel that we should go ahead with EF 4.0 or we should stick to ADO .Net and use any code geneartion tool like code smith or any other you feel best

1. Also i need to answer questions like what time it will take to port application from EF 4.0 to ADO .Net if in future we stuck up with EF 4.0 for some features or we are having serious performance issue.

2. In reverse case if we go ahead and choose ADO .Net then what time it will take to swith to EF 4.0

Lastly..as i was going through the article i found the code only approach (with POCO classes) seems to be best suited for our requirement as switching is really easy from one technology to other.

Please share your thoughts on the same and please guide on the above questions

Answer 1

Where do you get the idea from that a stored procedure bound DAL is normal? I personally said goodbye to them like 15 years ago going THEN for systems like any ORM on the market (hing: Entity Framework is ap retty bad one compared to stuff like nhibernate). I am personally always hughely amused that everyone except MS "fans" thing ORM's are something new.

Even given the limitations Entity Framework has - it is vastly better than anything you can manually come up with with stored procedures.

1: normally about 40% to 60% of you code deals with database interaction. This is eliminated by any non-stupid ORM -> make the math.

2: see 1.

3: good question. MS has a history of doing really stupid things in the area of data access, sadly.

4: most likely the same like your handwritten one - within 5%. Sadly your anemic ORM (Entity Framework) does not implement any of the really interesting features (caching etc.), so hugh gains are not expected automatically.

Answer 2

Using EF4 will save you a lot of manual coding - or code generation. That alone seems to justify using it - the best code and the only code guaranteed to be bug-free is the code you don't need to write.

EF4 and NHibernate and the like are very powerful tools that can handle the most demanding and complicated business requirements - things like inheritance in database tables and a lot more. They do add some overhead, however - and they're not always easy to learn and pick up.

So my provocative question would be: why not use Linq-to-SQL? If you only need SQL Server as a backend, if your mappings are almost always a 1:1 mapping between a table and a class in your domain model, this could be a lot easier than using either EF4 or NHibernate.

Linq-to-SQL is definitely easier and faster than EF4 - it's just a rather thin layer on top of your database. It's a lot easier than learning NHibernate - no messy mapping syntax to learn, you have support through a visual designer. And it's simple enough that you can even get in there and manipulate the code generation using e.g. T4 templates (see Damien Guard's Linq-to-SQL templates).

Linq-to-SQL is 100% supported even in .NET 4 / VS 2010, so there's a really good chance it will still be around in VS2012 (or whatever the next one will be called). So all those doomsday predictions about its demise are largely exaggerated - I wouldn't worry about it's future proofness for a second.

UPDATE: Some performance comparisons between Linq-to-SQL and EF:

• Entity Framework and Linq-to-SQL Performance Comparison
• Looking at EF Performance - some surprises
• Entity Framework and LINQ to SQL

In general, Linq-to-SQL seems to have an edge on query performance, but EF seems to be doing better on updates.