Linq to NH progress (or lack thereof)

[Steve]

Hi Chaps,

The good news is that I'm still here - we're past the initial 3 months
that iMeta originally provided, but I'm glad to say that the powers
that be are happy for me to continue to see this completed. The bad
news is that the last 3 or 4 weeks have been insane with other stuff
(particularly an office move, which we completed today), so things
haven't progressed much at all from my last update. I'm about to head
off on holiday for a couple of weeks, but when I return I will be back
onto my ASTs in anger, and hope to get things progressing along again.

In the meantime, I need some help. Consider the following query:

var query = from p in db.Products
join o in db.OrderLines.Where(ol => ol.UnitPrice > 5) on p.Id
equals o.Product.Id
where p.Id == o.Product.Id
select p;

It might be a little contrived, but don't let that get in the way -
the general form is what's interesting. If I were mapping this to
SQL, I'd create something like this:

select *
from Products p
join
(
select * from OrderLines where UnitPrice > 5
) o on p.Id = o.ProductId

Again, don't take too much notice of the *exact* sql, I know it isn't
correct but it's the form that's important. In particular, the nested
select to get the subset of OrderLines that the query requests. In
SQL (for most databases, at least) this is fine, but it isn't
supported in HQL. In HQL (afaik), we can't do nesting within the
select or from / join clauses.

If we can't do nesting, then mapping arbritrary Linq expressions to
HQL is, I think, going to be a challenge. So there are three things
I'd like to ask:

* Am I wrong about the nested selects in HQL? I think not, since I've
just ported the parser and it doesn't support them :)

* If we can't do nested selects, does anyone fancy a stab at a general
purpose algorithm for mapping nested LINQ queries to "flattened" HQL
ones. I've not thought of a good one yet, and I've a gut feel that
it's going to be hard. Note that things like re-linq don't help here
- the AST that it creates still has nesting in place (I think)

* How much effort would be involved in adding nested selects into NH?
If this were in any way possible, it would get my vote - it both makes
the LINQ work easier, and makes HQL more powerful.

Whilst on holiday I'm only going to be online sporadically, so don't
be surprised if any questions to me take a while to answer! Looking
forward to hearing your responses...

Cheers,

Steve

[Fabio Maulo]

In HQL should look like

select distinct ol.Product from OrderLine ol where ol.UnitPrice > 5

In LINQ (perhaps)

var query = from ol in db.OrderLines where ol.UnitPrice > 5 select ol.Product

2009/5/27 Steve <srst...@gmail.com>

--
Fabio Maulo

[Steve Strong]

If the LINQ query was written the way you wrote it, then sure, generating the correct HQL would be easy.  But we can't stop devs from writing it the way I did, and we should probably be able to handle it (or our claim to be a "full linq provider" will be a bit weak).  Plus, I'm quite sure I could write LINQ queries that did need the nested form to acheive the intention.

[Steve Strong]

BTW, H3 has an open issue on the same thing, so maybe they'll add support in the next couple of weeks, and I can just port it over :)

[Fabio Maulo]

2009/5/27 Steve Strong <srst...@gmail.com>

If the LINQ query was written the way you wrote it, then sure, generating the correct HQL would be easy.  But we can't stop devs from writing it the way I did, and we should probably be able to handle it (or our claim to be a "full linq provider" will be a bit weak).  Plus, I'm quite sure I could write LINQ queries that did need the nested form to acheive the intention.

Steve,

I think we should support something when we have a real use case to support it.

As you know, so far, HQL is not supporting sub-queries in the from clauses or better it is not supporting not-related subqueries.

We can study a solution but, please, not stop your work only because this problem.

In this case the first step is support all queries are supported by NHLINQ in contrib.

After have the first target solved we can think in something else, no?

I have the suspect that HQL is supporting a lot of use-case for entities queries.

Take a look to your applications and try to find a query not supported in HQL...

Don't wait few weeks.... that issue in H3 is one year old ;) 

Be "Strong" LOL

--
Fabio Maulo

[Steve Strong]

LOL!

right now, what I've been doing is exactly that - porting over the tests from NHContrib.Linq to get parity with that provider, which has progressed quite well.  It's just that this sub-select thing keeps bothering me - I'm slightly concerned that solving it may require a completely different approach to the one I'm currently taking.  I wouldn't quite say that it's keeping me awake at night, but it still troubles me :)

[Fabio Maulo]

Where ?

2009/5/27 Steve Strong <srst...@gmail.com>

--
Fabio Maulo

[Steve Strong]

"Where" is one of the things that I'm afraid hasn't happened due to the chaos of the last couple of weeks - the code is still just sat on my laptop.  I promise that I'll get that sorted as soon as I get back :) 

Sent from my iPhone

[Fabio Maulo]

2009/5/27 Steve Strong <srst...@gmail.com>

"Where" is one of the things that I'm afraid hasn't happened due to the chaos of the last couple of weeks - the code is still just sat on my laptop.  I promise that I'll get that sorted as soon as I get back :) 

In an "Buenos Aires" expression:

Pedazo de un animal!!!

En el buen sentido, obvio.

--
Fabio Maulo

[Dario Quintana]

You can open a branch I think

--
Dario Quintana
http://darioquintana.com.ar