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; }
}

Hi. A friendly advice: Include all relevant code in your questions. In order to solve questions, sometimes we'll need to run the code locally. — Roar S., Aug 14 '20 at 11:47
You still need to provide us with all the classes, else we'll have to guess how you have implemented this. And as we all know: Assumptions is the mother of all f..-ups :-) — Roar S., Aug 14 '20 at 12:24
You need to show your Orm-Poco's and your Mapping(s) (via attribute or Fluent). And you need to post text code, not screenshot code. — granadaCoder, Aug 14 '20 at 12:29
Does Account have 1 or N number of AccountDescriptionHistory(s)? AccountDescriptionHistory has the Account, but you do not have the reciprocal relationship defined. — granadaCoder, Aug 14 '20 at 12:48
@RoarS. Please explain, why it has been down voted any specific reason. — Coder007, Aug 18 '20 at 10:33
Because the selected answer returns duplicates in cases where there are more than one child instance (which I guess will be the normal). Tested and verified. Others should judge my code, but code in the selected answer does not look great. — Roar S., Aug 18 '20 at 10:36
@RoarS. On which basis you said it returns duplicate. Can you please point eject place. — Coder007, Aug 18 '20 at 10:41
I guess you have code you can test this with. Try to populate your child-end with 2 or more instances, and apply code from selected answer, you'll see this. Then you can test my code. But main reason for the downvote, was that you asked for Linq, and accepted an HQL-answer with errors, hence this is useless to others than you. — Roar S., Aug 18 '20 at 10:44

granadaCoder · Accepted Answer · 2020-08-14T14:50:30.657

0

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

edited Aug 14 '20 at 14:50

answered Aug 14 '20 at 12:33

granadaCoder

26,328
10
113
146

You are missing a select, please see my answer. – Roar S. Aug 14 '20 at 13:01
Roar. Hey. I am not missing the Select. This is NHibernate hql, not SQL. the lack of a SELECT means "get everything". If you follow the link I have in my answer, you will also see no "SELECT" in the hql. --> "from Order o" + " inner join fetch o.OrderLines" + " inner join fetch o.Customer" + " where o.Id=:id";" – granadaCoder Aug 14 '20 at 13:19
Hi grenadaCoder. Sorry for the delay, because we both have been in the trade for a long time, I felt like testing this thoroughly before responding. My findings: Without select, query returns a list of objects, which is what I expected. With a simple select, it returns a result with duplicates. I've updated my answer with select distinct. – Roar S. Aug 14 '20 at 13:48
This turned out to be incorrect with fetch, but true without: "Without select, query returns a list of objects, which is what I expected". It still returns duplicates though. – Roar S. Aug 14 '20 at 13:56

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

1 Answers1