ORM - specific to SQL Server 2008+ for .NET

Question

Is there an ORM tied to MS SQL 2008+, generating .NET code, which takes advantage of MS SQL specific features?

I'm specifically interested in 2 features:

(1) fetching a group of records by their key, which will not be mapped to "select in" query.
-this can be achieved by using either OPENXML in 2005+ or by using Table-Valued Parameters in 2008+

(2) inserting more than one record with a single request to the DB (this is similar to, yet not the same as Bulk-Insert). For example:

Customer[] customers = CreateSomeCostomers();  
dataManager.Save(customers);

So the SQL related code is prepared on the client, and then sent entirely to the SQL server.

BTW, stored procedures are out of the question.

I'd be happy if you stackoverflowers have any advice for me.

Thanks!

Update:

I'm looking for a complete solution, not an extensible framework/product that I could customize to my needs. So customizing NHibernate/linq2sql/etc. by writing my own code implementation does not suit my needs.

---

A few words about the accepted answer.

It seems that currently all ORM frameworks try to cover more than a single database/technology. So I guess there's no ready solution for my needs, at least nothing out of the box.

I understand that @Ryk, pointing to Fluent NHibrenate, believes that such solution actually exists, and it's a matter of configuration. I took a look into the source code of both NHibernate and Fluent NHibernate, and I found nothing that supports the features I'm looking for. For instance, I couldn't find the keyword OPENXML or sp_xml_preparedocument in both source codes. So maybe I'm missing something, but I don't think so. Since the bounty is time-limited, I can't take a deeper investigation in FN or NH. I still think these features are not supported there.

So, the accepted answer is, per my best understanding, that there's no such thing. The most generic answer claiming that, is @Cade Roux's answer.

I thank you all for your participation. Cheers!

Answer 1

If you want to use SQL Server features, then a black box ORM isn't going to cater to that.

If you want to get the most out of your chosen database (any database), you are going to be more tightly coupled and so you are better off with code-generation for (generating both stored procs and) your mapping layer instead of a drop-in ORM.

Answer 2

Unfortunately, no. Microsoft decided to go down the multi-platform lane with Entity Framework. Although it would have been nice if they built something around SQL Server and left other DBs for the respective db vendors or third party OR mappers.

Answer 3

Nhibernate is the only your option except writing your own data acccess layer, you can easily extend Nhibernate to use new|custom|specific SQL syntax.

Answer 4

Yes, I believe that Fluent NHibernate will do what you want. Takes a bit setting up, not too hard, but once it is working, it is a treat.

On a performance note, it is very good, but if you are planning to do a transactional system with it that does thousands per minute, I would probably go down the old stored proc way for tuning.

Basic idea is that you create entity classes and mapper classes that will map your database objects to the c# objects. From there you simply populate and object or list of objects, and do Object.Update() or Object.Delete() or Object.Get() etc. Already built in, you dont have to go and wire it up. You can always override it if you want. It is very flexible.

Answer 5

I realize that this answer comes a bit late (may be helpful to others seeing it though). For the reasons given here plus also for getting proper DB source code management in the process we have our own mapper, which is available as open-source project: bsn ModuleStore

This toolset does not go all the way that ORMs such as nHibernate or Entity Framework do and it does require some more coding. However, it is following the "good old" approach with SPs for all data access (no debate please - both dynamic and static DB code have their pros and cons). The consequence is that queries have to be hand-coded in SQL, giving you full control over the code executed on the DB server.

As for some benefits, you get full bidirectional mapping of entities (yes, using TVPs, with an efficient custom data reader that "streams" the entities instead of building an in-memory DataTable as most examples with TVP do). It also supports SPs returning multiple result sets and last but not least it provides completely integrated and quite extensible support for database modularization, versioning (at the source code level for proper integration with your SCM) and integrity checking (comparing the objects present in the DB against the source files).