Is everyone here jumping on the ORM band wagon?

Question

Microsoft Linq to SQL, Entity Framework (EF), and nHibernate, etc are all proposing ORMS as the next generation of Data Mapping technologies, and are claiming to be lightweight, fast and easy. Like for example this article that just got published in VS magazine:

http://visualstudiomagazine.com/features/article.aspx?editorialsid=2583

Who all are excited about implementing these technologies in their projects? Where is the innovation in these technologies that makes them so great over their predecessors?

Venavenable

Astoria is not an ORM but a data access mechanism using REST/HTTP. – Vehicle

You are right, I will remove that. – Venavenable

I don't use that stuff, I made my own: valueinjecter.codeplex.com/… – Rudderhead

Answer 1

I have written data access layers, persistence components, and even my own ORMs in hundreds of applications over the years (one of my "hobbies"); I have even implemented my own business transaction manager (discussed elsewhere on SO).

ORM tools have been around for a long time on other platforms, such as Java, Python, etc. It appears that there is a new fad now that Microsoft-centric teams have discovered them. Overall, I think that is a good thing--a necessary step in the journey to explore and comprehend the concepts of architecture and design that seems to have been introduced along with the arrival of .NET.

Bottom line: I would always prefer to do my own data access rather than fight some tool that is trying to "help" me. It is never acceptable to give up my control over my destiny, and data access is a critical part of my application's destiny. Some simple principles make data access very manageable.

Use the basic concepts of modularity, abstraction, and encapsulation--so wrap your platform's basic data access API (e.g., ADO.NET) with your own layer that raises the abstraction level closer to your problem space. DO NOT code all your data access DIRECTLY against that API (also discussed elsewhere on SO).

Severely apply the DRY (Don't Repeat Yourself) principle = refactor the daylights out of your data access code. Use code generation when appropriate as a means of refactoring, but seek to eliminate the need for code generation whenever you can. Generally, code generation reveals that something is missing from your environment--language deficiency, designed-in tool limitation, etc.

Meanwhile, learn to use the available API well, particularly regarding performance and robustness, then incorporate those lessons into your own abstracted data access layer. For example, learn to make proper use of parameters in your SQL rather than embedding literal values into SQL strings.

Finally, keep in mind that any application/system that becomes successful will grow to encounter performance problems. Fixing performance problems relies more on designing them out rather than just "tweaking" something in the implementation. That design work will affect the database and the application, which must change in sync. Therefore, seek to be able to make such changes easily (agile) rather than attempt to avoid ever changing the application itself. In part, that eventually means being able to deploy changes without downtime. It is not hard to do, if you don't "design" away from it.

Tridactyl

"Severely apply the DRY (Don't Repeat Yourself) principle" also implies not rewriting the same plumbing in every application. Your attachment to the data layer implementation will affect every decision and ultimately stifle you with boring technical details. – Wolf

I agree with you 100%. Writing your own data access layer using pure ADO.NET with full control over the SQL and the Objects is the way to go. Here is a tool that might interest you.. forums.orasissoftware.com/… – Venavenable

That looks like another ORM tool Ahmad. It usually boils down to DataReaders and so on under the covers... ORM is just about saving you from writing the same code by hand over and over. Then again, we wrote our own ORM tool, so I do admit that I like to control the leaks in my abstractions. – Merrick

It is not another ORM tool! It is a code generator that builds your DAL for you according to your business requirements against your own business objects. You get to keep your generated code, and the code is free of any dependencies on any proprietary libraries. No ORM can do that. – Venavenable

@Ahmad: From your URL: "Orasis Mapping Studio is an Object to Relational Mapping Tool and Code Generator...", so they say it is an ORM tool. It even has its own IDE?!? Yikes. – Tridactyl

@Ahmad: What do you think an ORM tool is? – Humfried

All ORMs are frameworks that you call into at runtime and some have GUI designers to ease configuration. Orasis is an IDE and a Code Generator that allows you to build your mapping layer between custom .Net Objects and existing Relational Database through SQL queries and generates pure ADO.net code. – Venavenable

What's the difference between Orasis and a CodeSmith template, like, say .NetTiers? (cyruscrypt.blogspot.com/2005/07/nettier-beta-1-is-released.html) – Plainsong

If you download and test Orasis you will see the difference. Orasis does not build code based on templates. It builds code according to your Business Objects and Queries and mappings that you develop in the IDE. Its much more sophisticated and even has a unit tester built in. – Venavenable

Answer 2

I'm a huge ORM advocate. Code generation with ORM saves my shop about 20-30% on most of our projects.

And we do contract development, so this is a big win.

Chris Lively made an interesting point about having to do a redeploy any time a query gets touched. This may be a problem for some people, but it does not touch us at all. We have rules against making production database changes directly.

Also, you can still rely on traditional sprocs and even views when appropriate... We are not dogmatically 100% ORM, that's for sure.

Merrick

What is the problem with having the Queries in the Application Code rather than in Stored Procs? It's better to have them in the Application Code and validated in compile time.....otherwise you will be up for surprises in run time. That is a big advantage of ORMs specially Linq To SQL. – Venavenable

There is a whole huge debate about this. Or there was. Maybe we're passed it. Basically, you don't really want concatenated SQL in your code, it's considered evil, but that's actually what ORMs do! They either build concatenated parameterized queries, or they tie into sprocs in the back end. – Merrick

Now, I am of the contraversial opinion that maybe it's okay to just use sprocs for the tasks that ORMs are bad at (anything that would cause a lot of round-trips, expensive batch transactions, etc) but many DBAs disagree. Some folks do need sproc-level security, etc. I acknowledge that. – Merrick

If you do a lot of contract development and want to save money, and control code quality and measure DAL progress you may want to check out this article. forums.orasissoftware.com/… – Venavenable

"measure DAL progress". What do you mean by this? – Plainsong

I think he meant to say: 'measuring ROI'. That's what the post he linked to is about. – Merrick

Just because you use an ORM and have got it setup for everything to work in your application, it does not mean you are done with DAL development. You keep building projections and queries as requirments grow. There is no way to measure what is done and what is left in your DALand the cost of it all. – Venavenable

I use ORM with code generation, so when my database changes my objects change as well. Most of my queries are also written (very quickly) in code using our ORM suite.There is indeed a way to measure the savings, because as contractors we estimate everything we do, and I can look at it both ways. :) – Merrick

