How to create a "dynamic" database structure (EF Code-First)

I'm trying to implement web application using Entity Framework Code-First. I'll explain the problem on such example:

In my database there is a collection of products.

public class Product
{
    public long Id { get; set; }
    public string Name { get; set; }
    public virtual Type type { get; set; }
    //..... Some other properties .....
}

Each product has it's own type (food, drugs, multimedia, etc.). Also in my database'll be a collection of this types and it will be defined by the end-user and might be modified/increased in the future.

public class Type
{
    public long Id { get; set; }
    public string Name { get; set; }
    //..... Some other properties .....
}

As you know, each product has it's own properties depends on the product's type. Lets say drugs may have

public bool PrescriptionOnly { get; set; }

and multimedia type may has

public Size DisplaySize { get; set; }

As I mentioned before types'll be defined by the end-user so count of properties and data type of each property is undefined right now. Moreover, user should be able to filter products by the specific values of properties (filtering model depends on the product type). And this all should be implemented using Code-First model.

To sum up I've got stuck because I don't know how to create such "dynamic" database structure with EF Code-First. I though about creating one string field inside Type class and save [key=value] pairs but this will make almost impossible to create fast and efficient filltering with paging results.

I'll be gratefull for any suggestion or solution to my problem.

Best regards! Lukasz

I've created such sample code to visualise the problem. The database structure looks like this:

Category1 = "Food" [Property1 = "ForVegetarian", Property2 = "Calories"] ## - Product1 = "Pizza" ["false", "1500"] - Product1 = "Salad" ["true", "300"]

Category2 = "Multimedia" [Property1 = "DisplaySize", Property2 = "Warranty"] ## - Product1 = "PlasmaTV" ["55''", "36m"] - Product1 = "LCDMonitor" ["24''", "12m"]

public class Property
{
    public long Id { get; set; }
    public string Name { get; set; }
}

