nHibernate QueryOver get all records when parameter is null

c#, nhibernate, null

Solution

In this case, we know the condition `@param is NULL` before the query is executed, or better before is assembled at all. So let's extend Criteria with `custGrp` only if it is filled.

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...

Problem

I have following situation. I have `Customer` and `CompanyCustomerAssignment` objects (with 1:1 relation). One of properties in `CompanyCustomerAssignment` is `CustomerGroup`. Now - I would like to `QueryOver` - when `CustomerGroup` is passed, than fetch `Customers` which belongs to this group, but when it comes as null I would like to query all. Well it seems simple in "SQL": ``` ...WHERE CustomerGroupId = @param OR @param is NULL; ``` Unfortunately I have no idea with 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 this does not work - I receive a message, that `Customer` does not have such a property, which sounds logical but does not help me at all.

Original source