Answer 3

I have been on the ORM train for the longest time, since the free version of LLBLGen to the latest and greatest commercial product LLBLGen Pro. I think ORMs fit in very well for a lot of the common data input output systems.

That isn't to say they solve all problems however. It is a tool which can be used where it makes sense to be used. If your database schema is relativly close to how your business objects need to be, ORMs are the best.

Pechora

Out of curiosity - how do you unit test your LLBLGen code? We've been using it for years, but really haven't put in the level of unit testing that we should. Thanks! – Scrutable

+1. "If your database schema is relativly close to how your business objects need to be, ORMs are the best." – Hindemith

Answer 4

It's not a bandwagon to jump on, is a reaction to a real problem! Object Relational Mapping (ORM) has been around for a long time and it solves a real problem.

Original Object Oriented(OO) languages were all about simulating real world problems using a computer language. It could be argued that if you are really using an OO language to build systems you will be simulating the real world problem domain using a Domain Driven Design (DDD). This logically takes you to a separation of concerns model in order to keep your DDD clean and clear from all the clutter of data persistence and application controls.

When you build systems following a DDD pattern and use a Relational database for persistence then you really need a good ORM or you will be spending too much time building and maintaining database crud (pun intended).

ORM is an old problem and was solved years ago by products like Object Lens and Top Link. Object Lens was a Smalltalk ORM built by ParkPlace in the 90's. Top Link was built by Object People for Smalltalk, then converted for Java, and is currently used by Oracle. Top Link has also been around since the 90's. DDD advocates are now beginning to clearly articulate the case for DDD and gaining mind share. Therefore ORM, by necessity, is becoming mainstream and Microsoft is just reacting as usual.

Marciemarcile

Answer 5

No. Not everyone is.

Here's the number one big ass elephant in the room with most of the ORM tools (especially LINQ to SQL:

You are guaranteed that ANY data related change will require a full redeployment of your application.

