IN operator with Linq or Create a SubQuery.

[Felipe Oriani]

Hello Guys, 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 cna 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=>xCategory).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! 

Thank you!

--

______________________________________
Felipe B. Oriani

felipeoriani.com.br felipe...@gmail.com @felipeoriani LinkedIn Facebook

"...Trabalhe quanto puder, tornando-se útil quanto possível..." , por André Luiz

[mysterd429]

Felipe,

I'd do something like .Where(p => idCategories.Contains(p.IdCategory)), but I find it is sometimes finicky about the type.  I use List<int> without a problem.

    Don

On Monday, July 16, 2012 10:50:09 PM UTC-4, Felipe Oriani wrote:

Hello Guys, 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 cna 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=>xCategory).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! 

Thank you!

--

______________________________________
Felipe B. Oriani

[Felipe Oriani]

Hi misterd429,

I could convert int[] ni List<int> without problem. THis way using .Contains(id) will generate a IN operator ?

Thank you!

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/Hqr2JRy-dmYJ.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--

______________________________________
Felipe B. Oriani

felipeoriani.com.br felipe...@gmail.com @felipeoriani LinkedIn Facebook

Contato: (19) 9611-8646 / (19) 3421-7850

[mysterd429]

Felipe,

I just tested a little bit, and I found that the int[] worked just fine.  The SQL that was generated was something like WHERE myId in (@p0, @p1, @p2), with @p0, @p1, and @p2 being set to whatever was in the array.  The number of parameters changes with the size of the array.

Cheers,

    Don

On Tuesday, July 17, 2012 9:52:37 AM UTC-4, Felipe Oriani wrote:

Hi misterd429,

I could convert int[] ni List<int> without problem. THis way using .Contains(id) will generate a IN operator ?

Thank you!

On Tue, Jul 17, 2012 at 10:34 AM, mysterd429 <> wrote:

Felipe,

I'd do something like .Where(p => idCategories.Contains(p.IdCategory)), but I find it is sometimes finicky about the type.  I use List<int> without a problem.

    Don

On Monday, July 16, 2012 10:50:09 PM UTC-4, Felipe Oriani wrote:

Hello Guys, 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 cna 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=>xCategory).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! 

Thank you!

--

______________________________________
Felipe B. Oriani

"...Trabalhe quanto puder, tornando-se útil quanto possível..." , por André Luiz

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/Hqr2JRy-dmYJ.
To post to this group, send email to nhu...@googlegroups.com.

To unsubscribe from this group, send email to nhusers+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--

______________________________________
Felipe B. Oriani

[mysterd429]

Felipe,

Sorry for the double reply.  The issue I had with the data types, just so you know, occurs when the collection that Contains is being called on is some more complex IEnumerable.

IEnumerable<Int32> someIntegers = Enumerable.Range(1, 100).Where(i => i % 2 == 0 || i % 5 == 0); // Just some arbitrary numbers

session.Query<MyEntityType>().Where(et => someIntegers.Contains(et.Id));

I get an error from NHibernate when running this code: "Failed to convert parameter value from a WhereEnumerableIterator`1 to a Int32"

Cheers,

    Don

[Alexander I. Zaytsev]

Hi,

What version of NH are you using?

2012/7/18 mysterd429 <don.lave...@gmail.com>

To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/3nhQ2HsR7XcJ.

To post to this group, send email to nhu...@googlegroups.com.

To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

[Felipe Oriani]

Hi guys, I'm using NHibernate 3.3.0 and I will show my entire problem with this query

I know how to do these queries:

// query 1 - get the List<int> or int[]

var idCategories = session.QueryOver<Category>()

   .Where(/* condiction here */)

   .And(/*another condition here*/)

   .And(/* another condition here too */)

   .Select(x => x.Id)

    .List<int>();

// query 2 - it's using Query<T>, 

// because I do not know how to use Fetch, FetchMany, ThenFetch on QueryOver, use operator IN to get from array

return session.Query<Product>()

                        .Fetch(x => x.Supplier)

                        .FetchMany(x => x.Collection).ThenFetch(x => x.PropertyOfCollection)

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

                        .OrderByDescendnig(x => x.Name)

                        .List<Product>();

