We are currently developing an SDK for our system and evaluating the possibility of having an ORM solution take care of all the mapping between our objects and our database. However, there are a few peculiarities we would like to bring up, hoping that the matter experts will offer some advise. The feedback we are seeking is "there's no way you can do that" or "there is out-of-the-box functionality implemented specifically for this called..." or "it's not easy, but you could find a work around using..."

**Retrieve and store ID ** When saving new objects to tables that have a primary consisting of one field which also has identity, the value inserted in this field must be retrieved and assigned to a specific property of the object that's being saved (the ID property). I can only guess this is standard behavior in almost any ORM solution, but wanted to mention it.

**One object, several tables ** There isn't a one-to-one relationship between classes and tables. For the most part, this does not happen, but there are cases in which properties of one class may be saved to more than one table.

**Audit trail ** When persisting an object, it is sometimes necessary to write to an audit/history table with the primary key of the table that holds the actual object data, the current date, user and sometimes other data.

**Property vs. Property.ID ** When a property of the object being persisted is another custom class, it is sometimes necessary to submit the ID property of this class instead of the actual class. For example, say we have a class named Department which has an ID int property. Say we also have a class named Employee with two properties: an ID int property and a Department property of type Department. When saving the employee data, the table will require Department ID and Employee ID.

**sp_prepexec ** Because of our database configuration, our applications cannot run INSERT, UPDATE or DELETE statements directly against it. The only alternative we have come up with is to use the sp_sqlexec system stored procedure (or, alternatively, sp_prepexec or sp_prepare and sp_execute) to execute the ad hoc SQL statements created by the ORM by passing the generated query as a string parameter, meaning we should be able to retrieve it and stop the ORM from executing it itself. Is there a way to do this? Can you think of a better solution?

**Versions and compatibility ** The SDK we are building is for an application we publish. This application gets upgraded to new versions and some of the changes affect the database. Our SDK needs to support all versions of this application. However, we are not planning on touching our object model. What may happen is that property A which used to be saved in table T, field F in previous versions, may need to be saved to a different field and, potentially, a different table, or not saved at all. We have seen this property-field mapping is oftentimes done via property attributes. Is there a way to add several values of this attribute per property and somehow have a logic that picks the right one?

.NET Framework We are currently developing using version 2.0, but are also planning on moving to 3.5. Is 3.5 currently supported or will support for it be added soon?

We are not asking for actual solutions in either of these cases, as we understand you don't have all the details of our application and SDK, but any feedback you offer on this may give us an indication of where to look in the documentation, what type of examples to search for and download and what to start playing with if we decide to create a proof of concept project.

Thank you very much in advance!

Retrieve and store ID When saving new objects to tables that have a primary consisting of one field which also has identity, the value inserted in this field must be retrieved and assigned to a specific property of the object that's being saved (the ID property). I can only guess this is standard behavior in almost any ORM solution, but wanted to mention it.

This is fully supported, and even controllable, you can choose wheter to refetch or not the entity after the save.

One object, several tables There isn't a one-to-one relationship between classes and tables. For the most part, this does not happen, but there are cases in which properties of one class may be saved to more than one table.

This is possible, but need a few steps from you to be implemented.

Audit trail When persisting an object, it is sometimes necessary to write to an audit/history table with the primary key of the table that holds the actual object data, the current date, user and sometimes other data.

LLBGenPro comes with a pretty awesome auditing system, please see our auditing example.

Property vs. Property.ID When a property of the object being persisted is another custom class, it is sometimes necessary to submit the ID property of this class instead of the actual class. For example, say we have a class named Department which has an ID int property. Say we also have a class named Employee with two properties: an ID int property and a Department property of type Department. When saving the employee data, the table will require Department ID and Employee ID.

Yes, this is fully supported.

sp_prepexec Because of our database configuration, our applications cannot run INSERT, UPDATE or DELETE statements directly against it. The only alternative we have come up with is to use the sp_sqlexec system stored procedure (or, alternatively, sp_prepexec or sp_prepare and sp_execute) to execute the ad hoc SQL statements created by the ORM by passing the generated query as a string parameter, meaning we should be able to retrieve it and stop the ORM from executing it itself. Is there a way to do this? Can you think of a better solution?

This one, I really don't know, sounds like won't work out of the box, but I'm sure there must be some kind of workaround. Correct me If I'm wrong, you would like to bypass all saves, deletes and updates through stored procedures right? and you would like that this step is handled by de ORM Mapper for you?

