Open discussion on ORMs

I’d like to open the floor for discussion on various ORMs, non-ORMs, and their relative advantages and disadvantages.

This idea stemmed from a recent project, which was done code-first as a proof of concept. This means that all it had was controllers, views, and viewmodels. No domain, service, or persistence layers existed yet.

It occurred to me, while looking at several of the controller actions, that I had all that I needed to save the data, without the need to first translate it into a domain representation.

In a typical DDD application, when adding an order to a customer, the customer is first fetched (if not saved in session), the order is created and added to the customer, then the customer is saved.

But why couldn’t I just take the incoming viewmodel and persist that using an SqlClient or Odbc call? The reality is, I could.

So what I was hoping to discuss here is why so many people nowadays are flocking to ORMs? Is it because they remove the requirement of understanding SQL? Or is it the opimization of said SQL (something any decent DBA should know anyway), or the oop nature of them?

Consider the two actions below. Both are object oriented, perform validation, and use repositories. Which one is better (in your opinion) and why?


public ActionResult Add(AddOrderViewModel model)

if (ModelState.IsValid)
Customer customer = Session[“customer”];
Order order = model.ToOrder();
return RedirectToAction(“Overview”);

return View(model);



public ActionResult Add(AddOrderViewModel model)

if (ModelState.IsValid)
return RedirectToAction(“Overview”);

return View(model);


In the same line of reasoning, selecting records could be done a lot easier by having the repository assemble and return view models rather than domain objects as well.

var models = orderRepository.GetForCustomer(customerId);

Of course this would make view models behave more like entities.

Feel free to drop your thoughts here. I’d really like to hear them.

Good Topic. Personally, I prefer the hybrid approach to .NET ORM vs. non-ORM. For example, I am going to use an N-Tier approach, including BOL and DAL layer. With Linq2SP as the ORM, while taking advantage of T-SQL for more complex apps.

Merry Christmas Everyone!

Merry Christmas to you too and everybody else as well.


Can you elaborate on why you made those choices? Do you prefer the speed of sprocs? Do you want to take advantage of sql server based rights?

It is funny that the first response heads down the road I’m looking at. I recently began to wonder about the impact of increased db hits brought about by some orm’s and related processing code. In addition, most of the apps I write have multiple front ends. Coming from an N-Tier based background myself, this just seems right. I also have been thinking about using the EF designer to simply import sprocs that generate the exact data my viewmodels need, and let the sprocs and triggers handle the logic.

There was a time when DBA’s ruled the db and Developers ruled the code. Somewhere along the way the lines blurred. Why do you think that is? Were DBA’s becoming more scarce? Or were coders becoming more impatient and wanting to do it themselves?

Either way, I came to a realisation during the time I spent recovering from my stroke. I will highlight it below.

The most important thing in any solution is the data. User interfaces may come and go, new platforms may be introduced, and even the db of choice may change. But the data will always live on, being imported and exported over and over.

So how did we reach the conclusion that the db is merely a storage medium? It’s where the data actually lives and it makes sense to keep integral logic code there as well to protect it from corruption does it not?

Sure, ORM’s make things easier for devs. But is easy always better? Why would I want to return a list of Customer entities, then loop through them to get thier Orders.Count? That’s multiple trips to the db. It makes more sense to simply select fields from customer and a count aggregate in an sproc. Once returned, only the loop to fill the viewmodel is needed.

Any counters or comments to this?

A must read:

Do you think it has to do with processing power and network connections being so much faster than before. Optimization becomes much less of an issue. Why not increase ease if the benefit of specialization is a mere tenth of a second per hour. That’s not an actually observed number, just a bit of hyperbole. :slight_smile: That really doesn’t answer the question at hand but it could be one of the underlying reasons, maybe.

You’re right, the data is the backbone of everything and no matter how you look at it, data is platform agnostic but that’s a good argument for separating it from logic because who is to say if or when you will want to change how the data is delivered.