It works, but this code does 2 hits on database, is there any way to use query 1 as a subquery on Where method of query 2 ?

Thank you

______________________________________
Felipe B. Oriani

felipeoriani.com.br felipe...@gmail.com @felipeoriani LinkedIn Facebook

[NeoDarque]

How about if you do it all with QueryOver, since I believe it's still not possible to do subqueries using the linq provider, but I could be mistaken...

So perhaps something like this:

var subQuery = QueryOver.Of<Category>()
                        .Where(c => /* condition here */ && /* another condition here */ && /* another condition here too */)
                        .Select(Projections.Id());

Product productAlias = null;
Supplier supplierAlias = null;
Order orderAlias = null;
OrderRow orderRowAlias = null;

var result = Session.QueryOver(() => productAlias)
                 .Left.JoinAlias(() => productAlias.Supplier, () => supplierAlias)
                 .Left.JoinAlias(() => productAlias.Order, () => orderAlias)
                 .Left.JoinAlias(() => orderAlias.OrderRow, () => orderRowAlias)
                 .WithSubquery.WhereProperty(() => productAlias.Category.Id).In(subQuery)
                 .OrderBy(() => productAlias.Name).Desc
                 .TransformUsing(Transformers.DistinctRootEntity)
                 .List();

[Felipe Oriani]

Hi NeoDarque, it works very fine, thank you so much man! Just one more question, look my final result (question in bold)

var subQuery = Session.QueryOver<Laudo>()

                .Where(x => /* conditions here */)

                .OrderBy(x => x.Numero).Desc

                .Select(Projections.Property<Laudo>(x => x.Numero)).Take(4);

            AnaliseInfo analiseInfo = null;

            Analise analise = null;

            CampoAnalise campoAnalise = null;

            CampoInfo campoInfo = null;

            var query = Session.QueryOver(() => analiseInfo)

                .Inner.JoinAlias(() => analiseInfo.Analise, () => analise)

                .Left.JoinAlias(() => analiseInfo.Campos, () => campoInfo)

                .Left.JoinAlias(() => campoInfo.Campo, () => campoAnalise)

                .WithSubquery.WhereProperty(x => x.NumeroLaudo).In((QueryOver<Laudo>) subQuery)

                .OrderBy(() => analiseInfo.DataAnalise).Desc

                .TransformUsing(Transformers.DistinctRootEntity);

I need only 4 records on the subQuery (4 numbers.. it is not the ID property) and if I try without .Take(4) method, it works fine but with it I got a exception saying that it is not possible to use limit in the IN operator (MySql Exception....). 

Another thing is, I need to cast on the .In(QueryOver<U> subQuery) method to QueryOver<U>? I'm saying it because the first query returns a IQueryOver<T, T>.

I prefer using QueryOver, I have using Linq because I'm begining with NHibernate and I'm learning! (Sorry for the code, it's pt-BR).

Thank you for your help.

 

--
You received this message because you are subscribed to the Google Groups "nhusers" group.

To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/v2l6mLTNlsQJ.

To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[NeoDarque]

Hi Felipe,

About the cast to QueryOver<Laudo>, no, you shouldn't need to do that, works fine anyway.

About the Take(4) though... odd, I just tried a similar thing here, and it works just fine, I am however working with Microsoft SQL Server. It could be a limitation with MySQL... Could you try doing a manual SQL query in the mysql console with a subquery containing a limiting parameter and see what it says?

[Felipe Oriani]

Hi NeoDarque,

Well, It's weird because it does not compile without the cast.

And my subquery does not work too, it's a limitation of mysql. I tried to execute in a mysql client and I got the same message when I got MySqlException :(

Well tank you anyway :)

I will do two commands on database without problem.

[]s

--

You received this message because you are subscribed to the Google Groups "nhusers" group.

To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/l5XSTo69quwJ.

To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Oskar Berggren]

2012/7/19 NeoDarque <ted.pa...@gmail.com>

How about if you do it all with QueryOver, since I believe it's still not possible to do subqueries using the linq provider, but I could be mistaken...

With 3.3.1 LINQ subqueries are much more capable.

/Oskar