Versions and compatibility The SDK we are building is for an application we publish. This application gets upgraded to new versions and some of the changes affect the database. Our SDK needs to support all versions of this application. However, we are not planning on touching our object model. What may happen is that property A which used to be saved in table T, field F in previous versions, may need to be saved to a different field and, potentially, a different table, or not saved at all. We have seen this property-field mapping is oftentimes done via property attributes. Is there a way to add several values of this attribute per property and somehow have a logic that picks the right one?

Version control must be implemented by your team.

.NET Framework We are currently developing using version 2.0, but are also planning on moving to 3.5. Is 3.5 currently supported or will support for it be added soon?

3.5 is fully supported, and newer versions of LLBLGen (2.6 beta) comes with lots of features like LINQ support.

Thank you for your quick response. Glad to read about the features that are fully supported.

goose wrote:

One object, several tables

This is possible, but need a few steps from you to be implemented.

Would you give us an idea of the extent of custom development we'd need to do?

goose wrote:

sp_prepexec

This one, I really don't know, sounds like won't work out of the box, but I'm sure there must be some kind of workaround. Correct me If I'm wrong, you would like to bypass all saves, deletes and updates through stored procedures right? and you would like that this step is handled by de ORM Mapper for you?

That would be ideal. We actually have a solution right now that's the one we are trying to replace with ORM. In it, we had to create every stored procedure manually, for every version of our application. And then create a data access layer that called the right stored procedure and passed the right parameters. And then have the object model call the right method in the data access layer. We want our ORM to take care of all or at least most of this. The problem is whatever solution we come up with cannot submit an INSERT, UPDATE or DELETE statement directly against the database. We thought of the sp_sqlexec idea, but we're open to others. If the ORM were to create the stored procedures on the fly and be able to maintain them (regenerate them whenever changes take place in the mapping), that would be perfect too.

goose wrote:

Versions and compatibility

Version control must be implemented by your team.

Absolutely. We would take care of detecting the version and invoking the right mapping, but does LBLLGen Pro allow for this? Does it allow to create or pick the right object-database mapping at runtime, if you will? Only during runtime, once the SDK connects to the database, does it know what version it is dealing with, since this is retrieved from a configuration table.

Thank you once again.

goose wrote: Quote: One object, several tables

This is possible, but need a few steps from you to be implemented.

Would you give us an idea of the extent of custom development we'd need to do?

Actually, the few steps you need to do are: One or Two clicks in the Designer. Absolutly no line of code.

This is called Entity Inheritance For more info, please chekc the following manual sectcions: 1- Concepts -> Entity inheritance and relational models 2- Designer -> Inheritance mapping

That would be ideal. We actually have a solution right now that's the one we are trying to replace with ORM. In it, we had to create every stored procedure manually, for every version of our application. And then create a data access layer that called the right stored procedure and passed the right parameters. And then have the object model call the right method in the data access layer. We want our ORM to take care of all or at least most of this. The problem is whatever solution we come up with cannot submit an INSERT, UPDATE or DELETE statement directly against the database. We thought of the sp_sqlexec idea, but we're open to others. If the ORM were to create the stored procedures on the fly and be able to maintain them (regenerate them whenever changes take place in the mapping), that would be perfect too.

I'm not 100% sure here, but LLBLGen Pro does not create SPs on the fly. But you get the source code of LLBLGen Pro runtime libraries, so I think there might be a possibility you can wrap the generated SQL. But it's better to hear from Frans (The Lead Developer, and creator of LLBLGen Pro) thoughts on this issue, I'll escepate this to him.

Absolutely. We would take care of detecting the version and invoking the right mapping, but does LBLLGen Pro allow for this? Does it allow to create or pick the right object-database mapping at runtime, if you will? Only during runtime, once the SDK connects to the database, does it know what version it is dealing with, since this is retrieved from a configuration table.

LLBLGen Pro gives you the ability to generate code for different frameworks and databases. But at runtime, you'd be responsible for switching.

That would be ideal. We actually have a solution right now that's the one we are trying to replace with ORM. In it, we had to create every stored procedure manually, for every version of our application. And then create a data access layer that called the right stored procedure and passed the right parameters. And then have the object model call the right method in the data access layer. We want our ORM to take care of all or at least most of this. The problem is whatever solution we come up with cannot submit an INSERT, UPDATE or DELETE statement directly against the database. We thought of the sp_sqlexec idea, but we're open to others. If the ORM were to create the stored procedures on the fly and be able to maintain them (regenerate them whenever changes take place in the mapping), that would be perfect too.