I do not like Sprocs, as I think the logic should be in the app itself. As you are right, the data is most important. And so for eg. you need change to a database that does not support Sprocs? So not using sprocs, removes that dependency.

I like Linq2Sql, as the output T-SQL it generates is of very high quality. And most developers would be very hard pressed to write better T-SQL code.

Other benefits to L2S is the compile time errors compared to run-time errors. Proper intelli-sense and ease of use. Also, getting paged results in sql can be quite and ugly T-SQL query, which looks a lot more elegant in linq2sql.

As for performance, you will need to know your way around the ORM. And if you know what you are doing, there are small tweaks that help a lot with performance. I built one of the biggest social networks/websites in our country with linq2sql.

As for you problem: “Why would I want to return a list of Customer entities, then loop through them to get their Orders.Count? That’s multiple trips to the db”. That is easily overcome by creating a custom entity/object which implements the same customers interface and adds an ordersCount int or list depending on what you need, and load that in one method, then use your new object.

Data load options is also very helpful for those times where joins can fix multiple query problems. And compiled queries also do a lot for performance. And one of my favourite features is the .AsParallel() method. Do a google on that for further explanation. It makes quite a nice difference when used.

All the above is for L2S. So depending on the ORM of choice, you would need to learn there pitfalls and how to get around it. But I definitely think it makes a lot of difference and should always be used where possible. In my opnion, as it is the new age of querying data and T-SQL is out. lol.

PS. Merry christmas to all

I’ve used compiled queries where ever possible ever since you introduced me to them. I suggest the technique to anyone that wants to listen. Even strangers. Even non-programmers. :wink:

What’s this now? Are you going make me refactor everything I’ve ever written again? :rofl:

Thanks for the comments NS. I’m not sure I agree with you though. Forget about sprocs for a minute. Even without them, I still have to wonder what the advantage to using an ORM is when I can loop through a DataReader and fill a list with a given object myself? Using odbc client, I have a wide array of db’s I can use. In some cases, using a given ORM actually reduces this number. So the question I originally posted is still unanswered. What are the real, practical advantages, and disadvantages to using an ORM vs not using one?

The reason people pander to ORM’s? Simple, they are not good at writing T-SQL and refuse to learn/use it. I will stick to using LINQ2SP, and be happy using it. So, in the future, if an employer ask’s if I know SQL and Linq, I will say sure, T-SQL and LINQ2SQL. As I said, best of both worlds.

Their is no disadvantage to using ORM’s vs. No-ORM’s. Only, the way the code looks, or the API.

Serena, to anwser your earlier question. I choose my way because, when I was in College in Community College in 2002 I took an SQL Server 2000 class and absolutely loved it, the programming and administration of it was fascinating to me. So, I am biased, but I love the database aspect of ASP.NET, and hece why I choose T-SQL and ADO.NET and Linq2SQL. Databases are the foundation of the web and will always be so, so why not master one?

As the saying goes, if it works, why change it?

In the end, it all comes down to personal taste, and your programming style. I choose non-ORM (excluding ling2sp) and love it.

EDIT: Glad your feeling better! :wink:

So for some people, it’s like hiring a chauffeur instead of learning how to drive?

Anyway, I think there ARE real pros and cons. I was hoping somebody would point some out, but it looks like I’m going to to have to do it myself.

ORM Pro - If you are using DDD, and need a quick way to automatically create and maintain a db from your object model, then using an orm would be of a definate advantage.

ORM Con - If your record needs are high enough, and plan on using eager loading, the cost in overhead for object instantiation may be a detractor for you.

This is what I want to see, from as many different people and viewpoints as possible. The reasoning though is simple. I’ve never seen a clearly defined list, just discussed over and over. I want to build a list like this for others to use, but I didn’t want it to be personally biased. So can we build such a list?

Lol, glad it helped. Just remember, compiled queries use a little more CPU for the first compile, but from then on is much faster

