Examples for Entity Framework 4: Mapping POCOs to EAV database?

Question

Our customer advertises products but the product's attributes are vastly different from one another. There are almost as many product "classes" as there are products.

Originally, we created an EAV database where we added and removed attributes for each "class" of product. The whole thing works very well, but the complexity is mind numbing. This is Database #1.

We finally came up with a set of fields common to represent all products (POCOs), moving the "extra" fields to an XML field "catch all". This is Database #2.

Now we have some customers using the old and some customers that will use the new. We really didn't want to update the old until we needed to do so, but we have changes from time to time that simply take longer than necessary because of the EAV structure.

Questions:

1. Any examples out there on how to code EF to persist POCOs to an EAV databases (where you have a table of field names and a table of data)?
2. Should we just scrap the database and write "normal" tables for all those older customers, given we have changes from time to time?

Normal of course is referring to the Boyce Codd Normal Form.
We handled the older database by bleeding its structure into the software, then mapping to POCOs in the repository.

Answer 1

EAV tables are generally a messy business, and Joe Celko (in Avoiding the EAV of Destruction) and a lot of other industry experts (e.g. Five Simple Database Design Errors You Should Avoid) rightfully warn against using EAV constructs too much.

All database critics aside: how would an object in .NET based on such an EAV even look like?? It could have any number of properties of any type, so basically, it would have to be a "universal" object that can take on any shape, pretty much.

This thought makes my skin crawl and goes against the most basic concepts of a strongly typed language - yes, you can do stuff like that in a dynamic language like Ruby and Python, but in C#?

The only viable option you have might come with the new "dynamic" type in .NET 4.0 and the ExpandoObject - an object that can take on any shape, have any properties of any type, and basically be whatever you want it to be.

You could imagine a mapping between an EAV structure in SQL Server and an ExpandoObject in C# 4.0 - but I highly doubt the EF team has done anything in this respect, and quite honestly, I don't think they will any time soon. But this might be a possibility for you to explore.

For the ExpandoObject, see:

• its MSDN documentation
• C# 4.0: Introducing the Expando Object
• here on SO: What are the true benefits of ExpandoObject?
• Dynamic in C# 4.0 - Introducing the ExpandoObject