Taking advantage of the Entity Framework's many-to-many relationships when the joining table has an extra field

A scenario:

In my database I have a table joining Customers and Movies together, with an extra field specifying the weight of the relation. EG:

CustomersMovies
  | -CustomerID
  | -MovieID
  | -Weight 

If I only had the first two fields, the Entity Framework would recognise the many-to-many relationship and I would be able to call Customer.Movies and Movie.Customers. With the third field, this relationship is broken.

Ideally, I would love to be able to go Customer.Movies[0].Weight or something similar to return the joining parameter. Even if this is not suppor开发者_C百科ted, I would still like the many-to-many relationship for other functions.

Is anything like this supported within the Entity Framework? I am almost about to create two tables, CustomersMovies (which just joins the two tables) and a CustomerMovieWeights table which specifies the weight for a given customer and movie, but the redundant data isn't ideal.

Kind Regards, Harry

If you dropped the Weight column from the junction table, then you would only need to map Customers and Movies table.

If not, you will need to map all three tables: Customer, CustomerMovies and Movies.

But of course, you will not be able to do this:

var customer = ctx.Customers.First();
var customerMovies = customer.Movies;

You will have to do this:

var customer = ctx.Customers.First();
var customerMovies = customer.CustomerMovies.Movies;

Because Customer will not have a direct navigational property to Movie - it will have a navigational property to CustomerMovie (and vice versa).

Honestly though, i would go with your last idea. Have the CustomerMovies table contain only the FK's (CustomerId, MovieId), and another "meta-data-like" table for weights.

This way you can map your many-to-many properly in the EDMX.