ORM Frustration in .NET

I'm very frustrated with .NET ORM's.

We've been using Entity Framework. The syntax is great -- it has very full LINQ support. The auto-refresh model from database feature is nice, but unfortunately comes at the price of a frustrating XML (EDMX) file that is very annoying to merge. Lately I've been making my schema changes in the XML by hand -- not cool. It does have a code-first way of doing it, but I haven't tried it... I'm sure it would be fine though.

We ran into some blocking issues with Entity Framework. For one, it doesn't support multiple databases in the same context -- even if they are on the same SQL Server instance. That one is just a nuisiance. But there were some severe performance issues. In one case, a single LINQ-to-Entities query generated two SQL queries under the hood: the first query took about 30 seconds and returned no data at all (or something meaningless like { { 1 } }, and the second query did all the work and took about a second. Only explanation for that is it was an Entity Framework bug.

We tried converting everything to NHibernate, and the same query ran in a second or so. Other queries also ran faster -- including ones that weren't Entity Framework outliers -- normal ones as well. So that's pretty awesome. The fluent code-first approach is nice, especially when paired with a quick-and-dirty home-grown code generator that queries the SQL database and dumps the POCO's for you. (The NHibernate conversion, along with that bit of awesomeness, was the doing of my colleage Frank.) Unfortunately, I ran into a lot of annoyances with NHibernate. Certain LINQ queries, in which I was doing joins and grouping by multiple columns and then doing aggregate functions in my select, caused NHibernate to barf. In some cases it could be worked around by splitting into two queries (minor performance hit, bit more code), but because of another limitation (too many parameters -- which could probably be avoided if they would just hardcode the query parameters when building their SQL query, as opposed to using a parameter for each one), that isn't always possible. It can also be worked around by passing a hardcoded SQL query to NHibernate -- but that kind of defeats the whole purpose of using an ORM if we're going to throw away all that strong typing and have to deal with typos in our SQL queries being detected at runtime. Another major nuisance is the inability to delete a range -- e.g., delete from tablename where column > criteria. The only way to delete (apart from hardcoding SQL) is to enumerate through each item in the IQueryable and delete it individually -- which is potentially a huge performance hit. It does, however, have multiple-database support, although I haven't played with it yet... Oh, and also, the syntax for executing stored procedures is just ridiculous -- transformer, AliasToBean -- I know it was ported from Java, but it just seems sloppy to have something called beans in a C# method name.

I was getting sick of the NHibernate issues, so I tried Telerik's OpenAccess ORM. Telerik is a decent company that makes .NET controls, a really awesome .NET decompiler (now that Red Gate bought out Reflector and made it no longer free), etc. OpenAccess used to be commercial only, but now it is free (but not open-source, which is really unfortunate). It supports query delete and supposedly has LINQ support as well. It also has a fluent mapping API with a bit of a twist -- it lets you write POCO's without special annotation, and it has a post-processor run after compile (done using MSBuild targets), which actually injects all the database logic code into the POCO's. It does work, but first I ran into a multiple-database issue. It supports multiple schemata, but if you try to prefix also with the database name, it fails. It shouldn't be so hard to implement, in my opinion, because it's really easy to execute a standard SQL query by just prefixing a table with the database and schema name. When I looked at their free (but closed-source) code in their own decompiler, I could see that they had a property for the full name of a table, but even though I was passing it in properly, they weren't setting the internal fields properly to support it, and it ended up giving weird errors. Maybe not the end of the world, but not cool. Especially not cool because the older (paid) version did support multiple databases in the same way (by simply specifying the database.schema name when specifying the table name). A lot of the old docs are still hanging around online, so there's some confusion there because they're practically two different products. One of the joined group-by queries, which worked in Entity Framework but made NHibernate explode, worked as-is in OpenAccess, so I was excited by that... But when I tried another more complicated one (joining four tables together and then grouping and aggregating in LINQ), it failed miserably. I rewrote the query many different ways (using wheres, using explicit joins, using sub-selects instead of joins, etc.), and each way failed with a different cryptic error.

I suppose by default we'll stick with NHibernate for now. It's not too much work switching between ORM's as long as they have LINQ support -- a lot of copy/paste and regex transformations on the POCO's and queries, or writing some code to regenerate tables. I am glad that we're using a solution that is open-source... Maybe one day we'll get bold and fix the issues ourselves (or if we were completely insane, write our own ORM)... And then we can close-source it, sell it (or at least sell consulting services around it) and change our name from DealerOn to ORMon (Mormon without the M).