IUserType with LINQ in where clause implementing BuildHql

[juanita]

I have implemented support for a custom user type in my domain model,
but struggle with supporting it in a LINQ where clause.

For the sake of simplicity, say that the database contains a COLOR
column where a color code is stored as RRGGBB in hex (e.g. FF00FF).
This column is mapped to a custom user type implementing IUserType
with three properties Red,Green,Blue, all integers.
Selecting, updating and inserting data all works fine. There are
plenty of examples for doing this.

However, what I am struggling with is implementing proper support for
using this type as part of Linq in a where clause. When trying to
lookup an item based on its color, then the linq query would need to
be something like this:

session.Query<Item>.Where( i => i.Color.Red == 255 && i.Color.Green==0
&& i.Color.Blue==255)

With just the IUserType implementation, the LINQ query will throw an
error. I have researched the matter and found recommendations to also
implement a HqlGenerator, specifically its BuildHql method. Some more
work is needed to get it registered and activated through subclassing
DefaultLinqToHqlGeneratorsRegistry and adding it as a property to the
NH configuration.

Here is what I have implemented so far:
----------------------
public class ColorHqlGenerator : BaseHqlGeneratorForProperty
{
public ColorHqlGenerator()
{
SupportedProperties = new[]
{
ReflectionHelper.GetProperty((Color x) => x.Red),
ReflectionHelper.GetProperty((Color x) => x.Green),
ReflectionHelper.GetProperty((Color x) => x.Blue)
};
}

public override HqlTreeNode BuildHql(MemberInfo member,
Expression expression, HqlTreeBuilder treeBuilder,
IHqlExpressionVisitor visitor)
{
.... now what .... ???
}
}
--------------------------
The issue is with implementing the BuildHql method. I have pretty much
no idea what would need to be done here to have NHibernate finally
execute a SQL statement with ... where color='FF00FF'.

Note that merging the three where parts for the individual properties
(red, green, blue) into a single SQL where is highly desirable so that
the database can make use of an index on the column.
I understand that an edge case would be if the query had values only
for 2 or 1 of the color constituents. While this will not actually
happen in my case, an acceptable solution would be to assume the
missing constituents as 0.

What I am confused about in the few other examples I have seen is that
the HqlGenerator seems to make no use of the IUserType's methods to
convert between database and object representation at all.

Any help would be highly appreciated.
J.-

[Oskar Berggren]

2012/2/8 juanita <juanita.v...@googlemail.com>:

> I have implemented support for a custom user type in my domain model,
> but struggle with supporting it in a LINQ where clause.
>
> For the sake of simplicity, say that the database contains a COLOR
> column where a color code is stored as RRGGBB in hex (e.g. FF00FF).
> This column is mapped to a custom user type implementing IUserType
> with three properties Red,Green,Blue, all integers.
> Selecting, updating and inserting data all works fine. There are
> plenty of examples for doing this.
>
> However, what I am struggling with is implementing proper support for
> using this type as part of Linq in a where clause. When trying to
> lookup an item based on its color, then the linq query would need to
> be something like this:
>
> session.Query<Item>.Where( i => i.Color.Red == 255 && i.Color.Green==0
> && i.Color.Blue==255)
>

[...]

>
> Note that merging the three where parts for the individual properties
> (red, green, blue) into a single SQL where is highly desirable so that

Given your last statement above, your query should be:

session.Query<Item>.Where( i => i.Color == MyColor.For(255, 255, 255))

This should make NHibernate call your IUserType to convert the
parameter MyColor instance to whatever format is used in the database.

/Oskar

[juanita]

> Given your last statement above, your query should be:
> session.Query<Item>.Where( i => i.Color == MyColor.For(255, 255, 255))

While this appears to be correct, unfortunately, I have no control
over how the query is created. This is a WCF data service request and
the where clause ends up as it is with no option to change that.

The question therefore remains: what can I do as part of the BuildHql
method or otherwise to have NHibernate generate the desired SQL where
clause?
J.-

[juanita]

On 8 Feb., 23:09, Oskar Berggren <oskar.bergg...@gmail.com> wrote:
> 2012/2/8 juanita <juanita.vollmer...@googlemail.com>:

>
> Given your last statement above, your query should be:
> session.Query<Item>.Where( i => i.Color == MyColor.For(255, 255, 255))
>

That would be desirable, but unfortunately I have to influence on how
the query is constructed. This is actually a WCF data service call and
the WCF library constructs the query. There does not seem to be any
way to change this.

So the question remains, is there anything I can do from within the
BuildHql method that would make NHibernate generate the SQL as
desired?
Is there any further information on HqlGenerator and the BuildHql
method? I have difficulties finding examples or documentation other
than the raw API doc and a few articles on how to add custom methods.

[David Schmitt]

I've got no direct experience with the BuildHql() method, but there's
really only one thing this method needs to do:

1. traverse the incoming Expression
2. find all parts referencing a Color
3. collect this data to build (one or more) comparisons
4. pass this on to the treeBuilder
5. probably modify the expression to not contain the Color references

If the incoming queries have a fixed form, you can probably take many
shortcuts over parsing a generic query.

Methodically, I'd recommend getting the NHibernate source for the
release you're working on, and break into the BuildHql method with the
debugger of your choice and poke around in the interfaces and data
structures you're receiving.

Good Hunting, David