Use or Clause in Queryover in Nhibernate

Use OR Clause in queryover in NHibernate

Here is description how we can build OR with NHiberante

• NHibernate QueryOver with WhereRestriction as OR

The syntax (in C# as the tag says) is:

• Restrictions.Or(restriction1, restriction1)
• Restrictions.Disjunction().Add(restriction1).Add(restriction2).Add(...

In this case, it could be like this (again in C#, while question seems to use VB):

db.QueryOver<Users>()()
  .Where((x) => x.Role == "Guest")
  .And(Restrictions.Or(
       Restrictions.Where<Users>((x) => x.Block == 0)
     , Restrictions.Where<Users>((x) => x.APPID == appId)
  ))
  .List<Users>();

QueryOver IN Clause?

Aha- got it! The AddRestrictions has an IsIn method:

var results = session.QueryOver<Foo>().AndRestrictionOn(x=>x.id).IsIn(ids)

With this last piece we might be ready to ditch our years-old hand-rolled ORM!

nHibernate QueryOver Having clause with OR condition

It could look like this

Course courseAlias = null;
var query = session.QueryOver<Course>(() => courseAlias)
    .SelectList(l => l
    .SelectGroup(item => item.DepartmentID).WithAlias( () => courseAlias.DepartmentID)
    .SelectCount(item => item.StatusID).WithAlias(() => courseAlias.StatusID)
    )
    // WHERE Clause
    .Where(item => item.DepartmentID > 1)
    // HAVING Clause
    .Where( Restrictions.In(
        Projections.Count<Course>(item => item.StatusID)
        , new List<int> {2, 3})
    )
    .TransformUsing(Transformers.AliasToBean<Course>())

var list = query
    // take skip...
    .List<Course>();

nHibernate QueryOver with a Left Join using *OR* and *AND* clauses

I think you're just missing a .Id on order in your where clause:

Order order = null;

var query = session.QueryOver<Customer>()
   .Left.JoinAlias(x => x.Orders, () => order)
   .Where(x => x.Active)
   .And(() => order.Id == null || (order.date >= date1 && order.date <= date2))
// -----------------^

This gives me the following SQL (SQL Server):

SELECT
    this_.Name as y0_,
    order1_.Id as y1_ 
FROM
    Customer this_ 
    LEFT OUTER JOIN [Order] order1_ ON this_.Id = order1_.CustomerId
WHERE
    this_.Active = @p0 AND (
        order1_.Id IS NULL OR (
            order1_.Date >= @p1 AND order1_.Date <= @p2
        )
    );

NHibernate ORs using QueryOver

This syntax should solve it

var list = session.QueryOver<MyTable>()
    .Where(
        Restrictions.Or(
          Restrictions.Eq(Projections.Property<MyTable>(tab => tab.x), "One"),
          Restrictions.Eq(Projections.Property<MyTable>(tab => tab.y), "Two")
        )
    )
    .List<MyTable>()

In case we want more, Disjunction is our way to multiple OR:

var list = session.QueryOver<MyTable>()
    .Where(Restrictions.Disjunction()
      .Add(Restrictions.Eq(Projections.Property<MyTable>(tab => tab.x), "One"))
      .Add(Restrictions.Eq(Projections.Property<MyTable>(tab => tab.x), "Two"))
      .Add(Restrictions.Eq(Projections.Property<MyTable>(tab => tab.x), "xxx"))
    )
    .List<MyTable>()

NHibernate multiple subqueries with in clause

You got it almost right, you are only missing the .Where(Restrictions.Disjunction()...) for the or in SQL.

Based on your code (assuming that you have a property Id in LocationEntity):

// get IDs to look for in CurrentLocation
var subQuery1 = QueryOver.Of<LocationEntity>()
    .Where(l => l.Name == LocationNameEnum.LA)
    .Select(x => x.Id);

// get IDs to look for in NextDestination
var subQuery2 = QueryOver.Of<LocationEntity>()
    .Where(l => l.Name == LocationNameEnum.CHG || l.Name == LocationNameEnum.NY)
    .Select(x => x.Id);

var poc = session.QueryOver<TruckEntity>()
    .Where(Restrictions.Disjunction() // this takes care of the OR
        .Add(Subqueries.WhereProperty<TruckEntity>(x => x.CurrentLocation.Id).In(subQuery1))
        .Add(Subqueries.WhereProperty<TruckEntity>(x => x.NextDestination.Id).In(subQuery2))
    )
    .List<TruckEntity>();

nhibernate queryover join alias with a where-or clause

There are a few ways to rewrite this in QueryOver, but the cleanest is probably:

Dish d = null;
DishType dt = null;
return this.Session.QueryOver<Dish>(() => d)
    .JoinAlias(() => d.DishType, () => dt)
    .Where(() => d.Name.IsIn(namesArr) || dt.Name.IsIn(dishTypesArr))
    .List<Dish>();

This will generate SQL that looks something like this:

SELECT this_.*
FROM   [Dish] this_
       inner join [DishType] dt1_
         on this_.DishTypeId = dt1_.Id
WHERE  (this_.Name in (/* Comma separated list of names */)
         or dt1_.Name in (/* Comma separated list of types */))

NHibernate QueryOver select where true

The most usual way is to apply an if statement in C#, while building the query.

var query = nhibernateSession
    .QueryOver<Demanda>(() => DemandaAlias);

var someTestIfShouldApplyThisFilter = ...;

if (someTestIfShouldApplyThisFilter)
{
    query = query.Where(() => (DemandaAlias.ID == userId);
}

and later we can consume that reference to get a list (or apply/not apply other where conditions based on other if statements)

query.List<Demanda>();

QueryOver conditionally having multiple or zero Where clauses

You can make use of the

 Restrictions.Conjunction()

As an example:

private IQueryOver<CustomerEntity> QueryForCustomer(int? companyCode, int[] zipCodes)
{        
    var customerRestritcions = Restrictions.Conjunction();
    if (companyCode.HasValue)
    {
        customerRestritcions.Add(Restrictions.Eq(Projections.Property<CustomerEntity>(c => c.CompanyCodeId), companyCode));
    }

    if (zipCodes != null)
    {
        customerRestritcions.Add(Restrictions.In(Projections.Property<CustomerEntity>(c => c.ZipCode), zipCodes));
    }

    return Session.QueryOver<CustomerEntity>(() => customer)
        .Where(customerRestriction)         
}

---

Related Topics