Mahdi Taghizadeh I'm who I'm!

EAV and the .NET Framework

Recently I've been working on a web application's database model in which I had different types of products and services that my client is going to offer in his eShop. The problem was different nature of attributes each type had and I should have defined a relationship between them and my online order pipleline. Using the common relational database approach I finished up with an OrderDetail table which had multiple foreign keys related to tables which was representing different kinds of products. Anyway, I'm not going to explain my project details here. That was a preface to explain a concept that Farid Arzpeyma introduced to me: Entity-Attribute-Value (EAV) Model, an approach that is widely used in enterprise eCommerce platforms like Magento.

There are many documents and articles out there describing EAV, its concept, principals and usages and I'm not going to talk about it here but quoting this which describes it shortly:

Entity-Attribute-Value model (EAV), also known as object-attribute-value model and open schema is a data model that is used in circumstances where the number of attributes (properties, parameters) that can be used to describe a thing (an "entity" or "object") is potentially vast, but the number that will actually apply to a given entity is relatively modest. In mathematics, this model is known as a sparse matrix.

What I came up with in my mind was possible existing frameworks for .NET developers to implement this model in their applications. Because EAV breaks the rule in which we're urged to use a vertical data representation (using columns) - by introducing another method of collecting data records and their types in table rows - it's necessary to adopt our OOP code to smoothly interact with this new structure. By the time Microsoft hadn't released SQL Server 2005, storing hierarchical data structures was a painful handy job for database developers but at the time Microsoft introduced XML datatype with SQL Server 2005 it became easier for them to natively store such structures right inside a column in a database. EAV is a similar concept and its main goal is to enable developers store attribute/value information in a table in a hierarchical manner.

Unfortunately it seems that there have been very few interests in the .NET community to work on open source frameworks in order to work with EAV database models. My searches shows a disappointing number of articles, blog posts and tools for this purpose.

The first article I found was Entity-Attribute-Value (EAV) model with SQL Server 2005 xml datatype that is talking about how to implement an EAV-like model using the new XML datatype in SQL Server 2005 and later. Mladen Prajdić has also referenced to another article explaining EAV in SQL Server more generally.

The second was a newer talking about a solution with Entity Framwork: Generic Entity Attribute Value Model – A POCO Implementation. This is closer to what I had in my mind because it's talking about EAV in general and how to implement our data layer, built on EAV pattern, using EF.

And finally the third was an open source project on CodePlex but unfortunately it seems that it's no longer active and there isn't even a public release out.

My conclusion is, the .NET community hasn't done much efforts on EAV concept and in my opinion it is because Microsoft policy - since creation of .NET Framework - has been just to spread this technology and best practices have rarely gone further on advanced and enterprise topics.