Entity Framework Many To Many Issues

Question

I have a Many To Many Relationship using a linking table which contains additional information. 3 Entities ,LHS, RHS, LHSRHS LHS 1 - * with LHSRHS RHS 1 - * with LHSRHS

I use the designer to generate EDMX based on the tables

I have 2 issues 1) If a create all 3 entities (new ones) link them up and try to persist i get a FK violation as it tries to insert into the LHSRHS tables before inserting into the RHS table

2) If I load a LHS object from the DB, then try to create a RHS object and link it to the LHS object i get a FK violation. If I load a RHS object from the DB, then try to create a LHS object and link it to the RHS object i works perfectly.

CODE for this :

        //WORKS
        eftestEntities es = new eftestEntities();
        RHS existing = es.RHS.First();

        LHS newObject = new LHS();
        newObject.ID = "LHs1";
        newObject.Name = "LHs1";
        LHSRHS link = new LHSRHS();

        link.Something = "some";

        link.LHS = newObject;

        existing.LHSRHS.Add(link);

        es.SaveChanges();

        ...........

        //DOES NOT WORK
        eftestEntities es = new eftestEntities();
        LHS existing = es.LHS.First();

        RHS newObject = new RHS();
        newObject.ID = "RHSNEW1";
        newObject.Name = "RHSNEW";
        LHSRHS link = new LHSRHS();

        link.Something = "some";

        link.RHS = newObject;

        existing.LHSRHS.Add(link);

        es.SaveChanges();

Anyone have any ideas ?

Answer 1

The fact that it tried to insert the link before saving the rhs suggests that one of more of the relationships isn't marked as a foreign key: it shouldn't have done this (if correctly configured).

Does your link table have any extra data of its own? If not, you can actually remove this table from the model competely - simply associate the two interesting tables (many-to-many), and select the link table as the storage mechanism of the assication (you'll need to click on the link between the two tables in the designer).

Alternatively, if your link table has a compsoite key: "lhsid, rhsid", then I believe the tooling will do all this for you automatically.

Answer 2

If the link table has anything more than a composite key (ie, if the table has more than 2 columns) EF won't do the smart thing and set up the navigation properties properly. When you have just 2 columns set up as a composite key, the link table will "disappear" from the entities, and you'll get a navigation property that exposes the other entity as a collection of the related entities. I've heard EF v2 will support the scenario where there is extra metadata tagged along with the composite key :(

Answer 3

I found an example on the web which worked, sitting line by line with Beyond Compare renaming all my variables to match my variable the only differences I found where, ordering in sections, which I adjust to no avail, and the fact that they where using int PK's where I was using varchars changing my keys in ints makes it work. This ofcouse does not really help given my DB is 15 years old and has varchar PK in just about every table :(