First get the value in the mapping table in Entity Framework code

Question

I use EF 4.1 code first , and I fight the association object and get the value that was set in the association table. I tried to follow the message :. Create code first, many to many, with additional fields in the association table

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

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 are:

 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 make 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 I am returning:

 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 the absence of keys mean? Would ProductId and SpecificationId display the product identifier and specification identifier, respectively?

How can I return a single product with all its specifications?

Answer 1

The Entity Framework recognizes that ProductId is the foreign key property for the Product navigation property, and SpecificationId is the foreign key property for the Specification navigation property. But the exception complains about the missing primary key ("Key" = "Primary Key") on your ProductSpecification object. Each object requires a specific key property. This can happen either by agreement - by the specific name of the key property, or by simplicity with data annotations or the Fluent API.

Your ProductSpecification class does not have a property that EF recognizes as a key by convention: No Id property and no ProductSpecificationId (class name + "Id").

Therefore, you must define this explicitly. Defining it with data annotations is shown in the message 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 the Fluent API, it will be:

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

Both methods define a composite key, and each part is a foreign key in the Product or Specification table at the same time. (You do not need to explicitly define the properties of FK because EF recognizes it because of its conditional names.)

You can return the product, including all specifications, with a downloaded download, for example:

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