And how are you planning to do the insert/update/delete statements if they can't be executed against the db?

Our queries are executed using normal ADO.NET statements, so if you're using sqlserver, they're executed with SqlCommand, which calls sp_executesql.

Using Adapter, you can modify any generated sql right before it's executed, see the reference manual: DataAccessAdapterBase.OnSave.... methods.

We also have an authorization system build in. This allows you to not only to control on a per-field level who can read/write data to the entity, you are also able to limit who can save changes, delete entities and fetch entities, using Authorizers. So you can, using dependency injection (build in, or use a 3rd party dependency injection framework like structuremap or windsor), inject authorizers automatically when an entity is instantiated and every time that entity is saved for example, the authorizer gets a call. In there, you can determine if the current user can indeed proceed or not, by simply returning true or false (or throw an exception if you want). When false is returned, the save of that entity is aborted.

Thank you, Walaa.

Otis wrote:

And how are you planning to do the insert/update/delete statements if they can't be executed against the db?

Our queries are executed using normal ADO.NET statements, so if you're using sqlserver, they're executed with SqlCommand, which calls sp_executesql.

That's exactly what we can't have. I'm not familiar with the configuration created by the DBA's, but the end result is that our main system impersonates users and that these users have no rights to do INSERTS (or UPDATES or DELETES) if they were to query the database directly. However, we are building an SDK for our system and this SDK will be used by other applications created by/for these users. Since these applications won't be using the impersonation methods found in our main system, it will be the users querying the database directly (via the application), so INSERT statements via SqlCommand or OleDbCommand won't work. Calls to stored procedures (like sp_executesql) will.

Also, one thing I read somewhere is that the LLBLGen philosophy favors the creation your domain model based on a database. Is this true? What we want to achieve is the opposite. We have our class library and we simply want to map all properties to existing database structures.

Razor1973 wrote:

Thank you, Walaa.

Otis wrote:

And how are you planning to do the insert/update/delete statements if they can't be executed against the db?

Our queries are executed using normal ADO.NET statements, so if you're using sqlserver, they're executed with SqlCommand, which calls sp_executesql.

That's exactly what we can't have.

Any call to SqlCommand.ExecuteQuery is converted by the .NET SQLServer client into: exec sp_executesql N"querytext", parms

i.o.w.: it's out of our hands.

I'm not familiar with the configuration created by the DBA's, but the end result is that our main system impersonates users and that these users have no rights to do INSERTS (or UPDATES or DELETES) if they were to query the database directly.

Using application roles, or .NET impersonation? A user connects to the DB using 2 steps: first to the db service, and then to the catalog. The rights assigned to the role(s) the user is in are inherited by the user. So if your application impersonates the user to a windows user which is in the role which doesn't allow any CUD operations on the tables, then the user can't save.

My question then is: how is a user ever going to be able to save?

However, we are building an SDK for our system and this SDK will be used by other applications created by/for these users. Since these applications won't be using the impersonation methods found in our main system, it will be the users querying the database directly (via the application), so INSERT statements via SqlCommand or OleDbCommand won't work. Calls to stored procedures (like sp_executesql) will.

all dyn. queries called through SqlCommand are always executed (by the .NET client) through sp_executesql.

You can use whatever connection string, per call to the db, you want, if you're using adapter for example. This allows you to impersonate to a user X if you want to. (Windows security works if the db system is in the domain, which is in general not a good idea, as a breach on the DB is also a breach on your domain and vice versa.)

Also, one thing I read somewhere is that the LLBLGen philosophy favors the creation your domain model based on a database. Is this true? What we want to achieve is the opposite. We have our class library and we simply want to map all properties to existing database structures.

Yes, see my more in depth explanation in this lengthy article why this is and why this doesn't matter: http://weblogs.asp.net/fbouma/archive/2008/05/19/why-use-the-entity-framework-yeah-why-exactly.aspx

If you're facing stored proc calls AND you need to use POCO classes, I think you're better of with iBatis, as LLBLGen Pro doesn't support classes-first at the moment and we don't support proc calls for persistence directly on the entities yet. We do support proc calls but not as replacements for generated queries, as this is in general very limiting: prefetch paths and other advanced queries are not doable with procs as it requires data at runtime to produce the query