NHibernate Left Join without Foreign Key - Join by String value

Question

I have 2 entities (Oracle DB Tables).

• ProductEntity (has column CaseSize - string)
• ProductCsPcsEntity (has column CaseSize - string and has not ID)

Between these tables there is no Foreign key. I need to map ProductCsPcsEntity to ProductEntity if ProductEntity.Case_Size = ProductCsPcsEntity.CaseSize.

That means if ProductEntity has Casesize which is in ProductCsPcsEntity than ProductCsPcsEntity row will be saved into the property in ProductEntity, else reference will be null.

There is a relationship (ProductCsPcsEntity) One-To-Many (ProductEntity), but ProductCsPcsEntity has not all Casesizes from ProductEntity (only few - 20 from 60). I want to use something like left join in NHibernate.

public sealed class ProductEntityXXMap : SubclassMap<ProductEntityXX>
{
    public ProductEntityXXMap()
    {
        Map(x => x.CaseSize, "CASE_SIZE");
        . . . . 
        References(x => x.CsPcs, "CASE_SIZE");


        DynamicUpdate(); 
    }
}

And

    class ProductCsPcsEntityMap : EntityMap<ProductCsPcsEntity>
{
    public ProductCsPcsEntityMap()
    {
        //Id(x => x.Id).GeneratedBy.Assigned();
        Table("ProductCsPcsEntity");
        Map(x => x.CaseSize, "CASESIZE");
        Map(x => x.Pcs, "PCS");
        Cache.ReadOnly();
    }
}

Is there any solution how to solve this? I use NHibernate v4.0.30319, Oracle DB and .NET 4.

• is it possible to have the table and entity without ID like Primary key in DB
• I do not use XML table map definition
• all rows in ProductEntityXX have any CaseSize VARCHAR value (60 type of values)
• ProductCsPcsEntityMap has 20 CaseSizes which I want to join to the ProductEntityXX and ProductEntityXX has only one or empty row from ProductCsPcsEntityMap

OR

How can I join these tables in maping ?

 Join("ProductCsPcsEntity", x =>
            {
                //x.Fetch.Join().Optional();
                x.KeyColumn("CASE_SIZE");
                //x.Map(t => t.LeadFramePcs.Pcs).Column("PCS");
                x.Map(t => t.LeadFramePcs).Column("CASESIZE");

            });

But its join ProductEntityXX.id = ProductCsPcsEntity.casesize

Answer 1

My solution that works:

Map(x => x.Pcs).Formula("(SELECT LF.PCS FROM ProductCsPcsEntity LF WHERE LF.CASE_SIZE = CASE_SIZE)");

it seems that this solution works, but it is not nice