NHibernate QueryOver get all records when parameter is null
I have the following situation. I have objects Customer
and CompanyCustomerAssignment
(1: 1 ratio). One of the properties in CompanyCustomerAssignment
is CustomerGroup
. Now - I would like QueryOver
- when CustomerGroup
passed than the fetch Customers
that belongs to this group, but when it comes as null, I would like to query everything. Well, it seems to be just in "SQL":
...WHERE CustomerGroupId = @param OR @param is NULL;
Unfortunately I have no idea about QueryOver ( custGrp
is paramater - can be an object or null
)
Customer c = null;
CompanyCustomerAssignment cca = null;
_session.QueryOver<Customer>(() => c)
.JoinAlias(() => c.CompanyCustomerAssignment, () => cca)
.Where(() => cca.Company == currentCompany && c.IsActive == true)
.And(() => cca.CustomerGroup == custGrp || custGrp == null ) // <- this seems to be problem to me
.List()
.Select(x => new CustomerApiModel() {CustomerId = x.Id})
.ToList();
But that doesn't work - I get a message that it Customer
doesn't have this property, which sounds logical, but doesn't help me at all.
source to share
In this case, we know the condition @param is NULL
before executing the request, or better, before collecting at all. So, let's expand the criteria with help custGrp
only if it's full.
var criteria = _session.QueryOver<Customer>(() => c)
.JoinAlias(() => c.CompanyCustomerAssignment, () => cca)
.Where(() => cca.Company == currentCompany && c.IsActive == true);
// if during the query build
if(custGrp != null)
{
criteria.Where(() => cca.CustomerGroup == custGrp);
}
var results = criteria
.List()
...
This makes the SQL part more efficient and we can do more tricks ...
source to share