There’s no doubt in my mind that code libraries and frameworks are fantastic for saving time and work, after all I want to spend time solving my business problem, not writing infrastructure. Nowhere is this more true than it is with Object-Relational Mapping, or ORM, libraries.
Broadly speaking, if you’re still writing application code that requires that you also write SQL, you’re wasting your time. Wasting time thinking about SQL syntax. Wasting time writing it. Wasting time testing, debugging and maintaining it.
I believe this so much, but was so dissatisfied with any existing ORM offering, that I wrote my own ORM. It wasn’t a trivial task, but I have something that does exactly what I need, on the platforms I need, and does it at a speed that I consider more than acceptable. Occasionally I hit a data storage requirement that ORMs, even my own, aren’t so good at.
ORM usage is usually viewed as one of two approaches: code first or data first. With the code-first approach, a developer defines the storage entity classes and the ORM generates a backing database from them. With data first, the developer feeds a database into the ORM or an ORM tool, and it then generates the entity classes for you.
But this doesn’t cover all scenarios – which is something no ORM that I’m aware of seems to acknowledge. Consider the following use-case (and this is a real-world use case that I had to design for, not some mental exercise).
I have an application that allows users to define storage for data at run time in a completely ad-hoc manner. They get to choose what data items they want to save, but even those data items are dynamically available so they are available only at run time.
So we need to store effectively a flat table of data with an unknown set of columns. The column names and data types are unknown until after the application is running on the user’s machine.
So the entities are neither data first nor code first. I’ve not thought of a catchy term for these types of scenarios, so for now I’ll just call it “user first” since the user has the idea of what they want to store and we have to accommodate that. This is why I created support in the OpenNETCF ORM for the DynamicEntity.
Let’s assume that the user decides they wanted to store a FirstName and LastName for a person. For convenience, we also want to to store a generated ID for the Person entities that get stored.
At run time, we generate some FieldAttributes that define the Person:
1: var fieldList = new List<FieldAttribute>();
2: fieldList.Add(new FieldAttribute()
4: FieldName = "ID",
5: IsPrimaryKey = true,
6: DataType = System.Data.DbType.Int32
9: fieldList.Add(new FieldAttribute()
11: FieldName = "FirstName",
12: DataType = System.Data.DbType.String
15: fieldList.Add(new FieldAttribute()
17: FieldName = "LastName",
18: DataType = System.Data.DbType.String,
19: AllowsNulls = false
And then we create and register a DynamicEntityDefinition with the DataStore:
1: var definition = new DynamicEntityDefinition(
Now, any time we want to store an entity instance, we simply create a DynamicEntity and pass that to the Insert method, just like any other Entity instance, and the ORM handles storage for us.
1: var entity = new DynamicEntity("Person");
2: entity.Fields["FirstName"] = "John";
3: entity.Fields["LastName"] = "Doe";
6: entity = new DynamicEntity("Person");
7: entity.Fields["FirstName"] = "Jim";
8: entity.Fields["LastName"] = "Smith";
The rest of the CRUD operations are similar, we simply have to name the definition type where appropriate. FOr example, retrieving looks like this:
1: var people = store.Select("Person")
Updating like this:
1: var person = people.First();
2: person.Fields["FirstName"] = "Joe";
3: person.Fields["LastName"] = "Satriani";
And Deleting like this
1: store.Delete("Person", people.First().Fields["ID"]);
We’re no longer bound by the either-or box of traditional ORM thinking, and it leads to offering users some really interesting and powerful capabilities that before were relegated to only those who wanted to abandon an ORM and hand-roll the logic.