O/R Mappers: Simple Database Features ?
I'm going to get grilled for saying this, but I don't see why most O/R mappers leave out some very simple database things. Note that I'm not saying I have everything down, nor do I really have any of the more complex things that some will need. What I'm saying is that there are some things that everybody wants and are very simple that are often left out of O/R mappers.
First and foremost is that every O/R mapper should work in some minimalistic sense with every ANSI SQL compliant database! Everybody that wants to use an O/R mapper expects to at least be able to do the very basic CRUD operations that are mostly just simple SQL. Sure, there are a few gotchas, like how to handle database generated primary keys with inserts, and the fact that some data types like DateTime vary in their handling. But its easy to handle these basic issues with the big players (MS Sql, Access, and Oracle), and then just expose a generic OleDb and Odbc provider and warn the user they are not guaranteed. .NET even makes such generic coding to all database providers very easy if you always code to the System.Data interfaces instead of using specific providers like SqlClient. Yes, I know that this won't work for creating the most performant database code, nor for more advanced situations, but that shouldn't prevent the basics from being handled genericly. In particular, I think its very very bad that Microsoft is going to release ObjectSpaces only working with MS Sql, at least initially. Maybe they will add others later (but maybe not), but you can't really design a flexible system if you don't test it in more than one case from the beginning.
Another relatively simple case that many people want which fear performance (warranted or not), and some people also really need, are stored procedures. Yes, the bread and butter of an O/R mapper is and should be dynamic SQL created at runtime, but does that mean you can't give the customer another often requested option when its easy to implement. Like it or not, there are people that won't ever believe dynamic SQL is often just as performant, and there are some cases at least where they are right. There are also other issues related, especially when data is denormalized for performance reasons, that stored procedures simplify, although certainly there are other alternatives, like triggers. Enabling stored procedures will also allow you to provide a work-around for those silly users that want to use one of those other databases and still have database generated primary keys. There's bound to be other reasons for allowing stored procedures too -- my point is that there really is no reason to not make them an option since they are simple to include.
Finally, I have one other "simple" scenario that I included in my WilsonORMapper that no one has asked for -- but I bet everyone will want now that I've included it! Basically, there's no reason that every O/R mapper can't provide a way to ask for a specific page of data, where you get to specify the size of each page. That's right -- my ORMapper will query the database for only a specific page of data, using standard ANSI SQL, and not force you to have to jump through hoops or return more records than you really want. You can actually see me using this feature on my site where I have added a "grid" (I use repeaters) with paging of 20 records, also with sorting ascending and descending enabled. Once again there's no reason why the basic SQL can't be optimized better for the major players while still providing the generic functionality for all databases. I'm sure I'm going to be ridiculed as naive, and I am when it comes to all the advanced features that others are providing that I will never even attempt, but that's no reason to not deliver the basics.
With that I'm done with my "lessons learned" topics. Those are the basic features I expect in all O/R mappers. What do you think are the simple basics that should be required?