How to map unrelated(no direct relation) tables in Fluent NHibernate

Question

I have following tables

1. Car(CarId, Name,...)
2. CarPartLink(CarId, PartId)
3. Part(PartId, Name)
4. SubPartLink(Parent_PartId, Child_PartId) where both parent and child comes from Part table

I want Car object to have list of Parts including the SubParts, here Car does not have direct relationship with Part its Subparts and neither Part has a direct relation with Subparts.

i.e.

class Car
{
  public virtual string Id { get; set; }
  public virtual string Name { get; set; }
  public virtual ICollection<Parts> AllParts { get; set; } //This should include all the parts and its subparts assuming subparts are only one level deep
}

How to make the map for the same in Fluent NHibernate?

Edit 1:

If it is not possible in Fluent NHibernate but possible in NHibernate mapping then also fine with me.

I am using Fluent NHibernate version: 1.4.0.1 and NHibernate version: 3.3.3

Edit 2:

I am also fine if I get only the subparts, or the id's of subparts in the map.

Edit 3:

Each vehicle( here in the example mentioned as Car) has more than 1 million parts and subparts combined, out of which user would be actually be using few 100 parts depending on the conditions. e.g. Get all parts that are weighing 100 kg or get all parts that are of type "Screw" etc. I will be needing these data in read only mode.

Answer 1

There is already a fine tutorial on relationship mappings in the FluentNH wiki. I suggest you read the guide, or even better, follow it step-by-step. Assuming the following entities:

public class Car
{
    public virtual int CarId { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Part> AllParts {get; set;}

    public Car()
    {
        AllParts = new List<Part>();
    }
}

public class Part
{
    public virtual int PartId { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Car> AllCars {get; set;}

    //never tried mapping a many-to-many on the same entity, but this should work...
    public virtual IList<Part> ParentParts {get; set;}
    public virtual IList<Part> SubParts {get; set;}

    public Part()
    {
        AllCars = new List<Car>();
        ParentParts = new List<Part>();
        SubParts = new List<Part>();
    }   
}

Your mapping will probably be something like this:

public class CarMap : ClassMap<Car>
{
    public CarMap()
    {
        Id(x => x.CarId);
        Map(x => x.Name);
        HasManyToMany(x => x.AllParts)
            //depending on your logic, you would either set .Inverse here or in the PartMap
            .Table("CarPartLink")
            .ParentKeyColumn("CarId")
            .ChildKeyColumn("PartId")
            .Cascade.All();
    }
}   

public class PartMap : ClassMap<Part>
{
    public PartMap()
    {
        Id(x => x.PartId);
        Map(x => x.Name);
        HasManyToMany(x => x.AllCars)
            .Table("CarPartLink")
            .ParentKeyColumn("PartId")
            .ChildKeyColumn("CarId")
            .Cascade.All();
        HasManyToMany(x => x.ParentParts)
            .Table("SubPartLink")
            .ParentKeyColumn("Parent_PartId")
            .ChildKeyColumn("Child_PartId")
            .Inverse() //saving done from the child side of the relationship, right?
            .Cascade.All();
        HasManyToMany(x => x.SubParts)
            .Table("SubPartLink")
            .ParentKeyColumn("Child_PartId")
            .ChildKeyColumn("Parent_PartId")
            .Cascade.All();
    }
}   

If the above doesn't work, let me know. Apparently there is a bug in some versions of FNH where you will need to employ a special workaround. You can find the workaround in this question (look for self-submitted answer by the OP), which is based on the same scenario as yours (many-many on the same entity).

EDIT: If you want to obtain all the parts and subparts for a Car, you will need to recursively access the SubParts for every single Part in your Car.AllParts collection.

Car Car1 = new Car();
//code to get car object
IList<Part> AllParts = new List<Part>();
GetAllParts(Car.AllParts, ref AllParts); //call a recursive method to add all the parts and subparts to the list
//do something with the list


public void GetAllParts(IList<Part> parentList, ref IList<Part> partsList)
{
    foreach (Part part in parentList)
    {
        if (!partsList.Contains(part)) //validate if the list already contains the part to prevent replication
            partsList.Add(part); //add this part to the list
        if (part.SubParts.Count > 0) //if this part has subparts
            GetSubParts(part.SubParts, ref partsList); //add all the subparts of this part to the list too
    }
}

Edit2: This blog post seems to be exactly what you need...

session.CreateQuery(
        "select parts from Car as car " +
        "join car.AllParts as parts join fetch parts.SubParts where ...")
         .SetResultTransformer(new DistinctRootEntityResultTransformer())
         .List<Employee>();