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.
Account
table should join with AccountDescriptionHistory
on left join with accountId
column.
Account Number should be filtered from the list of passing accountnumbers
.
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.
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;
}
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
)
);`