First matching mapping tables in EF 4.1 code

Question

I'm not sure how to map the following tables below in EF 4.1 code first and what objects I need to represent the tables. How to get a list of product specifications?

Currently, I only have a Product class.

 Products Table: Id Name IsActive ProductSpecification Table: ProductId SpecificationId Specifications Table: Id Name IsActive 

ProductSpecifications - table of associations. I also have the following definition in my context class:

 public DbSet<Product> Products { get; set; } 

EDIT

See my updated original post. I have changed the tables of product identifiers and specifications.

In my class class, I have the following:

 public DbSet<Product> Products { get; set; } public DbSet<Specification> Specifications { get; set; } 

In my repository, I have the following:

 public Product GetById(int id) { return db.Products .Include("Specifications") .SingleOrDefault(x => x.Id == id); } 

My Product class (partial):

 public class Product : IEntity { public int Id { get; set; } public string Name { get; set; } public bool IsActive { get; set; } public ICollection<Specification> Specifications { get; set; } } 

My Specification class :

 public class Specification : IEntity { public int Id { get; set; } public string Name { get; set; } public bool IsActive { get; set; } public ICollection<Product> Products { get; set; } } 

That is all I have made from the answer of Slauma. I didn’t do the mapping manually, as he said I should, but I need to understand the following first:

Given my classes and tables above, what exactly do EF 4.1 naming conventions conclude in how they handle association tables? The reason I'm asking is because I get the following error in the GetById method:

 Invalid object name 'dbo.SpecificationProducts'. 

EDIT 2

I forgot to mention the following: a product may have a specification height as a value. And for this height I need to specify a value. Like 100 inches. So I changed the ProductSpecifications table to a column of SpecificationValue values, this column will contain a value of 100 inches. How do I change the code to get this value? I need to display it in my view.

Answer 1

In a many-to-many relationship, you only define classes for the objects you want to associate, but not an entity for an association table. This table is "hidden" in your model and is managed automatically by the Entity Framework. So you can define these classes:

 public class Product { public int ProductId { get; set; } public string Name { get; set; } public ICollection<Specification> Specifications { get; set; } } public class Specification { public int SpecificationId { get; set; } public string Name { get; set; } public ICollection<Product> Products { get; set; } } 

This is usually enough to define a many-to-many relationship. EF will create a join table from this mapping. If you already have such a table in the database and its naming does not correspond to the exactly named Entity Framework conventions, you can define the mapping manually in the Fluent API:

 modelBuilder.Entity<Product>() .HasMany(p => p.Specifications) .WithMany(s => s.Products) .Map(c => { c.MapLeftKey("ProductId"); c.MapRightKey("SpecificationId"); c.ToTable("ProductSpecification"); }); 

Edit

Then you can load the product specifications using Include , for example:

 var productWithSpecifications = context.Products .Include(p => p.Specifications) .SingleOrDefault(p => p.ProductId == givenProductId); 

This will download the product along with the specifications. If you only need specifications for this product identifier, you can use the following:

 var specificationsOfProduct = context.Products .Where(p => p.ProductId == givenProductId) .Select(p => p.Specifications) .SingleOrDefault(); 

... which returns a set of specifications.

Edit 2

EF Code-First naming conventions will take the name of a join table, constructed as a combination of two related class names, and then pluralize it. Thus, without explicite being displayed in your table name, ProductSpecification EF will accept ProductSpecifications (Plural) and build queries with that name in the form of a table name. Since this table does not exist in the database, you get an exception "Invalid object name" dbo.SpecificationProducts ". When you run the query. Therefore, you must either rename the table in the database or use the matching code above.

Edit 3

I would highly recommend using explicite mapping anyway, because the name of the EF join table suggests depending on the order of DbSets in your context . By changing the order of these sets, the join table may be SpecificationProducts . Without mapping explicite to a fixed (usually unimportant) table name, swapping sets in context can break your production application.