Where with IN operator using Linq on NHibernate

Question

I have a linq query with NHibernate using Session.Query<T> method and I in this query I Fetch some complex properties and collection properties. I would like to know, how can I add an condition with IN operator from an int[]? Look my code:

public IEnumerable<Product> GetProducts(int[] idCategories) 
{
    // how to add IN condition here or a subquery 
    var query = Session.Query<Product>()
                   .Where(?????)
                   .Fetch(x=>x.Category)
                   .FetchMany(x=>x.Status).ThenFetch(x=>x.Item);

    return query.ToList();
}

I have another method doing a query to get this int[] and I would like to apply it here, or if is there any way to add this subquery on the IN operator, I really appreciate!

OBS: I could convert int[] to List<int> if its necessary.

Edits

I got this int[] by a query like:

return session.Query<Category>.Where(...).Select(x => x.Id).ToArray();

My second question is, how could I add this query as a subquery to filter by category?

Thank you!

score 7 · Accepted Answer · answered Jul 17 '12 at 14:14

7

You don't really need the IN operator. You can just do it like this:

.Where(x => idCategories.Contains(x.Category))

Note: Contains is an extension method. You need to ensure that you have a using statement for System.Linq, but you probably already have it.

answered Jul 17 '12 at 14:14

FishBasketGordo

22,904
4
58
91

I will try this. I have just one more question. How could i do a subquery that return this int[] and apply on the Constains method? Thanks – Felipe Oriani Jul 17 '12 at 14:33
I don't understand your second question. Why do you need to do a subquery to get the `int[]`? Isn't that what you pass in as `idCategories`? – FishBasketGordo Jul 17 '12 at 16:42
I add some edits on the main question. Take a look. If you coudl help me I really appretiate man! Thanks – Felipe Oriani Jul 17 '12 at 17:43
Sorry, I'm afraid we've reached the boundary of my NHibernate knowledge. I would suggest posting the as-yet-unanswered second part of this question as its own question if you still need it. – FishBasketGordo Jul 18 '12 at 17:05
Thank you man, you have help me with this awser. I'll post another question later with more detail about it. – Felipe Oriani Jul 18 '12 at 17:47

score 2 · Answer 2 · answered Jul 17 '12 at 22:17

2

Take a look on Restrictions, for example

var query = Session.Query<Product>()
     .Where(Restrictions.In(Projections.Property<Product>x=>x.Id),idCategories))
     .Fetch(x=>x.Category)
     .FetchMany(x=>x.Status).ThenFetch(x=>x.Item);

answered Jul 17 '12 at 22:17

GSerjo

4,725
1
36
55

You can't mix `Session.Query<>()` with `NHibernate.Criterion.Subqueries`. I think you were referring to `Session.QueryOver<>()`, which the original question was not about. – Nathan Aug 13 '20 at 04:27

score 1 · Answer 3 · edited May 23 '17 at 10:34

1

About sub query, you need Subqueries class

var query = Session.Query<Product>()
               .Where(Subqueries.WhereProperty<Product>(x=>x.Id)...)
               .Fetch(x=>x.Category)
               .FetchMany(x=>x.Status).ThenFetch(x=>x.Item);

for more details please look here How to do subqueries in nhibernate?

edited May 23 '17 at 10:34

Community

1
1

answered Jul 17 '12 at 22:25

GSerjo

4,725
1
36
55

You can't mix `Session.Query<>()` with `NHibernate.Criterion.Subqueries`. I think you were referring to `Session.QueryOver<>()`, which the original question was not about. – Nathan Aug 13 '20 at 04:27

Where with IN operator using Linq on NHibernate

Edits

3 Answers3