NHibernate: Conjunctions and Disjunctions using QueryOver

nhibernatenhibernate-criteriaqueryover

Let's say I have a SQL query I need to render using nHibernate. The SQL query's WHERE clause consists of three OR statements, each of which contains a list of conditions. For example:

SELECT * 
FROM MyTable 
WHERE
    (OrderId = 1 and ItemId = 100) 
    OR
    (OrderId = 2 and ItemId = 200)
    OR
    (OrderId = 3 and ItemId = 300)

Using nHibernate's Criteria syntax, I could use a disjunction to implement this:

var disjunction = Restrictions.Disjunction();

foreach (var tuple in OrdersAndLineItems)
{
    var conjunction = Restrictions.Conjunction();
    var order = tuple.Item1;
    var lineitem = tuple.Item2;

    conjunction.Add(Restrictions.Eq("OrderId", order.Id));
    conjunction.Add(Restrictions.Eq("LineItemId", lineitem.Id));

    disjunction.Add(conjunction);
}

var result = Session.CreateCriteria<SomeClass>().Add(disjunction).ToList();

How would I write the same type of query using the QueryOver syntax in nHibernate 3.x?

Best Answer

There are multiple overloaded of Add method at least in NH version 3.0.0.4000. One of them is having generic parameter that can be used for your case, for example:

disjuction.Add<TypeinWhichPrimaryKeyPropertyExists>(x => x.PrimaryKey == 1)

Related Solutions

C# – NHibernate using QueryOver with WHERE IN

I've found the solution!! :-)

var qOver = _HibSession.QueryOver<MyModel>(() => baseModel)
    .JoinAlias(() => baseModel.Submodels, () => subModels)
    .WhereRestrictionOn(() => subModels.ID).IsIn(IDsSubModels)
    .List<MyModel>();

NHibernate QueryOver Subquery

You need to create a dettached query containing the Id's and then use this sub query with the main query.

I have pasted an example here so you will need to replace the relevant bits with your class names etc.

First the set up (you can ignore this bit):-

public class TestDto {
  public long Id { get; set; }
  public string Name { get; set; }
}
...
TestDto dto = null;
var ids = new List<int> { 1,2,5,7 };

Now the dettached query:-

var idSubQuery = QueryOver.Of<CmsRegionContent>()
  .WhereRestrictionOn(w => w.Id).IsIn(ids)
  .Select(Projections.Distinct(Projections.Property<CmsPage>(s => s.Id)));

And the final bit is to put it all together:-

var query = Session.QueryOver<CmsPage>()
    .JoinQueryOver<CmsRegionContent>(l => l.CmsRegionContentList)
    .WithSubquery
    .WhereProperty(m => m.Id)
    .In(idSubQuery)
    .SelectList(list => list
                            .Select(p => p.Id).WithAlias(() => dto.Id)
                            .Select(p => p.PageName).WithAlias(() => dto.Name)
                )
                .TransformUsing(Transformers.AliasToBean(typeof(TestDto)));

var model = query.List<TestDto>();

This will create the following SQL:-

SELECT
     this_.Id as y0_,
     this_.PageName as y1_ 
FROM cmspage this_ inner join cmsregioncontent cmsregionc1_ 
  on this_.Id=cmsregionc1_.PageId 
WHERE cmsregionc1_.Id in (
    SELECT
         distinct this_0_.Id as y0_ 
    FROM cmsregioncontent this_0_ 
    WHERE this_0_.Id in (
        1 /* ?p0 */,
         2 /* ?p1 */,
         5 /* ?p2 */,
         7 /* ?p3 */)
    )

Hopefully you will be able to follow this with your class/property names.

Best Answer

Related Solutions

C# – NHibernate using QueryOver with WHERE IN

NHibernate QueryOver Subquery

Related Topic