Search code examples
c#sqlormdapperpetapoco

ORM for use with databases that have semi-flexible schema design

I like the "micro" approach of Dapper, Massive, PetaPoco etc. and I like to have control over the SQL we send to the database, most of the time it's relatively simple. I also like working with POCO's however when dealing with a somewhat flexible schema design you often run into trouble :)

Let's say we have a Person entity, that ALWAYS have the following properties.

  • Id
  • Name
  • Email
  • Phone

But in some cases there might be additional properties like

  • SpecialPhoneNumber
  • VeryCustomValue

I would really like a POCO with the common properties that we know will always be there. But have the other accessible in a key/value collection.

What would be the best approach? And does any of the mentioned "mirco-orm's" support this? I have looked at them all but have not found any indication that they do, but maybe I'm missing something.

Would it be possible to do this directly with an SqlDataReader? or would the performance of reading hundreds of rows be bad when using reflection to create the objects? The mentioned orm's all seem to do pretty well and I guess the use a DataReader underneath.

Hope you can help :)

EDIT: I should probably mention that we have no control over the application architecture. This is an ERP solution that allows the individual customer to customize their application and the underlying database. Adding fields in the application adds columns in the database. Bad me! for not making that clear in the first place

Solution

  • Massive can support this, because it materializes data into ExpandoObject's:

    The secret sauce is the ExpandoObject. Everything that goes in and everything that comes out of Massive is an Expando – which allows you to do whatever you want with it. At it’s core, an ExpandoObject is just an IDictionary< string,object >

    ExpandoObject implements IDictionary<string, Object> and IEnumerable<KeyValuePair<string, Object>>, so you can enumerate and test for members in any way you need to.

    Massive's default is to issue a SELECT * query, so the ExpandoObject will contain all fields in the table, even those you do not know about.

    You can get Massive's current code from Github.

    Dapper can also select into dynamic objects, but unlike Massive it can only select data and cannot insert, update, or delete it. EDIT: Reviewing the Dapper docs, it appears it can perform modifications, as it can execute any sql. There is some more recent information about performing inserts with Dapper.