Programming Microsoft ASP.NET 4 - Dino Esposito [314]
In general, if your application has enough life ahead of it (no less than two years), after which a full redesign is acceptable, you can go with LINQ-to-SQL today and plan to upgrade later. However, keep in mind LINQ-to-SQL is not the light edition of Entity Framework; it has a slightly different programming API, and no migration path exists yet.
NHibernate
NHibernate is perhaps the most popular O/RM available today. It’s open-source software with a strong and active community to back it up. NHibernate requires you to provide a library of classes and a bunch of mapping XML files. Based on that, it offers a rich API to write your logic for persistence.
With the release of Entity Framework 4, the technical gap is shrinking more and more and mostly has been reduced to fine-tuning the framework’s behavior. The two main differences are the LINQ provider for expressing queries on entities, which is definitely superior in Entity Framework, and the absence in Entity Framework of second-level caching.
In addition, NHibernate looks like a more mature framework, closer to perfection in a way. Put another way, with the exception of adding a LINQ provider to it, I don’t really see how NHibernate can be significantly improved. As it is, NHibernate offers a number of extensibility points (lacking in Entity Framework) and more expressivity when it comes to dealing with paged collections and batch reads and writes. Companion tools (for example, profilers, caches, and sharding) are numerous for NHibernate and (currently) hard to write for Entity Framework because of the aforementioned lack of extensibility points.
Note
Sharding is a database design technique that consists of horizontal partitioning. In essence, the row set of a logical table is physically stored in multiple tables. Each partition is known as a shard and may be located on a distinct database server. The goal of sharding is gaining scalability by reducing table and index size and making search faster.
Note
So here’s a point-blank question: Entity Framework or NHibernate? Skipping the usual (and reasonable) point that it depends on the context, skills, and requirements, I’d say that with Entity Framework you don’t get the same programming power of NHibernate. However, if you don’t need that power, over all, you can work nicely and safer with Entity Framework, in the sense that you hardly ever screw things up.
O/RM Tools and SQL Code
An O/RM tools persists entities by generating and executing SQL commands. Is the SQL code generated by O/RMs reliable? In general, trusting an O/RM is not a bad idea, but constantly verifying the quality of the job they do is an even better idea. With any O/RM, a savvy developer will usually define the fetch plan and use the SQL profiler tool of choice to see what is coming out. Obviously, if the SQL code is patently bad, you intervene and in some way (changing the fetch plan or inserting stored procedures) you fix it.
In general, using stored procedures should be considered a last resort, but there might be cases in which they come to the rescue. An example is when quite complex queries can’t be expressed efficiently through classic cursor-based syntax and requires, instead, a SET-based approach to boost the performance. In this case, a stored procedure can be the best option.
Beyond Classic Databases
A plausible scenario that could lead you to unplugging your DAL is that you replace the current storage with something else, from yet another relational DBMS system. If you simply switch from SQL Server to, say, Oracle, most of the O/RM tools can absorb the change quite nicely. At worst, you pay some money to a third-party company to get a driver. A more delicate situation, though, is when you replace the storage layer of the application with something different, such as a cloud database or, say,