Hi,

We want to extend our application with the ability to create new entities (with attributes and relationships) by the application administrator of our software.

The application administrator should be able to create new entities which inherit an existing (base)entity and can extend it with attributes and relationships.

We like the concept of a Semantic Data Modeling approach. This concept and related issues are described in this technical artical of Microsoft SQL Server. http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/BPSemanticDBModeling.docx

Do you (from a LLBLGen Pro perspective) have any support, ideas, suggestions or best practices to support Semantic Data Modeling in our application?

We like to hear from you.

Regards

We don't support creating entities on the fly. The main reason is: how to query them, as the code isn't adjusted for the new types you create? So the use case is mostly for systems which offer dyn. query systems based on constructs which don't require strong types (as the types aren't used in code, as the code has already been written).

One way to model this is is with a meta-system, where an entity you want to be extensible is actually modeled as an element which has a relationship with property entities which form the fields. This however can be tedious to query as it requires a lot of joins and you obviously can't utilize the relational model.

I've done a little research on the subject. To support custom entities in our program we have roughly the choice about two options: a) Entity-Attribute-Value / semantic modeling technique b) Relational database model with OR mapper which supports the creation of custom entities

We prefer the latter for several reasons like querying, strong typing, relational database (table per entity), use of indexing, foreign keys database support, stability and performance.

If I could choose a best-of-breed combination then I would like to have:

• An application manager of our program can create new custom entities directly in the UI of our webbased solution. Additionally the Quick Model Editor in LLBLGen Pro 4.2 would be near perfect if it was webbased and supports adding methods (like an UML diagram).
• Every new custom entity would store its data in a relational database (MS SQL server), where every entity has its own table.
• Relationships/associations are new custom entities and stores there data also in there own tables. Foreign keys are enforced by the database model, and are automatically indexed.
• A customer can write the implementaion for the methods of the custom entities.
• Changes in the model are automatically migrated including data.
• Every new custom entity is derived from a base entity which supports generic functionality like workflow, security, auditing, etc.
• A custom entity can create relationships to our out-of-the-box entities.

I know its a big wishlist .

Can LLBLGen Pro be of use in one way or another to support this scenario?

SanderF wrote:

Can LLBLGen Pro be of use in one way or another to support this scenario?

(a) scenario, yes, no (b) scenario though. LLBLGen is a powerful tool that works on your model (db first or model first) and the final product is to produce generated code that represent that model. So, its a generation tool that produces .net classes, and can't support what you described in your (b) scenario, without a re-generation process.

Indeed. I fail to see how it would work in practice, where a user creates a new custom entity 'Order' and all of a sudden the system can query that entity (how? what does that code look like?) and it's fetched into typed classes? (How? the code has to be recompiled!)

It reads as if you want to embed the designer into your application and let the user add new entities to the project, generate code, and write methods against the generated code...

If the code needs to recompile after adding/modifying/deleting an entity, field or relationship then this is the price we have to pay. These changes aren't happening very often.

Changes in the custom entity model of our application needs to: * generates database migration script (maybe we can use System.Data.Entity.Migrations namespace for doing the job). * execute the database migration script * generates the source code * compiles the source into a seperate DLL * restart application and load the new DLL

The functionality we like to build is similar with custom entities in Microsoft CRM, SalesForce and PegaSystems (PRPC).

These packages also has their limitations, i.e. in Microsoft CRM you cannot change the schemaname of an entity, or the (internal)name and datatype of a field.

SanderF wrote:

If the code needs to recompile after adding/modifying/deleting an entity, field or relationship then this is the price we have to pay. These changes aren't happening very often.

But that's asking to embed the designer, isn't it?

Changes in the custom entity model of our application needs to: * generates database migration script (maybe we can use System.Data.Entity.Migrations namespace for doing the job). * execute the database migration script * generates the source code * compiles the source into a seperate DLL * restart application and load the new DLL

The functionality we like to build is similar with custom entities in Microsoft CRM, SalesForce and PegaSystems (PRPC).

Isn't that simply saying: your client wants to use the designer to add custom entities through the designer? Custom entities at runtime IMHO mean that you define tables, projections on these tables and can execute these projections but all in an untyped manor as there's no way you can add types at runtime and query with those (as the code doesn't know about these types, so doesn't refer to them). Or of course using dynamic<T> which is pretty much the same thing as e.g. a datatable with rows, as the consuming code doesn't know about any of the fields so it won't refer to typed properties in the code, it can only refer to e.g. property P using strings obtained from the DB.

Indeed, an embedded webbased entity designer would be a very nice solution. Or a 'white label' LLBLGen designer with restrictions and pre-configured settings.

Take a look at Microsoft CRM XRM feature. It describes more or less what we are aiming for:

http://msdn.microsoft.com/en-us/library/ee830281.aspx

I don't know how MS CRM does this is in the background, but I'll like to find out.

ah

They do it like all these systems do it: they have tables in which they store the entity definitions, the relationships etc. They don't work with typed classes, they create on the fly at runtime an untyped datatable with the columns defined for entity E as stored in the 'entity definition table' (or however it's called), and create a query to fetch the entity with the meta-data in that entity definition table. The data is then read into the datatable created for the result, and bound to a UI which is dynamically generated from the same meta-data.

I.o.w.: no code generation/type creation done, it's all based on meta-data obtained from tables in the database. The user can alter the entity definitions by effectively altering the meta-data in the entity definition tables. Systems then have to be in place to either alter the related data or toss it away.

The above is actually doable today, you need to setup the tables yourself and your logic reads the meta-data and builds the UI dynamically from it.

Do you have other experiences with this, i.e. that they do generate code at the backend which then has to be compiled? For a user who only operates a UI, compiling code doesn't really add much (as the user won't be able to alter it anyway)

We have take a deeper look at Microsoft CRM. Entities which are made in CRM are 1-on-1 created as tables in SQL Server. Relationships can be managed too.

In the UI of CRM there is no need to access the entities programmatically, but as a developer for CRM there is a choice for early or late binding. With early binding the c# code is generated and you can access the code strong-typed.

Take a look at this article: http://msdn.microsoft.com/en-us/library/gg328210.aspx

Changes in the entities in CRM are reflected in the SQL server schema, but there are restrictions. Like renaming a table or field.

The underlying XRM framework cannot be seperated used/bought from Microsoft CRM.

If LLBLGen Pro could be used as the XRM framework for our application that would give our solution the flexibility we need for our customers and we know that it would be a reliable solution because Solutions Design has many years of experience with OR mapping technologies .

The scenario you described (early bound entities) is indeed possible if you regenerate the code. Even Microsoft CRM needs such re-generation using the command line tool CrmSvcUtil.exe (ref: http://msdn.microsoft.com/en-us/library/gg327844.aspx ), similar to our Command Line Generator tool (you can download the source code from Customer Area).

You can create your own meta-data system that store some custom entity definitions into your entity metadata definition's tables. Then you create a Web application that create/modify/delete such definitions. At this point you could work in an un-typed meta system that store data in your data tables. Then, you can run a process that converts all to a typed style. First you create your custom entities stored in your meta data tables into real tables. Then you can run LLBLGen's command line refresher and LLBLGen's command line generator, to update your source code that you need to recompile. All this could be in a single batch file. At this point your DAL assemblie is updated with the custom entity's definition that you can use in your business logic code.

That's how I see it, I hope that make sense.