C# – NHibernate QueryOver with Restrictions

clinq-to-nhibernatenhibernatequeryover

I am new to NHibernate and trying to use the QueryOver. I have the following NHibernate query

var departments = session
   .QueryOver<Department>()
   .Where(Restrictions.On<Department>x=> x.Parent.Id).IsIn(new List<int> {100}))
   .List().ToList();

I have a single value to pass into IsIn and for that I have to new up a list (new List<int> {100})). Is there a more cleaner way of doing this?

Best Answer

Your syntax is ok, and only few changes or improvements can be done... here they are:

IList<Department> departments;
var parents = new List<int> {167};

// advantage of this "original" QueryOver is, that it can accept
// more parent IDs.. not only one "100" as in our example
// so if we neet children of 100,101,102
// we can get more from this syntax: new List<int> {100, 101,102...};
departments = session
    .QueryOver<Department>()
    .Where(Restrictions.On<Department>( x=> x.Parent.Id)
        .IsIn(parents))
    .List();

// this style is just a bit more straightforward 
// saving few chars of code, using 'WhereRestrictionOn'
departments = session
    .QueryOver<Department>()
    .WhereRestrictionOn(x => x.Parent.Id).IsIn(parents)
    .List();

// in case we do have the only one parent ID to search for
// we do not have to use the IS IN
departments = session
    .QueryOver<Department>()
    .Where(x => x.Parent.Id == 100)
    .List();

See more: 16.2. Simple Expressions

Related Solutions

C# – Use of var keyword in C#

I still think var can make code more readable in some cases. If I have a Customer class with an Orders property, and I want to assign that to a variable, I will just do this:

var orders = cust.Orders;

I don't care if Customer.Orders is IEnumerable<Order>, ObservableCollection<Order> or BindingList<Order> - all I want is to keep that list in memory to iterate over it or get its count or something later on.

Contrast the above declaration with:

ObservableCollection<Order> orders = cust.Orders;

To me, the type name is just noise. And if I go back and decide to change the type of the Customer.Orders down the track (say from ObservableCollection<Order> to IList<Order>) then I need to change that declaration too - something I wouldn't have to do if I'd used var in the first place.

Nhibernate queryover join with subquery to get aggregate column

After reading hundreds of similar questions in here, I have found the answer: a correlated sub-query. Like this:

// aliases
Branch branch = null; AssignmentBranch assignment = null;

var subquery = QueryOver.Of<AssignmentBranch>(() => assignment)
    .Where(() => assignment.Branch.ID == branch.ID)
    .ToRowCountQuery();

var query = session.QueryOver<Branch>(() => branch)
     .Where(() => branch.Project.ID == projectID)
     .SelectList
     (
         list => list
         .Select(b => b.ID)
         .Select(b => b.Name)
         .SelectSubQuery(subquery)
     )
     .TransformUsing(Transformers.AliasToBean<BranchAssignments>())
     .List<BranchAssignments>();

The similar question I got my answer from is this one.

Best Answer

Related Solutions

C# – Use of var keyword in C#

Nhibernate queryover join with subquery to get aggregate column

Related Topic