Description
Jerome Haltom created an issue — 7th August 2008, 8:04:22:
Expression.Sql("") only allows use of the root alias using {alias}. It should allow usage of any subcriteria alias. Attached is a patch which implements this.
The patch is non-ideal in that it pokes a whole through to CriteriaImpl, which it probably should not do. I'm seeking advice on the best way to obtain the required information.
Fabio Maulo added a comment — 7th August 2008, 9:33:20:
This is part of the documentation of Expression class.
/// This class is semi-deprecated. Use .For me this issue is candidate to "won't fix".
Jerome Haltom added a comment — 18th August 2008, 9:22:31:
Hmm. I meant to set it as effecting 1.2.1. I can't seem to figure out how to do that though.
Jesse added a comment — 22nd October 2008, 18:17:55:
Restrictions don't contain an operation to Create a Sql filter except for projections. To add a where clause filter we still have to use Expression.Sql. I have attached a patch which uses a regular expression to find the aliases and properly apply the table alias used in the query. This makes it so that you can have multiple aliases like this
"dbo.MinDate({alias}.dtEndDate, {type}.dtEndDate) > ?"
where {alias} still refers to the root entity and {type} refers to another alias in the criteria.
Jesse added a comment — 22nd October 2008, 18:18:44:
Adding Patch as mentioned in previous comment
luis cornejo added a comment — 28th October 2008, 23:30:23:
I second this, the aliases references should be the same as if one id using createSQL
luis cornejo added a comment — 28th October 2008, 23:34:28:
Also,
if it won't be implemented, can anyone suggest a way to get the right alias of subcriterias in there for custom sql use with criterias?
lcorneliussen added a comment — 11th November 2009, 5:17:32:
+1 Especially when working with legacy-databases it is often necessary to create custom sql-statements. It would be great if we could reuse the aliases there.
Boris Drajer added a comment — 9th April 2012, 10:46:54:
+1. This is important as a last-resort solution for all database-specific features that aren't supported in another way. Supporting a single alias here means supporting only the most trivial cases.
Note that the previous patches don't seem to resolve certain types of subqueries - if I'm not mistaken, it's the detached queries that don't work. For example, a query like
mainQuery.Where(Subqueries.Exists(detachedCriteria.Where(Expression.Sql(sql_expression)))
will fail to find mainQuery aliases referenced by sql_expression. Looking through the source, I have been able to find the way to traverse from the inner expression to the outer, but it involved a bit of reflection and a casting of interfaces to real types, so it's a bit of a hack. I'm including a patch as a proof of concept, but it needs to be worked on by someone who knows more about NH.
Josh Berke added a comment — 16th January 2013, 15:11:08:
I ran into this issue as well...aliases should work the same way across the system.