Foreign Keys in the O/R Designer Visual Studio 2008 RRS feed

  • Question

  • Please forgive me if I am doing something bone headed, but it seems that if I copy and paste properties or classes while working on the design surface, I end up wit messy problems when creating associations.

    Unfortunately, the true nature of the problem is hard to determine, as it seems to intermittent, but here is the things I have done and found...aplogies if it is a bit detailed, but there couls be something in the way i've done it that sheds some light!

    I initially used "sqlmetal /dbml:db.dbml /namespace:test db.sdf"
    I then "Added Existing Item" (the .dbml file rceated in the last step) to my console project...
    I then deleted all of the associations that had been inferred by clicking on the link and hitting the delete key...

    So let's assume now that I have a class called Product(PK=ID), and a class called PriceHistory(PK=ProductID,EffectiveDate)

    I create an association between them, from Product --> PriceHistory using the ID->ProductID and "one-to-many" settings.

    If then have a mainline console program that executes:

                Pricebook PB = new Pricebook(@"Data Source=|DataDirectory|\Pricebook.v2.sdf");
                if (PB.DatabaseExists())

    Firstly, DeleteDatabase() doesn't do it supposed to? But other than that, it runs just fine and creates a new .sdf file.

    Now I create an association between a third class SupportProduct(PK=SupportDataIndex) which relates to a non-unique and nullable column in Product of the same name.

    I run the program and it tells me that I am missing a foregin I mark the field in Product as being a Primary Key (it remains nullable) and it all still fails. I give up and delete the association and figure I'll just manage it manuallly. After all, it looks like there are comments out there about the LinQ to SQL Team considering this capability for future releases....

    Problem is, once deleted the association with the PriceHistory class is now giving the very same error. So I delete that association and re-run. Still getting the error!   So I copy the Product class to Product1 and re-create the association and it works! I delete Product, rename Product1 to Product and it works.

    Am I misuing the tool, or are these known issues (or even reproducible).  I am tempted to start from scratch, but I have quite a few classes, and am really just reorganising and cleaning up and existing databas, so I really don;t want to start without the generated dbml.

    I'm using Visual Studio 2008 and SQL Compact 3.5 SP1.

    Monday, January 5, 2009 1:09 PM

All replies

  • Have found the problem i think....

    If I import a dbml generated by sqlmetal and then play around with the associations in the editor, and then follow up by looking at the actual .dbml as xml in an xml editor, I notice that the associations aren't necessarily deleted always (couldn't quite determine the conditions, but it's a start).

    I also established that it is absolutely necessary to have the child->parent have the relationship be the only primary key....

    I'll join the people asking for an optional link back to the parent table so that this requirement can be relaxed....there are often quite valid reasons (I think) that you don't need the reverse... Perhaps in the case where cardinality is zero-to-many and/or the attribute in the parent is not a candidate key...

    If this is not the case, would love to hear it.
    Tuesday, January 6, 2009 12:36 PM