NHibernate using QueryOver with WHERE IN

Question

I would create a QueryOver like this

SELECT *
FROM Table
WHERE Field IN (1,2,3,4,5)

I've tried with Contains method but I've encountered the Exception

"System.Exception: Unrecognised method call: System.String:Boolean Contains(System.String)"

Here my code

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

Prohibitionist

Could you show the definition of IDsSubModels? – Dorr

Answer 1

I've found the solution!! :-)

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

Prohibitionist

better in this way .WhereRestrictionOn(() => subModels.ID).IsIn(IDsSubModels) – Prohibitionist

Faber, your comment should be placed in your answer since .Where(Restrictions.In(subModels.ID,IDsSubModels)) won't work if i try it. your comment does work. – Cramoisy

what if the the "list" from the "IN" query is a list of objects? – Chaparral

Use IsIn for a simpler syntax. – Lupita

Answer 2

You can try something like this:

// if IDsSubModels - array of IDs
var qOver = _HibSession.QueryOver<MyModel>() 
                       .Where(x => x.ID.IsIn(IDsSubModels))

You don't need a join in this situation

Gastrotrich

This would filter by MyModel.ID, not by MyModel.Submodels.ID as @Prohibitionist wanted, right? – Wheaten

x here is the instance of MyModel class, same as instance\record of Table in your SQL request. SELECT * FROM Table WHERE Field IN (1,2,3,4,5) And x.ID in (1,2,3) is same as Table.Field in (1,2,3). 1,2,3 is IDsSubModels – Gastrotrich

Answer 3

This works and is more elegant

var Strings = new List<string> { "string1", "string2" };

var value = _currentSession
.QueryOver<T>()
.Where(x => x.TProperty == value)
.And(Restrictions.On<T>(y=>y.TProperty).IsIn(Strings))
.OrderBy(x => x.TProperty).Desc.SingleOrDefault();

where T is a Class and TProperty is a property of T

Acacia

Yes, you're right! I've used your syntax, but I forgot to publish here ;) – Prohibitionist