1

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

delucaezequiel
  • 483
  • 2
  • 9
  • 26
  • This is not one to many but many to many. Does [this](https://stackoverflow.com/questions/9108083/fluent-nhibernate-many-to-many-mapping-way) help? – jeroenh Oct 13 '20 at 16:09
  • yes, it is a Many To Many, but I had the same error with Parent and Child References, that's why I was trying in this way. – delucaezequiel Oct 17 '20 at 18:18

0 Answers0