UNION Support

[nfplee]

Hi, I have to search other multiple tables and union the results together. For example say I have the following:

 

var search = "Temp";

var query1 = session.Query<Page>().Where(p => p.Name.Contains(search)).Select(p => p.Name);

var query2 = session.Query<Category>().Where(c => c.Name.Contains(search)).Select(c => c.Name);

var results = query1.Union(query2).Take(5);

 

One solution is to execute each query individually and then do the union/concat in memory but this isn't efficient when a single query returns back thousands of results.

 

I've done a search online and realized the Union operator is not supported (https://nhibernate.jira.com/browse/NH-2710). I've done some extensions to the linq provider but nothing as complicated as this but I thought I would download the NHibernate source code and take a look. From my findings I see the linq provider uses the Remotion.Linq project. I'm not familiar with the project but I found the following blog post (https://www.re-motion.org/blogs/mix/2012/10/12/re-linq-subquery-boundaries-after-groupby-union-and-similar-operators/) which would indicate that the Union operator is supported.

 

I was wondering how hard it would be to enable it for NHibernate? If it's difficult then I guess I'll just scrap the idea and wait until one of you clever people implements it.

 

Thanks

 

Lee

[Gunnar Liljas]

Since it's not supported by HQL, it can't be supported by the Linq provider. 

Fetching all Pages and Categories if you only need 5 is of course a bit redundant.

/G

2013/10/10 nfplee <lee.t...@outlook.com>

--
 
---
You received this message because you are subscribed to the Google Groups "nhibernate-development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhibernate-develo...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Ricardo Peres]

I am not a committer, but IMHO, the problem, as I see it, is that it involves a lot of work: change the domain specific language (ANTLR definition) that represents HQL and implement a translation to SQL. This is not the same as fixing some bug.

RP

[Diego Mijelshon]

Actually, you don't have to retrieve all the records. You can Take(5) from each source (which probably has an OrderBy), then do client-side Union/Concat(), OrderBy() and Take(5) again.

It might be slightly less efficient than a union (or not, how about using future queries?), but it's certainly not a big deal.

Diego

--