How to map entities to dimension table which includes a hierarchy?

Consider the following hierarchy:

Department -> Category -> Product

(Each department contains multiple categories, each of which contains multiple products.)

Using the Kimball approach to dimensional modeling, I have created a ProductDim table with the following columns:

ProductKey
Product
Category
Department

I'm trying to use EF 4.1 to map my Department, Category, and Product entities to the ProductDim table. Here is a simplified version of the relevant classes:

public class Department
{
    public string Name { get; set; }
}

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

public class Product
{
    public string Name { get; set; }
}

public class MyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Department>().ToTable("ProductDim");
        modelBuilder.Entity<Department>().HasKey(t => t.Name);
        modelBuilder.Entity<Department>().Property(t => t.Name).HasColumnName("Department");

        modelBuilder.Entity<Category>().ToTable("ProductDim");
        modelBuilder.Entity<Category>().HasKey(t => t.Name);
        modelBuilder.Entity<Category>().Property(t => t.Name).HasColumnName("Category");

        modelBuilder.Entity<Product>().ToTable("ProductDim");
        modelBuilder.Entity<Product>().HasKey(t => t.Name);
        modelBuilder.Entity<Product>().Property(t => t.Name).HasColumnName("Product");
    }
}

The problem is that when I try to use these classes, I get the following exception:

System.InvalidOperationException : The entity types 'Category' and 'Department' cannot share table 'ProductDim' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them.

Is there any workaround f开发者_Python百科or this? And if not, can Entity Framework be used successfully with dimensionally-modeled databases?

Entity framework can work only with correctly modelled and normalized relational (OLTP) database. Data warehousing is for business intelligence (OLAP) tools not for ORM mapping. You will not map your table to your entities - entity framework is not able to do that.

As error says EF allows mapping multiple entities to the same table only if:

• You are using Table per type inheritance (that means your entities will have to be in inheritance hierarchy). Your entities are obviously not part of the same inheritance structure.
• You are using Table splitting where one table is split into multiple entities related in one-to-one relation.

Something about you code just doesn't seem right. You should create you Product class like the example below.

public class Department
{
    public string Name { get; set; }
    public List<Category> Categories { get; set; }
}

And you Category class should look like this.

public class Category
{
    public string Name { get; set; }
    public List<Product> Products { get; set; }
}

This follows you logic of Department has many Categories which has many Products.