Modifying an Entity Framework Model at Run-Time

This is purely a conceptual and design idea related to EF4.

The example/scenario is a large ERP or CRM type 开发者_StackOverflow中文版system where companies may need to add traditional "user defined fields" to capture additional data.

I know that the EF has some capabilities to push out the model to a database at run-time but the question really is can you use EF to modify a model and update the data store in real-time?

In other words, if I provide the user a mechanism to add a user defined column, associated data type and null requirements can that be done on the fly with EF and then remembered for all future sessions?

It's out there but I think you all will see what I'm getting at.

Brent

I've been asked this question a few times in the past. There is no obvious or easy way. It's possible that there is no way but we are developers and there's always a way! Do I know what that is? No. Can I dream up some ideas? ....Mmmm.. at runtime, the model is based on strongly typed classes from the metadataworkspace. You can create those on the fly. But then you need to modify the xml of the edmx file.There's LINQ to XML or xpath for that. Modifying the database schema... how does model first build dbs...it creates sql and then you execute it. You'd have to create sql (how? shrug) and the execute it (objectcontext.executestorecommand()). Feasible? Possible? I have no clue. Really the answer is no...there's nothing in VS or .NET 4 (EF APIs) to readily enable this as far as I know. Surely someone more clever and patient than I was spent (wasted??) a lot of time (trying to) outsmart EF to pull this off. Based, however, on your response t the suggestion re Jeremy's blog post, you're looking for something built in/handy. That is easier to answer with a "nope".

julie

There are often more than one way to solve a problem, and this is not an exception. In this case, what you're looking for is allowing users to add new fields to your app and database and use those fields from within your app. Some ways to do that are:

a) Allow the users to modify the database schema.
b) Create a separate structure for defining 'user defined fields' and storing data in them.
c) Create nullable 'reserved' fields in the tables where users are more likely to need their own fields.

I prefer the (b) approach and sometimes the (c) approach whenever user-defined custom fields are needed in an app. Here are some of the pros/cons for each of the three:

Modify schema

• Risky: If you allow the users to modify the database schema, where do you draw the line? If they can add fields they may also go off changing the definition of existing fields, add or remove indexes etc. This can lead to a support nightmare with bugs, performance issues etc triggered by schema modifications done by users.
• Performant: storing the new user defined fields inline in existing tables will usually have a performance advantage over a separate structure, but only as long as they don't go overboard with changes.
• Clunky: EF determines the schema at design-time, so to make this work at runtime you will need to generate new entity classes at runtime with members representing the new fields, and you will need to update the mapping metadata at runtime. The runtime-generated entity classes can inherit from the design-time generated classes so you only need to add members and mappings for the new user defined fields. Although possible, it is clunky. The code that consume the runtime-generated classes will need to use reflection to access the new members that are created at runtime.

Separate structure

• User-friendly: By creating a separate structure for storing custom fields, you can build app logic for users to add/remove those fields etc. They don't need to mess with the database, instead you can have maintenance forms within the app for adding new fields.
• EF-friendly: no need to mess with entity classes and mapping metadata at runtime. Everything is defined at design-time, and the user-defined fields are just data.
• Slightly less performant: Having separate tables for defining and storing the user defined fields can make lookups slightly more costly due to additional roundtrips or additional joins.

Reserved fields

• Often enough: In many situations, custom fields are only used for one or a few extra fields. Reserving a couple of columns will often cover 99% of user needs. Even a generic "remarks" field in every table is often enough in LOB apps.
• Limited: If the users want more fields than you have reserved, or other data types than you have reserved then that can be a limitation.
• Performant: Columns inline, retrieved without extra roundtrips or joins.
• Defined at design-time: No runtime messing about with entity type definitions or mappings.

Brent pinged me on Twitter today (almost 2 years after this post) to see if anything has changed. This scenario is still not supported by EF. However Rowan Miller has an interesting post on how to pull this off with Code First http://romiller.com/2012/03/26/dynamically-building-a-model-with-code-first/

This may or may not do what you need but worth a look.

Jeremy Miller discusses a similar set up in this article. He's using nHibernate, but it might give you some ideas.

This is a version of Reserved fields from @KristoferA answer that I have used in the past.

You can add one extra XML (or JSON) column on each table that is likely to require custom data and then read/write it from db using EF. After read deserialize XML (JSON) and pass it to the mechanism that is supposed to handle the mappings then present it to user. Same goes for writing data you read it from UI -> map to object -> serialize to XML (JSON) -> and write to these extra fields.