Join table on custom columns

Question

I want to join table based NOT on their Database-level relationship (PK, FK), but some custom criteria:

SELECT * FROM Users u inner join Docs d on u.Doc = d.ISBN

and map it to

public class User 
{
   public Doc Document { get; set; }
}
public class Doc
{
   public Int64 Id {get; protected set}
   public Int64 Isbn {get; set}
}

Is it possible with only mappings or do I need to call some method on Criteria API (which I use)?

I have tried the following:

public class UserMap : ClassMapping<User>
{
    public UserMap()
    {
        Table("Users");
        Id(x => x.Id, p => p.Column("Id"););
        ManyToOne(x => x.Document, m =>
        {
            m.Column("Doc");
            m.ForeignKey("ISBN");
            m.Fetch(FetchKind.Join);
            m.Access(Accessor.Property);
            m.Lazy(LazyRelation.NoLazy);
            m.NotNullable(true);
        });
    }
}
public class DocMap : ClassMapping<Doc>
{
    public DocMap()
    {
        Table("Docs");
        Id(x => x.Id, p => 
             { 
                p.Column("Id");   
                p.Generator(Generators.GuidComb); 
             });
        Property(x => x.Isbn, p => 
             { 
                p.Column("ISBN"); 
                p.NotNullable(true); 
             });
    }
}
//2.Criteria API
var criteria = Session.CreateCriteria("User")
// ...    
criteria.CreateCriteria("Document", "Docs", JoinType.InnerJoin, docCriterion);

but the result is:

SELECT * FROM Users u 
    inner join Docs d on u.Doc=d.Id /* docCriterion */ 
    left outer join Docs d2 on u.Doc=d.Id

Answer 1

try this

criteria.CreateCriteria("Users")
   .CreateCriteria("Document").Add(Restrictions.Eq("Isbn ", ?);

in your query u.Doc = d.ISBN is not correct, cause Doc is Doc entity and ISBN is number