CODEWITHSUNDEEP
c#sqlnhibernatefluent-nhibernatefluent-nhibernate-mapping
userdc8
userdc8

Reputation: 21

Fluent Nhibernate mapping to join by two non primary key columns

I was wondering is there a way to join two tables with two non primary key columns without creating a view? I have a table called 'Make' with columns of 'Name' and 'Year' that I want to join with another table called 'Style' with columns 'MakeName' and 'MakeYear'. One 'Make' can have many 'Style'. Here are the entities that I have created so far:

public class Make
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Year { get; set; }
    public virtual IList<Style> Styles { get; set; } 
}

public class Style
{
    public virtual int Id { get; set; }
    public virtual string MakeName { get; set; }
    public virtual string MakeYear { get; set; }
    public virtual string Class { get; set; }
    public virtual Make Make { get; set; }
}

Also these are the class maps I have so far:

public class MakeMap : ClassMap<Make>
{
    public MakeMap()
    {
        Id(x => x.Id);
        Map(x => x.Name);
        Map(x => x.Year);
        // Bad mapping...
        //HasManyToMany(x => x.Styles).Table("Make").AsBag()
          .ParentKeyColumn("MakeName")
          .ChildKeyColumn("MakeYear").Cascade.All();
        Table("Make");
    }
}

public class StyleMap : ClassMap<Style>
{
    public StyleMap()
    {
        Id(x => x.Id);
        Map(x => x.Class);
        Map(x => x.MakeName);
        Map(x => x.MakeYear);
        // Ends up overwriting the "MakeName" column
        References(x => x.Make).Column("MakeName").PropertyRef("Name").
              Column("MakeYear").PropertyRef("Year");
        Table("Style");
    }
}

Thanks!

Upvotes: 2

Views: 3992

Answers (1)

Firo
Firo

Reputation: 30803

3 options where i would prefere the first

  1. Option:

    change the db schema to include the make id instead of the name and year into the style table

  2. Option

References(x => x.Make)
    .Formula("(Select s.Id FROM Style s WHERE s.Name = MakeName AND s.Year = MakeYear)");
  1. Option
public class Make
{
    public virtual int Id { get; set; }

    public virtual MakeId BusinessId { get; private set; }
}

public class MakeId
{
    public virtual string Name { get; set; }
    public virtual string Year { get; set; }
}

public class MakeMap : ClassMap<Make>
{
    public MakeMap()
    {
        Id(x => x.Id);
        Component("BusinessId", c => 
            c.Map(x => x.Name);
            c.Map(x => x.Year);
        });

        HasMany(x => x.Styles)
          .PropertyRef("BusinessId")
          .KeyColumns.Add("MakeName", "MakeYear")
          .Cascade.All();
        Table("Make");
    }
}

public class StyleMap : ClassMap<Style>
{
    public StyleMap()
    {
        Table("Style");

        Id(x => x.Id);
        Map(x => x.Class);

        References(x => x.Make)
            .PropertyRef("BusinessId")
            .Columns.Add("MakeName", "MakeYear");
    }
}

Upvotes: 7

Related Questions