none
Linq to SQL relationships RRS feed

  • Question

  • I have a database with the following tables setup:

     

    Users

    PK_Id

     

    Contacts

    PK_Id

     

    UsersContacts

    PK_Id

    FK_UsersId

    FK_ContactsId

     

    The UsersContacts table is simply a look-up/mapping table. What I THINK i want to do (perhaps someone can suggest a better way?) is be able to access the users contacts like this (essentially bypassing the UsersContacts table to the outside user):

     

    Code Snippet

    //User.Contacts

    foreach (Contact contact in user.Contacts)

    {

    //Do Something.

    }

     

    However, what it actually winds up being generated by the Linq to Sql designer looks more like this:

    Code Snippet

    //User.UserContacts.Contacts

    foreach (UserContact userContact in user.UserContacts)

    {

    foreach (Contact contact in UserContact)

    {

    //Do Something.

    }

    }

     

     

    What's the best way to approach this? Am I looking at this completely wrong? Any help would be much appreciated.

    Friday, April 11, 2008 2:26 PM

Answers

  • Hi,

     

    Linq To Sql maps classes to tables (in other words for each table you get one class).

    If you want to define classes which have data from several tables you need Linq To Entities (RTM maybe summer this year).

     

    Your second code snippet is not right. Users to User contacts is a one-to-many relationsship and user.Contacts is of type EntitySet and therefore it's a collection.

    UserContacts to Contacts is a many-to-one relationship and user.Contact is of type EntityRef and therefore it's an object.

    You second code snippet can look like

    Code Snippet

    //User.UserContacts.Contacts

    foreach (UserContact userContact in user.UserContacts)

    {

    // the get the whole object

    Contact contact = userContact.Contact;

    // or you could access the properties by

    string contactName = userContact.Contact.ContactName;

    //Do Something.

    }

     

    You do not need a second foreach since there is no collection it's just the object.

     

    Regards

    Philipp

    Friday, April 11, 2008 3:17 PM