How to join two tables queries and select matching the records in Nhibernet linq?

Question

I am trying to develop one single query which gives me records from both the table in one result using linq-Nhibernet query.

I have two tables first one :account (accountId , accountDescription) and second one: accountdescriptionhistory (accountId, description). AccountId is foreign-key reference to second table. Now, i am fetching all the records from first table with below query.

What i want is, If record exist in AccountDescriptionHistory for accountId reference than it should return me the description from AccountDescriptionHistory not from account table. i want to achieve this in single query.

Note : I need this in linq NHibernate query.

Adding a class details

Account class look like below :

public class Account : EntityModelHasOwner<Account>, ISupportsIdLookup
{

    /// <summary>
    /// The account number
    /// </summary>
    public virtual string AccountNumber { get; set; }

    /// <summary>
    /// The account's description
    /// </summary>
    public virtual string Description { get; set; }

}

Account description class :

public class AccountDescriptionHistory : EntityModel<AccountDescriptionHistory>
{
    #region Public Properties

    /// <summary>
    /// The account description of an account that is valid for a specific date range
    /// </summary>
    public virtual string AccountDescription { get; set; }

    /// <summary>
    /// The account this AccountDescriptionHistory is associated with.
    /// </summary>
    public virtual Account Account { get; set; }
}

Answer 1

You can accomplish this via a query.

        /* this.Session is a NHibernate.ISession */

        string hql = "FROM Account acc" +
             " inner join fetch acc.MyAccountDetails"
               "where acc.IsDeleted= :myIsDeletedParameter";
        IQuery q = this.Session.CreateQuery(hql);
        q.SetBoolean("myIsDeletedParameter", false);
        IList<Account> returnItems = q.List<Account>();
        return returnItems;

OR with a Fluent style;

            ////using NHibernate;
            ////using NHibernate.Linq;

            IQueryable<Account> returnItemsQuery = (from myalias in this.Session.Query<Account>()
                .FetchMany(x => x.MyAccountDetails )
                .Where(acc => false == acc.IsDeleted)
                    select myalias);

            IList<Account> returnItems = returnItemsQuery.ToList();

I am assuming your poco looks like this.

public class Account
{
  /* scalars */

  public virtual ICollection<AccountDetails> MyAccountDetails {get; set;}
}

See:

https://nhibernate.info/doc/howto/various/lazy-loading-eager-loading