For example, my day job can currently fix most query problems by modifying an existing stored procedure and redeploying just that one piece. With Linq, et al, your data queries are moved into your actual code. Guess what that means?

Humfried

That's not entirely correct. Most ORM's (including LINQ) allow for querying off of stored procedures, instead of directly against your database tables. – Spiderwort

Linq to Sql can use procs if you'd like: weblogs.asp.net/scottgu/archive/2007/08/16/… – Sandbox

Yeah, you can use sprocs and even traditional views in almost every case. You're right though, ORM is probably not a silver bullet, although it's at least an especially shiny bronze bullet in my opinion. :) – Merrick

Stored procs are not a portable solution. They're no more a silver bullet than ORM either. You're just polluting your DB with code instead of your code with queries. – Unmeaning

Note, in many cases deploying database schema changes is a much bigger deal than deploying an app code change. – Cantlon

I was going to reply some more, but I don't want to create a religious debate. Let's just say I like ORM but it isn't a perfect fit for every situation. – Merrick

Chris you are right. ORM and even Linq to SQL means tight coupling of database schema with application logic. I don't like that! – Venavenable

This has come up in conversation at my place of employment. What about using updatable views with ORMs. This should allow some change flexibility in the db. – Converted

I'll reinforce this with the comment that, if you know how to make the database dance (SQL, query optimization, etc.) ORM will get in the way, and sometimes not do as good a job. If you can't make the database walk fast, ORM is more likely to be for you. – Islamism

Chris - I'd disagree. If your ORM is tied only to your business objects, a change to the database should only require the redeployment/update of the database itself and the code containing your business objects - which should be a separate assembly/dynamic library in most cases. – Rik

@Harper: "and the code containing your business objects" kind of proves my point that a SQL update requires a code deploy – Humfried

le dorfier - I disagree. The creators of ORMs actually say, it is a requirement to know SQL and Relational Databases very well before you implememt an ORM. – Venavenable

@Draemon: "polluting your DB with code".. I dont think anyone would call keeping SQL statements with the database server polluting it. – Humfried

I'd just like to say that I think the idea that you can magically change the database without affecting the app is in most real-world cases nonsense. Applications are so strongly data-driven that even a minor change to the database is in most cases going to require a code change, in my experience. – Lectern

About Linq to Sql with procs: Is anyone actually doing that? Anyone? That would be no. Because it just doesn't work that well and you lose just about every benefit of using Linq. – Humfried

@DotNetDaddy: Let's see, you have a simple select query which is causing a deadlock on a large highly used table. How do you fix it? With S'procs you simply fix the query. With an ORM, you fix the query and redeploy the entire application. – Humfried

@Chris and @DotnetDaddy: If the database change has any effect on your Application Data Layer then your code should fail to compile rather than at run time. There are multiple ways to achieve this. – Venavenable

You can compromise, for example with NHibernate you could keep externally-stored HQL queries which you could easily redeploy without changing any compiled code. – Plainsong

@Ahmad: When you have several projects that depend on the same database it is critical that you get the sql code OUT of the applications. Imagine needing to make slight changes to the database. With your method you MUST redeploy ALL of the apps. With mine, you MAY not have to redeploy anything. – Humfried

Answer 6

ORM is a good match for people who get along ok with software that writes software for them; but if you are obsessive about controlling what's happening and why, ORM can be suboptimal particularly with database optimization. Any abstraction layer has costs and benefits; ORM has both, but the balance isn't right yet IMHO. And ORM, in its current form, ironically adds an abstraction layer that still puts classes and unabstacted database schemas to intimately together.

My experience is that it can help you get a proof-of-concept version together quickly, but can introduce refactoring requirements you may not be familiar with (at least yet.)

Add to that, that the tool is evolving, and best-practices and patterns are not well established, nor a concensus of the kind that lets other programmers (or future programmers) feel comfortable with your code. So I expect to see higher-than-usual refactoring requirements for a while.

I'll reserve judgment (optimistically) about where it will settle in terms of being mainstream. I wouldn't bet a current project on it at this point. My patterns for dealing with the impedance mismatch are satisfactory for my purposes.

Islamism

Here is a good discussion on how to choose an Data Access Implementation: forums.orasissoftware.com/… – Venavenable

