views:

47

answers:

0

Hi guys, I am trying to map these entities Messages table fields are Id generated Id,SentFrom int, sentTo int where Sentfrom and sentTo are linked to the users table I also have a linker table MessageUser table with userId and MessageId

    CREATE TABLE [dbo].[MessageUser](
 [MessageId] [int] NOT NULL,
 [UserId] [int] NOT NULL,
 CONSTRAINT [PK_MessageUser] PRIMARY KEY CLUSTERED 
(
 [MessageId] ASC,
 [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MessageUser]  WITH CHECK ADD  CONSTRAINT [FK_MessageUser_Messages] FOREIGN KEY([MessageId])
REFERENCES [dbo].[Messages] ([Id])
GO
ALTER TABLE [dbo].[MessageUser] CHECK CONSTRAINT [FK_MessageUser_Messages]
GO
ALTER TABLE [dbo].[MessageUser]  WITH CHECK ADD  CONSTRAINT [FK_MessageUser_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([Id])
GO
ALTER TABLE [dbo].[MessageUser] CHECK CONSTRAINT [FK_MessageUser_Users]
GO

I think the db design is Ok but I can't figure out the best way to map the entities my ultimate goal to be able to get the Messages sentTo and message sentFrom using the user and the message entities. Any idea how to achieve that goal. Mapping class would look like

public MessageMap()
        {
            Table("Messages");
            Id(x => x.Id).GeneratedBy.Identity();
            Map(x => x.Title);
            Map(x => x.Body);
            References(x => x.Recipient).Column("SentTo").Column("Id");//.Cascade.All();
            References(x => x.Sender).Column("SentFrom").Column("Id");//.Cascade.All();
        }

EDIT This seems to work without using the extra MessageUser table

  public MessageMap()
        {
            Table("Messages");
            Id(x => x.Id).GeneratedBy.Identity();
            Map(x => x.Title);
            Map(x => x.Body);

            References(x => x.Recipient).Column("SentTo").ForeignKey("Id").Cascade.All();
            References(x => x.Sender).Column("SentFrom").ForeignKey("Id").Cascade.All();
        }

Thanks