OR Mappers
Alex has added compile time query checking to Base4.NET! It's cool to see so many "O/R Mappers" adopting the strongly typed query expressions that I've blogged about a couple times. Now that I'm used to using strongly typed query API's I have a really hard time falling back to older API's that don't have such support.
In addition to the querying benefits, we've also made use of our code generated "query" classes for building bulk update statements, as well as "select into" statements for copying data between tables. I've been meaning to write a post on the topic, but since I haven't gotten around to it I'll give a little teaser here.
Bulk Updates:
Customer.Update().Set(Customer.Columns.Name == "Foo").Where(Customer.Columns.Age > 21).Execute();
And Bulk Copy/Moves
CustomerStaging.Select(CustomerStaging.Columns.FirstName, CustomerStaging.Columns.LastName)
.InsertInto(Customer.Columns.FirstName, Customer.Columns.LastName)
.Execute();
More to come later...
tags: base4, linq, queryapi, orm
Ayende continues with some more enhancements to the Query API for ActiveRecord. Adding support for automatic joins is something I've been thinking about trying to add for a while to our Query API but I've never gotten around to it. Hopefully, I'll be able to lift some ideas from Ayende if and when I do. 
Post.FindAll(Where.Post.Blog.Author == ayende &&
(Where.Post.Title == "Overloading" || Where.Post.Title == "Operator"));
One of the announcements that I’ve heard rumors about coming from TechEd is that Microsoft is going to be releasing two separate O/R Mappers along with LINQ. LINQ to SQL (aka: DLINQ) will be a lightweight O/R Mapper and LINQ for Entities will be more fully fledged O/R Mapper that will be integrated with the Entity Framework that is going to be part of ADO.NET 3 (or 4, 5, 6 depending on how many renames we end up with before it’s released).
One of the biggest problems in the O/R Mapper space is that there are way too many options to choose from. Seemingly everyone has written their own mapper, released it to the public and gone on to attempt to distinguish themselves from the thousands of other mappers available.
Apparently Microsoft has done the same thing with two separate divisions working on their own mapping technology. Rather then merging them into a single solution they’ve decided to make them both available and try and spin them as solving different problems. Yeah right.
Here’s a new idea. Create a single O/R Mapping solution that is integrated with LINQ. Make it extensible so that if it doesn’t fit someone’s specific need they have ways to make it fit. Focus on making it use the Provider model that you’ve made so famous so that we can swap out the behavior if need be. Don’t create two separate mapping solutions. We already have two many, we don’t need too many from you as well.
Paul has an excelent post that addresses much of the FUD that is being thrown around regarding O/R Mapping and stored procs.
I think the most interesting observation is that I know of many, myself included, that switched from these stored proc based systems to using O/RM without stored procs, but I know no one that switched the other way! Why is that? Could it possibly be that we know based on our experience, instead of idle academic guessing, that are apps are just as secure and performant as they were before O/RM, but far easier to build/maintain? And why is it that I still use stored procs when they make sense, especially for batch jobs and reports, as well as for the occasional situation where there are some serious performance optimizations to be made? Could it be that O/RM users are not anti- stored proc, but instead simply use the best tool for the job? And why do they assume we can't possibly use triggers for maintaining history -- I've recommended triggers. And if they really insist on stored procs, then why do they ignore O/RMs that do support those stored procs? It seems instead that some people must defend their decision to continue doing what they have always done, in spite of the many facts and success stories that speak differently, thus the continuing parade of FUD. Can you do things poorly with O/RM? Absolutely -- just like you can make poor choices without an O/RM!
I couldn’t have said things better myself. Thanks for the words of wisdom Paul!
Dinesh recent post, Lazy ORM users deserve it!, talks about how lazy loading can lead to performance and concurrency problems. I’m slowly coming to the conclusion that lazy loading is the root of all that people see as evil when it comes to O/R Mappers. By hiding the loading of objects from a data store behind a lazy loaded property we make the cost hidden to the end user who is utilizing our objects. It might be known that lazy loading leads to additional database hits, however, often times the scope and depth of database interaction that can result from lazy loading is not fully realized.
Many O/R Mappers support eager fetching of related objects to help reduce the amount of database interaction that is required for loading objects which are known to be required for a particular set of domain logic (See Ayende Combating the SELECT N + 1 Problem). In additional to supporting eager fetching some mappers have a caching layer that helps reduce the amount of database interaction necessary for loading the data you need. Often times it isn’t enough to prevent an innocent looking set of domain logic from causing an explosion of database hits.
So the question is...do we need to get better at using our O/R Mappers efficiently, or should our O/R Mappers make us be explicit about what we want them to retrieve and do away with lazy loading? What’s your take?
* By the way this problem isn’t unique to O/R Mappers
I'm a big fan of O/R Mappers. By leveraging an O/R Mapper within an application we're able to greatly reduce the amount of custom data access logic necessary for building our applications.
In a previous life I spent a pretty good amount of time developing my own O/R Mapper. At the time I didn't know what I was building, I just knew I wanted to reduce the amount of repetative data access code within my applications. I used my custom mapper in a lot of applications over the last couple of years. Having built the mapper myself I was never put in a spot where the mapper didn't do what I needed it to. It certainly had things it didn't handle but those things weren't important for the applications I was building. And if something that I thought was unimportant all the sudden became important I could just modify the framework to account for it.
As I was working on ActiveType I started to think about switching from my own custom O/R Mapper to one of the more popular mappers such as Wilson OR Mapper or NHibernate. As you can probably tell by the lack of posts on ActiveType I haven't spent as much time on it as I had hoped over the last couple months. This will likely change shortly as I start to wrap up a couple other projects that have been taking my time away. Anyway get to the point already.
One of the things that I think is missing within the "popular mappers" is basic support for joins. In a lot of application I work on I have lookup tables that are referenced by other tables in the database. The lookup tables often contain an ID along with a textual description. Being the good normalized developers that we are we only store the ID of the lookup value in our tables rather then the textual descriptions. When displaying objects within the application users often want to have the textual description for the lookup displayed rather then just the ID that is stored within the table. In applications that aren't using an O/R Mapper this is usually pretty straight forward. We just write some simple SQL to join the lookup table with the primary table that we're displaying to the user. We then execute the SQL and return a data reader with all data and fill our custom objects. As soon as we introduce an O/R Mapper we lose this basic functionality. Since most mappers map to a single table it becomes more difficult to bring back all the textual lookups that we want.
A good number of mappers support mapping to views and stored procedures but when developing an application that supports 3+ databases the maintenance of stored procedures and views for each different type of database (Oracle, SQL Server, Sybase) can quickly become a bit of a maintenance nightmare.
What I'd like to see is very basic support added to some of the popular .NET O/R Mappers for supporting this basic scenario. Allow mappings to be setup to foreign key tables so that textual lookups can be returned when retrieving lists of objects. Don't go nuts and think of every possible scenario, just provide the basics. Let me say what table to join on and whether I want an inner or outer join. Require me to explicitly tell the mapper that I want this full load to occur and when I don't tell the mapper I want the full load revert back to the basic load that just uses the information in the table which my object is mapped to.
Thoughts?
Paul Wilson posted a
great rant on why he believes in O/R Mapping. All I can say is I'm in 100% agreement with Paul. I know there are lots of people out there who don't believe in O/R Mapping, but I'm certainly not one of them. I think one of the problems people have with O/R Mappers is they try and make them work for every circumstance. Just like everything else that we have in our toolkit, O/R Mappers have a time and place. Their are times when forcing your application to use an O/R Mapper is NOT the way to go. When you look at the breakdown with what is involved with building an application that uses a O/R Mapper and one that does not it's clear what is the better choice. O/R Mappers are not a silver bullet, but they do kick ass