I am not going to get into it here, as it will be hi-jacking the thread. Google it and do a bit of research of what it is and how it works. Not much re-factoring to do, just add it to queries and see the magic :wink:

Well, like you said, this is an open discussion. This is just merely my opinion and by no means to I expect you to agree with me.

I think the only thing pulling a person to any given technology is that persons likes and dislikes. And the decision in the end comes down to the person using said tech. I prefer an ORM as I think it is more elegant, but mainly because it saves a lot of time. As the queries and debugged on compile time, not runtime and the ORM handles all the nitty gritty of putting the data into an object for you and saves you from doing that yourself.

I think an application should work with any data layer, regardless of ORM or not, so if need be, you can write T-SQL. But in my opinion, T-SQL is old school and ORM is in. It is a developer aid, to help things a long quick.

It is like cars. All the news cars have driver aids to make driving more pleasurable and easier. But many say it takes away from the true driving experience. To I think and ORM is an aid. Makes coding more pleasurable and easier, but takes away true programming experience.

So its up to you to decide how you like to drive/code. lol

One particular area where .NET shines is with queryable models, thanks to LINQ. Both LINQ to SQL and LINQ to Entities offer this with natural in-code query syntax.

I used to be a huge fan of LINQ to SQL (still a fan) for <i>simple scenarios</i>. With that I mean for example a simple to moderately complex website with a backing database which is not accessed by other components or services.

In the beginning I was wary of LINQ to Entities because of its initial shortcomings and somewhat more cumbersome programming model. Most of those initial reservations have gone away with the latest release.

L2E still has a more explicit programming model than L2S. But there are serious shortcomings with L2S. Most importantly there is no easy way to design serializable entities and reattach entities. This is solved much better with L2E.

I don’t much care for sprocs. I find them hugely detrimental to an agile, queryable entity model. I cannot count the examples of poorly performing applications which owed their dismal performance to chatty db interfaces. The main problem with sprocs is that they

  • Tend to create an interface where often too much data is fetched and returned from the db. This is because developers will shy away from requesting/creating too many variants of the same fetch procedure. Instead of letting the database perform filtering they will then choose the “best fitting” existing SP and filter/project its output.
  • Create chatty interfaces because developers will gravitate towards composing from existing SPs instead of requesting a new special-purpose SP. Rather than create a new SP which could perform a number of (outer) joins, they will create a nested loops in the application.
  • Suck application functionality into the database tier. Attempts to address the above points will invariably lead to a mess of special-purpose and logic-heavy SPs being created.

IMO sprocs makes sense only when you are designing an application to be deployed in a very retro organization where you have an all-mighty DBA who is responsible for the database performance. Not even enterprises work that way anymore. Today the db performance responsibility falls upon the architect and (lead)developers. Applications have grown far too complex for a single DBA to offer real value.

NHibernate offers a compelling alternative. However, in my Java days I was burned more than once by some of the strange limitations, like e.g. the cache and the strange heuristic which will sometimes flush your object graph to the database (and thus start locking and inhibit scalability) if you performed a query which could return some of the changed entities. EF performs much more predictable. In fact predictability is the big differentiator, and here L2E (EF) wins hands down.

Recently I have been impressed with OData. Until recently I didn’t realize that it actually allows me to expose an entity model using http/atom and JSON. This seems to be a perfect match for both HTML5 and Silverlight apps. And it is still a queryable entity model where you can instruct OData to eagerly fetch related records in a single query. This will still (through L2E) translate to a very optimized SQL query using inner- and outer joins.

To that, I agree completely. As I stated in a post much earlier, I actually prefer the KISS methodology whereby I use an orm like EF, and simply use the given ObjectContext directly in the controllers. There are several reasons for this. The primary reason is that doing so eliminates cross-session errors. To do so using NHibernate would require writing a per-request caching mechanism. The second reason is that it eliminates the need for a unit-of-work pattern. As the request ends, and the controller is destroyed, so is the context. The only drawback to this is that it forces things into a single layer rather than separate libraries per layer. Of course, I like this approach while others do not. It truly is up to the individual, their environment, and the project at hand.

