What is the best database architecture to use for my asp.net site?

Question

I am looking into creating a new website using ASP.NET 4.0. I am currently starting to build a site that will need to store reporting information.

My database has around 25-30 tables (with a lot of relationships) to which my web application will need to store and read information from. Normally, the architecture I use is the Layered Architecture where I have a Business Logic, Business Objects and Data Access Layer. But I am thinking on moving on and using .NET 4.0 features.

So I am thinking of using the Entity Framework mainly because I really like the idea of using LINQ to a larger extent.

I would be grateful if someone could tell me if this would be a good idea for a project of my size. I have seen some good and bad points on EF and I just can't seem to make a decision (maybe due to my lack of dev experience).

I just need to make sure the solution is maintainable on the event additional tables are added in the future.

Thanks for any help!

Answer 1

I have to say, that the method described in Professional ASP.NET MVC has never let me down, I've used it twice on to very successful systems. If you wish to use WebForms a similar approach could be used for the data access type code.

Basically, they use MVC for the front-end, and the use a simple repository pattern for the back end, it has worked very well for me.

Answer 2

Entity Framework 4 has come a long way. They now support a POCO model which eases a lot of the pain that was associated with Entity Framework 1. Julie Lehrman's 2nd edition book covers these issues nicely.

That said, NHibernate 3 with their linq provider is a great solution too. And their SysCache implementation which comes with it and is very powerful for helping performance. EF does not have as good a story with 2nd level cache. AppFabric is different and more effort to setup.

ORMs are powerful. You will find some people telling you to stay away, but it's pretty time-consuming to write a data layer that does as much to parameterize all queries, cache objects, encourage transactions, and simplify validation.

Personally, I prefer NHibernate, but both are great tools.

Answer 3

I think the EF is great, I would recommend using it on large and small projects alike. Like anything it has it's quirks and things that you may not "like", but all in all it makes your data access much simpler.

The designer in EF makes adding new tables/entities to your model very simple (all you have to do is do a Refresh from Database).