FluentNhibernate Formula mapping with parameters from joined table

Question

I have a problem with FluentNhibernate Formula mapping. I need to use a column from joined table in the formula.

Problem is demonstrated on the following three tables: City, Person, Address

class Person {
    int PersonId { get; set; }

    int AddressId { get; set; }  

    Address PersonAddress { get; set; }

    string CityName { get; set; }
}

class Address {       
    int AddressId { get; set; }

    string Street { get; set; }
}

class AddressMap<Address> { 
    Id(x => x.AddressId, "ADDRESS_ID");

    Map(x => x.Street, "STREET");
}


class PersonMap<Person> {
    Id(x => x.Id, "PERSON_ID");

    References(x => x.PersonAddress).Column("ADDRESS_ID);

    Map(x => x.CityName).Formula("select Name from City c where c.street = STREET"); 
    // Doesn't work, STREET is a part of the joined table !
}

Any idea how to write the formula mapping properly? When I rewrite the mapping using the values NHibernate will generate, everything will work, nevertheless, this is pretty dirty solution:

Map(x => x.CityName).Formula("select Name from City c where c.street = address1_.STREET"); 
// Works !!

Would appreciate any help!

score 1 · Answer 1 · answered May 10 '14 at 14:24

There is no direct way in NHibernate how to support alias of another joined table. Why?

because that joined table simply does not have to be part of the join

Think about lazy loaded Address- i.e. not part of the SELECT, or some projections without Address table.

Formula is intended as a smart or smarter way, how to access data in the current table. Or how to create some indpendent subselect, subquery.. and passed the current row id, as a reference filter.

E.g. here, in Ayende's NHibernate property Mapping, we can see:

<property name="CountOfPosts"
    formula="(select count(*) from Posts where Posts.Id = Id)"/>

Generating SELECT like this:

SELECT ...
       // the injected 'Id' is from current table
       (select count(*) from Posts where Posts.Id = this_.Id) 
FROM [MainTable] this_ // the alias of current table

Suggestion: The City or city name could be simply another reference (if not directly the string property of the Address). It will allow us to work with it very easily (select, projections, filtering, order by) and we won't be dependent on some "hidden, hard coded" mapping. It will be clean model:

Person.Address.City.Name

score 1 · Answer 2 · answered Sep 21 '18 at 11:38

Most of general tips provided by Radim Köhler are true, but regarding the original problem of formulating a formula - NH (at least >=5, I don't know how's that with older ones) should be able to cover that case.

First of all, your attempt would fail, because in formulas any column names referring to anything other than the current main entity must be prefixed with aliases. Otherwise, NH will assume that the column name refers to the current main entity.

For example, your original attempt was:

Map(x => x.CityName).Formula("select Name from City c where c.street = STREET"); 
                                     ^ fault        ^CEE    ^CEE       ^fault

Note how you added c prefix to street to disambiguate which street is from City and which one isn't. NHibernate will detect such prefixes and will assume all columns with prefixes are bound to tables specified in the sql text. So, c.street will be assumed to be not important. However, STREET and Name don't have prefixes. NH will assume that they come from the main entity, the Person that you are defining mapping for. That may yield funny results sometimes, but more likely, there's no Name nor Street columns on Person table and you'll get an error from rdbms.

To fix that, you should have something like:

Map(x => x.CityName).Formula("select c.Name from City c where c.street = a.STREET");
                                     ^CEE                                ^AYE

Of course, now another error will say that prefix a is not known. Sure, we don't have it defined anywhere, since we have PERSON and CITY and the intermediate ADDRESS is totally ommitted in the sql query.

Now, take a look at this:

Map(x => x.CityName)
    .Formula(@"
        select c.Name
        from City c
        where c.street =
        (    select a.Street
             from Address a
             where a.address_id = address_id
        )");                      ^
                                  ^ no prefix!

Since inner address_id has no prefix, NH will assume it is from Person. Great. There are no other unprefixed columns so all other columns are ignored and assumed to be bound to scopes defined in the SQL text itself.

Thus, the inner subquery will select the Address basing on Person.AddressID, and pick the street from that. Hopefully it's just one street since we match addresses by ID. After finding the street the outer subquery will use it to match a City.

We also could write the formula as

        select c.Name
        from Address a
        inner join City c no c.street = a.street
        where a.address_id = address_id

with the same effect and probably different performance.

One thing to note, when you write a subquery Formula or when you write where foo = (select x from...) in subquery, you MUST ensure that the subquery always returns zero or one result. Not two or more. Many RDBMSes treat such thing as an error. In case of this query, the same street name may occur in multiple Cities so you have high chance of failure just because you match address to City by Street.

FluentNhibernate Formula mapping with parameters from joined table

2 Answers2