One facet we’ve yet to hear from though is the code-generation camp. I know they are out there and would like to hear from them.

I use CodeSmith and PLINQO. It generates objects and interfaces as well as the DBML file which supporst multiple databases. It is still linq2sql though. But I think it is great, as all the objects are seperated into files, instead of one big file like L2S used to do it in the early days. It also adds a lot of extra built in features. Such as .Paginate() for example.

Before I discovered EF, I actually wrote a small fluent library that worked a lot like fluentnhibernate. You could author simple entity classes and mapping classes. The base repository created a context in it’s ctor using reflection to grab all the maps and entity classes and hook the up. No xml was needed at all. The only thing I left out was the use of conventions. I tried sharing it here once but sombody flagged the post as self-advertising, when all I wanted to do was share some free code. I’d considered trying the same thing with EF, but they are already addressing these things with the CTP efforts.

Speaking of which, I played around with EF CTP 5 recently and am pleased with the direction they are taking on it. There are some things though which would be of great benefit that they haven’t addressed. At the very least, what little documentation there is on it doesn’t seem to reflect it. For example, being able to map a collection to a private list, allowing us to expose the list as an enumerable instead (preventing direct adding).

I agree that having repositories return things as a queryable to be of great benefit. Offering a “starting point” like that makes fine tuning the selection criteria a breeze in your services without having to add a ton of specialized calls. It also makes using extension method based filters a lot easier as well. I am told that the Linq library for NHibernate begins to address some of these issues, but there is something about adding a library to an already added library, just to come full circle on certain things, to be a bit of a put-off.

Just to throw this one out here – is the ORM getting irrelevant now that document databases are coming into their own given that ORMs main feature is making your relational data store largely feel like a document DB?

ORM’s get OWNED! I like XML and the concept of Document Databases. Thanks for pointing that out, wwb, I have never heard of them until now. Hit the wiki page when I read it.

I like the idea of document databases. I even played with the concept early on using xml. I think the two are separate things though. An ORM is a programmatic access strategy while the document db is a storage medium. You’d still need a way to access one via code, and this would result in some sort of orm. The only problem I could foresee is in having records of different types and lengths within a given set. It would make it very hard, if not impossible, to code since generic collections need a specific type. Basing them on object is not much different than the old ado model using recordsets.

If the doc-db takes off, I very well could see an end to the ORM movement.

What exactly is a “programatic access strategy?”

Anyhow, I’d really contend that, when done and used right, you never see the ORM outside of the data implementations – it does not bleed up into the service layer or the models. I’m just saving a Customer object, weather that gets serialized into SQL UPDATE statements for a RDBMS or serialized into JSON for MongoDb isn’t really material to the app.

As for the typing question, the documentdb guys seem to be handling that one fine. Here’s the rub – typically you know what the hell you are asking for and you definitely don’t load every document into memory to deal with them as an IList<T>.

My preferred ORM is NHibernate, as many of you will know :slight_smile: object database are nice, and can remove a lot of cruft, but don’t expect them to become mainstream in .Net for a good number of years, look at how long it look MS to sort out an ORM, and it’s still miles behind NHibernate and FluentNHibernate

I personally hate returning IQueryable from a repository! It lets connected queries bleed out, your breaking seams and can hinder testing. Unfortunately, I lost the argument in work and our work framework lets you do it but it’s still BAD :frowning:

but there is something about adding a library to an already added library, just to come full circle on certain things, to be a bit of a put-off

I hate it when you need to add stuff for extra functionality when you need it. Like when I want to play 2 player on the PS3, I need an extra controller. So instead I play on my own. Or when I might want to use EF, I need another assembly, remember LINQ is another assembly reference, it just happens to be in the GAC.