nhibernate queryover join alias with a where-or clause

Question

I'll just post the code:

public IList<Dish> GetByNameDishTypes(IEnumerable<string> names,
    IEnumerable<string> dishTypes)
{
    // return dishes (ie food) whose name is in the names enumerable,
    // or whose type is in the dish types enumerables
    // (this would be 'breakfast', 'dinner') ..

    var namesArr = names != null ? names.ToArray() : new string[0];
    var dishTypesArr = dishTypes != null ? dishTypes.ToArray() : new string[0];

    var criteria = this.Session.CreateCriteria<Dish>();
    criteria.CreateAlias("DishType", "dt");
    criteria.Add(
        Restrictions.Or
        (
            Restrictions.In("Name", namesArr),
            Restrictions.In("dt.Name", dishTypesArr)
        ));

    return criteria.List<Dish>();

    /* get this to work?
    Dish d = null;
    DishType dt = null;
    return this.Session.QueryOver<Dish>(() => d)
        .JoinAlias(() => d.DishType, () => dt)
        .Where(
            Restrictions.Or
            (
                Restrictions.In(d.Name, namesArr),
                Restrictions.In(dt.Name, dishTypesArr)
            ))
        .List<Dish>(); */
}

So, I'd like to know how to do this with QueryOver. This QueryOver code I posted throws a null reference exception. The criteria code works.

Thank you :)

Andrew Whitaker · Accepted Answer · 2012-08-19T23:21:20.767

23

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 */))

edited Aug 19 '12 at 23:21

answered Aug 19 '12 at 23:15

Andrew Whitaker

124,656
32
289
307

Trying to do something similar. Is there a particular `using` I have to include to get the lambdas to work properly? I get errors for the `() => d` and `() => dt` stuff. – Sam Aug 21 '13 at 14:32
Cannot convert lambda expression to type 'string' because it is not a delagate type. I have `using System.Linq;` and `using System.Linq.Expressions;` included at the top of the file. The target for the application is .Net 4. – Sam Aug 21 '13 at 16:53
@Sam: It might be best to open a new question rather than try to figure it out in the comments here – Andrew Whitaker Aug 21 '13 at 18:27
@Andrew, agreed. Just commented here because you obviously had it working, so it could have potentially been something simple. – Sam Aug 21 '13 at 18:39

nhibernate queryover join alias with a where-or clause

1 Answers1