Reference parent table alias in Fluent NHibernate

Question

Is it possible to reference or rename an entity's table alias in a Fluent NHibernate Formula? I can usually use "this_" but that unfortunately does not seem to be consistent based on the number of other aliases NHibernate generates.

this.Map(x => x.IsLocked).Formula("CASE WHEN (SELECT COUNT(*) FROM dbo.Child c WHERE c.InboundDate >= BeginDate AND c.InboundDate < EndDate) > 0 THEN 1 ELSE 0 END");

This gets me by but I run into trouble when I need to match of identically named fields, (like "ID.")

this.Map(x => x.IsLocked).Formula("CASE WHEN (SELECT COUNT(*) FROM dbo.Child c WHERE c.ID = ID) > 0 THEN 1 ELSE 0 END");

Here, because ID is a column in c, c.ID = ID will always evaluate to true. In most cases, I can change this to c.ID = this_.ID to get by, but in some of my entities, NHibernate will not use this_ as an alias, but something like parent1_.

Answer 1

i do not understand the question completly. What is the problem/error with Formula presented?

Note: NHibernate will prepend all column names in the Formula without an alias with the alias of the table of the Entity containing the Forumula.

Note2: it is better to wrap the whole formula in parentesis (CASE ... END)

Update:

c.ID = ID will always evaluate to true

no it should not because the second ID has no prefix and will be given the prefix of the entitytable the formula is contained in. try log the sql generated to see.

example:

Map(u => u.RolesCount).Formula("(SELECT COUNT(*) FROM roles r WHERE r.user_id = id)");

//results in

SELECT
    user0_.Id as Id0_0_,
    (SELECT
        COUNT(*)
    FROM
        roles r
    WHERE
        r.user_id = user0_.id) as formula0_0_
FROM
    "User" user0_
WHERE
    user0_.Id=@p0;