2

Database Structure
I've got a very denormalized SQL table with a structure like this:

CREATE TABLE logistix.shipments
(
    shipment_id INT NOT NULL PRIMARY KEY,
    destination_id NVARCHAR(15) NOT NULL PRIMARY KEY,
    pallet_id INT NOT NULL PRIMARY KEY,
    destination_order INT NOT NULL,
    pallet_description NVARCHAR(40) NOT NULL
)

While each particular record is unique, one shipment can have multiple pallets going to multiple destinations.

.NET Interface
This is going to be manipulated by EF objects, which I want to structure like this:

class ShippingContext : DbContext
{
        public virtual DbSet<Shipment> Shipments {get; set;}
}

class Shipment
{
    int ShipmentId {get; set;}
    List<Destination> ShipmentStops {get; set;}
}

class Destination
{
    string DestinationId {get; set;}
    int DestinationOrder {get; set;}
    List<Pallet> Pallets {get; set;}
}

class Pallet
{
    int PalletId {get; set;}
    string PalletDescription {get; set;}
}

The Problem
While I've found tutorials on splitting tables into one-to-one entities and on mapping foreign-keyed data to collections in EF, I can't find anything about mapping columns from one table into collections. Is this possible, or am I limited to splitting the table, creating views, or creating a POCO class with a property for each column?

Endmatter
Another application will access the SQL table to generate reports on an arbitrary number of shipments, so the Powers That Be chose to use a denormalized table for performance's sake, rather than a suite of normalized tables and a view, which would take longer to retrieve.

JAF
  • 385
  • 1
  • 2
  • 12

1 Answers1

2

Your classes should look something link this

public class ShipmnetContext : DbContext
{
    public DbSet<Shipment> Shipments { get; set; }
    public DbSet<Destination> Destinations { get; set; }
    public DbSet<Pallet> Pallets { get; set; }  
}

public class Shipment
{
    public int ShipmentId { get; set; }
    public ICollection<Destination> ShipmentStops { get; set; }

    public Shipment()
    {
        ShipmentStops = new HashSet<Destination>();
    }
}

public class Destination
{
    [Key]
    public string DestinationId { get; set; }
    public int DestinationOrder { get; set; }
    //[Required]
    public Shipment Shipment { get; set; } //Foreign key to Shipment table, make property NotNull by adding [Required] attribute
    public ICollection<Pallet> Pallets { get; set; }

    public Destination()
    {
        Pallets = new HashSet<Pallet>();
    }
}

public class Pallet
{
    public int PalletId { get; set; }
    public string PalletDescription { get; set; }
    public Destination Destination { get; set; } //Foreign key to Destination table
}
Rodion Shishkov
  • 41
  • 3