Retrieving the value in an association table in Entity Framework code first

Question

I am using EF 4.1 code first and I am struggling with the association entity and getting the value that was set in the association table. I tried to follow the post on: Create code first, many to many, with additional fields in association table.

My tables are as follows (all are in plural form):

Table: Products

Id int
Name varchar(50)

Table: Specifications

Id int
Name varchar(50)

Table: ProductSpecifications

ProductId int
SpecificationId int
SpecificationValue varchar(50)

My related classes:

public class Product : IEntity
{
     public int Id { get; set; }
     public string Name { get; set; }
     public virtual ICollection<ProductSpecification> ProductSpecifications { get; set; }
}

public class Specification : IEntity
{
     public int Id { get; set; }
     public string Name { get; set; }
     public virtual ICollection<ProductSpecification> ProductSpecifications { get; set; }
}

public class ProductSpecification
{
     public int ProductId { get; set; }
     public virtual Product Product { get; set; }
     public int SpecificationId { get; set; }
     public virtual Specification Specification { get; set; }
     public string SpecificationValue { get; set; }
}

My context class:

public class MyContext : DbContext
{
     public DbSet<Product> Products { get; set; }
     public DbSet<Specification> Specifications { get; set; }
     public DbSet<ProductSpecification> ProductSpecifications { get; set; }

     protected override void OnModelCreating(DbModelBuilder dbModelBuilder)
     {
     }
}

My repository method where I do my call (not sure if it is correct):

public class ProductRepository : IProductRepository
{
     MyContext db = new MyContext();

     public Product GetById(int id)
     {
          var product = db.Products
               .Where(x => x.Id == id)
               .Select(p => new
               {
                    Product = p,
                    Specifications = p.ProductSpecifications.Select(s => s.Specification)
               })
               .SingleOrDefault();

          return null;  // It returns null because I don't know how to return a Product object?
     }
}

Here is the error that I am getting back:

One or more validation errors were detected during model generation:

System.Data.Edm.EdmEntityType: : EntityType 'ProductSpecification' has no key defined. Define the key for this EntityType.
 System.Data.Edm.EdmEntitySet: EntityType: EntitySet �ProductSpecifications� is based on type �ProductSpecification� that has no keys defined.

What does it mean that no keys are defined? Won't the ProductId and SpecificationId map to Id of Product and Id of Specification respectively?

How would I return a single product with the all the specifications for it?

Slauma · Accepted Answer · 2011-11-04T14:09:41.550

Entity Framework will recognize that ProductId is a foreign key property for the Product navigation property and SpecificationId is a foreign key property for the Specification navigation property. But the exception complains about a missing primary key ("Key" = "Primary Key") on your ProductSpecification entity. Every entity needs a key property defined. This can happen either by conventions - by a specific naming of the key property - or explicity with data annotations or Fluent API.

Your ProductSpecification class doesn't have a property which EF would recognize as a key by convention: No Id property and no ProductSpecificationId (class name + "Id").

So you must define it explicitely. Defining it with data annotations is shown in the post you linked:

public class ProductSpecification
{
    [Key, Column(Order = 0)]
    public int ProductId { get; set; }
    public virtual Product Product { get; set; }

    [Key, Column(Order = 1)]
    public int SpecificationId { get; set; }
    public virtual Specification Specification { get; set; }

    public string SpecificationValue { get; set; }
}

And in Fluent API it would be:

modelBuilder.Entity<ProductSpecification>()
    .HasKey(ps => new { ps.ProductId, ps.SpecificationId });

Both ways define a composite key and each of the parts is a foreign key to the Product or Specification table at the same time. (You don't need to define the FK properties explicitely because EF recognizes it due to their convention-friendly names.)

You can return a product including all specifications with eager loading for example:

var product = db.Products
    .Include(p => p.ProductSpecifications.Select(ps => ps.Specification))
    .SingleOrDefault(x => x.Id == id);

Retrieving the value in an association table in Entity Framework code first

1 Answers1