Here is another, IMO better: ayende.com/Blog/archive/2006/05/12/… Does Orasis comply with all of those features? – Plainsong

Orasis is not an ORM, its a DAL Code Generator. A code generator that generates executable code to populate your Objects with data from queries. So that list does not apply. All those features are not necessary to be implemented by all DALs, some of those are business level functions. – Venavenable

Answer 7

You have to fight with the ORM system once you want to do anything beyond the simplest select, update or delete. And your performance goes into the toilet once you begin doing real stuff.

So no.

Chromite

Correct. Shouldn't a DAL layer all about performance? I have seen scenarios where it takes ORM like nHibernate minutes to pull up a data intensive screen. When we saw through the SQL log it had executed hundreds of queries! – Venavenable

"I have seen scenarios where it takes ORM like nHibernate minutes to pull up a data intensive screen" - I have seen the same situation with hand rolled sql. The tools arent to blame its the developer. – Violative

Yes, but in SQL you can improve the performance by tuning your SQL and thats it. In an ORM you have no control over the SQL that gets generated, so you are bound. The only option you have is to change Object relationships and create "lightweight" objects which corrupts your Domain model. – Venavenable

@Ahmad: if you're using NHibernate and are running into "hundreds of queries", you're probably hitting "select N+1", there are lots of articles on the web about this. Any tool can be used the wrong way. – Plainsong

Answer 8

I look forward to the day my team starts looking into ORM solutions. Until that day, we are a pure DataSet/Stored Procedure shop and let me tell you that it isn't all biscuits and gravy being "pure".

Firstly, the new Linq to SQL is performing close to that of stored procs and data readers. We use datasets everywhere, so performance would improve. So far so good for ORM.

Secondly, stored procs have the added benefit of being released separate of code, but they also have the detriment of being released separate of code. Pretend for a second that you have a database with more than 5 systems connecting to it and more than 10 people working on it. Now think about managing all those stored procedure changes and dependencies, especially when there is a common code base. It is a nightmare...

Third, it is difficult to debug stored procs. They often result in erroneous behavior for any number of reasons. That is not to say the same could no result from the dynamic sql being generated by the ORM, but one less problem is one less problem. No permissions issues (though mostly resolved in SQL 2005), no more multi step development. Writing the query, testing the query, deploying the query, tying it into the code, testing the code, deploying the code. The query is part of the code and I see this as a good thing.

Fourth, you can still used stored procedures. Running some reports that take a long time? Stored procs are a better solution. Why? Query execution plans can be optimized by the database engine. I won't pretend to understand all the workings of the database, but I do know there are some limitations to optimizing dynamic sql currently and that is a trade off we make when going with an ORM. However, stored procs are not ruled out when using an ORM solution.

Really the biggest reason I see people avoiding an ORM is that they simply don't have experience with one. There will be an obvious learning curve and ignorance stage. However, if it is going to improve development performance and hardly hinder (or in my case improve) performance. It is a trade off worth making.

Waterscape

Here is my two cents after experiencing working on a large scale ORM project. theahmadblog.blogspot.com – Venavenable

What ORM were you using Ahmad? – Waterscape

Answer 9

I'm a big fan as well, using EF and Linq-to-SQL. My reasons are:

Since LINQ is compiled and type safe, you don't get the problems of typos in "string-based" SQL. I don't know how many hours I've spent of my life tracking down an error in an SP or other SQL where a "tick" or some other keyword was in the wrong place.

The above and other factors make development faster.

Though there certainly is overhead compared to "closer to the metal" methods of database querying, none of us would be using .NET at all, or even C++ if performance was our #1 concern. For most apps, I've been able to get excellent performance from Linq-To-SQL, even without using the stored proc approach (the client-based compiled queries is my usual approach).

Certainly for some applications you still want to do things the old fashioned way though.

Lectern

what good is type-safety if you can't move the data around (i.e. the anonymous types that give the strong-typing can't be moved out of the local function) – Wollongong

The anonymous types aren't what gives the strong typing - the strong typing comes from the classes created for database entities. Those can be returned and referenced outside of the actual database call, though I usually find it to be best practice to use a POCO instead at that point, just for better separation. – Lectern

