CODEWITHSUNDEEP
ormentity-framework-4
Jay Stevens
Jay Stevens

Reputation: 5913

Can this data structure be modeled in EF4, nHibernate, Subsonic?

We are not currently using any kind of ORM. I want to model a relatively simple database structure in an existing application. It is an attempt to add flexibility and run-time customization to the schema while still using SQL Server (2008) vs. moving to a non-relational document database (Couch, Mongo, etc.)

We are using this pattern in a number of places, but here I will genericize it for clarity:

We have an Object table with the following structure:

create table Object (
    ObjectID int,
    FixedObjectProperty1 varchar(25),
    FixedObjectProperty2 int,
    etc.
)

There is also a related Object_Attributes table which holds the "flexible" properties for each object. This is essentially a group of name/value pairs for each object with the following structure:

create table Object_Attributes (
    Object_AttributesID int,
    ObjectID int,
    PropertyName varchar(50),
    PropertyValue varchar(50)
)

I want to model this using some ORM technology (EF4, Subsonic, nHibernate, or "roll my own") to take the data structure above and yield a dynamic/anonymous object (which will ultimately be send down to a web client as JSON) with the following structure (ex. 3 records in the Object_Attributes table:

ObjectID
FixedObjectProperty1
FixedObjectProperty2
PropertyName1
PropertyName2
PropertyName3,
etc

Of course, I'd also need to be able to deserialize from JSON back into the DB. Any thoughts or suggestions are appreciated.

Upvotes: 0

Views: 96

Answers (2)

Doobi
Doobi

Reputation: 4842

The way to do it is to create views in the database and create entities for the views.

Upvotes: 0

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

IMO it is not possible to map this in EF. You will not be able to project 1-N relation into flatten structure.

Moreover I don't think that this is scenario for ORM. ORM means object realtion mapping where in case of .NET the object is strongly typed language structure which has to be defined at compile time. So each new property in your Object_Attributes would require redefining mapping, modifing class and recompiling your application.

Your requirement would probably be possible in fully dynamic language with its own ORM tool. .NET is not such environment.

What you can try to do is load object and object_attributes with common approach (object will have collection of attributes) and build some dynamic flatten wrapper (DLR) on the top of this structure. But in case of JSON it would be easier simply to create custom serialization.

Upvotes: 1

Related Questions