Custom entity builder for custom database table

Question

I'm designing a multi-tenant application using Entity Framework 6. A key requirement in this application is that each tenant is allowed to have customized tables. This means that one table with the same name may differ between schemas.

For example, one tenant may have a "People" table with: ID, FirstName, LastName, BirthDate.

Another tenant may have a "People" table with: ID, FirstName, LastnName, BirthDate, FavoriteColor.

When I design my model, this is what I have:

public class Person
{
  public int ID { get; set; }

  public string FirstName { get; set; }

  public string LastName { get; set; }

  public DateTime BirthDate { get; set; }
}

This works for the first tenant, but the custom column in the second tenant will not get loaded. What I am hoping to do is something like this:

public class Person : Dictionary<string, object>
{
  public int ID { get; set; }

  public string FirstName { get; set; }

  public string LastName { get; set; }

  public DateTime BirthDate { get; set; }
}

And then write a custom model mapper/builder that will place the table columns who don't have a matching property into the dictionary collection.

There are a couple problems with this:

Entity Framework creates a SELECT statement that lists all the properties/columns in the model. Somehow I would need to replace the list with * so that all the columns, even the ones EF doesn't know about, get returned.
I need to be able to intercept the result set, map the known columns to the model properties, and put the unknown columns into the dictionary.

Is any of this possible? Is there a better way to solve this problem?

Well, it would be very interesting to see if `EF` has to offer something out of the box. What I can think of is making some enum like `CustomColumns` and have a table like `PeopleCustomColumns` junction table, or something in this order.. — Leron, Mar 05 '14 at 15:14
Yeah there are three solutions to this problem: 1. have another closely related table that holds "extra" column info 2. Add generic columns to each table that can be appropriated for custom uses and 3. handle tables with different layouts between schemas. The first two are not difficult to create, but require additional overhead. The last solution would be ideal in my case because of the flexibility and lack of overhead. — Sparafusile, Mar 06 '14 at 15:06

score 3 · Accepted Answer · edited May 23 '17 at 12:28

I faced a similar problem, a more generic one.

In my application the end user is able to define his on data objects. At design time I do not even know about the Person class. That means from a design perspective adding properties to classes I don't even know at runtime. :)

Just to start, forget about Entity Framework. You will not get far with it trying to solve this. I spent some good time comparing and analyzing solutions. And the good news: I found one!

What I am doing now is allowing the user to specify a name for the data object, along with the properties. Next, I am using CodeDOM to generate an assembly containing POCO classes based on the user data. All classes derive from an empty IDataObject interface.

Finally using reflection, I am loading the classes and using them again depending on the user needs. The inheritance from the empty interface is very useful for two main reasons:

Getting the classes using reflection: I am using the interface to get classes that derives from it. Type.GetType() helps selecting the one I need.
The ORM I am using (see below) is using generic methods. I am simply adding a where T : IDataObject to my methods to limit the usage.

The ORM I am using is ServiceStack OrmLite. V4 is not free but you can still use V3 (which you can get from NuGet). And by the way, it is one of the fastest .NET ORMs.

All what OrmLite asks for is the type of the object being inserted/updated etc.. for that I am using the dynamic keyword to keep the type resolving to the runtime. Have a look here:

Pass concrete object type as parameter for generic method

More info about CodeDOM here:

How to: Create a Class Using CodeDOM

By the way, I am sorry for not posting code samples and that is because of two reasons: size and employer IP.

Finally, here is another question I asked in SO trying to solve this problem. It might be helpful.

Add property to POCO class at runtime

And sorry, I forgot to mention that the assembly generation is done for each end user. This might result in an application restart if you want to extend existing types (as a new assembly versions is going to be generated). OrmLite takes care of extending the tables with its CreateTable<T>(true) method.

Custom entity builder for custom database table

1 Answers1