How to read dynamic properties from database

Question

I will try to explain my scenario the best way I can. I have the following tables in my database:

Products{ProductId, CategoryId ...}
Categories{CategoryId ...}
CategoryProperties{CategoryPropertyId, CategoryId, Name ...}
PropertyValues{ProductId, CategoryPropertyId, Value ...}

So the goal is to have list of products which belongs to some category and each category can have 'n' number of properties with values in the 'PropertyValues' table. I have a query that returns data based on 'categoryId' so I can always get different results from the database:

For the CPU category:

intel i7 | CPU | Model | Socket | L1 Cash | L2 Cahs | etc.

For the HDD category:

samsung F3 | HDD | Model | Speed | GB | etc.

So based on categories from my query as a result I always can get different column numbers and names. For database access I use simple ADO.NET call to stored procedure that return result. But because query result is dynamic in it's nature I don't know what is a good way to read this data.

I made a Product entity but I am confused how to make it really :( I thought that I can make a Product entity and make other entities which inherit Product like Cpu, Hdd, Camera, PcCase, GraphicCard, MobilePhone, Gps etc.

but I think that it's stupid because I can end this with 200+ entities in domain.

What would you do in this situation?
How to read and where to put this dynamic properties?

UPDATE - some solution

All right based on @millimoose suggestion for Dictionary and @Tim Schmelter idea to use DataTable object I came to some solution.
Now... this works I get data read them and I can display them.
But I still need advice from smarter people than me on am I did this good or should I handle this better or I am made some spageti code. So here what I did:

public class Product
    {
        public Product()
        {
            this.DynamicProperties = new List<Dictionary<string, string>>();
        }

        public List<Dictionary<string, string>> DynamicProperties { get; set; }

    }
...
List<Product> products = new List<Product>();
...
using (SqlDataAdapter a = new SqlDataAdapter(cmd))
                {
                    DataTable t = new DataTable();
                    a.Fill(t);

                    Product p = null;

                    foreach (DataRow row in t.Rows)
                    {
                        p = new Product();
                        foreach (DataColumn col in t.Columns)
                        {
                            string property = col.ColumnName.ToString();
                            string propertyValue = row[col.ColumnName].ToString();

                            Dictionary<string, string> dictionary = new Dictionary<string, string>();

                            dictionary.Add(property, propertyValue);

                            p.DynamicProperties.Add(dictionary);
                        }

                        products.Add(p);
                    }
                }

Is there any reason you need to have those properties available in your model classes, instead of just having a `Product` have a single `Dictionary` (or something like that)? — millimoose, Mar 04 '13 at 21:28
The easiest approach is to use `DataTables`. Then you just need a sql-query and a `SqlDataAdapter` to fill the table. — Tim Schmelter, Mar 04 '13 at 21:31
@millimoose I thought about `Dictionary` at first. But I wan't to know is there some different solution for this scenario. Or `Dictionary` is actually the only or best solution. — 1110, Mar 04 '13 at 21:31
@1110 Then it seems you're asking for a security blanket. How about you try and use `Dictionary` which seems like the obvious choice, and if that leads to a problem in your design, come back and ask about said problem? — millimoose, Mar 04 '13 at 21:36
@1110 You might also look at Steve Yegge's post about what he calls [the Properties pattern](http://steve-yegge.blogspot.sk/2008/10/universal-design-pattern.html#Who_uses). Disclaimer: it's long and he has a tendency to go off the rails possibly more than once per post. — millimoose, Mar 04 '13 at 21:38
millimoose and Tim Schmelter based on your comments I came up with some solution. Can you check update at my question? — 1110, Mar 04 '13 at 23:21

score 2 · Answer 1 · edited May 23 '17 at 11:53

Your original approach

public class Product
{
    public List<Dictionary<string, string>> DynamicProperties { get; set; }
}

is quite good. I would use a custom collection though, with better naming so the intent is clearer.

You could also utilize C# 4.0's dynamic feature which more cool, but I'm not sure what benefit it gives in your case. You could do something like,

public class Product
{
    public List<dynamic> DynamicProperties { get; set; }
}

...

conn.Open();
using (var reader = cmd.ExecuteReader())
{
    var p = new Products();
    p.DynamicProperties = reader.AsDyamic().ToList();

    //or

    foreach (var item in reader.AsDynamic())
        yield return item;
}

// carry this extension method around
public static IEnumerable<dynamic> AsDynamic(this IDataReader reader)
{
    var names = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
    foreach (IDataRecord record in reader as IEnumerable)
    {
        var expando = new ExpandoObject() as IDictionary<string, object>;
        foreach (var name in names)
            expando[name] = record[name];

        yield return expando;
    }
}

Somewhat related question: How to convert a data reader to dynamic query results

Keith Nicholas · Accepted Answer · 2013-03-04T23:48:15.750

1

You have a product, a bunch of categories, and each category has a bunch of properties.

class Product
{
    public int Id { get; set; }
    public Category Category { get; set; }
    public List<ProductProperty> Properties { get; set; }
}

class Category
{
  public int Id { get; set; }
  public string Name { get; set; }
}

class ProductProperty
{
   public  int Id { get; set; }
   public  string Name { get; set; }
   public string Value { get; set; }
}

you could store the product properties in a list / dictionary

then you just add properties to the product

Properties.Add(new ProductionProperty() { Name="intel i7"});

or if its name/value

Properties.Add(new ProductionProperty() { Name="Speed", Value="150MB/s"});

edited Mar 04 '13 at 23:48

answered Mar 04 '13 at 21:47

Keith Nicholas

43,549
15
93
156

I can't handle it like this. `Properties` property should be dynamic so I can't read it as I don't know name of properties. – 1110 Mar 04 '13 at 21:55
it is "dynamic" you just populate each ProductProperty with whatever comes back – Keith Nicholas Mar 04 '13 at 23:47

How to read dynamic properties from database

2 Answers2

Linked