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