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.

+3


source to share


1 answer


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

+3


source







All Articles