CODEWITHSUNDEEP
c#nhibernatenhibernate-mapping
Răzvan Flavius Panda
Răzvan Flavius Panda

Reputation: 22106

How to map NHibernate variable table reference?

Having a table called ChildTable with 2 columns SourceTable and SourceId and some other tables ParentTable1, ParentTable2, etc.

The Id found in SourceId can be used to join to a parent table when the SourceTable has a value associated with that table (1 -> ParentTable1, 2 -> ParentTable2). For example, to get all the ChildTable rows which are associated to rows in ParentTable1 it would be achieved with this query:

select *
from ChildTable ct
join ParentTable1 pt1
  on ct.SourceTable = 1 and ct.SourceId = pt1.Id

I would like to map those 2 ChildTable columns as 1 property per parent table: Parent1, Parent2,... so 1 of them would be not null and the rest of the parent properties would be null:

public class ChildClass
{
    public Parent1Class Parent1 { get; set; }
    public Parent2Class Parent2 { get; set; }
    public Parent3Class Parent3 { get; set; }
    .
    .
    .
}

Question is: how to write the mapping for this case (mapping by code if possible)?

Note: This is for mapping existing tables, refactoring the table schema is not a solution yet (but suggestions are welcome).

Update

For the purpose of querying it seems to be enough to map a ChildClass property Parent1 with:

ManyToOne(property => property.Parent1, map => map.Formula("(select pt1.Id from dbo.ParentTable1 pt1 where SourceTable = 1 and pt1.Id = SourceId)"));

and the Children collection of Parent1Class with:

mapper.Where("SourceTable = 1");

For update/insert it is probably achievable using accessors, will post an update later.

Upvotes: 4

Views: 887

Answers (1)

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

Why don't you use Any?

Class:

public class ChildClass
{
    public virtual ParentBase Parent { get; set; }

    // beware of proxies when casting... this may not work like this
    public Parent1Class Parent1 { get { return Parent as Parent1Class; } }
    public Parent2Class Parent2 { get { return Parent as Parent2Class; } }
    .
    .
    .
}

Mapping:

Any(x => x.Parent, typeof(int), m =>
{
    m.IdType<int>();
    m.MetaType<int>();

    m.MetaValue(1, typeof(Parent1));
    m.MetaValue(2, typeof(Parent2));

    m.Columns(
      id => id.Name("SourceId"), 
      classRef => classRef.Name("SourceTable"));
});

There is also many-to-any, which maps collections of any types into a relation table.

When using it in a query, you can check the .class, or use a subquery:

HQL: 

select *
from ChildTable ct join Parent
where pt1.class = Parent1

or

select * 
from ChildTable ct 
Where ct.Parent in (from Parant2 p where p.Property = 'Hugo')

Upvotes: 2

Related Questions