Hi, We are busy selecting an ORM tool for our the business software we are building for our organization. After checking many ORMs, LLBLGen is one of the two remaining ORMs that fit our requirements. Our database will be SQL Anywhere.

I read some (old) forum posts about LLBLGen’s architecture and why LLBLGen generates parameterized queries instead of using stored procedures. But that did not really give an answer to our questions. Can you help us?

The data (physical) model in our database is different from our entity model. Therefore we need a mapping from the data model to the entity model. This goes further than just mapping a database column name to its corresponding C# field name (see below). We want to hide the data model (and its complexities) from the client application. The client app will see the entity model only. We see a few challenges: A) mapping. We have multiple tables that are mapped to a single entity class Therefore we want to implement the retrieve actions with database Views (mapping data model to entity model). And we want to implement the CUD (create, update, delete) actions with stored procedures (mapping entity model to data model). Note that the ORM tool does not (cannot) know how we map the data model to the entity model, because the mappings can be complex. B) history. Copying previous versions of records to a separate table during the Update action. C) access control. Also we need to allow write access only to users with certain roles and deny write access to other roles. The access can differ per table (write access to one table, read access to another table). Advantage of putting that access logic in the Stored Procedure would be: better performance because the access logic is executed in the database. In that case we need to pass the user name as an additional parameter in our CUD Stored Procedures. D) WCF Data Services. We pass the data to the client through WCF Data Services.

Therefore the following questions: 1. Can we use views for the Retrieve queries? 2. Can we use stored procedures for the CUD actions? 3. Is it possible to pass the user name (or other values) as extra arguments to our Stored Procedures in LLBLGen? How / where do we do that in our code? 4. If stored procedures for CUD are not supported, can you give us a simple example of how to perform case A), B) and C) mentioned above?

Thank you very much in advance.

Arjan

I'm confused whether you are asking about LLBLGen Pro or Open Access? If for LLBLGen Pro, could you explain an exampleof the maping complexity that push you to use Sps for CRUD.

Hi Walaa,

about the mapping complexity: I described that in A) : a record from table A and table B is combined (through a 1..n JOIN) into a single entity.

About B): how would I implement such a requirement with LLBLGen? What options do I have?

Thank you very much for your help.

Arjan

Hi Arjan, I will try to answer some of your questions:

aburggra wrote:

1. Can we use views for the Retrieve queries?

Yes, you can. Here are some options: a. Database View mapped to a TypedView. b. Database View mapped to an Entity c. Use a TypedList which is an object that you can use to mix multiple related entities into one. Just like a View but you use information in your model, so you don't need a DB View. d. Use a DynamicList, which is a way to fetch fields from multiple entities related, grouped, filtered, etc, into one object which inherits form DataTable.

aburggra wrote:

1. Can we use stored procedures for the CUD actions?

No. LLBLGen don't use SPs for mapping. We support StoreProcedure calls though. Maybe you read some info about this, but just in case here are some articles written by Frans Bouma, the LLBLGen's Lead Developer. We don't want to create a debate about SPs but just point out why we don't support them for mapping purposes. http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx

aburggra wrote:

1. Is it possible to pass the user name (or other values) as extra arguments to our Stored Procedures in LLBLGen?

You can use Stored Procedures Calls. Whatever parameters you have in your SP you can pass them into.

aburggra wrote:

How / where do we do that in our code?

As simple as this:

DataTable resultSet = RetrievalProcedures.CustOrderDetail(10254);

It returns a DataTable. You also can use projections to project the SP resulset into LLBLGen Entities or another custom class (more info...).

aburggra wrote:

1. If stored procedures for CUD are not supported, can you give us a simple example of how to perform case A), B) and C) mentioned above?

Ok, here we go:

aburggra wrote:

A) mapping. We have multiple tables that are mapped to a single entity class Therefore we want to implement the retrieve actions with database Views (mapping data model to entity model). And we want to implement the CUD (create, update, delete) actions with stored procedures (mapping entity model to data model). Note that the ORM tool does not (cannot) know how we map the data model to the entity model, because the mappings can be complex.

You simply use LLBGen entities and framework as a black box between your application and your DB, then you perform your LLBLGen Entity <-> Custom Class mapping yourself. LLBLGen entity itself generates an Entity model you can use right away, but if you need to use your own classes you can do it anyway. However you loose some built-in features already present at LLBLGen entities.

aburggra wrote:

B) history. Copying previous versions of records to a separate table during the Update action.

You can do it at: - You business logic code - LLBLGen Auditing - A DB trigger.

aburggra wrote:

C) access control. Also we need to allow write access only to users with certain roles and deny write access to other roles. The access can differ per table (write access to one table, read access to another table). Advantage of putting that access logic in the Stored Procedure would be: better performance because the access logic is executed in the database. In that case we need to pass the user name as an additional parameter in our CUD Stored Procedures.

You can use LLBLGen Authorizers to put this logic at code. If you really need to have this on your DB, you can use a trigger, your own SP and then project the results to your class, or you can use a SP just to check the permissions before execute the query.

Hi David,

thank you very much for your detailed answer. BTW my intention was not a debate, but to think things through in advance and know what the options are in our design.

One more question remains: OData.

We will have three tiers: 1. database 2. ORM 3. WPF client The client will communicate with the ORM through OData. One advantage is that this requires almost no plumbing code at the client side.

I read that LLBLGen has an OData provider. For the Retrieve we might write our own IHttpHandler to construct a feed (because using an Expand expression seems to get very slow when retrieving a complex tree because it duplicates data in the feed).

But for CUD we'd like to use LLBLGen's OData provider. 1. How does this combine with the solutions you've given, for implementing challenge A, B and C)

A) mappings: Can we use the solutions you proposed together with the OData provider? Assuming we have CUD actions and the mapping has 1 to 1 joins from one 'primary' table to several other tables with related info.

You simply use LLBGen entities and framework as a black box between your application and your DB, then you perform your LLBLGen Entity <-> Custom Class mapping yourself. LLBLGen entity itself generates an Entity model you can use right away, but if you need to use your own classes you can do it anyway. However you loose some built-in features already present at LLBLGen entities.

B) history / auditing

You can do it at: a) You business logic code b) LLBLGen Auditing c) A DB trigger.

C)

a) You can use LLBLGen Authorizers to put this logic at code. b) If you really need to have this on your DB, you can use a trigger, your own SP and then project the results to your class. or c) you can use a SP just to check the permissions before execute the query.

1. Where does the OData provider get its data from?
   • Materialized objects? (- small overhead of materialization, but pro: opportunity to add business logic to the entities themselves through partial classes / dependency injection).
   • or something like data tables?

Thanks for your help.

Arjan

aburggra wrote:

1. How does this combine with the solutions you've given, for implementing challenge A, B and C)

It will work just fine with all the approaches I gave above, because all of that (mappings, auditing, authorizers) happen in the server. The difference is: you can't return your own custom objects in OData. LLBLGen OData's support works with Entities, so the client will get entities. For the rest, it's the same. It's wise to read the Limitations section though.

aburggra wrote:

1. Where does the OData provider get its data from?
   • Materialized objects? (- small overhead of materialization, but pro: opportunity to add business logic to the entities themselves through partial classes / dependency injection).
   • or something like data tables?

It works with materialized entities, and yes, the advantage is that you can get all the stuff around the entity working transparently. You should give it a spin to see how it works. Btw LLBLGen Framework is very efficient materializing objects, so I don't why you will face a problem there.