+1 However, a good DB tool will be able to analyze SPs (new or existing) and tell you if they contain any syntax errors or potential mismatches. I am sure there are similar tools for other "SQL wrapper" approaches. LINQ/EF just force this due to how they operator (but a number of dynamic ORMs -- e.g. Rails -- do not offer this "static" verification even if they generated valid SQL syntax). – Hindemith

Answer 10

I guess what I meant was, what is the innovation that ORMs provide over building your DAL using traditional ADO.NET, SQL and mapping them to objects in code?

Here are the three major peices of my DAL and I am comparing with ORMs to see the benefits:

1. You still have to have a query in an ORM = SQL (SQL is more powerful by far)

2. Mapping code moves to configuration but still not eliminated, just shifts from one paradigm to another

3. Objects have to be defined and managed tightly relatedto your Data Schema unlike in the traditional approach which I can keep them decoupled.

Am I missing something?

Venavenable

Ahmad - that's basically what ORM is. They are just intended to be very flexible objects to get data in and out of the database... More flexible than CRUD. And with code generation, you don't have to do it by hand. You can build 200 objects instantly. Look into CodeSmith for instance. – Merrick

And there are collections, etc so you can do databinding. It's just an easier way to work with data, and it works in a lot of scenarios.. – Merrick

Oh, and it's easier to query as well. You don't have to write a whole sproc any time you need to get some new data. That's a huge time saver. – Merrick

@Ahmad: with NHIbernate's HQL for example, most of the queries are actually smaller than their equivalent SQL. See hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/… – Plainsong

The HQL maybe smaller, but the SQL that gets generated underneath maybe much bigger and slower than if you had written the SQL. So what do you buy besided readability. At the end of the day the query should bring correct results and fast. Both of which an HQL query cannot gaurantee. – Venavenable

I've been using NHibernate since v0.8.3 (3 years). It generates correct and performant SQL 99% of the time. I just replace the other 1% with hand-crafted SQL and that's it. – Plainsong

Some of the things that ORM makes possible: ayende.com/Blog/archive/2008/01/27/ORM--2.aspx It would take you a lot of effort to do such things without an ORM... – Plainsong

@mausch: Impossible to evaluate that statement--there is no content beyond a "wish list" at the URL. – Tridactyl

@Rob Williams - no, at the URL , ayende has a list a topics that he gave a talk about . It's not SF. – Bi

yes, you can build "200 objects instantly" -- now you get to manage 200 more objects that also don't necessarily do anything you need! – Wollongong

Absolutely NagaMensch. We had hundreds of generated objects in our system, but none did what was needed! Rather then figuring out which ones to use and gather, it was easier just to create one for our needs and move on. The generated objects became a prblem instead of a solution!! – Venavenable

Answer 11

I have been following Fluent-NHibernate very closely as it has some of the most potential I've ever seen in a project.

Basketball

Answer 12

I am a big ORM guy, get the logic out of the database, use the database only for speed.

I love the speed you can develop an application. The biggest advantage, depending on the ORM, is you can change the back end without having to translate your business logic.

I switched to LINQ and have never looked back. DBLinq is awesome for doing other database than MSSQL. I have used it with MY SQL and it is GREAT.

Squamulose

ORMs do not speed up things in my experience. They actually inhibit you from using tuning / optimizing Queries for performance. They may improve development speed, but in my experience they always perform slower or equal to traditional approaches. – Venavenable

Most ORMs support the use of stored procedures. On performance critical parts use the correct tool. – Arsyvarsy

Answer 13

not yet, still skeptical; like most microsoft products, i wait for SP2 or a year and a half before trusting them in a producton environment

and note that pretty much every new thing introduced by anyone, not just microsoft, is hailed as "lightweight, fast and easy" - take it with a block of salt. They do not advertise the problems/issues quite as loudly as the benefits/features! That's why we wait for early adopters to discover them.

This is not to disparage ORM or LINQ or anything like that; I'm reserving judgement until

• I have time to evaluate them,
• some need arises that only they can satisfy,
• the technology appears stable and well-supported enough to risk in one of my clients' production environments, and/or
• a client requests it

Please note: I've done ORM before, manually, and it worked just fine, so I have high hopes for the newer ORM systems.

Burgett