public class ProductCategory
{
    public long Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Property> Properties { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

public class PropertyValue
{
    public long Id { get; set; }
    public virtual Property Property { get; set; }
    public string Value { get; set; }
}

public class Product
{
    public long Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<ProductCategory> Categories { get; set; }
    public virtual ICollection<PropertyValue> Properties { get; set; }
}

public class TestDataBase : DbContext
{
    public DbSet<ProductCategory> Categories { get; set; }
    public DbSet<Property> Properties { get; set; }
    public DbSet<PropertyValue> Values { get; set; }
    public DbSet<Product>   Products    { get; set; }
    public TestDataBase()
    { }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    { }
    public static readonly TestDataBase context = new TestDataBase();
}

public class TestDataBaseInitializer : DropCreateDatabaseIfModelChanges<TestDataBase>
{
    protected override void Seed(TestDataBase context)
    {
        ProductCategory cat1 = new ProductCategory
                               {
                                   Name = "Food",
                                   Properties = new List<Property>(),
                                   Products = new List<Product>()
                               };
        Property prop1_1 = new Property
                           {
                               Name = "ForVegetarian"
                           };
        Property prop1_2 = new Property
                           {
                               Name = "Calories"
                           };
        cat1.Properties.Add(prop1_1);
        cat1.Properties.Add(prop1_2);
        Product product1_1 = new Product
                             {
                                 Name = "Pizza",
                                 Categories = new List<ProductCategory>(),
                                 Properties = new List<PropertyValue>()
                             };
        product1_1.Categories.Add(cat1);
        PropertyValue val1_1_1 = new PropertyValue 
                                 {
                                     Property = prop1_1,
                                     Value = "false"
                                 };
        PropertyValue val1_1_2 = new PropertyValue
                                 {
                                     Property = prop1_2,
                                     Value = "1500"
                                 };
        product1_1.Properties.Add(val1_1_1);
        product1_1.Properties.Add(val1_1_2);
开发者_如何学C        cat1.Products.Add(product1_1);
        Product product1_2 = new Product
        {
            Name = "Salad",
            Categories = new List<ProductCategory>(),
            Properties = new List<PropertyValue>()
        };
        product1_2.Categories.Add(cat1);
        PropertyValue val1_2_1 = new PropertyValue
        {
            Property = prop1_1,
            Value = "true"
        };
        PropertyValue val1_2_2 = new PropertyValue
        {
            Property = prop1_2,
            Value = "300"
        };
        product1_2.Properties.Add(val1_2_1);
        product1_2.Properties.Add(val1_2_2);
        cat1.Products.Add(product1_2);
        //--------------------------------------------------------------------------------
        ProductCategory cat2 = new ProductCategory
                               {
                                   Name = "Multimedia",
                                   Properties = new List<Property>(),
                                   Products = new List<Product>()
                               };
        Property prop2_1 = new Property
                           {
                               Name = "DisplaySize"
                           };
        Property prop2_2 = new Property
                           {
                               Name = "Warranty"
                           };
        cat2.Properties.Add(prop2_1);
        cat2.Properties.Add(prop2_2);
        Product product2_1 = new Product
                             {
                                 Name = "PlasmaTV",
                                 Categories = new List<ProductCategory>(),
                                 Properties = new List<PropertyValue>()
                             };
        product2_1.Categories.Add(cat2);
        PropertyValue val2_1_1 = new PropertyValue
                                 {
                                     Property = prop2_1,
                                     Value = "55''"
                                 };
        PropertyValue val2_1_2 = new PropertyValue
                                 {
                                     Property = prop2_2,
                                     Value = "36m"
                                 };
        product2_1.Properties.Add(val2_1_1);
        product2_1.Properties.Add(val2_1_2);
        cat2.Products.Add(product2_1);
        Product product2_2 = new Product
        {
            Name = "LCDMonitor",
            Categories = new List<ProductCategory>(),
            Properties = new List<PropertyValue>()
        };
        product2_2.Categories.Add(cat2);
        PropertyValue val2_2_1 = new PropertyValue
        {
            Property = prop2_1,
            Value = "24''"
        };
        PropertyValue val2_2_2 = new PropertyValue
        {
            Property = prop2_2,
            Value = "12m"
        };
        product2_2.Properties.Add(val2_2_1);
        product2_2.Properties.Add(val2_2_2);
        cat2.Products.Add(product2_2);
        context.Properties.Add(prop1_1);
        context.Properties.Add(prop1_2);
        context.Properties.Add(prop2_1);
        context.Properties.Add(prop2_2);
        context.Values.Add(val1_1_1);
        context.Values.Add(val1_1_2);
        context.Values.Add(val1_2_1);
        context.Values.Add(val1_2_2);
        context.Values.Add(val2_1_1);
        context.Values.Add(val2_1_2);
        context.Values.Add(val2_2_1);
        context.Values.Add(val2_2_2);
        context.Categories.Add(cat1);
        context.Categories.Add(cat2);
        context.SaveChanges();
    }
}

Now lets say I'm inside Multimedia category:

        var category = (from c in TestDataBase.context.Categories
                        where c.Name == "Multimedia"
                        select c).First();

I know that this category has two properties: DisplaySize and Warranty Lets say I would like to select all products (as IEnumerable) which are in Multimedia category (note that products may be inside more than one category).

        var categoryProducts = (from c in TestDataBase.context.Categories
                                where c.Name == "Multimedia"
                                select c.Products).First();

Moreover, I must filter this set of products by the DisplaySize property. I would like to select those products which:

• has NOT NULL DisplaySize property
• DisplaySize == 55''

And here goes a problem: I don't know how to specify in LINQ to Entity to select such products because each product has its own collection of PropertyValue objects - NOT just one PropertyValue.

Could anyone give me a help. Thank you in advance!

OK. I think I've solved my problem.

    var categoryProducts = (from p in category.Products
                                from c in p.Properties
                                where c.Property.Name == "DisplaySize" &&
                                      c.Value == "55''"
                            select p).ToList();

Here is the good article about such selection: http://weblogs.asp.net/salimfayad/archive/2008/07/09/linq-to-entities-join-queries.aspx