5

I have a question very similar to this, How do you build extensible data model, with regards to building an application using an extensible data model, except using EF 4.

My requirement is to be able to allow usersi of my application to extend the data model at runtime on the fly. We're currently underway with building the system and have made use of EF as the DAL layer, with POCO classes generated from the standard T4 template.

Taking this post by Ayende, http://ayende.com/blog/3498/multi-tenancy-extensible-data-model, as a concise summary of the options, we've taken the option of an xml column in a table allowing us to put pretty much anything in there with no need to recompile.

As I understand it, the extended table approach would be better, it seems to work quite nicely for dynamics CRM, however how/would it be possible whilst using EF 4 on the fly?

Community
  • 1
  • 1
stu432
  • 105
  • 1
  • 6
  • What are your design goals for these extensions? Do you want to support actual programming against these extensions, such as CRM's support for providing a strongly-typed web services wrapper that includes extended properties, or do you just need to allow users to store additional data for entities in your model? The way in which these extensions will be used will impact the way in which you approach the problem. – Jamie Thomas Jun 08 '11 at 15:06
  • Not actual programming as in intellisense fashion, but from a UI perspective yes, I'd need to be able to get a listing of the associated properties on an entity and then allow the user to build ad-hoc queries against these. The problem with the xml approach is that the generated queries will be slow, whereas splitting out into relational tables should markedly improve performance. – stu432 Jul 05 '11 at 09:34

3 Answers3

1

One possible solution to this kind of task is the EAV Pattern > http://en.wikipedia.org/wiki/Entity-attribute-value_model

MattDavey
  • 8,897
  • 3
  • 31
  • 54
  • 1
    This would work, not exactly what I'm after, will try a similar method to this in ef (http://ayende.com/blog/4776/support-dynamic-fields-with-nhibernate-and-net-4-0) and see if I can get it working on the fly. – stu432 Jul 10 '11 at 14:21
0

One approach, I have used in the past is to create generic columns for example, int1, int2, ... intn, varchar1, varchar2, ..., varcharn etc. This has advantages and disadvantages. Its not clean from the DB perpective (some DBAs will be horrified). But with SQL Severs Sparse Columns support storage is not a issue. So you can have a really wide table. But you will need to store some meta data somewhere like, varchar1 -> Name, int1 -> Age etc.

Now you can write normal sql/ef queries, searching is easier, SSRS is straight forward (no xml parsing).

I too would like to know if there is a better solution.

ravi
  • 949
  • 11
  • 22
0

You might want to look at XML Property Promotion as a way to speed up access to the properties you have defined in the XML.

Ann L.
  • 13,760
  • 5
  • 35
  • 66