none
Entity Framework Many to Many relationship missing referential constraint Database First RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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.

    Tuesday, March 26, 2013 11:12 PM
    |

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Bryan;

    In a Many-to-Many relationship in the Entity Framework if the table junction / bridge table only has the primary keys of the other two tables then the junction / bridge table will not show up in the designer and that is by design. Please see the articale at

    Entity Framework: Queries involving many to many relationship

    Not sure what you mean by this, "It then does the count on the server instead of in the db", seeming that the db is on the server.

    Please post the query that is causing the concern.

      

      

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, March 27, 2013 4:19 AM
    |