Yep, what we really do at my work is we take our own internal ORM tool (which actually is pretty decent) and then use CodeSmith to generate BusinessObjects. It works great. I am evaluating alternatives like Linq2Sql, Subsonic, etc. But it's a big call. – Merrick

Linq2sql is better than any traditional ORM ... actually any ORM that has Linq support is better than traditional ones. – Convery

If codesmith generates code based on your tables, aren't you still tightly coupled to your data schema? I would prefer a solution that decouples my objects from my database schema for mor flexibility in the architecture. – Venavenable

Ahmad - in codesmith, you can write templates that will generate whatever you want based on whatever you want - it's .Net. In our ORM tool, it ultimately works off of an xml document which is initially based on the database. You can do all sorts of things in there. Next gen tools do even more. – Merrick

@steven: ORMs are not new in .net. I've been using NHibernate for three years, it's a very stable product by now. – Plainsong

@mausch: "new" is relative. ORMs have been around for 20+ years; the hype in the .NET world is relatively new – Burgett

Answer 14

If codesmith generates code based on your tables, aren't you still tightly coupled to your data schema? I would prefer a solution that decouples my objects from my database schema for mor flexibility in the architecture

That's from one of your comments - It's true, CodeSmith tightly couples you to your tables.

NHibernate on the other hand has allot of features that can help with that : you have Components so that in code you can have : Person with a property Address ,where Address is a separate class .

You also inheritance mapping. So it does a fair job of decoupling your schema from your domain.

Bi

I guess I disagree about CodeSmith tightly coupling you to tables. You can write templates to do generate whatever you want. :) My ORM tool lets you generate all sorts of things. – Merrick

Answer 15

We still use a hand rolled, repetitive cut'n'paste DAL where I work. It's extremely painful, complex, and error prone, but it's something all developers can understand. Although it is working for us at the moment, I don't recommend it as it begins to break down quickly on large projects. If someone doesn't want to go to full blown ORM, I'd at least advocate some sort of DAL generation.

Converted

You should use a DAL generator, that can generate code to map SQL to Objects. Ibatis is one and Orasis Mapping Studio is another one. You can Google them. – Venavenable

Answer 16

I'm actually working on writing an ORM tool in .NET as a side project to keep me entertained.

SQL is dead to me. I hate writing it, especially having it in my code anywhere. Manually writing select/insert/update/delete queries for each object is a waste of time IMO. And don't even get me started on handling NULLs ("where col_1 = ?" vs "where col_1 is null") when dynamically generating queries. The ORM tools can handle that for me.

Also, having 1 place that SQL may be dynamically generated would hopefully go a long was to eliminating SQL injection attacks.

On the other hand, I've used Hibernate, and I absolutely hate it. In a real-word project, we ran into limitations, unimplemented bits, and bugs every few weeks.

Keeping the query logic DB side (usually as a view or stored procedure) also has the benefit of being available for DBAs to tune. That was a constant battle at my last job, using Hibernate. DBAs: "give us all the possible queries so we can tune" Devs: "uh, I don't know because Hibernate will generate them on the fly. I can give you some HQL and an XML mapping though!" DBAs: "Don't make me punch you in the face!"

Aleris

So if you're uncomfortable with handling NULLs and manually writing simple SQL, then it's either ORM or find someone who isn't? – Islamism

"give us ALL the possible queries so we can tune": that is not the way to optimize. First find the pages/use-cases that perform poorly and analyze those. If it can't be optimized at the code/ORM level (i.e. "select N+1") then replace it with the DBA's hand-tuned SQL, check for missing indexes, etc. – Plainsong

Answer 17

I dislike the code generation used in most ORMs. In fact, code generation in general I find to be a weak tool that is usually indicative of using the wrong language in the first place.

In particular with .Net reflection, I don't see any need for code gen for ORM purposes.

Channelize

By code generation do you mean the SQL that is emitted by things like LINQ to SQL? – Whelk

Answer 18

Here's one strong opinion.

Hairbreadth

Another one about this that I love from Ayende: ayende.com/Blog/archive/2006/05/12/… – Plainsong

Answer 19

No, I dumped ORMs and switched to an smalltalk and an OODB: Gemstone. Better code, less code, faster development.

Drool