I am trying to create a One to Many relationship with a Join table and I an having a hard time trying to do it. Below is the tables structure, Entities and Maps in C#
CREATE TABLE tbl_configurations
(
strCode uniqueidentifier default NEWID(),
strName nvarchar(MAX) NOT NULL,
.....
CONSTRAINT pk_configurations PRIMARY KEY (strCode)
)
CREATE TABLE tbl_environments
(
strCode uniqueidentifier default NEWID(),
strName nvarchar(15) NOT NULL,
strDescription nvarchar(MAX) NOT NULL
CONSTRAINT pk_environments PRIMARY KEY (strCode)
)
CREATE TABLE tbl_configurationsenvironments
(
strConfiguration uniqueidentifier NOT NULL,
strEnvironment uniqueidentifier NOT NULL
CONSTRAINT pk_configurationsenvironments PRIMARY KEY (strConfiguration, strEnvironment),
CONSTRAINT fk_configurationsenvironmentsconfigurations FOREIGN KEY (strConfiguration) REFERENCES tbl_configurations(strCode),
CONSTRAINT fk_configurationsenvironmentsenvironments FOREIGN KEY (strEnvironment) REFERENCES tbl_environments(strCode)
)
public class ConfigurationEntity
{
public virtual Guid strCode { get; set; }
public virtual string strName { get; set; }
.....
public virtual ISet<EnvironmentEntity> colEnvironments { get; set; }
}
public class EnvironmentEntity
{
public virtual Guid strCode { get; set; }
public virtual string strName { get; set; }
public virtual string strDescription { get; set; }
}
public class EnvironmentMap : ClassMap<EnvironmentEntity>
{
public EnvironmentMap()
{
Table("tbl_environments");
Id(x => x.strCode).GeneratedBy.Guid();
Map(x => x.strName);
Map(x => x.strDescription);
Not.LazyLoad();
}
}
public class ConfigurationMap : ClassMap<ConfigurationEntity>
{
public ConfigurationMap()
{
Table("tbl_configurations");
Id(x => x.strCode).GeneratedBy.Guid();
Map(x => x.strName);
Join("tbl_configurationsenvironments", m =>
{
m.Fetch.Join();
m.KeyColumn("strConfiguration");
m.HasMany<EnvironmentEntity>(x => x.colEnvironments)
.AsSet()
.Cascade.All()
.KeyColumn("strCode")
.Fetch.Join()
.Not.LazyLoad();
});
Not.LazyLoad();
}
}
Based on what I see on the console, it properly map the relationship between tbl_configurations and tbl_configurationsenvironments
tbl_configurations confi11_ left outer join tbl_configurationsenvironments confi11_1_ on confi11_.strCode=confi11_1_.strConfiguration
However it doesn't properly set the relationship between tbl_environments and tbl_configurationenvironments
left outer join tbl_environments colenviron12_ on confi11_.strCode=colenviron12_.strCode
instead it makes a relationship between tbl_configurations and tbl_environments I am pretty sure that the error is on the ConfigurationMap, particularly on this section:
m.HasMany<EnvironmentEntity>(x => x.colEnvironments)
.AsSet()
.Cascade.All()
.KeyColumn("strCode")
.Fetch.Join()
.Not.LazyLoad();
});
Do you know how can I specify that the relationship is between tbl_configurationsenvironments.strEnvironments? I attempt with .PropertyRef("strEnvironment"), however when I do that it looks the property on tbl_configurations which is not correct