I'm working on an Asp.net MVC3 project using Database First in Entity Framework 4.1. When creating the model, I selected the option to add code generation objects, which created a separate cs file for each generated class. When looking at my model (in the edmx
file), i noticed that every many to many association is missing a referential constraint, while all of the 1 to many relationships have them. I believe this may be causing a problem I am noticing.
The tables in question look like this
table1 cols: id (pk), etc...
table2 cols: id (pk), etc...
joint table cols: table1id, table2id (the pk is both cols)
The table1 objects generated by my model correctly have an Icollection, and vice versa. However, if i try to do something along the lines of t1Obj.t2objs.Count, i notice the query being sent to the database is selecting all of the t2 objects in t1, and not
just the count of them. It then does the count on the server instead of in the db. There can potentially be tons of different t2 objects in a given t1, so takes a long time to run.
I have a feeling the lack of a referential constraint is causing this, but I am not positive. I also notice the same thing happening with another many to many relationship, although there are very few objects in it so it doesn't make anything slow.
When I tried to manually add a referential constraint in the UI for the edmx file, it just deleted all of the cs files in the model and gave me these errors.
Error 1 Running transformation: Multiplicity is not valid in role 'table1' in relationship 'joint table name'. Valid values for multiplicity for Principal Role are '0..1' or '1'.
Error 2 Running transformation: Multiplicity is not valid in Role 'table2' in relationship 'joint table'. Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be 1.
I believe these errors have something to do with the fact that neither role is really principal or dependent. But I'm not positive as i'm not sure the purpose of the referential constraint.