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>();

Could you show the definition of `IDsSubModels`? – Gabe Moothart Mar 23 '11 at 17:21 — Gabe Moothart, Mar 23 '11 at 17:21

score 68 · Accepted Answer · edited Nov 13 '17 at 22:42

68

I've found the solution!! :-)

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

edited Nov 13 '17 at 22:42

Nick DeVore

9,423
3
37
41

answered Mar 24 '11 at 10:38

Faber

2,184
2
27
35

68

better in this way .WhereRestrictionOn(() => subModels.ID).IsIn(IDsSubModels) – Faber Mar 25 '11 at 17:01
1

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. – Tom B. Jan 11 '16 at 14:00
1

what if the the "list" from the "IN" query is a list of objects? – Juran Jul 14 '16 at 06:44
1

Use IsIn for a simpler syntax. – Timothy Gonzalez Jun 12 '17 at 19:09

score 52 · Answer 2 · edited Oct 31 '11 at 10:02

52

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

edited Oct 31 '11 at 10:02

LPL

16,517
6
48
94

answered Jul 14 '11 at 15:40

Artem G

1,867
1
21
27

3

This would filter by MyModel.ID, not by MyModel.Submodels.ID as @Faber wanted, right? – kroimon Oct 09 '12 at 22:27
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 – Artem G Apr 27 '17 at 09:55

Arnold · Answer 3 · 2011-08-12T00:05:12.063

13

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

edited Aug 12 '11 at 00:05

answered Aug 11 '11 at 23:28

Arnold

131
1
3

1

Yes, you're right! I've used your syntax, but I forgot to publish here ;) – Faber Aug 24 '11 at 07:14

NHibernate using QueryOver with WHERE IN

3 Answers3

Linked