R – .NET ORM for Database with no primary keys defined

netorm

Does anyone know of a ORM tool for .Net that will work on a database with no primary keys defined? We are running MS SQL Server 2005 by the way.

I cannot change the DB schema and add primary keys – the DB is the of our ERP system and their business logic is aware of what would make a table non-sensical.

For example, it would make sense for the 'Customer' table to have a primary key on the field CustomerNumber. SQL Server 2005 does not have any primary key on the 'Customer' table, but the ERP software prevents anyone from adding two customers with the same CustomerNumber. So if I go straight to the database I can add two customers with the same CustomerNumber but when going through our ERP software that is prevented.

I've used LINQ-SQL with success so far. I can define primary keys in the designer in VS2008 and use all of LINQ-SQL's abilities. However, I'm about to begin a large project and am looking for a more mature ORM tool. I've checked out LLBL Gen Pro but can't seem to get it to work. I've also checked out MS Entity Framework and have run into trouble there.

Any ideas or has anyone managed something similar to what I hope to do?

Best Answer

I think you've already identified the key you need, possibly without realising it.

Your database does have primary keys, they're just not enforced by the database engine.

For example, your Customer table has a CustomerNumber field, and so on.

Any .NET ORM (I happen to favour NHibernate) can manage just fine for Read, and Update.

Create and Delete are the times you may run into issue.

For Create, you need to generate new valid CustomerNumber values; and for Delete you need to ensure you don't violate any foreign key relationships. (In NHibernate you can use a primary key type of assigned which basically means your code will assign key values and NHibernate has to use them.)

Related Topic