entity framework: How to relate 2 tables..

Question

I have following tables:

Profiles:

ProfileID (Primary Key)
ProfileName, etc.

Contacts:

ContactID (Primary Key)
ProfileFromID (Foreign Key)
ProfileToID (Foreign Key)
Message varchar(50) , etc.

Profiles could have many contact messages. So a given message will have who sent the message and to whom the message was sent.

I am having difficult time establishing relationship between the 2 tables. Its easy to connect Profile.ProfileID to Contact.ProfileIDFrom.

But I also want to connect Profile.ProfileID to Contact.ProfileIDTo

So... later on I want to query as follows in my entities...

Profile.MessageFrom would give me profile name and... Profile.MessageTo would give me profile name....

I want to be able to extract profile names both for ProfileIDFrom and ProfileIDTo.

I am not sure how to connect the 2 tables.

Answer 1

You have 2 (foreign-key) relationships from Contacts - Profiles (* - 1)

But as far as ORM-s go you may need to create 2 or more different entity-(types?) which are aliases depending of your use cases (I'm not familiar with the entity-framewrk)

Answer 2

You have to create two foreign keys in database:

-- Creating foreign key on [ProfileFromId] in table 'Contacts'
ALTER TABLE [dbo].[Contacts]
ADD CONSTRAINT [FK_ProfileContactFrom]
    FOREIGN KEY ([ProfileFromId])
    REFERENCES [dbo].[Profiles]
        ([ProfileId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProfileContactFrom'
CREATE INDEX [IX_FK_ProfileContactFrom]
ON [dbo].[Contacts]
    ([ProfileFromId]);
GO
-- Creating foreign key on [ProfileToId] in table 'Contacts'
ALTER TABLE [dbo].[Contacts]
ADD CONSTRAINT [FK_ProfileContactTo]
    FOREIGN KEY ([ProfileToId])
    REFERENCES [dbo].[Profiles]
        ([ProfileId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProfileContactTo'
CREATE INDEX [IX_FK_ProfileContactTo]
ON [dbo].[Contacts]
    ([ProfileToId]);
GO

Add two navigation properties to Contact entity in your model. For example ProfileFrom and ProfileTo.

You can use eager loading to do queries like this:

using (var ctx = new TestModelContainer())
      {
            foreach (Contact contact in ctx.Contacts
                                          .Include("ProfileFrom")
                                          .Include("ProfileTo"))
                  {
                         Console.WriteLine("From: {0}, to: {1}, Message: \"{2}\"", contact.ProfileFrom.ProfileName, contact.ProfileTo.ProfileName, contact.Message);
                  }
      }