How to create multiple table references to one table - Entity framework

asp.net c# database entity-framework-6 sql-server

Question

enter image description here

We have a requirement that several tables need to be referred in a one table as shown in the diagram.

Please note that the diagram is NOT the correct DB model but just represent what our requirement. Can you suggest a proper way to implement above in SQL Server DB and Entity Framework 6?

Example: A sales order (in SalesOrder table) can have multiple files and those uploaded file details will be stored in UploadedFile table. Likewise OrderTable and Invoice too.

So we need to have a proper DB model with FK relationships between FileUpload table with each other related table.

Note: All tables PKs are auto-increment int values and we may need add more entities (tables) in future

1
3
10/23/2018 3:33:01 PM

Popular Answer

In order to provide you an alternative, as you said you are using Entity Framework, here is a sample of Code First implementation done on C#. You can create and update the schema via Package Manager Console migrations, within Visual Studio. I have used the Fluent API in order to define the relationships, as this is recommended over the alternative.

public class SampleContext : DbContext
{
    public SampleContext()
        : base("name=YourConnection")
    {
    }

    public DbSet<SalesOrder> SalesOrders { get; set; }
    public DbSet<CreditOrder> CreditOrders { get; set; }
    public DbSet<Invoice> Invoices { get; set; }
    public DbSet<UploadedFile> UploadedFiles { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SalesOrder>()
            .HasKey(so => so.Id);

        modelBuilder.Entity<CreditOrder>()
            .HasKey(co => co.Id);

        modelBuilder.Entity<Invoice>()
            .HasKey(i => i.Id);

        modelBuilder.Entity<UploadedFile>()
            .HasKey(u => u.Id);

        modelBuilder.Entity<UploadedFile>()
            .HasRequired(u => u.SalesOrder)
            .WithMany(s => s.UploadedFiles)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<UploadedFile>()
            .HasRequired(u => u.CreditOrder)
            .WithMany(c => c.UploadedFiles)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<UploadedFile>()
            .HasRequired(u => u.Invoice)
            .WithMany(c => c.UploadedFiles)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<UploadedFile>()
            .Property(uf => uf.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        modelBuilder.Entity<SalesOrder>()
            .Property(so => so.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        modelBuilder.Entity<CreditOrder>()
            .Property(co => co.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        modelBuilder.Entity<Invoice>()
            .Property(i => i.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        base.OnModelCreating(modelBuilder);
    }
}

// Collections of navigation properties should be included in classes for a one-to-many relationship

public class SalesOrder
{
    public int Id { get; set; }
    public string MyColumn { get; set; }
    public IList<UploadedFile> UploadedFiles { get; set; }
}

public class CreditOrder
{
    public int Id { get; set; }
    public string MyColumn { get; set; }
    public IList<UploadedFile> UploadedFiles { get; set; }
}

public class Invoice
{
    public int Id { get; set; }
    public string MyColumn { get; set; }
    public IList<UploadedFile> UploadedFiles { get; set; }
}

public class UploadedFile
{
    public int Id { get; set; }
    public SalesOrder SalesOrder { get; set; }
    public CreditOrder CreditOrder { get; set; }
    public Invoice Invoice { get; set; }
    public string FilePath { get; set; }
    public string FileType { get; set; }
}

public class SalesOrder
{
    public int Id { get; set; }
    public string MyColumn { get; set; }
    public IList<UploadedFile> UploadedFiles { get; set; }
}

public class CreditOrder
{
    public int Id { get; set; }
    public string MyColumn { get; set; }
    public IList<UploadedFile> UploadedFiles { get; set; }
}

public class Invoice
{
    public int Id { get; set; }
    public string MyColumn { get; set; }
    public IList<UploadedFile> UploadedFiles { get; set; }
}

public class UploadedFile
{
    public int Id { get; set; }
    public SalesOrder SalesOrder { get; set; }
    public CreditOrder CreditOrder { get; set; }
    public Invoice Invoice { get; set; }
    public string FilePath { get; set; }
    public string FileType { get; set; }
}
2
10/23/2018 4:16:33 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow