Fluent API Mapping

Question

I have a database that has three tables structured as follows:

Table 1: Product
Columns: ProductID, PK
         SKU,       PK
         Name

Table 2: ProductImages
Columns: ImageID    PK
         SKU

Table 3: Images
Columns: ImageID    PK
         ImageContent

Ignore for a moment that table ProductImages looks to be in a many-to-many relation except that the Unique PK constraint is forcing to be one-to-one and therefore an unnecessary one-to-many-to-one table (it's an existing database).

I would like to have the following POCO entity class:

public class Product
{
    public int ProductId { get; set; }
    public string SKU { get; set; }
    public virtual Image Image { get; set; }
}

Assume Image is also an entity in my entity model.

I am using Entity Framework 5.0 with code first and the Fluent API and I am going nuts trying to figure out how to write the ProductMap class (which derives from EntityTypeConfiguration<Product>). Specifically the relationship mappings.

The rendered SQL should look like Entity Framework's version of the following:

select p.SKU, p.Name, p.ProductID, I.ImageID, I.ImageContent
from Products p
inner join ProductImages si on p.SKU = si.SKU
inner join Images i on i.ImageId = si.ImageId

Any help that anyone can provide will be met with heart felt appreciation.

Answer 1

I figured it out. There was a logical disconnect between by database structure and my Entity model.

The productImages table supports a *-1 mapping therefore the Products POCO class needs to have a collection of ProductImages. Then the entities need to be configured as follows:

public class ProductImagesMap : EntityTypeConfiguration<ProductImage>
{
        //Other field and key configuration...

        this.HasRequired(t=>t.Image).WithRequiredDependent();

        this.HasRequired(t => t.Product)
        .WithMany(t => t.ProductImage)
        .HasForeignKey(d => d.SKU);
}

public class ProductImage
{
    public int ImageId { get; set; }
    public string SKU{ get; set; }
    public virtual Image Image { get; set; }
    public virtual Product Product { get; set; }
}

public class Product
{
    public Product()
    {
        this.Features = new List<Feature>();
    }

    public int ProductId { get; set; }
    public string Name { get; set; }
    public string SKU{ get; set; }
    public virtual Brand Brand { get; set; }
    public virtual ICollection<ProductImage> ProductImages { get; set; }
}

I struggled with trying to configure this relationship in the ProductMap class (as Products in the parent/mater table), it was not until configured it from the ProductImages class that it worked so my two open questions are:

1) What rules determine which entity drives the relationship configuration?

2) Is there a way I can configure the Products POCO class to have an ICollection Images property and bypass the ProductImage entity all-together as ProductImage only serves as a link table between products and images?

Answer 2

I had the same problem until I started using Entity Framework Power Tools

Using it you can generate clear entities like a business objects and mapping classes. Good article that helped me to create amazing data access layer: Reverse Engineer Code First

And i think mapping should looks like:

public class ProductMap : EntityTypeConfiguration<Product>
{
    public ProductMap ()
    {
        // Primary Key
        this.HasKey(t => t.ProductId);

        // Properties
        this.Property(t => t.Name)
            .IsRequired()
            .HasMaxLength(256);

        // Table & Column Mappings
        this.ToTable("Product");
        this.Property(t => t.ProductId).HasColumnName("ProductID");
        this.Property(t => t.Name).HasColumnName("Name");

        // Relationships
        this.HasMany(t => t.Products)
            .WithMany(t => t.Images)
            .Map(m =>
            {
                m.ToTable("ProductImages");
                m.MapLeftKey("ProductID");
                m.MapRightKey("ImageID");
            });
        }
    }