How to find matching pair of data in NHibernate QueryOver?

Question

I have two lists.

AccountNumbers  ["account1", "account2", "account3"]
Descriptions [ "desc1",  "" ] (possible should be empty value)

And I have two database tables Account and AccountDescriptionHistory. Tables have a relation with accountId.

Account Table Design:- AccountId , AccountNumber, Description,

Account Description history :- AccountId, Description, validTo, validFrom

Now, I want to design a Nhibernet Query with a query over with below requirement.

1. Account table should join with AccountDescriptionHistory on left join with accountId column.

2. Account Number should be filtered from the list of passing accountnumbers.

3. Description should be matched from the passing list of descriptions and select description from accountdescriptionhistory table if passing param dateToCheck is greater than and less than of validFrom and validTo columns of accountdescriptionhistory otherwise, select from account table.

4. Consider empty and lower case value from database column

I have tried with the below query but don't know how to filter from collections.

        public IEnumerable<Account> CheckForExistingAccounts(ISession session, Owner owner, IEnumerable<string> accountNumbers,
        IEnumerable<string> descriptions, bool isUpdating, DateTime? dateToCheck = null)
    {
        Account accountAlias = null;
        AccountDescriptionHistory accountDescripAlias = null;


        var existingaccountsquery = session.queryover(() => accountalias).
                                joinentityalias(() => accountdescripalias, () => accountdescripalias.account.id == accountalias.id, jointype.leftouterjoin).
                                where(account => !account.isdeleted && account.owner.id == owner.id).
                                selectlist(list => list
                                        .select(() => accountalias.id).withalias(() => accountalias.id)
                                        .select(projections.conditional(expression.isnull(
                                                projections.property(accountalias.accountnumber)),
                                                projections.property(string.empty),
                                                projections.property(string.empty))).withalias(() => accountalias.accountnumber)
                                        .select(() => accountalias.changed).withalias(() => accountalias.changed)
                                        .select(() => accountalias.created).withalias(() => accountalias.created)
                                        .select(() => accountalias.sequencenumber).withalias(() => accountalias.sequencenumber)
                                        .select(() => accountalias.accounttype).withalias(() => accountalias.accounttype)
                                        .select(() => accountalias.parentaccount).withalias(() => accountalias.parentaccount)
                                        .select(() => accountalias.coreaccountid).withalias(() => accountalias.coreaccountid)
                                        .select(() => accountalias.sourceid).withalias(() => accountalias.sourceid)
                                        .select(() => accountalias.description).withalias(() => accountalias.description)
                                ).transformusing(new deeptransformer<account>()).future<account>();



        var data = existingaccountsquery.List();

        return data;
    }

Answer 1

I developed the query for the above descriptive problem OR question. i am posting here if it helps in the future to other developers.

`var existingAccountsQuery = session.QueryOver(() => accountAlias)
        .JoinEntityAlias(() => accountDescripAlias,
                    Restrictions.And
                    (
                        //And clause 1
                        Restrictions.EqProperty($"{nameof(accountAlias)}.{nameof(accountAlias.Id)}",
                        "accountDescripAlias.Account.Id"),
                        //And clause 2
                        Restrictions.And(
                            Restrictions.Le(
                                Projections.SqlFunction("date",
                                    NHibernateUtil.Date,
                                    Projections.Property(
                                    $"{nameof(accountDescripAlias)}.{nameof(accountDescripAlias.ValidFrom)}"))
                            , dateToCheck),
                        Restrictions.Ge(
                                Projections.SqlFunction("date",
                                    NHibernateUtil.Date,
                                    Projections.Property(
                                    $"{nameof(accountDescripAlias)}.{nameof(accountDescripAlias.ValidFrom)}"))
                            , dateToCheck)
                            )
                    ),
                JoinType.LeftOuterJoin)
        .Where(() => !accountAlias.IsDeleted && accountAlias.Owner.Id == owner.Id)
        .Where(() => accountAlias.AccountNumber == null || accountAlias.AccountNumber.Lower().IsIn(accountNumbers.ToArray()))
        .Where(() =>
            (
                accountDescripAlias.AccountDescription != null &&
                accountDescripAlias.ValidFrom <= dateToCheck && accountDescripAlias.ValidTo >= dateToCheck &&
                accountDescripAlias.AccountDescription.Lower().IsIn(descriptions.ToArray())
            ) ||
            (
                accountDescripAlias.AccountDescription == null &&
                accountAlias.Description != null &&
                accountAlias.Description.Lower().IsIn(descriptions.ToArray())
            ) ||
            (
                accountDescripAlias.AccountDescription == null &&
                accountAlias.Description == null
            )
        );`