Best ORM tool for Oracle with 4000 tables

Question

I am looking for the best .Net ORM tool that would work with Oracle and SQL Server 2005. We have an Oracle database with around 4000 tables. I tried TierDeveloper and Codesmith and they are not responding when I try to map objects with my Oracle DB. Which will be the best ORM tool to work with a big Oracle db?

Answer 1

I'd take a serious look at ibatis (for both Java and .Net). I say that for a few reasons:

First, ORMs like NHibernate that really try and abstract away the database I think can be counterproductive, particularly when dealing with "legacy" databases. Legacy here doesn't mean what you think it means. Tools like Hibernate and JPA (Java) define pretty much anything that doesn't do things the "proper" way as legacy, and that can include using composite keys (seriously, I read a JPA book taht called composite keys "legacy").

Ibatis on the other hand gives you most of the power of an ORM (and some things that Hibernate, for example, can't do like the groupBy functionality) but still leaves you able to just write plain SQL. I imagine with a database that large you're going to have some questionable modelling decisions that will be difficult if not impossible to map into many ORMs. By writing direct SQL you can cater for these situations in ibatis by definition.

ORMs that aren't vendor specific are also lowest common denominator when it comes to query language. If all databases can't do it then you can't do it anywhere. Oracle has one of the most sophisticated SQL dialects. You should use it. Things like CONNECT PRIOR don't exist in many other SQL dialects (and thus aren't modelled in a performant manner in abstracted ORMs).

I wrote more about this in Using an ORM or plain SQL?

The fact that your hands are tied by existing design makes an even strong case for keeping as close to SQL as possible.

Answer 2

If you don't have an object model, I'd think twice about ORM. I think it's a mistake to have a 1:1 mapping between tables and columns, objects and attributes. If the objects are just structs, with no behavior or encapsulation of rules, what's the point?

In that case, I'd prefer another approach: iBatis, straight JDBC, stored procedures, or something else that would allow you to tune the SQL instead of depending on the stuff generated by the ORM.

Answer 3

Try EntityORM: http://entityorm.uuuq.com

EntityORM is a fully typed Object Relational Mapping library for .NET 2.0.

The main strength of EntityORM is the ease of use. Most ORM libraries still require a lot of type casting and other plumbing to be written, EntityORM is designed to relieve the programmer from these tedious and error-prone tasks, making it very intuitive to use.

The main features are:

* DataBase independent
* Ease for build new drivers that are independent from the EntityORM core framework (for now there is Sql Server, Sql CE, MySql, Oracle, PostgreSQL and Access drivers)
* Automatic mark changed for changes entities (optional)
* Automatic lazy loading (optional)
* Automatic transactions (optional manual transaction for instance for two-phase commit)
* Ease to map for an existing database with minimal effort
* All relational types are supported (One-To-One, One-To-Many, Many-To-One, Many-To-Many)
* Flexible event framework
* Conditions para load filter data into entities
* Capability to map to different table names or field names
* Default values
* Rules validation
* Autonumber
* Guid
* Generic list to managed multiple entities hidden deleted entities
* Typed entities are lazy loading with caching reducing significantly the needed for reflection
* Entity views to faster load read-only data from one ore more tables into a single flat entity
* Join conditions to join several tables in to a sigle entity view
* Generic list to managed multiple entity views
* Distinct, automatic group and aggregate functions (count, sum, largest, average, smallest) supported in entity views