CODEWITHSUNDEEP
c#entity-framework-coreentity-relationshipdata-warehousestar-schema
fass33443423
fass33443423

Reputation: 117

StarSchema - Entity Framework Core - Migration

I am trying to build a datawarehouse (DWH), using the code-first approach (star-schema):

Fact-/dimension classes:

    [Table("FactBase")]
    public class FactBase
    {
        [Key]
        public Guid Id { get; set; }

        [ForeignKey("DimTest1")]
        public string DimDigitalesBuchISBN { get; set; }

        public virtual DimTest1 DimTest1 { get; set; }  
    }


    [Table("DimTest1")]
    public class DimTest1
    {
        [Key]
        public string ISBN { get; set; }

        public string Bla { get; set; }
    }

Context:

public class XDBContextDWH : DbContext
{      
    public DbSet<FactBase> FactBase { get; set; }
    public DbSet<DimTest1> DimTest1 { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(new string("connection string"));
    }
}

After using migration the schema looks like this:

Here

Based on that star schema, shouldn't be the relationship (here: SQL) like that?

here

Upvotes: 1

Views: 585

Answers (2)

Anonymous
Anonymous

Reputation: 1

As you imply is your question, the star schema Fact table should use a composite key made up of the foreign keys it's referencing. So I would say there are a couple issues with your situation that should be addressed. First, a fact table probably shouldn't have a a column called Id, though it's not really going to hurt anything, it probably wouldn't ever be used to query by, so you are just adding extra data taking up disk space. Second, and probably the answer you are looking for is, if you want a composite primary key on your fact table, you need to specify that in the Database Context.


protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<FactBase>()
        .HasKey(x => new { x.Id, x.DimDigitalesBuchISBN });
}

As I mentioned, you probably don't want to include the Fact.Id column in your PK but instead you would refer to more than one dimension like this:


protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<FactBase>()
        .HasKey(x => new { x.Dim1Id, x.Dim2Id, x.Dim3Id});
}

Where Dim1Id, Dim2Id and Dim3Id are the primary keys of your dimensions. I should also mention that you need to remove the [Key] attribute from the Id field of your FactBase class.

refer to: https://learn.microsoft.com/en-us/ef/core/modeling/keys?tabs=data-annotations

Upvotes: 0

Ortiga
Ortiga

Reputation: 8824

When you specify the attribute [ForeignKey("DimTest1")], you're telling EF to use DimTest1 as the navigation property of the FactBase class, not pointing to the DimTest1 class.

But since that property does not exist, it does not create the relationship.

Change your class to:

[Table("FactBase")]
public class FactBase
{
    [Key]
    public Guid Id { get; set; }

    [ForeignKey("DimTest1")]
    public string DimDigitalesBuchISBN { get; set; }
    public virtual DimTest1 DimTest1 { get; set; } // navigation property
}

This should make it work as intended.

Upvotes: 0

Related Questions